Ado for VC development database (2)

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 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, ad1_text );
/// 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
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
For example

M_precordset-> open ("select * from users", _ variant_t (idispatch *) m_pconnection, true), adopenstatic, adlockoptimistic, adshorttext );

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.
};
⑤ For options, refer to the introduction to the execute method of the connection object in this article.

[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, move the cursor to the second record, change its age, and save it to the database.

_ Variant_t vusername, vbirthday, vid, vold;
_ Recordsetptr m_precordset;
M_precordset.createinstance ("ADODB. recordset ");
M_precordset-> open ("select * from users", _ variant_t (idispatch *) m_pconnection, true), adopenstatic, adlockoptimistic, adshorttext );
While (! M_precordset-> adoeof) // Why is it adoeof instead of EOF? Do you still remember Rename ("EOF", "adoeof?
{
Vid = m_precordset-> getcollect (_ variant_t (long) 0); // obtain the value of the 1st column and count it from 0. You can also give the column name directly, 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/R/N", 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 (INT I = 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 ("Silverlight "));
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 a record from the first record 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.

[6] disable record set and Connection
You can close Record Sets or connections using the close method.
M_precordset-> close (); // close the record set
M_pconnection-> close (); // close the connection

At this point, I think you are familiar with the general process of operating the database through ADO. Maybe you have a good idea. Maybe you still have something to worry about! I suggest you try to write a few examples to better familiarize yourself with ADO. Finally, I wrote a small example for you, in this example, all records are read and placed in the list control, and records can be added, deleted, or modified.
Click here to download the sample code

Note: Due to the length constraints, many content in ado has not yet been introduced. Next time, we will detail the attributes and methods of the recordset object and discuss several key technologies: binding method to process record set data, call of stored procedures, transaction processing, storage and reading of images in the database, and use with table controls