Use ADO for database programming (medium) in Visual C ++)

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: _ RecordsetPtr m_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_t CommandText, 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_t RecordsAffected;
/// 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, username TEXT, old INTEGER, 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_t vindex = (long) 0;
_ Variant_t vcount = 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_t vcount = 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

_ Commandptr m_pcommand;
M_pcommand.createinstance ("ADODB. Command ");
_ Variant_t vnull;
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 ));
CString strTemp = (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,
Enum CursorTypeEnum CursorType,
Enum LockTypeEnum LockType,
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:

Enum CursorTypeEnum
{
AdOpenUnspecified =-1, // do not specify
AdOpenForwardOnly = 0, // roll the static cursor forward. The cursor can only browse the record set forward. For example, if MoveNext is used to scroll forward, this method can increase the browsing speed. However, neither BookMark, RecordCount, AbsolutePosition, or AbsolutePage can be used.
AdOpenKeyset = 1, // the record set with this cursor cannot see the new or delete operations of other users, but you can see the operations to update the original records.

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 the addition, deletion, and update operations of other users are invisible to your record set.
};
④ LockType: it can be one of the following values. See the following enumeration structure:

Enum LockTypeEnum
{
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. Before that, you can update, insert, or delete data.
AdLockBatchOptimistic = 4, optimistic batch update. The record is not locked during editing. Modification, insertion, and deletion are completed in batch 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