Reference resources: http://blog.csdn.net/augusdi/article/details/7005597 Some additions and deletions were made.

Dead work:

(1) Introducing ADO classes

#import "c:\program files\common files\system\ado\msado15.dll"  no_namespace rename ("EOF", "adoEOF")

(2) Initialization of COM

AfxOleInit() can be used in MFC.

Use CoInitialize (NULL) in non-MFC environments; / CoUnInitialize ();

(3) Import contains three smart pointers: _ConnectionPtr, _RecordsetPtr and_CommandPtr

Use steps:

1. Connection and closure data base

(1) connection

Example: Connect to Access database

1. //Initialization  
2. AfxOleInit();  
3.   
4. HRESULT hr;  
5. try  
6. {  
7.     /// Create Connection Objects  
8.     hr = m_pConnection.CreateInstance("ADODB.Connection");  
9.     if(SUCCEEDED(hr))  
10.     {  
11.         m_pConnection->ConnectionTimeout = 0;  
12.         hr = m_pConnection->Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb", "", "", adModeUnknown);  
13.         //M_pConnection - > PutDefaultDatabase ((_bstr_t)"DB"; // Set the default database  
14.         m_pCommand.CreateInstance(__uuidof(Command));  
15.         m_pCommand->CommandTimeout = 5;  
16.         m_pCommand->ActiveConnection = m_pConnection;  
17.     }  
18. }  
19. catch(_com_error e)/// Catching exceptions  
20. {  
21.     CString errormessage;  
22.     errormessage.Format(" Failure to connect database! error message:%s",e.ErrorMessage());  
23.     AfxMessageBox(errormessage);/// Display error message  
24. }  

(2) shut down

1. //If the database connection is valid  
2. if( m_pConnection->State )  
3. m_pConnection->Close();  
4. m_pConnection = NULL;   

(3) Setting connection time

1. //Setting connection time  
2. pConnection->put_ConnectionTimeout(long(5));  

2. Open a result set

(1) Open, first create an instance of _RecordsetPtr, and then call Open() to get the execution result of an SQL statement.

1. _RecordsetPtr m_pRecordset;  
2. m_pRecordset.CreateInstance(__uuidof(Recordset));  
3. try  
4. {  
5.     m_pRecordset->Open("SELECT * FROM DemoTable",//Query all fields in the DemoTable table  
6.                         m_pConnection.GetInterfacePtr(), //Get IDispatch pointer for library connection Libraries  
7.                         adOpenDynamic,  
8.                         adLockOptimistic,  
9.                         adCmdText);  
10. }  
11. catch(_com_error *e)  
12. {  
13.     AfxMessageBox(e->ErrorMessage());  
14. }   

(2) Close the result set
m_pRecordset->Close();

3. Operating on a result set

(1) Traverse (read)

(a) Use pRecordset - > adoEOF to determine whether the database pointer has moved to the end of the result set;

M_pRecordset - > BOF determines whether or not the first record precedes:

1. while(!m_pRecordset->adoEOF)  
2. {  
3.     //Get field values  
4.     _variant_t var = m_pRecordset->GetCollect("Name");  
5.     if(var.vt != VT_NULL)  
6.     {  
7.         CString strName = (LPCSTR)_bstr_t(var);  
8.     }  
9.       
10.     var = m_pRecordset->GetCollect("Age");  
11.     if(var.vt != VT_NULL)  
12.     {  
13.         CString strAge = (LPCSTR)_bstr_t(var);  
14.     }  
15.   
16.     m_pRecordset->MoveNext();  
17. }   

b) There are two ways to get the value of a field

The first is:

// Represents getting the value of field 0
rdset->GetCollect("Name");
/ / or
rdset->GetCollect(_variant_t(long(0));

Second kinds

pRecordset->get_Collect("COLUMN_NAME");
/ / or
pRecordset->get_Collect(long(index));

(2), add

Call m_pRecordset - > AddNew ();

Call m_pRecordset - > PutCollect (); assign values to each field

Call m_pRecordset - > Update (); confirm

(3), modification

Call m_pRecordset - > PutCollect (); assign values to each field

Call m_pRecordset - > Update (); confirm

(4), delete

Move the record pointer to the record to be deleted, and then call Delete(adAffectCurrent)

1. try  
2. {  
3.     //Suppose that the second record is deleted  
4.     m_pRecordset->MoveFirst();  
5.     //Move a record downward from the current position  
6.     m_pRecordset->Move(1);   
7.     //The parameter adAffectCurrent deletes the current record  
8.     m_pRecordset->Delete(adAffectCurrent);   
9.     m_pRecordset->Update();  
10. }  
11. catch(_com_error *e)  
12. {  
13.     AfxMessageBox(e->ErrorMessage());  
14. }  

4. Execute SQL statements directly

(1) Cooperate with _CommandPtr and _RecordsetPtr

1. _CommandPtr m_pCommand;  
2. m_pCommand.CreateInstance(__uuidof(Command));  
3. //Assign library connections to it  
4. m_pCommand->ActiveConnection = m_pConnection;   
5. //SQL statement  
6. m_pCommand->CommandText = "SELECT * FROM DemoTable";   
7. //Execute the SQL statement and return the Recordset  
8. m_pRecordset = m_pCommand->Execute(NULL, NULL,adCmdText);   

(2) Executing SQL statements directly with _ConnectionPtr

_RecordsetPtr Connection::Execute ( _bstr_t CommandText, VARIANT * RecordsAffected, long Options )

Parameters:

Records Affected is the number of rows affected by the completion of the operation.

Options denotes the type of content in CommandText, and Options can take one of the following values:

adCmdText: Indicates that CommandText is a text command, usually an SQL statement;

adCmdTable: Indicates that CommandText is a table name;

adCmdProc: Indicates that CommandText is a stored procedure

adCmdUnknown: Unknown, or Default

1. try   
2. {   
3.     m_pCommand->CommandText ="INSERT INTO tTest(age) VALUES('23f2') ";   
4.     m_pRecordset = m_pCommand->Execute(NULL,NULL, <span style="color:#66cccc;">adCmdText</span>);   
5. }  
6. //Catching exceptions  
7. catch(_com_error e)  
8. {  
9.     //Display error message  
10.     AfxMessageBox(e.ErrorMessage());  
11. }  

5. Calling stored procedures

(1) Utilizing _CommandPtr

1. _CommandPtrm_pCommand;  
2. m_pCommand.CreateInstance(__uuidof(Command));  
3. //Assign library connections to it  
4. m_pCommand->ActiveConnection = m_pConnection;   
5. m_pCommand->CommandText = "Demo";   
6. m_pCommand->Execute(NULL,NULL, <span style="color:#ff6666;">adCmdStoredProc</span>);   

(2) Call directly with _ConnectionPtr

Special applications:

Display database and table information

The member function openSchema() of _ConnectionPtr is used to obtain the schema information of the database.

Function prototype:

_RecordsetPtr recordset = connection.OpenSchema (QueryType, Criteria, SchemaID)

Parameters:

1. QueryType

SchemaEnum enumerates the value of the type, indicating the type of the acquisition pattern (Schema);

2. Criteria

Options, corresponding to SchemaEnum's Query options

3. SchemaID

Optionally, it is only useful if QueryType is set to adSchemaProvider Specific, otherwise this parameter is not used;

The commonly used values for SchemeEnum enumeration types are as follows:

adSchemaColumns: Returns the column names of the tables in the database directory accessible to the user (including View views);

Returns the column name (field name) in the result set:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME

adSchemaTables: Returns the name of the table in the database directory accessible to the user;

Returns the column name (field name) in the result set:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE

Example 1: Traverse all table names in the database;

1. _ConnectionPtr m_pConnect;  
2. _RecordsetPtr pSet;  
3. HRESULT hr;  
4. try  
5. {   
6.     hr = m_pConnect.CreateInstance("ADODB.Connection");   
7.     if(SUCCEEDED(hr))   
8.     {  
9.         CString dd;   
10.         dd.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s",file);  
11.         hr=m_pConnect->Open((_bstr_t)dd, "", "", adModeUnknown);   
12.         pSet=m_pConnect->OpenSchema(adSchemaTables);   
13.         while(!(pSet->adoEOF))   
14.         {   
15.             //Get the table name  
16.             _bstr_t table_name = pSet->Fields->GetItem("TABLE_NAME")->Value;  
17.             //Get the table type  
18.             _bstr_t table_type = pSet->Fields->GetItem("TABLE_TYPE")->Value;  
19.             //Filter it out and only output the name of the table. Other omissions  
20.             if(strcmp(((LPCSTR)table_type),"TABLE")==0)  
21.             {  
22.                 CString tt;  
23.                 tt.Format("%s",(LPCSTR)table_name);   
24.                 AfxMessageBox(tt);   
25.             }   
26.             pSet->MoveNext();   
27.         }   
28.         pSet->Close();   
29.     }   
30.   
31.     m_pConnect->Close();   
32. }  
33. //Catching anomalies  
34. catch(_com_error e)  
35. {  
36.     AfxMessageBox(e.ErrorMessage());  
37. }  

Example 2: Traversing through all fields in a table