ADO database access)

Http://www.joyvc.cn/DatabaseTechnical/DatabaseTechnical00037.html

I. Ado Overview

ADO is an easy-to-use application layer interface designed by Microsoft for the latest and most powerful data access example ole db. Ado enables you to write applications to access and operate data on database servers through the OLE. DB Provider. The main advantages of ADO are ease of use, fast speed, low memory consumption, and small disk relics. Ado uses the least network traffic in key application solutions, and uses the least number of layers between the front end and the data source. All these are designed to provide lightweight, high-performance interfaces. ADO is a familiar metaphor for OLE Automation interface.

Ole db is a set of "Component Object Model" (COM) interfaces. It is a new low-level database interface that encapsulates ODBC functions, access data stored in different information sources in a unified manner. Ole db is the technical basis for Microsoft UDA (Universal Data Access) policies. Ole db provides high-performance access to any data source, including relational and non-relational databases, e-mail and file systems, text and graphics, and custom business objects. That is to say, ole db is not limited to isam, Jet, or even relational data sources. It can process any type of data without considering their format and storage methods. In practical applications, this diversity means that you can access data that resides in an Excel spreadsheet, text file, email/directory service, or even a mail server, such as Microsoft Exchange. However, the ole db Application Programming Interface is designed to provide the best functions for various applications and does not meet the simplification requirements. The API you need should be a bridge between applications and ole db, Which is ActiveX Data Objects (ADO ).

Ii. use ADO in VC

1. Introduce the ADO Library File

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

Code 1: Use # import to introduce the ADO Library File

# 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.

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.

Code 2: Initialize the OLE/COM library Environment

Bool cadoapp: initinstance ()
{
If (! Afxoleinit ())

{
Afxmessagebox ("OLE initialization error !");
Return false;
}
......
}

The afxoleinit function initializes the OLE/COM library environment every time the application starts.

Like Dao and cdatabase, ADO consists of several interfaces:

_ Connectionptr, _ commandptr, and _ recordsetptr.

Unlike Dao and cdatabase, ADO is based on the COM interface. Therefore, if you have never touched COM, you should first read related books about com before using ADO.

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. Generally, like cdatabase, you can use it to create a data connection and use other objects to perform data input/output operations.

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 pointer with the connection Member variable assigned 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, and then use _ recordsetptr to execute the stored procedure and SQL statement.

4. Use the _ connectionptr Interface

_ Connectionptr is a connection interface, which is similar to cdatabase and cdaodatabase. They work in a similar way. First, create a _ connectionptr interface instance, and then point to and open an ODBC data source or ole db data provider (provider ). The following code creates a data connection based on DSN and a non-DSN respectively with cdaodatabase.

Code 3: Use cdaodatabase (based on DSN)

Cdaodatabase mydb = new cdaodatabase ();

Mydb. Open (null, false, false, "ODBC; DSN = Samp; uid = admin; Pwd = admin ");

Code 4: Use cdaodatabase (based on non-DSN)

Cdaodatabase mydb = new cdaodatabase ();

Mydb. Open (null, false, false, "ODBC; driver = {SQL Server}; server = server;

Database = Samp; uid = admin; Pwd = admin ");

Code 5: Use _ connectionptr (based on DSN)

_ Connectionptr mydb;

Mydb. createinstance (_ uuidof (connection ));

Mydb-> open ("DSN = Samp; uid = admin; Pwd = admin", "", ",-1 );

Code 6: Use _ connectionptr (based on non-DSN)

_ Connectionptr mydb;

Mydb. createinstance (_ uuidof (connection ));

Mydb-> open ("provider = sqloledb; server = server; database = Samp; uid = admin;

Pwd = admin "," "," ",-1 );

5. Use the _ recordsetptr Interface

The use of the _ recordsetptr interface is similar to that of cdaodatabase. By comparing the following code, you will find that using the _ recordsetptr interface is very simple (the following code uses the data connection created above ):

Code 7: Use cdaodatabase to execute SQL statements

Cdaorecordset myset = new cdaorecordset (mydb );

Myset-> open (afx_dao_use_default_type, "select * From t_samp ");

Now using ADO:

Code 8: Use _ recordsetptr to execute an SQL statement

_ Recordsetptr myset;

Myset. createinstance (_ uuidof (recordset ));

Myset-> open ("select * From some_table ",

Mydb. getinterfaceptr (), adopendynamic, adlockoptimistic, adshorttext );

Now we have a data connection and a record set, and then we can use the data. From the following code, we can see that the _ recordsetptr interface of ADO does not need to use the large and complex data structure variant as frequently as Dao and forcibly convert various data types, this is also one of the advantages of ADO. Assume that the program has a ListBox control named m_list. The following code uses the _ recordsetptr interface to obtain record set data and fill in the ListBox control:

Code 9: use DAO to access data

Variant * vfieldvalue;

Colevariant covfieldvalue;

Cstring holder;

While (! Myset-> iseof ())

{

Myset-> getfieldvalue ("field_1", covfieldvalue );

Vfieldvalue = (lpvariant) covfieldvalue;

If (vfieldvalue-> VT! -Vt_null)

{

Holder. Format ("% s", vfieldvalue-> pbval );

M_list.addstring (holder );

}

Myset. movenext ();
}

Code 10: use ADO to access data

_ Variant_t holder

Try {

While (! Myset-> adoeof)

{

Holder = myset-> getcollect ("field_1 ");

If (holder. VT! = Vt_null)

M_list.addstring (char *) _ bstr_t (holder ));

Myset-> movenext ();

}
}
Catch (_ com_error * E)

{

Cstring error = e-> errormessage ();

Afxmessagebox (e-> errormessage ());

}

Catch (...)

{

MessageBox ("ADO error! ");

}

You must always use try and catch in the code to capture ADO errors. Otherwise, ADO errors will cause your application to crash. When an error occurs during running of ADO (for example, the database does not exist), the ole db data provider will automatically create a _ com_error object and fill in the error information to the member variable of this object.

6. 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 myset;

Pcommand. createinstance (_ uuidof (command ));

Pcommand-> activeconnection = mydb;

Pcommand-> commandtext = "select * From some_table ";

Pcommand-> commandtype = ad1_text;

Pcommand-> parameters-> refresh ();

Myset = pcommand-> execute (null, null, adcmdunknown );

_ Variant_t thevalue = myset-> getcollect ("field_1 ");

Cstring svalue = (char *) _ bstr_t (thevalue );

7. 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.

8. Summary

The trend of data access development is ole db. the simplest way to use ole db is ADO. the object hierarchy model of ADO encapsulates database access details and provides a very good data access policy for C ++ programmers.