ADO programming tips and tricks
Microsoft introduced ADO and Active Server Pages (ASP) in 1997 as the primary means of building data-driven Web applications. Since then, ADO has become the standard API developers use to work with databases for applications built with Microsoft tools and technologies. Micro-soft also touts ADO as the standard API for the future. These tips and examples will help you understand and use ADO.
How ADO Works
Because ADO is a set of COM objects, you must use either CreateObject or the Object tag to instantiate the objects before you can use them. (I discuss using CreateObject later in this article.) Then, if you're programming in Visual Basic (VB), you'll need to create a project reference to the ADO library, as Screen 1, page 32, shows. Next, in your VB program, you need to create an object variable of the appropriate type for the object you want to use. VB will use early binding to the ADO object.
After you've set up the project reference, VB will read the ADO type library and use the constants defined in the library. For example, when you use the adOpenForwardOnly constant in your VB program, VB finds this constant's definition in the ADO type library. If you're using ADO from ASP instead of VB, you need to take a different approach. ASP doesn't allow explicit variable definitions, so it can't use early binding. However, you can create a reference to the ADO type library in the global.asa file. For example, using Visual InterDev you can create a project reference to the ADO type library. Visual InterDev will place the following entry in the global.asa file:
<!--METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data
Objects 2.1 Library" UUID="{00000201-0000-0010-8000-
00AA006D2EA4}" VERSION="2.1"--!>
Creating such a reference in the global.asa file lets you use ADO type library constants in your ASP code without an include file. This capability makes your code more readable, and it might execute faster. When you use the ADO type library constants, your program doesn't have to load the constants as an include file. This approach can make a big performance difference when you load ASP pages from disk.
Creating Explicit or Implicit Connections
ADO needs an explicit or implicit connection to connect with SQL Server. To create an explicit connection, you use the ADO Connection object to create the connection, then you use that connection with a Recordset or Command object. To create an implicit connection, instead of creating a Connection object and passing it to the command object, you simply specify the connection string when you create the Command or Recordset object.
Listing 1 shows how you can use a Connection object explicitly. When the application starts, CreateObject creates the ADO objects. Then the code sets the ConnectionString property of the Connection object to the connection string for the Pubs database. Next, the connection opens, and the program passes the Connection object to the Command object when it sets the Command object's ActiveConnection property.
One benefit of explicitly creating the Connection object is flexibility. You can easily set properties for the connection, such as its timeout parameter or the cursor or location at the Connection object. You can also pass the Connection object to one or more commands or recordsets.
Many developers use a shortcut way of creating connections. I call this approach an implicit connection, and Listing 2 illustrates it. Instead of creating a Connection object and passing it to the Command object, you can simply specify the Connection string when you create the Command or Recordset object, as the statement at Callout A in Listing 2 shows.
The only difference between the explicit and implicit ways of setting the connection is that when you set it implicitly, you don't have a separate Connection object to work with. You can also create a connection implicitly when you open a recordset. Instead of using the Recordset object in the way Listing 1 and Callout B in Listing 2 show, you can use
rs.Open "select * from titles", sDSN ,
adOpenForwardOnly, adLockReadOnly
With this one line of code, you can specify the SQL and connection string cleanly without extraneous commands. However, you have less flexibility when you use this single line of code than when you treat the connection as a separate object and pass it from one command or recordset to another. If you create a Connection object, you can use it for many connections. This method lets you save code and localize the database connection information to one connection.
Another consideration with this approach is that the last Open statement doesn't specify the Options parameter. The Options statement lets you specify the command type. Setting the command type optimizes ADO's performance because ADO doesn't have to do as much work to execute the command. When you don't specify the command type, ADO must query the provider each time the command executes to determine which type of command it is. You can also specify the command type of the Command object by using the CommandType property.
Connection and command objects are flexible. If your application uses COM objects, you can hide connection information in a COM object and pass the connection to other COM objects and ASP script. This capability gives you security and flexibility. The Connection object works well because of this flexibility, but you can choose how you want to implement connections in your code.
Prev. page
[1]
2
3
next page 
