The ADO Object Model - The Main ADO Objects
(Page 2 of 9 )
The Connection Object
The Connection is actually the hub of ADO, because it provides the methods that allow us to connect to a data store. Note that when you create a Recordset or Command object, it is not necessary to create a connection first, because a Connection object is automatically created for you behind the scenes. What this means is that you can actually create a recordset or run a command with just a single line of code. For example:
rsAuthors.Open "authors", "DSN=pubs", adOpenStatic,
adLockReadOnly, adCmdTable
This creates a recordset on the authors table from the pubs database, based on the ODBC Data Source Name (DSN) pubs. Although this example is using a Recordset object, it actually creates a connection underneath. In fact, you can create a separate and explicit Connection object from the one automatically created by ADO :
Set conPubs = objRec.ActiveConnection
This sort of mix-and-match approach to connection creation frees the programmer from a rigid structure and allows you to write the code in a way that is naturally easier.
Although this looks simple, you create a new connection every time you use a connection string to establish a connection to a data store (subject to connection pooling—see Chapter 3). If you will be running several commands or creating several recordsets, I recommend that you create a Connection object directly:
conPubs.Open "DSN=pubs; UID=sa; PWD="
or:
conPubs.Open "Provider=SQLOLEDB; Data Source=Piglet; " & _
"Initial Catalog=pubs; User Id=sa; Password="
You can then use this Connection throughout your code without worrying about the number of connections you create. Not only is this more efficient, but it’s faster too; once a connection to a data store is established, it can be reused.
One area where you shouldn’t try to use a single connection is in the Session or Application scope in ASP pages. Creating an ADO object and storing it in a Session or Application variable has serious performance penalties if you want your ASP pages to scale. You can, however, store the connection string in Session or Application scope.
Once you have decided upon the provider, the properties of the Connection object allow you to see what kinds of facilities are available from the data provider. For example, picking the SQL Server provider allows you to see some of the properties for that provider, even before the connection is opened:
conPubs.Provider = "SQLOLEDB"
For Each objProp in conPubs.Properties
Print objProp.Name
Next
Because ADO is designed to run against different data stores, it’s often worth checking that a particular function is supported before you call it. You do this by examining the Properties collection of the Connection object. This is particularly useful when using ADO as part of a development or query tool that gives users the ability to connect to different data stores. You could also, for example, use some of the properties to define a schema if manually creating XML from the data.
The Connection object isn’t just about connecting to, and holding information about, a data store. You can actually run commands and create recordsets with a Connection object too. For example:
conPubs.Execute "UPDATE titles SET price = price * 1.10", _
,adCmdText + adExecuteNoRecords
This would run a SQL UPDATE query. Alternatively, to run a command and return a read-only recordset:
Set rsAuthors = _
objConn.Execute ("SELECT * FROM authors", , adCmdText)
So, not only can you use a Recordset object to connect to a data store, but you can also use a Connection object to create recordsets. It’s all part of the “code as you want” strategy.
An ADO operation may generate more than one error, and these are stored in the Connection object’s Errors collection, which is discussed in more detail in Chapter 8. Although you might think this breaks the flexibility of the object model, it gives a central place to find error information. Because the Recordset and Command objects have a connection underneath, it is extremely simple to access this error information without creating a Connection object.
The Command Object The Command object is designed to run SQL statements (assuming the provider supports the Command object), especially those that require parameters. This is an important point, because the use of stored queries and procedures is a great way to improve speed and segment your application, and it fits naturally with Windows DNA.
Like the Connection object, the Command object can be used to run action queries that don’t return a recordset, and for creating recordsets. Unlike the Connection, however, you’ll need a couple more lines of code, because you cannot specify the command text in the same line as the line that specifies the command type:
objCmd.CommandText = "UPDATE titles SET price = price *
1.10"
objCmd.CommandType = adCmdText + adExecuteNoRecords objCmd.Execute
To return a recordset, you use a similar style:
Set rsAuthors = objCmd.Execute
The greatest difference in command execution comes when using the Parameters collection, because this allows you to use stored procedures, queries, and template and parameterized queries saved in the data store. This removes the SQL code from your program, making it faster and easier to maintain. Using stored procedures, you can have SQL statements in a compiled form that perform a set of actions using the values you supply as parameters.
With a Command—if the data provider supports this facility—you can also save the command details for execution at a later date. This is known as a prepared statement, and it allows the precompiled command to be run many times within the same connection. Once you’ve finished with your connection, the saved commands are deleted automatically. You can also use a Command object more than once, and on different connections if you require (assuming that the command can be run on another connection). Just change the ActiveConnection property of the command to point to the new connection, then run the command again. If you wish to reuse the Command object, it’s wise to close any active recordsets that were created as a result of the command’s execution. This ensures that memory is deallocated correctly.
Another feature allows you to turn the command into a method of the Connection object, allowing you to build up a Connection object that contains many new methods. This is quite useful in client/server scenarios, because you could attach different commands to the connection depending upon user privilege. This is explained in more detail in Chapter 4.
Unlike the Connection and Recordset objects, which must be supported by every OLE DB provider, a provider’s decision to support the Command object is entirely optional. Consequently, not all OLE DB Providers support the Command object. |
The Recordset Object
The Recordset object is probably the most frequently used object in ADO; consequently, it has more properties and methods than other objects. Like the Command object, a Recordset can exist on its own or be attached to a Connection:
rsAuthors.Open "authors", "DSN=pubs", adOpenStatic, _
adLockReadOnly, adCmdTable
This opens a recordset using a new connection. To use an existing connection, simply substitute the connection string for the existing Connection object:
rsAuthors.Open "authors", conPubs, adOpenStatic, _
adLockReadOnly, adCmdTable
This is the preferred method if you are creating several recordsets, because it means that the connection to the data store doesn’t have to be established each time you create a recordset.
Another important point about the Recordset object is that it’s the only way to specify the recordset’s cursor type and lock type. If you use the Execute method of the Connection or Command object, it gives you the default cursor type, a read-only, forward-only cursor (often referred to as a firehose cursor, because the data is just “squirted” to the recordset). If you need to use a Command and require a different cursor type, then you can specify the Command object, instead of the table name or query text, as the source of the Recordset.
You use recordsets to examine and manipulate data from the data store. The Recordset object gives you facilities to move about through the records, find records, sort records in a particular order, and update records. You can perform updating in two modes: directly (changes are sent back to the data store as they are made) or in batches (changes are saved locally and then sent back to the data store all at once).
In client/server applications, you can also pass recordsets between the business-logic tier and the user-interface tier, where they can be manipulated locally. This saves database resources, which can be critical on large-scale applications, and it minimizes network traffic. These recordsets are called disconnected recordsets.
The Record Object The Record object was introduced in version 2.5, and is used in conjunction with a new type of OLE DB Provider, called Document Source Providers. Document Source Providers are designed to enable access to data stores of semistructured data, such as file or mail systems. It was necessary to create a new object because of the way data is stored. In relational data stores, such as SQL Server or Access, we work using set-based data, where every row is in a rowset and has the same structure as the other rows. Semistructured data, on the other hand, doesn’t follow this pattern, because each row might have a different structure. If you think about the structure of a Web site, then you’ll start to understand how this works. A Web site generally consists of directories and files. If you look at the top-level directory for a site, you might see something like this:
| File Name | Type |
| default.htm | File |
| Images | Directory |
| menu.xml | File |
All three items here have some common properties, such as a name. However, they also have different properties; the directory doesn’t have a size, but must indicate that it contains other files. These different properties are why we need a different object to cope with this sort of data. When dealing with semistructured data, the Record object maps onto individual files, and the Recordset object is mapped onto a collection of files.
To access a directory through a Record object, use the following syntax:
Dim recRoot As New ADODB.Record
recRoot.Open "", "URL=http://localhost"
recRoot.Close
Specifying URL= tells ADO to use the Internet Publishing Provider.
The Record object not only allows access to files, but also gives management over them too. The CopyRecord, MoveRecord, and DeleteRecord methods give the ability to control files remotely.
The Stream Object The Stream object is a wrapper around a memory manager (in fact it’s based around the COM IStream interface) and gives control over the contents of the memory used to store an object. This might sound rather complex, but it has wide-ranging uses and is simple to use.
You can use a Stream in several ways. One way is in conjunction with a Document Source Provider (such as the OLE DB Provider for Internet Publishing) to provide access to file contents on a Web server. For example:
Dim stmFile As New ADODB.Stream
Dim sContents As String
stmFile.Open "URL=http://localhost/postinfo.html"
stmFile.Charset = "ascii"
sContents = stmFile.ReadText
stmFile.Close
This simply opens a file and reads the contents into a string. You also get the ability to write data out to the stream, where it is saved automatically to the file. This gives the ability to perform editing on remote files.
Stream objects have other roles. They can be used when saving and opening recordsets, and in conjunction with the Microsoft XML Parser.
 | This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today. Buy this book now! |
Next: The Other ADO Objects >>
More ASP.NET Articles
More By Apress Publishing
/ 14 
