I
n most applications involving SQL Server, not all of the development is done on
the server itself. This is the essence of client-server computing: Work is parti-
tioned between a central server and distributed clients. To view and modify
server-side data from a client application, one uses a client data-access library.
Over the years, Microsoft has released a number of client data-access libraries that
can use SQL Server data, including DB-Lib, Data Access Objects (DAO), and Remote
Data Objects (RDO). Although all of these libraries are still in use, they’re no longer
undergoing active development. Instead, Microsoft recommends that all new client
applications use ActiveX Data Objects (ADO) to interact with the server.
ADO is the only client data-access library that we’re going to cover in this book.
Even if you’ve used another library for that purpose in the past, you should consider
migrating to ADO to take advantage of current advances in the state of the art. In this
chapter, we’ll start by describing the ADO object model and then take a look at what
you can do with ADO. We’ll close with a brief section on ADOX, an ADO extension
designed to help you work with schema information.
ADO provides an object model atop the OLE DB interface, which is the low-level
“plumbing” that SQL Server uses between its own components. Because of this inti-
mate connection, ADO is a good choice for working with data stored in SQL Server.
The ADO Object Model
Figure 19.1 shows the ADO object model for ADO 2.6, the version that ships with SQL
Server 2000. An object model lists the various objects that a library contains and shows
their relationships. As you can see, the ADO object model is fairly simple.
FIGURE 19.1
The ADO object
model
Connection
Command
Parameters Record
Fields
Recordset
Fields
Stream
Errors
2627ch19.qxd 8/22/00 11:11 AM Page 724
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
725
In addition to the objects shown in Figure 19.1, the Connection, Command,
Parameter, Recordset, Record, and Field objects each have a Properties collection of
Property objects. This enables your code to easily enumerate the properties of
these objects. Objects shown in Figure 19.1 with multiple boxes are collections.
For example, the Command object contains a Parameters collection containing
individual Parameter objects.
NOTE Despite the simplicity of the ADO object model, ADO offers quite a bit of complex-
ity in its operations, because there are many alternatives for performing basic operations, as
well as lots of optional arguments. In this chapter, we’ll provide the basics of ADO to get you
started. For more details, refer to the Microsoft Data Access Components SDK. You can
download a copy of this SDK, which has the complete documentation for all ADO objects,
from the Microsoft Universal Data Access Web site at http://www microsoft com/data.
Understanding Objects
Before we dig into the objects offered by ADO, let’s step back a bit and talk about the
concept of an object in programming. In software development, an object represents a
package of functionality provided for client programs to use. Usually an object repre-
sents some “thing” within a particular piece of software. Objects have methods (activ-
ities they can perform), properties (characteristics that describe the objects), and
events (occurrences that can cause the object to invoke your code) that are set by the
provider of the object. As an application developer, you can use those methods and
properties to interact with the original product.
For example, ADO includes an object called a Recordset. This object represents a
set of records (for example, the results of a query) from a data provider. A Recordset
object can be used to represent any set of records. The Recordset object has methods,
such as MoveFirst (which positions an internal pointer to the first record in the
Recordset) and MoveLast (which positions an internal pointer to the last record in the
Recordset). It also has properties, such as RecordCount (the number of records in the
Recordset) and EOF (a Boolean property that indicates the last record of the Recordset
has been retrieved). The Recordset object also has events, such as FetchComplete,
which occurs when all of the records from an asynchronous operation are available in
the Recordset.
Objects can be arranged in collections, which are groups of similar objects. For
example, in ADO there is a Parameters collection of Parameter objects. You can use a
THE ADO OBJECT MODEL
Development with
SQL Server
PART
V
2627ch19.qxd 8/22/00 11:11 AM Page 725
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 19 • ADO AND SQL SERVER
726
collection to view each object in turn of a similar group. This is called iterating
through the collection.
Objects can also contain other objects. For example, the ADO Recordset object
contains a collection of Field objects, each of which represents one of the individual
fields in a record in the Recordset.
Objects provide an abstract view of the underlying software. It’s unlikely that
there’s actually a data structure within SQL Server that you could point to and say,
“This is a Recordset.” By manipulating Recordsets in your code, though, you can
access many of the abilities of SQL Server to retrieve and modify data. The Recordset
object provides a convenient abstraction for the underlying functionality of storing
and modifying data.
In the remainder of this section, we’ll discuss the objects that the ADO object
model provides. We’ll keep the discussion on an abstract level, without presenting all
of the methods and properties of each object. SQL Server Books Online includes an
exhaustive list of these methods and properties, as well as those of the other object
models that can be used with SQL Server.
Connection and Error
At the top of the ADO hierarchy, you’ll find the Connection object, which is associ-
ated with an Errors collection. Neither of these objects provides a direct connection to
data, but they’re both very important in working with other ADO objects.
The Connection object represents an open connection to an OLE DB data source.
You can create a Connection object and use it to create other objects further down the
ADO object hierarchy. However, if you need only a single Recordset object from a par-
ticular Connection, it’s probably more efficient to just create the Recordset directly,
which will create a Connection object implicitly. You should reserve explicitly creat-
ing actual Connection objects for situations where you’ll need to perform multiple,
diverse operations on the connection.
An Error object represents a single error. Because one data-access operation can
generate multiple errors, Error objects are contained in an Errors collection. If the last
operation succeeded, this collection will be empty. Otherwise, you can use the For
Each operator to examine each Error in turn.
Command and Parameter
The Command and Parameter objects are the basic query-building objects of ADO.
You can use them in various combinations to represent tables, SQL statements, or
stored procedures. You can use Command objects both for commands that return
2627ch19.qxd 8/22/00 11:11 AM Page 726
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
727
data and for commands that instruct SQL Server to do something, such as action
queries.
Think of a Command object as a single instruction to SQL Server to produce or
alter data. The easiest way to use a Command object is to create an independent
Command object, set its other properties, and then set its ActiveConnection property
to a valid connection string. This will cause ADO to create an implicit Connection
object for use by this Command only. However, if you’re going to execute multiple
Commands on a single Connection, you should avoid this technique, because it will
create a separate Connection object for each Command. Instead, you can set the
ActiveConnection property to an existing Connection object.
A Parameter object represents a single parameter for a Command object. This
might be a runtime parameter in a SQL query, or an input or output parameter in a
stored procedure. If you know the properties of a particular Parameter, you can use
the CreateParameter method to make appropriate Parameter objects for a Command
object, which allows you to initialize parameters without any server-side processing.
Otherwise, you must call the Refresh method on the Command object’s Parameters
collection to retrieve parameter information from the server, a resource-intensive
operation.
Recordset and Field
The Recordset and Field objects are the actual data-containing objects in ADO.
A Recordset object represents a set of records retrieved from SQL Server. Because
this is the object that allows you to directly retrieve data, it’s indispensable to ADO
processing. ADO allows you to open a Recordset object directly, or to create one from
a Connection or Command object. As you’ll see later in the chapter, Recordsets have
a variety of properties and behaviors depending on how they’re created.
A Field object represents a single column of data in a Recordset. Once you’ve
retrieved a Recordset, you’ll usually work with the Fields collection to read the data in
the Recordset. However, since the Fields collection is the default property of the
Recordset object, you won’t often see its name in your code. For example, if you’re
working in Visual Basic or a VBA host application, the following two lines of code
produce an identical result:
Recordset.Fields(0).Value
Recordset(0)
THE ADO OBJECT MODEL
Development with
SQL Server
PART
V
2627ch19.qxd 8/22/00 11:11 AM Page 727
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 19 • ADO AND SQL SERVER
728
Properties
The Property object is the building block of the other ADO objects. That is, properties
describe the other objects. Although you can iterate through the Properties collection
of ADO objects, there’s usually not any reason to do so unless you’re writing special-
ized tools to manipulate ADO code.
Record and Stream
For completeness, you should also know about two other objects introduced in
ADO 2.5, although these objects are not useful in working with SQL Server data.
The Record object is a dual-purpose object. It can represent a row in a Recordset. It
can also represent a file or folder in a file system. However, it’s important to realize
that these are not distinct features of the Record object. Rather, the Record object is
designed to represent a row in a Recordset when the underlying OLE DB provider nat-
urally supports a hierarchical data store. For example, Record objects can be used with
providers that supply information from file systems or e-mail storage. Record objects
can’t be used with providers that supply information from standard relational data-
bases (even if there’s a hierarchy within the database).
The Stream object represents binary data associated with a Record object. For
example, if you have a Record object representing a file in a file system, its associated
Stream object would represent the binary data in that file.
Because SQL Server is a relational database, it doesn’t support Record or Stream
objects.
Understanding Cursors
You learned about T-SQL cursors in Chapter 8. A cursor, you’ll recall, is a set of records
along with a pointer that identifies one of these records as the current record. ADO
also supports cursors, in the form of the Recordset object. When you open a Recordset
object to contain a set of records, ADO identifies a particular record as the current
record. Thus, if you talk of cursors in an ADO context, you’re normally talking about
Recordsets.
Unlike T-SQL cursors, though, ADO cursors can have a variety of different behav-
iors, depending on the properties you set for the Recordset object. In this section,
we’ll discuss the three key properties that control ADO cursor behavior:
• CursorLocation
• CursorType
• LockType
2627ch19.qxd 8/22/00 11:11 AM Page 728
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
729
CursorLocation
The CursorLocation property can be set to either adUseServer, for server-side cursors,
or adUseClient, for client-side cursors. A cursor is a set of records in memory, and of
course some software has to be responsible for keeping track of this set of records.
Server-side cursors are maintained by SQL Server using the same native cursors that
you met in Chapter 8. Client-side cursors are maintained by the Microsoft Cursor Ser-
vice for OLE DB, which attempts to level the playing field by supplying capabilities
that are lacking in some servers. If no CursorLocation is specified, a server-side cursor
is the default.
Just because SQL Server supports server-side cursors doesn’t mean you have to
use them. Some functionality is available only in client-side cursors—for example,
re-sorting Recordsets or using an index to find records. If you need these capabili-
ties, you should use client-side cursors. Otherwise, you may find that server-side
cursors provide better performance.
CursorType
The CursorType parameter further specifies the desired behavior of the Recordset
object. You can specify one of four constants:
• To open a dynamic Recordset, use adOpenDynamic. A dynamic Recordset
allows all types of movement through the Recordset and keeps you up-to-date
with changes made by other users.
• To open a keyset Recordset, use adOpenKeyset. A keyset Recordset functions
like a dynamic Recordset, except that you won’t see new records added or
records deleted by other users.
• To open a static cursor, use adOpenStatic. A static Recordset does not show you
any changes made by other users while the Recordset is open and is therefore
most useful for reporting or other applications that don’t need to be kept com-
pletely up-to-date.
• Finally, to open a forward-only cursor, use adOpenForwardOnly. A forward-
only cursor is identical to a static cursor, except that you can only move for-
ward in the Recordset to go to a different record. This offers the fastest
performance of any of the cursor types, at the expense of flexibility. Some-
times you’ll see a forward-only, read-only cursor called a firehose cursor.
UNDERSTANDING CURSORS
Development with
SQL Server
PART
V
2627ch19.qxd 8/22/00 11:11 AM Page 729
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 19 • ADO AND SQL SERVER
730
NOTE The forward-only Recordset is more flexible than you might think at first. In addi-
tion to using the MoveNext method, you can also use the Move method to skip intervening
records, as long as you’re moving forward. A forward-only Recordset also supports the
MoveFirst method, although this seems contradictory. Be aware, though, that this may be
an expensive operation, because it might force the provider to close and reopen the
Recordset.
In general, if you stick to a cursor type that has no more functionality than you
need in your application, you’ll get the best possible performance. If you don’t specify
a cursor type, ADO defaults to the fastest type, which is a forward-only cursor.
LockType
Finally, you can use the LockType parameter to specify the record-locking behavior
that will be used for editing operations. Here again you have four choices:
• adLockReadOnly, for Recordsets that cannot be edited
• adLockPessimistic, for pessimistic locking (record locks are taken for the dura-
tion of all editing operations)
• adLockOptimistic, for optimistic locking (record locks are taken only while data
is being updated)
• adLockBatchOptimistic, for Recordsets that will use the UpdateBatch method to
update multiple records in a single operation
If you don’t specify a lock type, ADO defaults to the fastest type, which is a read-
only Recordset.
WARNING The default Recordset in ADO is server-side, forward-only, and read-only.
If you want to move through records at random or edit records, you must specify the
cursor type and lock type to use.
Graceful Degradation
Just to make things more interesting, what you ask for isn’t always what you get.
Not every provider supports every possible combination of these parameters. In
almost every case, though, you’ll get something close to what you asked for. The
2627ch19.qxd 8/22/00 11:11 AM Page 730
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
731
ADO term for this process is graceful degradation. Rather than refuse to create a
Recordset, ADO will always return some kind of Recordset. However, for example, if
you try to open a client-side, static, pessimistic Recordset on a SQL Server data
source, what you actually get will be a client-side, static, batch optimistic Recordset.
If you aren’t sure what you’re getting, you need to check the values of the CursorType,
CursorLocation, and LockType properties of the Recordset object after calling its Open
method to see what ADO delivered.
TIP You should also realize that different Recordsets can have very different perfor-
mance implications. In general, the Recordsets with fewer capabilities are faster, but you’ll
want to test this in your own application to determine the best type of Recordset to open.
Table 19.1 shows the possible options you can choose when opening a Recordset
using SQL Server data and the actual Recordsets that are delivered by ADO.
TABLE 19.1: GRACEFUL DEGRADATION OF RECORDSETS
Requested Delivered Identical?
Server-side, forward-only, read-only Server-side, forward-only, read-only Yes
Server-side, forward-only, pessimistic Server-side, forward-only, pessimistic Yes
Server-side, forward-only, optimistic Server-side, forward-only, optimistic Yes
Yes
Server-side, keyset, read-only Server-side, keyset, read-only Yes
Server-side, keyset, pessimistic Server-side, keyset, pessimistic Yes
Server-side, keyset, optimistic Server-side, keyset, optimistic Yes
Server-side, keyset, batch optimistic Server-side, keyset, batch optimistic Yes
Server-side, dynamic, read-only Server-side, dynamic, read-only Yes
Server-side, dynamic, pessimistic Server-side, dynamic, pessimistic Yes
Server-side, dynamic, optimistic Server-side, dynamic, optimistic Yes
Server-side, dynamic, batch optimistic Server-side, dynamic, batch optimistic Yes
Server-side, static, read-only Server-side, static, read-only Yes
Server-side, static, pessimistic Server-side, keyset, pessimistic No
Server-side, static, optimistic Server-side, keyset, optimistic No
Server-side, static, batch optimistic Server-side, keyset, batch optimistic No
Server-side, forward-only, batch
optimistic
Server-side, forward-only, batch
optimistic
UNDERSTANDING CURSORS
Development with
SQL Server
PART
V
2627ch19.qxd 8/22/00 11:11 AM Page 731
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 19 • ADO AND SQL SERVER
732
TABLE 19.1: GRACEFUL DEGRADATION OF RECORDSETS (CONTINUED)
Requested Delivered Identical?
Client-side, forward-only, read-only Client-side, static, read-only No
Client-side, forward-only, pessimistic Client-side, static, batch optimistic No
Client-side, forward-only, optimistic Client-side, static, optimistic No
No
Client-side, keyset, read-only Client-side, static, read-only No
Client-side, keyset, pessimistic Client-side, static, batch optimistic No
Client-side, keyset, optimistic Client-side, static, optimistic No
Client-side, keyset, batch optimistic Client-side, static, batch optimistic No
Client-side, dynamic, read-only Client-side, static, read-only No
Client-side, dynamic, pessimistic Client-side, static, batch optimistic No
Client-side, dynamic, optimistic Client-side, static, optimistic No
Client-side, dynamic, batch optimistic Client-side, static, batch optimistic No
Client-side, static, read-only Client-side, static, read-only Yes
Client-side, static, pessimistic Client-side, static, batch optimistic No
Client-side, static, optimistic Client-side, static, optimistic Yes
Client-side, static, batch optimistic Client-side, static, batch optimistic Yes
Sample ADO Code
Understanding the objects supplied by ADO is an important part of grasping this
technology, but it’s no substitute for actually using those objects. In the rest of this
chapter, we’ll demonstrate a number of basic ADO techniques for retrieving and
working with data.
TIP We can’t hope to cover all of ADO in a single chapter. The definitive reference for
this technology is the Microsoft Data Access Components Software Development Kit
(MDAC SDK). You can get to the MDAC SDK online by going to the Microsoft Universal
Data Access Web site (www.microsoft.com/data) and following the Documentation link.
Client-side, static, batch optimistic Client-side, forward-only, batch
optimistic
2627ch19.qxd 8/22/00 11:11 AM Page 732
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
733
Creating a Connection
To do anything with ADO, you need to create a Connection object and use it to con-
nect to the database in which you’re interested. In some cases, such as when opening
a Recordset directly, you won’t need to explicitly create the Connection object.
There’s always a Connection object involved, even if you don’t explicitly create it.
To connect to a database, you use the Connection object’s ConnectionString prop-
erty and Open method. The ConnectionString property holds an OLE DB connection
string. Connection strings are a standardized method of describing where a database
is and what information should be used when connecting to the database. The Open
method takes some optional arguments:
Connection.Open ConnectionString, UserID, Password, Options
All four of these arguments are optional:
• The ConnectionString argument can be used to supply a connection string when
calling the Open method. In this case, you don’t need to set the Connection-
String property in advance.
• The UserID argument specifies the username to use with the data source.
• The Password argument specifies the password to use with the data source.
• The Options argument can be set to adConnectUnspecified (the default) for a
synchronous connection or adAsyncConnect for an asynchronous connection.
Once the connection is made, either type performs the same. The difference is
that an asynchronous connection lets other code in your client application con-
tinue running while the connection is being made.
Of course, to build a connection string, you need to understand from what it’s
made up. The basic syntax of an OLE DB connection string is as follows:
keyword=value;keyword=value;keyword=value…
Table 19.2 shows the keywords that you can use in a SQL Server connection string.
TABLE 19.2: OLE DB CONNECTION STRING KEYWORDS FOR SQL SERVER
Keyword Value Comments
Provider SQLOLEDB Must be specified. This tells OLE DB the
type of database with which you want to
connect.
Data Source Name of the SQL Server Must be specified. You can also use the
special value “(local)” if the SQL Server is
on the computer where the client code
will run.
SAMPLE ADO CODE
Development with
SQL Server
PART
V
2627ch19.qxd 8/22/00 11:11 AM Page 733
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét