ADO存取Access数据库[C++数据库操作]

    C++对数据库的操作有多种方式，这里笔者抛砖引玉，以ADO存取Access数据库的例子来说明在C++是如何进行数据库编程的。
    ADO操纵数据库主要用到三个智能指针：
    _ConnectionPtr：    用于连接数据库
    _RecordsetPtr:      用于操纵结果集
    _CommandPtr:        用于执行SQL命令

下面用一个完整的例子来说明如何使用这三个智能指针。

首先建立一个Access数据库，名字为testdb.mdb，里面只有一个表txl，表里面有3个字段：
ID : 自动增长
Name: text类型，长度50
QQ: text类型， 长度50

下面是完整的C++代码，实现了添加、删除、查询和更新的操作：

/* txl.cpp */

#include
#include

// 导入ADO组件，它包含了三个操纵数据库的智能指针
#import "c:\program files\common files\system\\ado\\msado15.dll" \
    no_namespace rename ("EOF", "adoEOF")

using namespace std;

void    DisplayMenu();
BOOL    DisplayAllRecord();
BOOL    AddRecord();
BOOL    DeleteRecord();
BOOL    UpdateRecord();
void    SearchRecord();

// 操纵数据库的三个智能指针
_ConnectionPtr  g_pConnection;
_RecordsetPtr   g_pRecordset;
_CommandPtr        g_pCommand;

int main()
{
    int                n_Choice;
   
    CoInitialize(NULL); // Initialize Com

    // Create and Open Connection
    g_pConnection.CreateInstance(__uuidof(Connection));
    // SQL Command Executing Time
    g_pConnection->put_CommandTimeout(long(5));
    try
    {
        // Open Connection
        g_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=testdb.mdb", "", "", adModeUnknown);
    }
    catch(_com_error e)
    {
        cout << "Database connection failed :" << e.ErrorMessage() << endl;
        return -1;
    }
    g_pRecordset.CreateInstance(__uuidof(Recordset));
    g_pCommand.CreateInstance(__uuidof(Command));
    g_pCommand->ActiveConnection = g_pConnection;

    DisplayMenu();
    cin >> n_Choice;
    while ( n_Choice )
    {
        switch ( n_Choice )
        {
        case 1:
            //DisplayMenu();
            break;
        case 2:
            DisplayAllRecord();
            break;
        case 3:
            AddRecord();
            break;
        case 4:
            DeleteRecord();
            break;
        case 5:
            UpdateRecord();
            break;
        case 6:
            SearchRecord();
            break;
        default:
            cout << " Bad Choice !!!!!!" << endl << endl;
            break;
        }
       
        DisplayMenu();
        cin >> n_Choice;
    }   


    if ( g_pConnection->State )
        g_pConnection->Close();
    g_pConnection = NULL;
    CoUninitialize();
    return 0;
}

void DisplayMenu()
{
    cout << endl << "---------TONG XUN LU--------" << endl ;
    cout << "1. Display Menu" << endl;
    cout << "2. Display All Record" << endl;
    cout << "3. Add A Record" << endl;
    cout << "4. Delete A Record " << endl;
    cout << "5. Update A Record " << endl;
    cout << "6. Search A Record " << endl;
    cout << "0. Exit" << endl;
    cout << "----------------------------" << endl;
    cout << endl << "Please Select A Choice: ";
}


BOOL DisplayAllRecord()
{
    BOOL nFlag = TRUE;
    try
    {
        g_pRecordset->Open("SELECT * FROM TXL",
                            g_pConnection.GetInterfacePtr(),
                            adOpenDynamic,
                            adLockOptimistic,
                            adCmdText);
       
    }
    catch(_com_error e)
    {
        cout << "Open Recordset Error: " << e.ErrorMessage() << endl;
        return FALSE;
    }

    cout << endl << "--------+ Begin Of Record +------" << endl << endl;
   
    cout << "ID\tName\t\t\tQQ" << endl;
    while ( !g_pRecordset->adoEOF )
    {
        _variant_t var = g_pRecordset->GetCollect("ID");
        if (var.vt != VT_NULL )
            cout << (LPCSTR)_bstr_t(var) << "\t";
       
        var = g_pRecordset->GetCollect("Name");
        if ( var.vt != VT_NULL )
            cout << (LPCSTR)_bstr_t(var) << "\t\t";

        var = g_pRecordset->GetCollect("QQ");
        if ( var.vt != VT_NULL )
            cout << (LPCSTR)_bstr_t(var) << endl;

        g_pRecordset->MoveNext();       
    }
   
    cout << endl << "--------+ End Of Record +------" << endl << endl;
   
    g_pRecordset->Close();
   

    return nFlag;
}

BOOL AddRecord()
{
    char  strName[50];
    char  strQQ[20];
   

    cout << endl << "Please Enter Your Name : ";
    cin >> strName;
    cout << endl << "Please Enter Your QQ: ";
    cin >> strQQ;

    BOOL nFlag = TRUE;

    try
    {
        g_pRecordset->Open("SELECT * FROM TXL",
                            g_pConnection.GetInterfacePtr(),
                            adOpenDynamic,
                            adLockOptimistic,
                            adCmdText);
       
    }
    catch(_com_error e)
    {
        cout << "Open Recordset Error: " << e.ErrorMessage() << endl;
        return FALSE;
    }

    g_pRecordset->AddNew();
    g_pRecordset->PutCollect("Name", _variant_t(strName));
    g_pRecordset->PutCollect("QQ", _variant_t(strQQ));
    g_pRecordset->Update();
    g_pRecordset->Close();

    cout << endl << "Successfully Add A Record!" << endl << endl;

    return nFlag;
}

BOOL DeleteRecord()
{
    BOOL nFlag = TRUE;
    long ID ;
    char strSQL[256];
    int nRecCnt = 0;

    memset(strSQL, 0, 256);
   
    cout << endl << "Please Select The Record ID You Want To Delete :";
    cin >> ID;

    sprintf(strSQL, "SELECT * FROM TXL WHERE ID = %d", ID);
    try
    {
        g_pRecordset->Open( strSQL,
                            g_pConnection.GetInterfacePtr(),
                            adOpenDynamic,
                            adLockOptimistic,
                            adCmdText);
       
    }
    catch(_com_error e)
    {
        cout << "Open Recordset Error: " << e.ErrorMessage() << endl;
        return FALSE;
    }
   
    while ( !g_pRecordset->adoEOF )
    {
        nRecCnt++;
        g_pRecordset->MoveNext();
    }

    g_pRecordset->Close();

    if ( nRecCnt == 0 )
    {
        cout << "The Record ID DOES NOT EXIST !!!" << endl << endl;
        return FALSE;
    }


    sprintf(strSQL, "DELETE FROM TXL WHERE ID = %d", ID);
    g_pCommand->CommandText = (_bstr_t)strSQL;

    try
    {
        g_pCommand->Execute(NULL, NULL, adCmdText);
    }
    catch (_com_error e)
    {
        cout << "Delete Error : " << e.ErrorMessage() << endl << endl;
        return FALSE;
    }

    cout << "Successfully Delete A Record !!!" << endl << endl;

    return nFlag;
}

BOOL UpdateRecord()
{
    BOOL nFlag = TRUE;
    char strSQL[256];
    char strName[50];
    char strQQ[20];
    long ID;
    int  nRecCnt = 0;

    cout << endl << "Please Enter The Record ID You Want To Update: ";
    cin >> ID;

    sprintf(strSQL, "SELECT * FROM TXL WHERE ID = %d", ID);
    try
    {
        g_pRecordset->Open( strSQL,
                            g_pConnection.GetInterfacePtr(),
                            adOpenDynamic,
                            adLockOptimistic,
                            adCmdText);
       
    }
    catch(_com_error e)
    {
        cout << "Open Recordset Error: " << e.ErrorMessage() << endl;
        return FALSE;
    }
    while ( !g_pRecordset->adoEOF )
    {
        g_pRecordset->MoveNext();
        nRecCnt++;
    }

    g_pRecordset->Close();
   
    if ( nRecCnt == 0 )
    {
        cout << "The Record ID DOES NOT EXIST !!!" << endl << endl;
        return FALSE;
    }


    cout << endl << "Please Enter New Name(0 No Change): " ;
    cin >> strName;
    cout << endl << "Please Enter New QQ(0 No Change): " ;
    cin >> strQQ;

    if  ( (strcmp(strName, "0") == 0) && (strcmp(strQQ, "0") == 0) )
    {
        cout << endl << "No Record Will Be Changed." << endl << endl;
        return FALSE;
    }
    else if ( strcmp(strQQ, "0") == 0 )
    {
        sprintf(strSQL, "UPDATE TXL SET Name='%s' WHERE ID=%d", strName, ID);
    }
    else if ( strcmp(strName, "0") == 0 )
    {
        sprintf(strSQL, "UPDATE TXL SET QQ='%s' WHERE ID=%d", strQQ, ID);
    }
    else
        sprintf(strSQL, "UPDATE TXL SET Name='%s', QQ='%s' WHERE ID=%d", strName, strQQ, ID);
   
    g_pCommand->CommandText = (_bstr_t)strSQL;

    try
    {
        g_pCommand->Execute(NULL, NULL, adCmdText);
    }
    catch (_com_error e)
    {
        cout << "Update Record Error: " << e.ErrorMessage() << endl << endl;
        return FALSE;
    }

    cout << "Successfully Update A Record !!!!" << endl << endl;

    return nFlag;
}


void SearchRecord()
{
    int nChoice;
    int nRecCnt = 0;
    char strName[50];
    char strQQ[20];
    char strSQL[256];
    long ID;

    memset(strSQL, 0, 256);

    cout << endl << "Search Type : " << endl;
    cout << "0. Search By ID [COMPLETE MATCH]" << endl;
    cout << "1. Search By Name [PART MATCH]" << endl;
    cout << "2. Search By QQ [COMPLETE MATCH] "<< endl;
    cout << endl << "Please Select Search Type:" ;
   
    cin >> nChoice;

    switch ( nChoice )
    {
    case 0:
        {
            cout << "Please Enter The ID You Want To Search: " ;
            cin >> ID;
            sprintf(strSQL, "SELECT * FROM TXL WHERE ID = %d", ID);
        }
        break;
    case 1:
        {
            cout << "Please Enter The Name You Want To Search: " ;
            cin >> strName;
            sprintf(strSQL, "SELECT * FROM TXL WHERE Name LIKE '%%%s%%'", strName);
        }
        break;
    case 2:
        {
            cout << "Please Enter The QQ You Want To Search: " ;
            cin >> strQQ;
            sprintf(strSQL, "SELECT * FROM TXL WHERE QQ = '%s'", strQQ);
        }
        break;
    default:
        memset(strSQL, 0, 256);
        cout << "Bad Search Type !!!" << endl << endl;
        return;
    }

    if ( strlen(strSQL) == 0 )
    {
        cout << "Bad Search !!!" << endl << endl;
        return;
    }

    try
    {
        g_pRecordset->Open(strSQL,
                           g_pConnection.GetInterfacePtr(),
                           adOpenDynamic,
                           adLockOptimistic,
                           adCmdText);
    }
    catch (_com_error e)
    {
        cout << "Error Open Recordset:" << e.ErrorMessage() << endl;
        return ;
    }

    cout << endl << "--------------Search Result------------" << endl;
    cout << "ID\tName\t\t\tQQ" << endl;
    while ( !g_pRecordset->adoEOF )
    {
        _variant_t var = g_pRecordset->GetCollect("ID");
        if (var.vt != VT_NULL )
            cout << (LPCSTR)_bstr_t(var) << "\t";
       
        var = g_pRecordset->GetCollect("Name");
        if ( var.vt != VT_NULL )
            cout << (LPCSTR)_bstr_t(var) << "\t\t";

        var = g_pRecordset->GetCollect("QQ");
        if ( var.vt != VT_NULL )
            cout << (LPCSTR)_bstr_t(var) << endl;

        nRecCnt++;

        g_pRecordset->MoveNext();       
       
    }
    cout << "---------------------------------------" << endl;
    cout << nRecCnt << " Record(s) Found." << endl;

    g_pRecordset->Close();
}