在VC程序中ADO连接 SQL SERVER数据库如何进行事务处理

Question

在VC程序中ADO连接 SQL SERVER数据库如何进行事务处理
ADO连接也可以像ODBC连接一样使用BeginTran /rollback/Commit tran.不用存储过程.

Answer 1

//Section 1
// BeginBeginTransCpp
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" \
    no_namespace rename("EOF", "EndOfFile")#include <stdio.h>
#include <ole2.h>
#include <conio.h>
#include <assert.h>
#include <malloc.h>
#include "BeginTransX.h"inline void TESTHR(HRESULT x) {if FAILED(x) _com_issue_error(x);};
void BeginTransX(void);
void PrintProviderError(_ConnectionPtr pConnection);void main()
{
    if(FAILED(::CoInitialize(NULL)))
      return;    BeginTransX();    //Wait here for user to see the output.
    printf("\nPress any key to continue...");
    getch();    ::CoUninitialize();
}///////////////////////////////////////////////////////////
//                                                       //
//      BeginTransX Function                             //
//                                                       //
///////////////////////////////////////////////////////////void BeginTransX()
{
    // Define ADO object pointers.
    // Initialize pointers on define.
    // These are in the ADODB::  namespace.
    _RecordsetPtr rstTitles = NULL;
    _ConnectionPtr pConnection = NULL;    //Define Other Variables
    HRESULT hr = S_OK;  
    IADORecordBinding   *picRs = NULL;  //Interface Pointer declared...
    CTitlesRs titlrs;
    _bstr_t strTitle; 
    _bstr_t strMessage;
    LPSTR p_TempStr = NULL;
    char chKey;
    int i = 0;    try 
    {
        // open connection.
        _bstr_t strCnn("Provider=sqloledb;Data Source=MyServer;"
            "Initial Catalog=pubs;User Id=sa;Password=;");        TESTHR(pConnection.CreateInstance(__uuidof(Connection)));        TESTHR(pConnection->Open(strCnn,"","",adConnectUnspecified));        rstTitles.CreateInstance(__uuidof(Recordset));          rstTitles->CursorType = adOpenDynamic;
        rstTitles->LockType = adLockPessimistic;        // open Titles table
        TESTHR(rstTitles->Open("titles",
            _variant_t((IDispatch*)pConnection,true),
            adOpenDynamic, adLockPessimistic,adCmdTable));        rstTitles->MoveFirst();
        pConnection->BeginTrans();        //Open an IADORecordBinding interface pointer which 
        //we'll use for Binding Recordset to a class    
        TESTHR(rstTitles->QueryInterface(
            __uuidof(IADORecordBinding), (LPVOID*)&picRs));

Answer 2

//Section 2        //Bind the Recordset to a C++ Class here    
        TESTHR(picRs->BindToRecordset(&titlrs));        // Loop through recordset and ask user if he wants
        // to change the type for a specified title.
          // Allocate memory to p_TempStr string pointer.
        p_TempStr = (LPSTR) malloc(sizeof(titlrs.m_szT_type));        // Check for null string.
        assert(p_TempStr != NULL);
        while (VARIANT_FALSE == rstTitles->EndOfFile)
        {
            // Remove blank string 
            strcpy(p_TempStr,strtok(titlrs.m_szT_type," "));            // Compare type with psychology
            if (!strcmp(p_TempStr,"psychology")) 
            { 
                strTitle = titlrs.m_szT_title;
                strMessage = "Title: " + strTitle + 
                        "\n Change type to Self help?(y/n)";                // Change the title for specified employee
                printf("%s\n",(LPCSTR)strMessage);
                do
                {
                    chKey = getch();
                }while(chKey != 'y' && chKey !='n');
                if(chKey == 'y')
                {
                    // Copy "self_help" title field
                    strcpy(titlrs.m_szT_type,"self_help");
                    picRs->Update(&titlrs);
                }
            }
            rstTitles->MoveNext();
        }
        // Ask if the User wants to commit to all the 
        //Changes made above
        printf("\n\n Save all changes(y/n)?");
        do
        {
            chKey = getch();
        }while(chKey != 'y' && chKey !='n');
    
        if(chKey == 'y')
            
            // Save the changes to the title table
            pConnection->CommitTrans();
        else
            // Unsave the changes to the title table
            pConnection->RollbackTrans();        // Print current data in recordset.
        rstTitles->Requery(0);        // Move to the first record of the title table
        rstTitles->MoveFirst();
        printf("\n\nPress any key to continue...");
        getch();        // Clear the screen for the next display   
        system("cls");         // Open IADORecordBinding interface pointer again 
        // for binding Recordset to a class.
        TESTHR(rstTitles->QueryInterface(
            __uuidof(IADORecordBinding),
            (LPVOID*)&picRs));        // Rebind the Recordset to a C++ Class.
        TESTHR(picRs->BindToRecordset(&titlrs));        while (!rstTitles->EndOfFile)
        {
            i= i+1;
            if (i % 23 == 0)
            {
                printf("\nPress any key to continue...");
                getch();                //Clear the screen for the next display   
                system("cls"); 
            }
            printf("%s -  %s\n",titlrs.m_szT_title,titlrs.m_szT_type);
            rstTitles->MoveNext();
        }
        // Restore original data becasue this is a demonstration.
        rstTitles->MoveFirst();        while (VARIANT_FALSE == rstTitles->EndOfFile)
        {
            strcpy(p_TempStr,titlrs.m_szT_type);
            p_TempStr = strtok(p_TempStr," ");            if (!strcmp(p_TempStr,"self_help")) 
            {
                strcpy(titlrs.m_szT_type,"psychology");
                picRs->Update(&titlrs);           
            }

Answer 3

//Section 3            rstTitles->MoveNext();  
        }
        // Deallocate the memory
            free(p_TempStr);        // Clean up objects before exit.
        rstTitles->Close();
        pConnection->Close();        //Release IADORecordset Interface    
        if (picRs)
            picRs->Release();
    }
    catch(_com_error &e)
    {
        // Notify the user of errors if any.
        _bstr_t bstrSource(e.Source());
        _bstr_t bstrDescription(e.Description());        PrintProviderError(pConnection);        printf("Source : %s\n",(LPCSTR)bstrSource);
        printf("Description : %s\n",(LPCSTR)bstrDescription);
    }
};///////////////////////////////////////////////////////////
//                                                       //
//      PrintProviderError Function                      //
//                                                       //
///////////////////////////////////////////////////////////void PrintProviderError(_ConnectionPtr pConnection)
{
    // Print Provider Errors from Connection object.
    // pErr is a record object in the Connection's Error collection.
    ErrorPtr  pErr    = NULL;
    long      nCount  = 0;
    long      i       = 0;    if( (pConnection->Errors->Count) > 0)
    {
        nCount = pConnection->Errors->Count;
        // Collection ranges from 0 to nCount -1.
        for(i = 0; i < nCount; i++)
        {
            pErr = pConnection->Errors->GetItem(i);
            printf("Error number: %x\t%s\n", pErr->Number,(LPCSTR) pErr->Description);
        }
    }
}
// EndBeginTransCppBeginTransX.h:// BeginBeginTransH
#include "icrsint.h"//This Class extracts only title and type 
class CTitlesRs : public CADORecordBinding
{
BEGIN_ADO_BINDING(CTitlesRs)
    //Column title is the 2nd field in the recordset  
    ADO_VARIABLE_LENGTH_ENTRY2(2, adVarChar, m_szT_title, 
         sizeof(m_szT_title), lT_titleStatus, FALSE)    //Column type is the 3rd field in the recordset  
    ADO_VARIABLE_LENGTH_ENTRY2(3, adVarChar, m_szT_type, 
         sizeof(m_szT_type), lT_typeStatus, TRUE)
END_ADO_BINDING()public:
   CHAR   m_szT_title[150];
   ULONG  lT_titleStatus;
   CHAR   m_szT_type[40];
   ULONG  lT_typeStatus;
};
// EndBeginTransH
//          -- end --