ADO technology Overview
ADO is designed for Ms's powerful data access interface ole db, which is an easy-to-use application layer.
Ole db provides high-performance access to any data source, including:
Relational Database Service,Non-relational databases,Email,File System,Text and graphicsAndCustom Business Objects.
Ado 2.0 is actually based on the dynamic link library msado15.dll,
Although the name of this library file is the same as that of ADO 1.5, it implements an updated interface.
New technologies in ADO 2.0 include:
1,Asynchronous OperationAndEvent Model
2,Dataset continuity
3,HierarchicalData Transmission
The features of using ADO to access data elements are summarized as follows:
1. Introduce the ADO Library File
Before using ADO, you must use the direct introduction symbol in the stdafx. h header file of the Project # import the ADO library file,
To make the compiler compile correctly. The Code is as follows:
Import ADO library files with # Import
Basic Process(1) initialize the com library and introduce the ADO library definition file.
(2) connect to the database using the connection object
(3) Use the established connection to execute SQL commands through the connection and command objects, or use the recordset object to get results
Query and process the set.
(4) Close the connection and release the object after use.
Use the _ connectionptr Interface
_ Connectionptr is mainly a connection interface that gets a connection to the database. Its connection string can be directly written by itself or directed to an odbc dsn.
Hresult connection15: open (_ bstr_t connectionstring, _ bstr_t userid, _ bstr_t password, long options)
Connectionstring is the connection string,
Userid is the user name,
Password is the login password,
Options is the connection option, used to specify the permission of the connection object to update data,
Options can be the following constants:
Admodeunknown: default. The current permission is not set
Admoderead: Read-Only
Admodewrite: Write only
Admodereadwrite: read/write
Admodesharedenyread: blocks other connection objects and opens the connection with the read permission.
Admodesharedenywrite: blocks other connection objects and opens the connection with write permission.
Admodemo-exclusive: prevents other connection objects from opening connections.
Admodesharedenynone: allows other programs or objects to establish connections with any permissions.
◆ Common Database Connection Methods:
(1) connection to the Access2000 Database through the Jet Database Engine
M_pconnection-> open ("provider = Microsoft. Jet. oledb.4.0; Data Source = C: // test. mdb", "", "", admodeunknown );(2) Use the DSN data source to connect to any database that supports ODBC:
M_pconnection-> open ("Data Source = adotest; uid = sa; Pwd =;", "", "", admodeunknown );
// M_pconnection-> open ("DSN = test;", "", "", 0); // connect to the ODBC data source named Test(3) do not use DSN to connect to the SQL Server database:
M_pconnection-> open ("driver = {SQL Server}; server = 127.0.0.1; database = vckbase; uid = sa; Pwd = 139", "", "", admodeunknown );
The server is the name of the SQL Server, and the database is the name of the database.
◆ First, we will introduce two useful connectiontimeout and State attributes in the connection object.
Connectiontimeout is used to set the connection timeout time, which must be called before open, for example:
M_pconnection-> connectiontimeout = 5; // set the timeout value to 5 seconds.
M_pconnection-> open ("Data Source = adotest;", "", "", admodeunknown );The state attribute specifies the status of the current connection object. 0 indicates that the connection object is closed, and 1 indicates that the connection object has been opened.
Read this attribute for processing. For example:
If (m_pconnection-> state)
M_pconnection-> close (); // if the connection has been enabled, close it.
| _ Connectionptr pconn; If (failed (pconn. createinstance ("ADODB. Connection "))) Cstring strsrc; Cstring strsql = "insert into student (No, name, sex, address) values (3, 'aaa', 'male', 'beijing ')"; _ Variant_t varsrc (strsrc ); If (failed (pconn-> open (bstrsrc, "", "",-1 ))) Colevariant vtoptional (long) disp_e_paramnotfound, vt_error ); Pconn-> execute (_ bstr_t (strsql), & vtoptional,-1 ); Pconn. Release (); Afxmessagebox ("OK! "); |
Use the _ recordsetptr interface (taking connecting to SQL Server as an example)
_ Recordsetptr pptr; Cstring strsrc; Cstring strsql = "select ID, name, gender, address from personal "; _ Variant_t varsrc (strsrc ); If (failed (pptr-> open (varsql, varsrc, adopenstatic, adlockoptimistic, adshorttext ))) While (! Pptr-> getadoeof ()) Varno = pptr-> getcollect ("ID "); Cstring strno = (char *) _ bstr_t (varno ); Strno. trimright (); Int ncount = m_list.getitemcount (); Pptr-> movenext (); Pptr-> close (); |
Use the _ commandptr Interface
The _ commandptr interface returns A recordset object and provides more record set control functions. The following code example shows how to use the _ commandptr interface:
Code 11: Use the _ commandptr interface to obtain data
| _ Commandptr pcommand; _ Recordsetptr PRS; Pcommand. createinstance (_ uuidof (command )); Pcommand-> activeconnection = pconn; Pcommand-> commandtext = "select * from student "; Pcommand-> commandtype = ad1_text; Pcommand-> parameters-> refresh (); PRS = pcommand-> execute (null, null, adcmdunknown ); _ Variant_t varvalue = PRS-> getcollect ("name "); Cstring strvalue = (char *) _ bstr_t (varvalue ); |
About data type conversion
Because the COM object is cross-platform, it uses a common method to process various types of data,
Therefore, the cstring class is incompatible with the COM Object. We need a set of APIs to convert data of the COM Object and C ++ type.
_ Vatiant_t and _ bstr_t are two types of objects. They provide common methods to convert data of the COM Object and C ++ type.
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 an instance of the 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)
Where
Commandtext is a command string, usually an SQL command.
Recordsaffected is the number of rows affected after the operation is completed,
Options 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.
Try
{
_ Variant_t RA;
M_pconnection-> execute ("create table student information (student ID integer, Name text, age integer, birthday datetime)", & RA, ad1_text );
M_pconnection-> execute ("insert into student information (student ID, name, age, birthday) values (112105, 'cheng Hongxiu ', 22, '2017-08-16 ')", & RA, adshorttext); // Add records to the table
M_precordset = m_pconnection-> execute ("select count (*) from student information", & RA, ad1_text); // run the SQL statistics command to obtain the record set containing the number of records
_ Variant_t vcount = m_precordset-> getcollect (_ variant_t) (long) (0); // get the value of the first field and put it in the vcount variable
M_precordset-> close ();
Cstring message;
Message. Format ("% d records in total", vcount. lval );
Afxmessagebox (Message );
}
Catch (_ com_error E)
{...}
◆ (2) use the command object to execute SQL commands
Try
{
_ Commandptr m_pcommand;
M_pcommand.createinstance ("ADODB. Command ");
M_pcommand-> activeconnection = m_pconnection; // key sentence, assign the established connection to it
M_pcommand-> commandtext = "insert into student information (student ID, name, age, birthday) values (112105, 'cheng Hongxiu ', 22, '2017-08-16 ')";
M_pcommand-> execute (null, null, ad1_text );
M_pcommand-> commandtext = "select count (*) from student information ";
M_precordset = m_pcommand-> execute (null, null, ad1_text );
_ Variant_t vcount = m_precordset-> getcollect (_ variant_t) (long) 0); // obtain the value of the first field
Cstring STR;
Str. Format ("% d records in total", vcount. lval );
Afxmessagebox (STR );
M_precordset-> close ();
}
Catch (_ com_error e ){...}
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 stored procedures. 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 student information", _ 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 for updating 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.
Record set traversal and update
According to the student information table created by executing the SQL command just now, it contains four fields: Student ID, name, age, and 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.
Record, change its age, and save it to the database.
Try
{
_ Variant_t vusername, vbirthday, vid, vold;
_ Recordsetptr m_precordset;
M_precordset.createinstance ("ADODB. recordset ");
M_precordset-> open ("select * from student information", _ variant_t (idispatch *) m_pconnection, true), adopenstatic, adlockoptimistic, adshorttext );
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 give the column name directly, the following line
Vusername = m_precordset-> getcollect ("name"); // obtain the name field value
Vold = m_precordset-> getcollect ("Age ");
Vbirthday = m_precordset-> getcollect ("Birthday ");
Trace ("ID: % d, name: % s, age: % d, birthday: % S/R/N ",
Vid. lval,
(Lpctstr) (_ bstr_t) vusername,
Vold. lval,
(Lpctstr) (_ bstr_t) vbirthday); // records in the output window output record set in debug mode
M_precordset-> movenext (); // move to the next record
}
M_precordset-> movefirst (); // move to the first record
M_precordset-> Delete (adaffectcurrent); // Delete the current record
For (INT I = 0; I <3; I ++) // Add three new records and assign values
{
M_precordset-> addnew (); // Add a new record
M_precordset-> putcollect ("student ID", _ variant_t (long) (I + 10 )));
M_precordset-> putcollect ("name", _ variant_t ("Wang binnian "));
M_precordset-> putcollect ("Age", _ variant_t (long) 21 ));
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 ("Age"), _ variant_t (long) 45); // modify the age
M_precordset-> Update (); // save it to the database.
} Catch (_ com_error e ){}
Close 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
Macro definition in stdafx. h:
# If! Defined catch_error
# Define catch_error/
{/
Cstring strcomerror ;/
Strcomerror. Format ("error no.: % 08lx/n error message: % s/n Error Source: % s/n error Description: % s ",/
E. Error (),/
E. errormessage (),/
(Lpcstr) E. Source (),/
(Lpcstr) E. Description ());/
: MessageBox (null, strcomerror, "error", mb_iconexclamation );/
}
# Endif
Usage:
Try
{...}
Catch (_ com_error E)
{
Catch_error;
}
| # Import "C:/program files/common files/system/ADO/msado15.dll" no_namespaces Rename ("EOF" adoeof ") |
This statement declares that ADO is used in the project, but the namespace of ADO is not used. To avoid constant conflicts, change the constant EOF to adoeof.
Now you can use the ADO interface without adding another header file.
The final function is similar to the familiar # include. during compilation, the system will generate msado15.tlh and ado15.tli C ++ header files to define the ADO library.
2. initialize the OLE/COM library Environment
Note that the ADO library is a group of COM dynamic libraries, which means that the application must initialize the OLE/COM library environment before calling ADO.
In an MFC application, a better method is to initialize the OLE/COM library environment in the initinstance member function of the main class of the application.
| Bool cmyadotestapp: initinstance () { If (! Afxoleinit () // This is the initialization com Library { Afxmessagebox ("OLE initialization error !"); Return false; } ...... } |
3. Introduction to ADO Interfaces
The ADO library contains three basic interfaces: _ connectionptr, _ commandptr, and _ recordsetptr.
_ Connectionptr interface returns a record set or a null pointer.
It is usually used to create a data connection or execute an SQL statement that does not return any results,
Such as a stored procedure. Using the _ connectionptr interface to return a record set is not a good method.
_ Recordserptr is usually used for operations to return records.
When using the _ connectionptr operation, you need to retrieve the number of records to traverse all records, but do not need to use the _ recordserptr operation.
The _ commandptr interface 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 you can directly use the connection string in the _ commandptr interface.
If you only perform one or several data access operations, the latter is a good choice.
However, if you want to frequently access the database and return many record sets, you should use the global _ connectionptr interface to create a data connection,
Use the _ commandptr interface to execute stored procedures and SQL statements.
_ 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.
Like the _ commandptr interface, it does not have to use a created data connection,
You can use a connection string to replace the connection Member variable assigned by the connection pointer to _ recordsetptr,
Create a data connection by yourself.
If you want to use multiple record sets,
The best way is to use the global _ connectionptr interface that has created a data connection like the command object,
Then, use _ recordsetptr to execute stored procedures and SQL statements.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
