ADO (ActiveX Data Objects) is a component-based database programming interface that is a COM component system that is independent of programming language. This paper mainly introduces the techniques you need to pay attention to using ADO program and the mode of ADO programming under VC, and the C Extensions is a simple discussion, I hope that ADO developers have a certain help. Because ADO is a COM component system-independent COM component system, the points discussed here are suitable for all programming languages and programming environments, such as: VB, VBScript, VC, Java, and more.
Program skill
1. Explicit definition object type
In fact, this standard is not only suitable for ADO programming, but also for other programming related to COM objects. Because if the variable type is defined, the compiler can know the type of variable when compiling. At this time, the compiler actually uses the VTABLE offset to get the address of the method containing the specific COM object (this Similar to the address acquisition method of the virtual function in C ); but if the variable type is not specified at the beginning, such as simply adopting the following statement:
DIM MyCon As Object
or it could be:
DIM MyCon
Thus, the compiler cannot obtain the type of variable when compiling, and only the information of the method can only be dynamically obtained when running (implemented by the Invoke method of the interface IDispatch), so in order to obtain the address and related variables The situation is required to be called internal, which will undoubtedly reduce the running speed of the program.
2. Bind lists to specific field objects
The reference to the field object is established at the beginning of the program, and it can avoid the display of the system in Recordset :: Fields after each recording.
For example, you can use the code as shown below:
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 the global object AdoDb.Connection
Set fld1 = rs.fields ("ID") 'Data table field
Set fld2 = rs.fields ("name") 'Data table
IF = fals.bof = false
While rs.bof = false
Debug.print Fld1.Value
Debug.print Fld2.Value
rs.movenext
Wend
END IF
Rs.close
End Sub
3. Data update with SQL statements and stored procedures
Although it is very convenient to update the data using the Recordset object, its overhead is also large. The query set returned by the data source object not only contains data, but also contain metadata, in some time metadata may be more data. It is also bigger, so it is best to use SQL statements to update data. There is also a stored procedure rather than a single SQL statement to get information. Because the stored procedure is executed on the server side, only the results return to the client, so on the one hand, it can reduce the overhead of the network to perform data interaction, and the system makes the system easier maintenance, and maintains the consistency of data.
4. Use a collection to operate a single SELECT statement
When using a cursor, it is best to use a collection method to operate a single SELECT statement. Recordset :: get_collect method and RECORDSET :: PUT_COLLECT method is a shortcut for the Recordset object, which can quickly get the value of a field without requiring 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 rscollect (0), RS.COLLECT (1), RS.COLLECT (2)
Debug.print rs! Au_id, rs! Au_fname, RS! AU_LNAME
End Sub
5. Just query the data required
Although many developers are used to using the "Select * from TBL" mode for query, but in order to improve the efficiency of the system, if only the values of some fields are required, it is best to write these few fields directly, while need to limit Returns the range of records (limited by the WHERE clause).
6. Correctly select the location, type and lock mode of the cursor
If you only need to read records in order and do not need to scroll and update records, you'd better use server-side cursors, only forward cursors (Adopenforward ", which can achieve the best performance. If you need to scroll records, the client cursor is better than the performance of the server-side cursor, because the ADO system is used by default to use the server-side cursor type. Of course, if the data set is quite large, the performance of the server-side game will be better. At the same time, it is necessary to pay attention: If the client game is used, it is best to only use the ADLockReadOnly lock type, because if you need to update the data, the client cursor engine needs to get additional information (metadata), and get the price of this information. it's very expensive.
7. Adjust the Cachesize property of the recordset object
ADO uses the Cachesize property of the record set object to determine the number of records of the extracted and cache. When the data is browsing within the cache, the ADO extracts data from the cache. When the data to be browsed is outside the cache range, the ADO releases the current cache and extracts some records (the number of extracted numments specified by cachesize), so the size of the Cachesize must be set according to the case of the specific application. Ensure optimal performance.
8. Define the parameters of the Command object
In many data sources, the price of parameter information and execution command is almost the same, so it is best to define the command parameters in the program (that is to say to define the name, type, and direction information of the parameters), avoid some from The data provider is available there.
9. Use the original OLE DB provider
MDAC provides the original data provider for many data sources, such as SQL Server, Oracle, and Access databases, so you don't need to get data through ODBC (that is, you don't need to drive this layer through ODBC), this benefit It is possible to get data faster and reduce the overhead of disk and memory.
10. Disconnect Connection connection
If you use a client game, disconnect the Connection connection. ADO has a feature that when you use the client cursor to operate the Recordset recordset, you don't need to keep in touch with the server. So you can make full use of this feature to reduce the overhead of the server side (the server does not need to maintain these connections). When the operator is updated, you can re-update the data when the record set needs to be updated. In order to create a recordset that can be disconnected, you need to use a static cursor (AdoPenStatic) and a batch of ADLockBatchOptimistic. Below is the processed VC code: prs.createInstance (__ uuid (recordset));
PRS-> CURSORLOCTION = aduseclient;
PRS-> Open (StrcmdText, StrConnection, AdoPenStatic, AdlmdText);
PRS-> PutreFactiveConnection (NULL);
// Operate the record set object PRS
/ / Re-establish the database
PRS-> PutreFactiveConnectio (PCON);
// Batch update data
PRS-> UpdateBatch (AdaffectAll);
It should be noted that when performing bulk updates, you must handle data conflicts, because of updating data, other users may also operate the data at the same time.
11. Use the adexecutenorecords option
If you don't need to return a record, use the adexecutenorecords option. ADO 2.0 includes a new execution option called Adexecutenorecords. When using this option, ADO does not create a recordset object and does not set any bike properties. The data provider is optimized because of the properties that do not require the validation set. Specific examples are as follows:
Con.Execute "Insert Into TBL VALUES", Adexecutenorecords
For only one execution statement, the effect :: execute method is better than using the Recordset :: Open method or the command :: execute method, because the ADO does not retain any information about any command state, so performing performance has improved.
12. Use the Session / Connection Buffer Pool
Because the database is turned on and off, it is highly consumed that the performance of the multi-layer application is greatly improved using the connection pool. When using MDAC, the developer itself does not need to consider the cache for database connections, and MDAC will automatically process it. The connection pool provides support at two levels: OLE DB Sessions and ODBC connections. If you use ADO, the database connection will automatically be cached by the OLE DB Session buffer pool; if you use ODBC, you can use the new connection buffer pool option in ODBC Data Source Management to set the ODBC buffer.
Implementation
We know that ADO-based programming is relatively simple under VB, and as long as the appropriate type library is loaded by Reference, the ADO object can be called normally. However, it is slightly more complicated to ADO-based database development under VC. There are usually three ways to implement the ADO operation in the VC:
● # import method;
● Use the MFC OLE's classwizard;
● Through the COM related functions in the Windows API.
In these three methods, # import is the most convenient method, which allows a similar VB based class structure to make program development very convenient. These three methods are described below. 1. #Import method
In the #import method, you need to provide the path and name of the type library you want, and VC can automatically generate a definition of GUIDS, and automatically generate a package to the ADO object. For any reference type library, VC will automatically generate two files when compiling:
● Header file (.tlh): contains the type of type and the definition of objects in the type library;
● Implement file (.TLI): Package the method in the type library object model.
For example, after the STDAFX.H file, the VC will generate two files in Msado15.TLH and Msado15.tli.
#Import can use a new class _com_ptr_t, which is also known as the smart pointer. Smart pointers can automatically execute quyerinterface, addref, and release functions.
The following code demonstrates how to use #import to implement the ADO in your application:
#Import "c: / program files / compon files / system / ado / msado15.dll" / no_namespace
Rename ("EOF", "Adoeof")
Renaming EOF is necessary because typical VC applications have defined EOF as constant -1.
In general, an automated object needs to define and initialize a variable used to operate. Can be used with intelligent pointers
The constructor of (_COM_PTR_T) passes an effective CLSID or ProgID, or the object can also be defined by the _com_ptr_t :: createInstance () method. The specific code is as follows:
_ConnectionPtr Conn1 (__UUIDOF (Connection));
You can also use the following code to achieve the same function:
_ConnectionPtr Conn1 = null; // Define objects
HRESULT HR = S_OK;
// Create an instance
HR = conn1.createInstance (__UUIDOF (Connection);
It is recommended to adopt a second method, because the first way cannot return a failure HRESULT, so the ADO connection object cannot be judged to be successful or failed, and the reason for failure. Note that the connection here in the __UUIDOF (Connection) is defined in the .tlh file. By passing it to method createInstance, you can create a valid AdoConnection object.
Need to note is #import's NO_NAMESPACE attribute, which tells the compiler that this class is not in a separate namespace. Using NO_NAMESPACE means that you don't need to reference the namespace when initialization variables. Of course, if you need to import multiple type libraries in your application, you should not use NO_NAMESPACE to avoid the name conflict.
Below is a simple example code that adopts the CIMPORT method:
#Include
#Import
void main ()
{
HRESULT HR = S_OK;
// Because no NO_NAMESPACE is specified in #import, you must use the form of ADODB :: this form to define variable types.
AdoDB :: _ RecordSetPtr rs1 = null; // Create an ADO connection via ODBC
_BSTR_T Connect ("DSN = Adodemo; UID = SA; PWD =;");
_BSTR_T SOURCE ("SELECT * from authors");
Coinitialize ();
// Initialize the RS1 object
HR = rs1.createInstance (__UUIDOF (adouteb :: rordset);
/ / Omitted the judgment of the return value HR
RS1-> Open (Source,
Connect,
Adodb :: AdopenForwardonly,
Adodb :: AdlockReadonly,
-1 );
// You can add code for the record set RS1.
RS1-> close ();
RS1 = NULL;
:: MessageBox (Null, "Success!", "", MB_OK);
Couninitialize ();
}
2. Create an ADO application with MFC OLE
MFC OLE is also able to encapsulate (Wrapper) a type library, but unlike #import, it cannot generate an enumeration type from the type library. The MFC class CString and Colevariant hide the details of BSTRS and Variants. The classes generated by the MFC OLE inherit the class ColedgeDispatchDriver, and the failing HRESULTS generated by the ADO is packaged in the ColedispatChexception.
The steps to create an ADO application with MFC OLE ClassWizard are as follows:
● From the Tools menu, select the Directories Tab entries in the Options option, add the path C: / Program Files / Common Files / System / ADO in the library files in Show Directories, and set the path to the ADO type library.
● From the View menu, activate ClassWizard, click the Add Class button and select "from a type library ..." option, then in the Type Library Dialog Box dialog, from C: / Program Files / Common Files / System / ADO Select the file msado15.dll, in the Confirm Classes dialog, select all listed classes and press the OK button to exit the ClassWizard. In this way, ClassWizard generates two files MSADO15.H and MSADO15.cpp.
Below is an example code for implementing the ADO application:
// Initialize COM object
Afxoleinit ();
...
// Define Data Set Objects
_Recordset RS1;
ColeException E;
Colevariant Connect ("DSN = Adodemo; UID = SA; PWD =;");
Colevariant Source ("SELECT * AUTHORS");
// Create a dataset object
Rs1.createdispatch ("AdoDb.RecordSet.2.0", & E);
RS1.Open (Variant) Source,
(Variant) Connect,
0, 1, -1);
// You can add code for the result set RS1.
Rs1.close ();
Rs1.releaseDispatch ();
AfxMessageBox ("Success!");
3. Create an ADO project with COM API
#Import and MFC OLes have a package class around a given automation object, which inherit itself from _com_ptr_t and ColedgeDispatchDriver. In fact, the ADO object can also be initialized by using the Windows API function. To use the ADO and COM objects directly, you need to add two header files adoid.h and Adoint.h, which defines the enumeration types required for CLSIDS, interface definitions, and operation ADO type libraries. In addition, it is necessary to add header file initguid.h. In order to be able to compile the ADO project file created with COM API, you also need to install OLE DB SDK in the machine or the MSDASDK tool. Below is a simple sample code for creating ADOs using the API:
#Include
#Include
#Include "adoid.h" // ADO GUID's
#Include "adoint.h" // ADO class, enumeration, etc.
void main ()
{
HRESULT HR = S_OK;
// adorecordset is defined in AdOint.h
AdorecordSet * RS1 = NULL;
Variant Source;
Variant connect;
Variantinit (& SOURCE);
Variantinit (& Connect);
Source.vt = vt_bstr;
Source.bstrval = :: sysallocstring (l "select * from authors);
Connect.vt = vt_bstr;
CONNECT.BSTRVAL = :: sysallocstring (l "dsn = adodemo; uid = sa; pwd =;");
HR = CocreateInstance (CLSID_CADORECORDSET,
NULL,
CLSCTX_INPROC_SERVER,
IID_IADORECORDSET,
(Lpvoid *) & rs1);
If (succeeded (hr)) hr = rs1-> open
(Source,
Connect,
AdopenForwardonly,
AdlockReadonly,
-1 );
/ / Treatment of the recordset RS1
IF (succeededed (hr)) hr = rs1-> close ();
IF (succeededed (hr)) {r r1-> release (); rs1 = null;}
If (ac)) :: MessageBox (Null, "Success!", "", MB_OK);
}
C extensions
If ADO applications developed with C , ADO C Extensions should be used. We know that using VB or VBScript to operate ADO is very convenient, but if you use C or Java, you must handle data structures like Variants to implement conversion and C data structure, and this processing is undoubtedly all C Developers are very painful. However, if you use C Extensions, ADO does not need to get column information from the data provider, but use the column information provided by the developer at design time. The following is a simple example: // Create and specific records corresponding to
Class Cauthor: Public Cadorecordbinding
{
Begin_ado_binding (ccustomrs1)
ADO_VARIABLE_LENGTH_ENTRY4 (1,
Advarchar, m_szau_id, sizeof (m_szau_id), FALSE
ADO_VARIABLE_LENGTH_ENTRY4 (2,
Advarchar, m_szau_fname, sizeof (m_szau_fname), FALSE
ADO_VARIABLE_LENGTH_ENTRY4 (3,
Advarchar, m_szau_lname, sizeof (m_szau_lname), FALSE
END_ADO_BINDING ()
protected:
Char M_SZAU_ID [12];
CHAR M_SZAU_FNAME [21];
Char M_SZAU_LNAME [41];
}
void fetchauthorda ()
{
Cauthor author;
// record set objects
_RecordSetPtr PRS;
Iadorecordbinding * piadorecordbinding;
// Get the COM object interface pointer
PRS.CREATEINSTANCE (__ uuidof (recordset);
// Get the required recordset
PRS-> Open ("SELECT AU_ID, AU_FNAME, AU_LNAME FROM EMPLOYEES", "Provider = SQLOLEDB; DATA SOURCE = SURESHK1; DATABASE = PUBS; user ID = sa; password =;",
AdopenForwardonly,
AdlockReadonly,
AdcmdText);
// Query interface Iadorecordbinding
PRS-> queryinterface (__ uuidof (ipvoid *), (lpvoid *) & piadorecordbinding;
// Bind object
Piadorecordbinding-> BindtorecordSet (& Author);
// Get the relevant content in the record
While (variant_false == PRS-> EOF) {
Printf ("% s% s% s", author.m_szau_id,
Author.m_szau_fname, author.m_szau_lname);
PRS-> MOVENEXT ();
}
// Release the object
Piadorecordbinding-> Release ();
}
