Programming with ADO in Visual C ++

　　1. Generate the application framework and initialize the OLE/COM library Environment

Create a Standard MFC AppWizard (exe) application, and then useADODatabase(Because the ADO library is a com dll library ).

In this example:

BOOL CAdotestDlg: OnInitDialog ()

{

: CoInitialize (NULL); // initialize the OLE/COM library Environment

}

The program must call: CoUninitialize (); // release the COM resources occupied by the program.

In addition:

M_pRecordset-> Close (); note !!! Do not close it multiple times !!!!!!!!!!!!

M_pConnection-> Close ();

M_pRecordset = NULL;

M_pConnection = NULL;

2. Introduce the ADO Library File

Before using ADO, you must use the import symbol # import to introduce the ADO library file at the end of the stdafx. h file of the project so that the compiler can compile it correctly. The Code is as follows:

# Import "C: Program Filescommon filessystemadomsado15.dll" no_namespace rename ("EOF", "adoEOF ")

ADO class is defined as a kind of resource stored in ado dll (msado15.dll), which is internally called a Type Library. The Type Library describes the autonomous interface and the COM vtable interface used by C ++. When the # import command is usedVisual C ++++ Read this type library from ado dll and create a set of C ++ header files. These header files have. Tli and. Tlh extension. You can find these two files in the project directory. The ADO class called in the C ++ program code should be defined in these files.

The third line of the program indicates that the ADO object does not use namespaces. In some applications, because the objects in the application may conflict with those in ADO, it is necessary to use an empty name. If you want to use a namespace, you can change the third-line program to rename_namespace ("AdoNS "). The fourth line of code changes the EOF In ADO to adoEOF to avoid conflicts with other libraries that define their own EOF.

3. database operations using smart pointers

Define two ADO smart pointer instances in the CaboutDlg header file, and add a ListCtrl in the dialog box.

Class CAdotestDlg: public CDialog

{

_ ConnectionPtrm_pConnection;

_ RecordsetPtrm_pRecordset;

ClistCtrlm_List;

......

}

The ADO library contains three smart pointers: _ ConnectionPtr, _ CommandPtr, and _ RecordsetPtr.

_ ConnectionPtr is usually used to create a data connection or execute an SQL statement that does not return any results, such as a stored procedure.

_ CommandPtr returns a record set. It provides a simple method to execute stored procedures and SQL statements that return record sets. When using the _ CommandPtr interface, you can use the global _ ConnectionPtr interface or directly use the connection string in the _ CommandPtr interface. _ RecordsetPtr is a record set object. Compared with the above two types of objects, it provides more control functions for the record set, such as record lock and cursor control.

In the event response using the ADO program, add the following code to OnButton1:

Void CAdotestDlg: OnButton1 ()

{

M_List.ResetContent ();

M_pConnection.CreateInstance (_ uuidof (Connection); // initialize the Connection pointer

M_pRecordset.CreateInstance (_ uuidof (Recordset); // initialize the Recordset pointer

Try

{

M_pConnection-> Open ("DSN = ADOTest", "", "", 0); // connect the ODBC data source called ADOTest

// Note: this is an open function that does not require a user ID or password for connection.

// Otherwise, the format is-> Open ("DSN = test; uid = sa; pwd = 123;", "", "", 0 );

// Execute the SQL statement to obtain a record set and assign its pointer to m_pRecordset

CStringstrSql = "select * from middle ";

BSTR bstrSQL = strSql. AllocSysString ();

M_pRecordset-> Open (bstrSQL, (IDispatch *) m_pConnection, adOpenDynamic, adLockOptimistic,

AdCmdText );

// AdOpenDynamic: Dynamic adLockOptimistic optimistic blocking method adshorttext: Text query statement

While (! M_pRecordset-> adoEOF) // traverses all records

{

// One of the methods for getting record Field Values

_ Variant_tTheValue; // VARIANT Data Type

TheValue = m_pRecordset-> GetCollect ("BIG_NAME"); // obtain the value of the BIG_NAME field.

If (TheValue. vt! = VT_NULL)

M_List.AddString (char *) _ bstr_t (TheValue ));

// Add the value to the List Control

// Method 2 for getting the record Field Value

// _ Bstr_t TheValue1 = m_pRecordset-> Fields-> GetItem ("BIG_NAME")-> Value;

// CString temp = TheValue1.copy ();

// M_List.AddString (temp );

// Data type conversion

_ Variant_tvUsername, vBirthday, vID, vOld;

TRACE ("id: % d, name: % s, age: % d, birthday: % s",

VID. lVal, (LPCTSTR) (_ bstr_t) vUsername, vOld. lVal, (LPCTSTR) (_ bstr_t) vBirthday );

M_pRecordset-> MoveNext (); // transfers to the next record

}

M_pRecordset-> Close ();

M_pConnection-> Close ();

}

Catch (_ com_error e) // Exception Handling

{

AfxMessageBox (e. ErrorMessage ());

}

M_pRecordset-> Close (); // note !!! Do not close it multiple times !!!! Otherwise, an error occurs.

M_pConnection-> Close ();

M_pRecordset = NULL;

M_pConnection = NULL;

}

The program converts data of the COM Object and C ++ type through _ variant_t and _ bstr_t. The _ variant_t class encapsulates the OLE autonomous VARIANT data type. Using the _ variant_t class in C ++ is much easier than directly using the VARIANT data type.

Well, the program can run after compilation, but remember to create an ODBC data source named ADOTest before running. This program will display the BIG_NAME field value in the table middle in the list control.

4. Execute the SQL command and obtain the result record set

To obtain the result record set, we define a pointer to the Recordset object: _ RecordsetPtrm_pRecordset;

And create a Recordset object for it: m_pRecordset.CreateInstance ("ADODB. Recordset");

SQL commands can be executed in multiple forms. The following describes how to execute these commands.

(1) Execute SQL commands using the Execute method of the Connection object

The prototype of the Execute method is as follows:

_ RecordsetPtr Connection15: Execute (_ bstr_tCommandText, VARIANT * RecordsAffected,

Long Options)

CommandText is a command string, usually an SQL command.

The RecordsAffected parameter indicates the number of rows affected by the operation,

The Options parameter indicates the content type in CommandText. Options can be one of the following values:

Adshorttext: indicates that CommandText is a text command.

AdCmdTable: indicates that CommandText is a table name.

Ad1_proc: indicates that CommandText is a stored procedure.

AdCmdUnknown: Unknown

After Execute is executed, a pointer pointing to the record set is returned. The specific code is provided and described below.

_ Variant_tRecordsAffected;

/// Execute the SQL command: CREATE TABLE users. users contains four fields: integer ID, string username,

Integer old, date type birthday

M_pConnection-> Execute ("create table users (id integer, usernameTEXT, oldINTEGER, birthday

DATETIME) ",

& RecordsAffected,

AdCmdText );

/// Add records to the table

M_pConnection-> Execute ("insert into users (ID, username, old, birthday) VALUES

(1, ''' Washington ''', 25, ''' 1970/1/1') ", & RecordsAffected, ad1_text );

/// Add the value of all record old fields to one

M_pConnection-> Execute ("UPDATE users SET old = old + 1", & RecordsAffected, ad1_text );

/// Execute the SQL statistics command to obtain the record set containing the number of records

M_pRecordset = m_pConnection-> Execute ("select count (*) FROM users", & RecordsAffected, ad1_text );

_ Variant_tvIndex = (long) 0;

_ Variant_tvCount = m_pRecordset-> GetCollect (vIndex); // get the value of the first field and put it in the vCount variable

The last two sentences can be written as-_ variant_tvCount = m_pRecordset-> GetCollect (_ variant_t) (long) 0 ));

M_pRecordset-> Close (); // Close the record set

CString message;

Message. Format ("% d records in total", vCount. lVal );

AfxMessageBox (message); // display the number of current records

(2) Execute SQL commands using Command objects

_ CommandPtrm_pCommand;

M_pCommand.CreateInstance ("ADODB. Command");

_ Variant_tvNULL;

VNULL. vt = VT_ERROR;

VNULL. scode = DISP_E_PARAMNOTFOUND; // It is defined as "No parameter ".

M_pCommand-> ActiveConnection = m_pConnection; // a very critical sentence. Assign the established connection to it.

M_pCommand-> CommandText = "SELECT * FROM users"; // command string

M_pRecordset = m_pCommand-> Execute (& vNULL, & vNULL, ad1_text); // Execute the command to obtain the record set

In this Code, we only use the Command object to execute the SELECT query statement. The Command object can truly reflect its role in calling the stored procedure. We will introduce it in detail next time.

(3) directly use the Recordset object for query to obtain the record set

Instance --

Void CGmsaDlg: OnDBSelect ()

{

// TODO: Add your control notification handler code here

_ RecordsetPtr Rs1; // defines the Recordset object

_ Bstr_t Connect ("DSN = GMS; UID = sa; PWD =;"); // define the connection string

_ Bstr_t Source ("SELECT count (*) FROM buaa. mdb010"); // SQL statement to be executed

: CoInitialize (NULL); // initialize the Rs1 object

HRESUL hr = Rs1.CreateInstance (_ uuidof (Recordset ));

// Omitting the judgment on the returned value hr

Rs1-> Open (Source,

Connect,

AdOpenForwardOnly,

AdLockReadOnly,

-1 );

_ Variant_t temp = Rs1-> GetCollect (_ variant_t (long) 0 ));

CStringstrTemp = (char *) (_ bstr_t) temp;

MessageBox ("OK !" + StrTemp );

}

For example

M_pRecordset-> Open ("SELECT * FROM users ",

_ Variant_t (IDispatch *) m_pConnection, true ),

AdOpenStatic,

AdLockOptimistic,

AdCmdText );

The prototype of the Open method is as follows:

HRESULT Recordset15: Open (const _ variant_t & Source,

Const _ variant_t & ActiveConnection,

EnumCursorTypeEnumCursorType,

EnumLockTypeEnumLockType,

Long Options)

Where:

① Source is a data query string

② ActiveConnection is a established Connection (we need to use the Connection object pointer to construct a _ variant_t object)

③ CursorType the cursor type. It can be one of the following values. Please refer to this enumeration structure:

EnumCursorTypeEnum

{

AdOpenUnspecified =-1, // do not specify

AdOpenForwardOnly = 0, // roll the static cursor forward. This cursor can only browse the record set forward, such as rolling forward with MoveNext.

To improve the browsing speed. However, neither BookMark, RecordCount, AbsolutePosition, or AbsolutePage can be used.

AdOpenKeyset = 1, // the record set with this cursor does not see the addition or deletion operations of other users, but the operation for updating the original records

It is visible to you.

AdOpenDynamic = 2, // dynamic cursor. All database operations are immediately reflected in each user record set.

AdOpenStatic = 3 // static cursor. It generates a static backup for your record set, but other user operations such as add, delete, and update

Is invisible.

};

④ LockType: it can be one of the following values. See the following enumeration structure:

EnumLockTypeEnum

{

AdLockUnspecified =-1, // not specified

AdLockReadOnly = 1, // read-only record set

AdLockPessimistic = 2, pessimistic locking mode. Lock all other actions when data is updated. This is the most secure locking mechanism.

AdLockOptimistic = 3, Optimistic Locking mode. The record is locked only when you call the Update method. You can do this before

Updates, inserts, and deletes data.

AdLockBatchOptimistic = 4, optimistic batch update. The record is not locked during editing, and modification, insertion, and deletion are performed in the batch processing mode.

.

};

⑤ Options can take one of the following values:

Adshorttext: indicates that CommandText is a text command.

AdCmdTable: indicates that CommandText is a table name.

Ad1_proc: indicates that CommandText is a stored procedure.

AdCmdUnknown: Unknown

5. Record set traversal and update

According to the users table created by executing the SQL command just now, it contains four fields: ID, username, old, birthday

The following code enables you to open a record set, traverse all records, delete the first record, add three records, and move the cursor to the second record,

Change its age and save it to the database.

_ Variant_tvUsername, vBirthday, vID, vOld;

_ RecordsetPtrm_pRecordset;

M_pRecordset.CreateInstance ("ADODB. Recordset ");

M_pRecordset-> Open ("SELECT * FROM users ",

_ Variant_t (IDispatch *) m_pConnection, true ),

AdOpenStatic,

AdLockOptimistic,

AdCmdText );

While (! M_pRecordset-> adoEOF)

{

VID = m_pRecordset-> GetCollect (_ variant_t (long) 0); // obtain the value of the 1st column and count it from 0,

/// You can also directly give the column name, as shown in the following line

VUsername = m_pRecordset-> GetCollect ("username"); // obtain the value of the username field

VOld = m_pRecordset-> GetCollect ("old ");

VBirthday = m_pRecordset-> GetCollect ("birthday ");

/// Records in the OUTPUT window OUTPUT record set in DEBUG mode

If (vID. vt! = VT_NULL & vUsername. vt! = VT_NULL & vOld. vt! = VT_NULL & vBirthday. vt

! = VT_NULL)

TRACE ("id: % d, name: % s, age: % d, birthday: % s",

VID. lVal,

(LPCTSTR) (_ bstr_t) vUsername,

VOld. lVal,

(LPCTSTR) (_ bstr_t) vBirthday );

M_pRecordset-> MoveNext (); // move to the next record

}

M_pRecordset-> MoveFirst (); // move to the first record

M_pRecordset-> Delete (adAffectCurrent); // Delete the current record

/// Add three new records and assign values

For (inti = 0; I <3; I ++)

{

M_pRecordset-> AddNew (); // Add a new record

M_pRecordset-> PutCollect ("ID", _ variant_t (long) (I + 10 )));

M_pRecordset-> PutCollect ("username", _ variant_t ("Leslie");

M_pRecordset-> PutCollect ("old", _ variant_t (long) 71 ));

M_pRecordset-> PutCollect ("birthday", _ variant_t ("1930-3-15");

}

M_pRecordset-> Move (1, _ variant_t (long) adBookmarkFirst); // Move one record down from the first record, that is, Move

To the second record

M_pRecordset-> PutCollect (_ variant_t ("old"), _ variant_t (long) 45); // modify the age

M_pRecordset-> Update (); // save it to the database.

Note: multiple queries can make the query process into a function ExecuteSQL that allows m_pRecordset to obtain the query result of the connection pointer m_pConnection.

Void ExecuteSQL (_ ConnectionPtrm_pConnection, _ RecordsetPtrm_pRecordset, CStringstrSql)

{

// Execute the Select statement

BSTR bstrSQL = strSql. AllocSysString ();

Try

{

M_pRecordset-> Open (bstrSQL, (IDispatch *) m_pConnection, adOpenDynamic, adLockOptimistic,

AdCmdText );

// AdOpenDynamic: Dynamic adLockOptimistic optimistic blocking method adshorttext: Text query statement

}

Catch (_ com_error error)

{

CStringerrorMessage;

ErrorMessage. Format ("% s", (LPTSTR) error. Description ());

AfxMessageBox (errorMessage );

}

}

// Error handling:

3127 -- the target table is not found.

3092 -- the target table already exists

For example:

Catch (const _ com_error e)

{

AfxMessageBox (e. Description ());

Long errorCode = e. WCode ();

If (3127 = errorCode) AfxMessageBox ("the table does not exist");

If (3092 = errorCode) AfxMessageBox ("the table already exists");

Return FALSE;

}