I use ActiveX Data Object (ADO) to start with version 1.5, that is already a very old version. Many things have changed in the current version. I have learned a lot of new things from every version upgrade. These things you can't all from the book, or at least say, you can't find it from one place.
I carefully choose and summarize these ADO development points and techniques. Some of these problems may be the problem you have always thought of; some is the technology you never understand; there are some knowledge of ADO development.
First, share connection objects
When passing the connection string to Command, RecordSet, or Record object, you have a Connection object every time you hiburnly command ADO:
Dim Rec1 as adoDb.record
Dim Rec2 as adoDb.record
Dim Rec3 as adoDb.record
SET REC1 = New AdoDb.Record
Rec1.open "LocalStart.asp", "URL = http:// localhost /"
SET REC2 = New Adodb.Record
Rec2.open "Global.asa", "URL = http: // localhost /"
SET REC3 = New AdoDb.Record
Rec3.open "Iisstart.asp", "URL = http:// Localhost /"
'Do some operations
Rec1.close
REC2.CLOSE
Rec3.close
SET REC1 = Nothing
SET REC2 = Nothing
SET REC3 = Nothing
In order to save resources, you should first create a Connection object and pass it to all objects that require the active connection. That is, the above code should be changed to the following form:
DIM Con As Adodb.Connection
Dim Rec1 as adoDb.record
Dim Rec2 as adoDb.record
Dim Rec3 as adoDb.record
Set con = new adodb.connection
Con.open "URL = http:// localhost /"
SET REC1 = New AdoDb.Record
Rec1.open "LocalStart.asp", Con
SET REC2 = New Adodb.Record
Rec2.open "Global.asa", Con
SET REC3 = New AdoDb.Record
Rec3.open "Iisstart.asp", Con
'Do some action'
Rec1.close
REC2.CLOSE
Rec3.close
Con. close
SET REC1 = Nothing
SET REC2 = Nothing
SET REC3 = Nothing
Set con = Nothing
Second, read the Connectionstring property
From any open Connection object, including the Connection object returned by the ActiveConnection property of RecordSet, Command, or Record object, you can always read the Connectionstring property.
DIM COM as adodb.command
DIM RST As Adodb.Recordset
Set com = new adoDb.commandcom.activeConnection = _
"Provider = microsoft.jet.Oledb.4.0;" & "data source = nwind.mdb;"
com.commandtext = "SELECT * from Customers"
SET RST = Com.execute
Msgbox com.activeconnection.connectionstring
Rst.Close
SET RST = Nothing
Set com = Nothing
When the above code is run, you will see the following output from the message box:
Provider = Microsoft.jet.OleDb.4.0;
PASSWORD = ""
User ID = admin;
Data Source = nwind.mdb;
Mode = share deny none;
Extended Properties = ""
Jet OLEDB: System Database = ""
Jet OLEDB: Registry Path = ""
Jet OLEDB: Database Password = ""
Jet OLEDB: Engine Type = 4;
Jet OLEDB: Database Locking Mode = 0;
Jet OLEDB: Global Partial Bulk OPS = 2;
Jet OLEDB: Global Bulk Transactions = 1;
JET OLEDB: New Database Password = ""
Jet OLEDB: CREATE SYSTEM DATABASE = FALSE
Jet OLEDB: Encrypt Database = FALSE
Jet OLEDB: DON 'TOPY LOCALE ON Compact = false;
Jet OLEDB: Compact without replica repair = false;
Jet OLEDB: SFP = FALSE
Now you can analyze this string to identify specific information about the connection, such as whether it will be encrypted when the database is compressed (Jet OLEDB: Encrypt Database property).
Third, use dynamic properties
The Properties collection of the Connection object can be used to set up vendor-specific options, such as the PROMPT dynamic properties of the SQL Server OLE DB driver.
DIM Con As Adodb.Connection
Set con = new adodb.connection
Con.Provider = "sqloledb"
Con.properties ("prompt") = adpromptalways
Con.open
'Tips Users Select Database'
Con. close
Set con = Nothing
When the above code runs, the user will see a dialog that allows the user to select which database to log in.
Fourth, I chose the position of the cursor
When choosing the position of the cursor, you must consider which services are more important for the current connection.
If the data provider's service is exactly what you need, you should use the server-side cursor. These services are the services provided by the data source driver, which usually has very good scalability. In addition, by retaining the server-side game, you don't need to send all the data to the client as like using the client game. On the other hand, local cursor services, such as Microsoft Data Shape Services for OLE DB, capable of providing some client cursors unique service. To make these services can work, the data must be sent to the local machine, just as required by the data shape service.
You can use the connection.cursorLocation property to set the cursor position, but choose to be sensible and cautious.
V. I chose the type of cursor in wisely.
Selecting the type of cursor is equally important to select the position of the cursor. There are four types of cursors, each of which has their own advantages and disadvantages.
Static Cursor (Static Cursor) provides a snapshot of data at a given moment. In this type of cursor, data changes (including other users' data increase or delete operations) are always not visible. The Static cursor is used to make a report ideal because the production report requires a consistent, no view of the data, but the Static cursor is not fast. Since changes in data are not displayed, the service provider must create and maintain a copy of the data for a given time for each connection using the STATIC cursor.
The Forward Only cursor (only the forward cursor) is basically the same as the static cursor, and the difference is that you can only move the data forward, but you cannot rearward. Compared to the Static cursor, this limit is conducive to improve performance, but it still requires data sources to maintain a temporary copy of a data, so that other users will not affect your data.
Dynamic Cursor (Dynamic Cursor) Allows you to see other users' modifications and delete operations, and you can freely move over the entire recordset. Unlike Static and Forward Only cursors, Dynamic cursors do not require data sources to maintain a static image of data, so the Dynamic cursor is fast than the first two cursors.
The last cursor type is a KeySet cursor (key set cursor). The KeySet cursor is very similar to the Dynamic cursor, and the difference is that you can't see new records of other users. In the KeySet cursor, other users deleted records will also be accessible. Like the Dynamic cursor, you can also see other users in the KeySet cursor. The KeySet cursor may be fast than the Dynamic cursor, because the KeySet cursor does not need to check whether there is a new record to join, whether there is a record deleted (because the new record is invisible, the deleted record is not accessible).
Consider each reason, then choose the type of cursor for you.
Sixth, handmade constructor
When the performance factor is important, please define the parameters:
DIM Con As Adodb.Connection
DIM COM as adodb.command
DIM PAR as adodb.parameter
DIM RST As Adodb.Recordset
Set con = new adodb.connection
Con.open "provider = SQLOLEDB;" & "Server = localhost;" _
& "Initial Catalog = Northwind;" & "User ID = SA;"
SET COM = New AdoDB.Command
Set com.activeconnection = con
Set par = com.createparameter ("categoryname", advarwchar, _adparaminput, 15)
com.Parameters.Append Par
Set par = com.createparameter ("ORDYEAR", Advarwchar, _
Adparaminput, 4)
com.Parameters.Append Par
com.Commandtext = _
"Execute Salesbycategory 'Product', '1997'"
SET RST = Com.execute
'Do some action'
Rst.Close
Con. close
Set com = Nothing
SET RST = Nothing
Set con = Nothing
After using manual definition parameters, ADO does not have to query data sources for the parameter list of stored procedures. This is not important when the user performs only a query process and does not have high requirements for stored procedures; however, if the user wants to perform a large number of stored procedures, and I hope to get answers immediately, then this is very important.
Seven, build cache with stream object
Stream objects can be used without physical data sources. You can use this object to create a cache in the memory of the local machine. The usage is very simple, just create an instance of the Stream object first, then you can start writing data:
Dim Str as adodo.stream
Set str = new adodo.stream
Str.Open
Str.Writetext "This is text information,"
Str.Writetext "We hope it remain"
Str.WriteText "Memory.", AdWriteline
Str.Writetext "This is the second"
Str.WriteText "line", AdWriteline
Msgbox "Cache" & _
Str.Size & "A character"
Str.Position = 0
Msgbox "Cache Content:" & str.ReadText
Str.close
In addition, you can also use Stream object to handle binary data, just replace the WriteText and ReadText methods for operating text with WRITE and READ methods. After putting the data into the cache, you can save data with the SaveTofile method.
8. Check the warning information
The ERRORS collection of the Connection object is not only used to report the data provider that occurs when an operation is executed, but it is also used to indicate the non-poorer warning information that appears during the operation.
Connection.Open, Record.cancelbatch, Record.Resync, and RecordSet.Updatebatch methods, there is also a RECORDSET.FILTER attribute that can generate warning messages.
To detect the warning information (or error message) of the data provider, call the connection.errors.clear method before starting an action using any of the above methods; after the operation is completed, check whether there is any warning information with the ownt property of the ErrorS collection.
Nine, business nested
When using Jet Ole DB Provider, you can nescery, up to five floors. Using multi-layer transactions will give you unprecedented data control capabilities.
DIM Con As Adodb.Connection
DIM Ilevel AS Integerseet Con = New Adodb.connection
Con.cursorLocation = aduseclient
Con -.open "provider = microsoft.jet.Oledb.4.0;" _
& "Data Source = nwind.mdb;"
Con.Begintrans
'Change 1
Con.Begintrans
'Change 2
Con.Begintrans
'Change 3
Ilevel = con.begintrans
'Change 4
Msgbox "Level" & Ilevel
Con.commitTrans
Con. RollbackTrans
Con.commitTrans
Con.commitTrans
Con. close
Set con = Nothing
In this example, the modifications 1 and 2 will be successful, the modifications 3 and 4 are invalid. The surface of the change 4 has been submitted, but due to the third layer of transaction rollback, it causes the transactions in all its transactions to be rolled back.
Ten, pay attention to data shape
I want to introduce the last trick to you not to underestimate the power of Microsoft Data Shaping Service for Ole DB. Data Shaping (Data Shape) Allows you to aggregate multiple SQL statements, construct a layered recordset. In the hierarchical record set, a single field can point to the entire sub-recordset.
For example, if there are two tables from the BIBLIO database, a sample (Biblio database is a sample of Microsoft, you can download it from here), you can construct the SQL command as described below to connect them to a recordset.
Select Publishers.name, Titles.Title
From publishers
Inner Join Titles on
Publishers.pubid = titles.pubid
ORDER by Publishers.name, Titles.Title;
The previous record is as follows:
Name (PUB) Title
-----------------------------------------
A K Peters a Physical Approach To Col ...
A k peters colour Principles for c ...
A System Pubns C Plus Plus Reference Card
A System Pubns C Reference Card
Aa Balkema Planning with Linear Progr ...
Aarp THESAURURUS OF AGING TERMIN ...
ABACUS Access 2.0 Programming Bible
ABACUS Advanced Access Programing
It can be seen that there is a lot of repetition data in this record. With data shape technology, we can greatly reduce the size of the result data. The following table is sent to the client and passed to the data shaped cursor service.
Name (Publisher)
------------------------------------
A k peters
A System Pubns
Aa Balkema
AARP
ABACUS
Title
------------------------------------
A Physical Approach to Color ...
Colour Principles for Computer ...
C Plus Plus Reference Cardc Reference Card
Planning with linear programming
THESAURURURURURURUS OF AGING TERMINOLOLOLOGY: ...
Access 2.0 programing bag
Advanced Access Programing
In the data shaped cursor service, the two tables are connected to a hierarchy through the Chapters field type, and the chapter field type is used to access the sub-recordset.
DIM Con As Adodb.Connection
DIM RSTPUBS as adodb.recordset
DIM RSTTITLES As Adodb.Recordset
DIM SSHAPE AS STRING
Set con = new adodb.connection
Set rstpubs = new adoDb.recordset
Con.Provider = "msdatashape"
Con -.open "Data Provider = Microsoft.jet.Oledb.4.0;" _
& "Data Source = Biblio.mdb;"
SSHAPE = "Shape {SELECT NAME, PUBID" _
& "From publishers}" _
& "Append ({SELECT TITLE, PUBID" _
& "From titles}" _
& "As pubtitles" _
& "Relate Pubid to Pubid"
Rstpubs.open sshape, con
DO Until (Rstpubs.eof)
Debug.print Rstpubs! Name
Set rsttitles = rstpubs ("pubtitles"). Value
Do Until (Rsttitles.eof)
Debug.print "" & RSTTITLES! TITLE
Rsttitles.movenext
Loop
Rstpubs.movenext
Loop
Rstpubs.close
Con. close
SET RSTPUBS = Nothing
Set con = Nothing
When we run the above code, we will see the following output:
A k peters
A Physical Approach to Color ...
Colour Principles for Computer ...
A System Pubns
C Plus Plus Reference Card
C Reference Card
Aa Balkema
Planning with linear programming
AARP
THESAURURURURURURUS OF AGING TERMINOLOLOLOGY: ...
ABACUS
Access 2.0 programing bag
Advanced Access Programing
It can be seen that the function of the data shape is very powerful.
