ADO Programming Applications (1)

ado| Programming ADO (ActiveX Data Objects) is a component-based database programming interface, which is a COM component system independent of programming languages. This paper mainly introduces the techniques of ADO programming and the mode of ADO programming under VC, and discusses the C + + extensions briefly, hoping to have some help to the ADO developers. Because ADO is a programming language-independent COM component system, the main points discussed here apply to all programming languages and programming environments, such as VB, VBScript, VC, Java, and so on.
Programming skills
1. Explicitly defining an object type
In fact, this rule applies not only to ADO programming, but also to other programming related to COM objects. Because if the variable type is defined at the outset, the compiler can know the type of the variable at compile time, and the compiler is actually using the vtable offset to get the address of the method contained in the specific COM object (this is similar to the way the address of the virtual function in C + + is obtained) , but if you do not specify a variable type at the outset, such as simply using the following statement:
DIM Mycon as Object
Or is:
DIM Mycon
In this way, the compiler can not get the type of the variable at compile time, and can only dynamically get the information of the method (by using the Invoke method of the interface IDispatch), so as to get the address of the method and the related variables, it needs to make two calls internally. Will undoubtedly reduce the speed of the program running.
2. Binding columns to specific Field objects
By establishing a reference to a Field object at the beginning of the program, you can avoid increasing the overhead of the system by looking in the Recordset::fields after each record is obtained.
For example, you can use the code that looks like this:
Private Sub Tblbrowse_click ()
Dim Fld1 as ADODB. Field
Dim Fld2 as ADODB. Field
Dim rs as ADODB. Recordset
Set Rs=g_cn.execute (...)
' G_CN is a global object adodb.connection
Set fld1 = Rs. Fields ("id") ' Datasheet ' field
Set fld2 = Rs. Fields ("name") ' Data table ' field
If Rs. BOF = False Then
While Rs. BOF = False
Debug.Print Fld1. Value
Debug.Print Fld2. Value
Rs. MoveNext
Wend
End If
Rs. Close
End Sub
3. Data updates with SQL statements and stored procedures
Although it is convenient to use a Recordset object to update data, it is expensive, and the query set returned through the data source object contains not only the data but also the metadata (metadata), which in some cases may be larger than the data itself, Therefore, it is best to use SQL statements to update data. There is also the need to use stored procedures rather than a single SQL statement to get information. Because the stored procedures are executed on the server side, only the results are returned to the client, which reduces the cost of data interaction on the one hand, making the system easier to maintain and maintaining data consistency.
4. Use a single SELECT statement with a set operation
When using cursors, it is best to use the method of the collection to manipulate a single SELECT statement. Recordset::get_collect methods and Recordsets::p Ut_collect method is a shortcut to a Recordset object to quickly get the value of a field without having to obtain a reference to a field. For example, you can use the following code:
Sub Collect ()
Dim rs as New Recordset
Rs. ActiveConnection = "..."
Rs. source= "A SQL query statement"
Rs. Open
Debug.Print Rs. Collect (0), Rs. Collect (1), Rs. Collect (2)
Debug.Print rs!au_id, Rs!au_fname, Rs!au_lname
End Sub
5. Query only the data that you need
Although many developers are accustomed to querying with the "SELECT * from TBL" pattern, in order to improve the efficiency of the system, it is best to write these fields directly if you need only the values of one or more of these fields, and you need to qualify the scope of the return recordset (qualified by the WHERE clause).
6. Correct selection of cursor location, type, and lock mode
If you only need to read records sequentially and do not need to scroll and update records, it is best to use server-side cursors (adUseServer), Forward-only Cursors (adopenforwardonly), and read lock (adLockReadOnly) to achieve the best performance. If scrolling is required, a client-side cursor (aduseserver) is preferable to the performance of a server-based cursor because the ADO system uses the server-side cursor type by default. Of course, if the data collection is quite large, the performance of server-side cursors will be better. Also note that if you are using a client-side cursor, it is best to use a read lock (adlockreadonly) lock type, because if you need to update the data, the client Cursor engine needs additional information (metadata), and the cost of getting this information is very expensive.
7. Adjust the CacheSize property of a Recordset object
ADO uses the CacheSize property of the Recordset object to determine the number of records fetched and cached, and ADO extracts data from the cache only when browsing the data in the cache. When the data to be browsed exceeds the cache range, ADO releases the current cache, extracts the following records (the size specified by the CacheSize), so you must set the size of the cachesize according to the specific application, to ensure the best performance.
8. Defining parameters for a Command object
In many data sources, the cost of getting parameter information and executing commands is almost the same, so it is best to define the command parameter in your program (that is, to define the name, type, and orientation of the parameter), and avoid some operations that get information from the data provider (Provider).
9. Using the original OLE DB provider
MDAC provides the raw data providers for many data sources, such as SQL Server, Oracle, and Access databases, so there is no need for ODBC to get the data (that is, no more ODBC-driven this layer), and the benefit is to get the data faster, And can reduce the disk and memory overhead.
10. Disconnect Connection Connection
If you are using a client cursor, disconnect the connection connection. One feature of ADO is that when using a client cursor to manipulate a recordset recordset, you do not need to keep in touch with the server. So you can take advantage of this feature to reduce server-side overhead (the server does not need to maintain these connections). When the recordset needs to be updated, you can reconnect to the database to update the data. In order to create a disconnected recordset, you need to use both static cursors (adOpenStatic) and the lock mode (adlockbatchoptimistic) of the batch. Here is the VC code for processing:
Prs.createinstance (__uuid (Recordset));
prs->cursorloction=aduseclient;
Prs->open (Strcmdtext,strconnection,adopenstatic,adlockbatchoptimistic,adcmdtext);
Prs->putrefactiveconnection (NULL);
To manipulate a Recordset object PRs
Reconnect to the database
Prs->putrefactiveconnectio (PCon);
Batch Update data
Prs->updatebatch (Adaffectall);
It's important to note that when you perform a bulk update, you must handle the data conflict yourself, because when you update the data, other users may also be working on the data.
11. Using the adExecuteNoRecords option
If you do not need to return records, use the adExecuteNoRecords option. ADO 2.0 includes a new execution option called adExecuteNoRecords. When this option is used, ADO does not create a Recordset object and does not set any cursor properties. The data provider optimizes performance because it does not need to authenticate the attributes of the collection. The specific examples are as follows:
Con. Execute "INSERT into TBL values (FV1, Fv2)", adExecuteNoRecords
Using the Connection::execute method for only one execution statement is better than using the Recordset::Open method or the Command::execute method, because ADO does not retain information about any of the command states, so performance improvements are performed.
12. Using the session/connection buffer pool
Because the opening and closing of a database consumes system resources very much, the use of connection pooling can greatly improve the performance of multi-tier applications. When MDAC is used, the developer itself does not need to consider caching the database connection, and MDAC handles it automatically. Connection pooling provides support on two levels: OLE DB sessions and ODBC connections. If you use ADO, the database connection is automatically cached by the OLE DB session buffer pool, and if you use ODBC, you can set the ODBC buffer with the new connection buffer pool option in ODBC data source management.
Implementation methods
We know that in VB under the programming based on ADO relatively simple, as long as the appropriate type library loaded through the reference, you can normally call the ADO object. But for VC under the ADO based database development is a little more complicated. VC in the implementation of ADO operations usually have three ways:
#import method;
The use of MFC OLE ClassWizard;
COM-related functions through the Windows API.
In these three methods, #import is the most convenient method, which allows the generation of a class structure similar to VB, so that the development of the program becomes very convenient. These three methods are described separately below.
1. #import method
In the #import method, you need to provide the path and name of the type library that you want to include, and VC can automatically generate a definition of GUIDs and an encapsulation of the ADO object automatically. For any referenced type library, VC will automatically generate two files at compile time:
Header file (. tlh): Contains the enumerated types and definitions of objects in the type library;
Implementation file (. tli): Encapsulates a method in a type library object model.
For example, after adding the #import to msado15.dd in the StdAfx.h file, VC generates MSADO15.TLH and Msado15.tli two files.
#import can use a new class _com_ptr_t, which is also called a smart pointer. The smart pointer can automatically perform quyerinterface, AddRef, and release functions.
The following code demonstrates how to use #import to implement the operation of ADO in an application:
#import "C:\Program Files\co