Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 722
Part V Data Connectivity
Open Database Connectivity (ODBC) is a technology for connecting applications to
databases that has been in use longer than OLEDB. Unlike OLEDB, ODBC is designed for
connecting only to RDBMS sources. The ODBC driver is included in the SNAC, however.
A small set of objects is used to work with ADO. Table 32-1 lists these objects and describes how to
use them. Most of these object types have a counterpart in predecessor technologies that Microsoft has
introduced, although the level of ADO-object functionality is much greater than that offered by previ-
ous technologies and, as demonstrated next, the potential usability for more recent technologies such as
ADO.NET and XML transcends even ADO.
TABLE 32-1
ADO-Object Overview
Object Description
Connection A connection object defines the connection with the OLEDB provider. Use this
object to perform tasks such as beginning, committing, and rolling back transactions.
There are also methods for opening or closing the connection and for executing
commands.
Error ADO creates an error object as part of the connection object. The error object
provides additional information about errors raised by the OLEDB provider. A single
error object can contain information about more than one error. Each object is
associated with a specific event, such as committing a transaction.
Command A command object performs a task using a connection or recordset object. Even
though commands can be executed as part of the connection or recordset
object, the command object is much more flexible and enables the definition output
parameters.
Parameter The parameter object defines a single parameter for a command. A parameter
modifies the result of a stored procedure or query. Parameter objects can provide
input, output, or both.
Recordset The recordset object contains the result of a query, and a cursor for choosing
individual elements within the returned table.
Record A record is a single row of data. It can stand alone or be derived from a record set.

Field A field object contains a single column of data contained in a record or recordset
object. In other words, a field can be thought of as a single column in a table; it
contains one type of data for all the records associated with a record set.
Stream When a data provider is not able to easily express the value and length of the data as
a record set with discrete fields, as is the case for large text, BLOB, or document data,
the data may be sent to the consumer via the stream object.
Property Some OLEDB providers will need to extend the standard ADO object. Property
objects represent one way to do this. A property object contains attribute, name, type,
and value information.
722
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 723
Programming with ADO.NET 3.5 32
There are also four object collections in ADO: Errors, Parameters, Fields, and Properties. Note that these
collections are containers for child objects in the ADO model. There are no collections at the root of the
object model, and the model is never more than two levels deep. The structure is consistent and simple,
always with the following progression: Parent Object  Collection of dependent objects  Child Object
OLEDB data providers
Even when ADO is used through the provided .NET primary interop assembly, all data access will
occur through one of the available OLEDB COM data providers. A data provider manages the connection
between the client and the DBMS using a number of objects. Of course, this means that a data
provider requires a source of information and must define the specifics for creating that connection.
Generally, a provider is database specific or provides a means for configuring a specific database.
Figure 32-3 shows a typical list of database providers. Some of the providers on the list are quite
specialized.
FIGURE 32-3
A typical list of database providers
The source of an OLEDB object is known as a provider. Consequently, ADO also relies on data
providers as a source of data. Even in this day of the .NET Framework, the number of OLEDB
providers — each specialized to a particular data source — is greater than at any time in the past.

One nice thing about OLEDB is that the same provider works with any Visual Studio programming
language.
723
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 724
Part V Data Connectivity
Generally, especially for SQL Server developers, it’s better to use the SQL Server–specific provider. Even
though other general-purpose providers will work, Microsoft has optimized the SQL Server provider
for use with SQL Server. The performance advantages of using the SQL Server provider over using a
general-purpose provider are well known.
To find out which OLEDB providers are available on a particular machine, create a new
text file and rename it to include an extension of ‘‘.
udl’’ (e.g., temp.udl — the name is
not important, only the extension). Open the file to see a dialog similar to the one shown in Figure 32-3
showing all the OLEDB p roviders installed locally on that computer.
Mapping data types
When working exclusively within SQL Server, the challenge with data types amounts to choosing the
right type for a given data-storage need. However, when moving the data from the DBMS through a
data provider to a client, several layers of transition occur. For some DBMSs this is an extreme problem
because the general providers supplied with OLEDB don’t support many special data types. For all
providers, the cost of data type conversions as data propagates through those layers is significant. These
problems of data typing are additional reasons to use the SQL Server–specific OLEDB data providers
when working with ADO.
When using data found in a SQL Server table in a client application, the provider must map the data
from a type that SQL Server understands to a type that the client application understands. Fortunately
for SQL Server developers, the ADO mapping for the SQLOLEDB provider is relatively straightforward.
Table 32-2 shows how the SQL Server provider maps ADO data types. One problem occurs when
ADO uses the same data type to represent two or three SQL Server data types when the application
requires subtle differences to appear in the user interface. The complete set of SQL Server 2008 data
types is exposed in the SQL Native Client. ADO is only aware of the SQL Server 2000 data types and

will not be able to handle the new types, such as
XML or a varchar(max), discussed later in this
chapter.
Table 32-2 shows the SQL Server 2008 data types and the equivalent ADO data types, along with the
data type conversion that the .NET Framework will conduct for each ADO data type.
The comments in Table 32-2 touch on the most significant problems that can occur in the mapping of
data between ADO and SQL Server. It is important to also consider data-conversion errors. According
to Microsoft, all nondirect data translations are subject to data loss. For example, neither the provider
nor SQL Server will complain if an eight-byte number is converted into a four-byte number, but obvi-
ously there is a potential for data loss. In addition, some types cannot be converted to other types. For
example, it’s impossible to convert an
adBinary data type into an adSmallInt data type. In this sit-
uation, the development environment would complain. The sort order of SQL character data types is, by
default, different from the sort order of .NET data types.
The .NET Framework adds another level of conversion and potential conversion errors to the ADO
implementation. ADO data types are specified within ADO objects. The .NET Framework will convert
those data types to valid .NET data types implicitly when they are used outside of the ADO objects in
the code. This conversion should happen without the need for explicit data conversion type casting,
although it is good programming practice to ensure that data is always of the type expected. The
developer must remain vigilant for data type conversion problems when programming ADO, even in the
.NET Framework. Using the .NET data types that support null values may provide relief for conversion
problems with many applications. There seems to be no perfect solution to this problem short of
environment coding standards that mandate consistent best practice programming techniques for all
programmers in the environments, and rigorous testing.
724
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 725
Programming with ADO.NET 3.5 32
Best Practice
T

o avoid unexpected data type conversions at assignment, ADO data types can be assigned using explicit
casting to the desired data type.
TABLE 32-2
SQL Server to ADO/ADO.NET Data Mapping
ADO Data Type
SQL Server (.NET Framework
Data Type Data Type) Notes
Bigint adBigInt
(int64)
The bigint data-type value ranges from -2 ˆ 63
(-9,223,372,036,854,775,807) through 2 ˆ 63-1
(9,223,372,036,854,775,807). This value is only
available for SQL Server 2000, but the OLEDB provider
will still try to send it to SQL Server 7.0 and older
systems, and data loss will result. Use the adBigInt
type only when necessary and then with caution.
Binary adBinary
(byte[])
ADO uses the same data-type equivalence for both
binary and timestamp.
Bit adBoolean
(Int16)
While this data transfer always works, conceptual
differences exist between the two. For example, a bit
can have values of 1, 0,orNULL, whereas an
adBoolean always has either a true or false value.
Char adChar
(string)
ADO uses the same data-type equivalence for char,
varchar,andtext data types. The .NET Framework

uses Unicode (UTF-16) to represent all character data.
Date 0001-01-01 through 9999-12-31. Three bytes storage; is
not time zone offset aware or daylight saving time
aware.
Datetime adDBTimeStamp
(DateTime)
The default precision for the Datetime data type in
SQL Server is 3.33 milliseconds.
Datetime2 0001-01-01 through 9999-12-31. Six bytes storage for
precision less than 3, 7 bytes for precisions 4 and 5, 8
bytes for all other precisions.
Datetimeoffset 0001-01-01 through 9999-12-31. Ten bytes storage.
Decimal adNumeric
(Decimal)
ADO uses the same data-type equivalence for both
decimal and numeric data types.
continued
725
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 726
Part V Data Connectivity
TABLE 32-2
(continued )
ADO Data Type
SQL Server (.NET Framework
Data Type Data Type) Notes
Float adDouble
(Double)
Hierarchyid
Image adVarbinary

(byte[])
This data type can be so large that it won’t fit in
memory. The lack of memory can cause provider errors
and possibly only a partial retrieval. When this
happens, the developer must write a custom routine to
retrieve the data in pieces. ADO uses the same
data-type equivalence for image, tinyint,and
varbinary.
Int adInteger
(Int32)
−2,147,483648 to 2,147,483647. Storage of 4 bytes.
Money adCurrency
(Decimal)
ADO uses the same data-type equivalence for money
and smallmoney.
Nchar adWChar
(string)
ADO uses the same data-type equivalence for nchar,
ntext, nvarchar,andsysname.The.NET
Framework uses Unicode (UTF-16) to represent all
character data.
Ntext adWChar
(string)
This data type can be so large that it won’t fit in
memory. The lack of memory can cause provider errors
and possibly a partial retrieval. When this happens, the
developer must write a custom routine to retrieve the
data in pieces. ADO uses the same data-type
equivalence for nchar, ntext, nvarchar,and
sysname. The .NET Framework uses Unicode (UTF-16)

to represent all character data.
Numeric adNumeric
(decimal)
ADO uses the same data-type equivalence for both
decimal and numeric data types.
Nvarchar adWChar
(string)
ADO uses the same data-type equivalence for nchar,
ntext, nvarchar,andsysname.The.NET
Framework uses Unicode (UTF-16) to represent all
character data.
NvarChar(MAX) None (string) SQL Server 2008 provides the same data-type
equivalence for what was previously Nvarchar if less
than or equal to 8 KB, and text if greater than 8 KB.
The .NET Framework uses Unicode (UTF-16) to
represent all character data.
continued
726
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 727
Programming with ADO.NET 3.5 32
TABLE 32-2
(continued )
ADO Data Type
SQL Server (.NET Framework
Data Type Data Type) Notes
Real adSingle
(Single)
Smalldatetime adTimeStamp
(DateTime)

Smallint adSmallInt
(Int16)
−32,768 to 32,767. Storage of 2 bytes.
Smallmoney adCurrency
(Decimal)
ADO uses the same data-type equivalence for money
and smallmoney.
sql_variant adVariant
(object)
This data type can contain any of a number of
primitive data types, such as smallint, float,and
char. It can’t contain larger data types such as text,
ntext,andimage.TheadVariant type maps to the
OLEDB DBTYPE_VARIANT data type and is only
usable with SQL Server 2000. Be careful when using
this data type because it can produce unpredictable
results. Even though OLEDB provides complete support
for it, ADO doesn’t.
Sysname adWChar
(string)
ADO uses the same data-type equivalence for nchar,
ntext, nvarchar,andsysname.The.NET
Framework uses Unicode (UTF-16) to represent all
character data.
Table
Text adChar
(string)
This data type can be so large that it won’t fit in
memory. The lack of memory can cause provider errors
and possibly a partial retrieval. When this happens, the

developer must write a custom routine to retrieve the
data in pieces. ADO uses the same data-type
equivalence for char, varchar,andtext data types.
The .NET Framework uses Unicode (UTF-16) to
represent all character data.
Time An integer from 0 to 7. Specifies the fractional part of
the seconds. 00:00:00.0000000 to 12:59:59.9999999.
Five bytes storage.
Timestamp adBinary
(byte[])
ADO uses the same data-type equivalence for both
binary and timestamp.
continued
727
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 728
Part V Data Connectivity
TABLE 32-2
(continued )
ADO Data Type
SQL Server (.NET Framework
Data Type Data Type) Notes
Tinyint adTinyInt
(byte)
0 to 255. Storage of 1 byte.
Uniqueidentifier adGUID (Guid) The data provider supports a string GUID, not a true
GUID. This means that when an actual GUID is
needed, the code must explicitly convert it into a
GUID data structure.
Varbinary adVarbinary

(byte[])
ADO uses the same data-type equivalence for image
and varbinary.
Varbinary(MAX) none (byte[]) SQL Server 2008 provides the same data-type
equivalence for what was previously varbinary if less
than or equal to 8 KB, and image if greater than 8 KB.
Varchar adChar
(string)
ADO uses the same data-type equivalence for char,
varchar,andtext. The .NET Framework uses
Unicode (UTF-16) to represent all character data.
Varchar(MAX) None (string) SQL Server 2008 provides the same data-type
equivalence for what was previously varchar if less
than or equal to 8 KB, and text if greater than 8 KB.
The .NET Framework uses Unicode (UTF-16) to
represent all character data.
ADO and scripting
ADO often appears in scripts of various types. Because ADO relies on COM technology, any scripting
language capable of creating an object can probably use ADO to retrieve data from a database. Using
scripts to perform small tasks makes sense because they can easily be modified if necessary and they are
quick to write. Be aware that scripting facilities such as the Windows Script Component can be created
and referenced from VB.NET or C#, and the Windows Scripting Host runs ADO as a COM component
only. Interoperability with .NET does not apply.
.NET code is compiled to IL. Because the compilation to IL must happen at some point before runtime,
the concept of late binding is not available to .NET code. Conversely, script-based ADO, such as that
used in the Windows Scripting Host 5.6, a SQL Server 2000 DTS ActiveX script step, or a pre .NET
ASP page, requires late binding. Late binding simply means that the COM object referenced is created
at runtime and is identifiable by the use of the
CreateObject()function. Therefore, to use ADO
in scripts, MDAC must be installed on any machine that will execute scripts. In contrast, ADO.NET

will be fully supported by the SQL Native Client and will not require MDAC. This important new
distinction with ADO.NET 2.0 (and subsequently ADO.NET 3.5) is covered in greater detail later in
this chapter.
728
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 729
Programming with ADO.NET 3.5 32
Of course, scripting languages don’t provide the robust interactive environment found in programming
languages such as C# or Visual Basic or even ASP.NET. It may be necessary to restrict the use of scripts
to small tasks such as calling on a stored procedure to perform some task automatically or to retrieve
the result of a data query for onscreen display.
Microsoft makes a point of demonstrating the flexibility of ADO with several languages, including Java,
JavaScript, VBScript, and the new XML-based Windows Script Component available in Visual Studio
2005 as well as 2008.
ADO.NET
It’s somewhat confusing that ADO.NET inherits the ADO part of its name from the original ADO. ADO
is the acronym for ActiveX Data Objects. ActiveX is a clear signal that the topic is
IUnknown and COM.
IUnknown is the prototype for all COM classes. In its raw form it indicates that the calling module
needs to know nothing about a called object and the caller will still be able to statefully interact with
the called module. While deep down in the bowels of .NET there still exists COM programmability, the
whole point of the .NET Framework and the common language runtime (CLR) is the shift away from
the limitations of the COM code execution environment and toward consistency in the object-oriented
class model.
IUnknown means there is no need to know anything about a class to instantiate and
use the class. The .NET Framework signifies that the system assemblies and the base classes will be
reliably consistent types. In short, there is little that is COM-based ADO in ADO.NET from a technical
perspective.
Only from a functional perspective is ADO.NET a natural progression from ADO. A new technology
was required to overcome the limitations of ADO performance and scalability — a technology that

provided the developer with a variety of execution options. ADO.NET was designed to both overcome
ADO limitations and leverage the developer’s ADO skills. The basic objects of ADO are found in
ADO.NET. Commands executed on connections to data providers are used in ADO.NET with only slight
differences from the commands executed on connections to data providers used with ADO. However,
with ADO.NET, developers have greater control over how the data will be retrieved and manipulated.
Execution can be asynchronous and batched. Results can be elegantly stored and manipulated at the
application, disconnected from the database. Alternately, results can as easily be streamed as binary data
or as XML.
Many developers labor under the misconception that ADO.NET is simply the upgrade to ADO. ADO
was developed to support client/server applications and presupposes that the user and the data will both
remain connected to the application for the lifetime of an execution cycle. At the risk of oversimplifying
the difference, ADO held state in the data source and ADO.NET is built to be able to maintain state dis-
connected from the database. One advantage for .NET is that there is no requirement that the database
remain connected to the application for the complete execution cycle. In part, this design goal is real-
ized because XML technology is fundamental to ADO.NET. To a larger degree, state is managed in the
ADO.NET application layer by the local application cache known as the
DataSet class. In ADO.NET
2.0 and later, state management is further extended with asynchronous command execution and Multiple
Active Result Sets (MARS).
The following sections describe ADO.NET objects, keeping ADO objects in perspective, and point
out the new features in the ADO.NET 3.5 release. In other words, the discussion builds on the ADO
information presented earlier in this chapter as ADO.NET concepts are introduced. ADO.NET is a man-
aged object model with functional capabilities much like the classic ADO, yet designed to enable more
scalable applications, particularly in the disconnected environments found in many n-tier, SOA, Web
Services, and ASP.NET applications.
729
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 730
Part V Data Connectivity
The ADO.NET object model

The ADO.NET object model is different from the object model used by ADO. It is more complex yet
undeniably richer. The most striking difference is the in-memory data cache known as the
DataSet.
This ADO.NET object can be divided into two components: the
DataSet and the data provider. A data
provider is used for connecting the application to a database, executing commands and returning the
results to the application. The results that are returned to the application are either processed directly
or placed into a DataSet.
ADO.NET provides many data providers in order to access the many types of data sources such as
Microsoft SQL Server, Access, Excel, and others. These and other data providers are very lightweight
and create a very thin layer between application source code and the data source. This provides
great performance without giving up functionality. The data provider contains the classes that create
the connection, issue commands, handle the data reader, and provide data-adapter support. The
connection provides the conduit for database communications. The command enables the client to
request information from the database server through the
data adapter. In addition to providing data,
the data adapter also enables the client cache to synchronize or update back to the data source. The
data reader is a one-way, read-only method of viewing data in ADO. The data adapter provides
the real-time connection support normally associated with live data connections.
The
DataSet is a special object that contains one or more tables. The data in the DataSet is retrieved
from the data source through the provider and stored in the application work space. The data can be
manipulated and constrained at the application. The
DataSet is a disconnected subset of the data
from the data source defined by the provider properties. The data provider properties include the
Connection, Command, DataReader,andDataAdapter objects. Each of these objects also has
capabilities not found in an ADO provider. For example, a
DataAdapter can actually handle more
than one connection and one set of rules.
As with many managed objects, enumerators are used to access the various objects within these main

objects in application code. The DataSet is the representation of information within the database.
It contains two collections:
DataTableCollection and DataRelationCollection.The
DataTableCollection contains the columns and rows of the table, along with any constraints
imposed on that information. The
DataRelationCollection contains the relational information
used to create the
DataSet.
Table 32-3 provides an overview of the most frequently used ADO.NET data classes.
Visual Studio 2008 provides a TableAdapter, which is a single-table data collection that
can be used in application code much like an ADO.NET 3.5
DataSet. The TableAdapter
is significantly easier for the developer to create and manipulate than the underlying ADO.NET
3.5 data components when only one table is needed at the application. The
TableAdapter is not
derived from the
System.Data.DataSet class, as are all other DataSets. In fact, TableAdapters
are not even part of the .NET Framework. They are a level of abstraction provided by Visual Studio
2008. All
TableAdapters inherit from System.Component.ComponentModel. This means that they
are fully integrated with the Visual Studio tools, such as the Data Grid. In addition, the base class
for a
TableAdapter can be any of the .NET providers that expose a DataTable class. The base
class is specified when the
TableAdapter is created. TableAdapters are type-safe and include all
properties and methods necessary to connect to a data source, retrieve the table’s data, and update
the data source. You can find more information about the
TableAdapter on the MSDN website:
/>730
www.getcoolebook.com

Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 731
Programming with ADO.NET 3.5 32
TABLE 32-3
ADO.NET 3.5 Class Overview
Class Type Description
Connection Creates the physical connection between the DBMS and a DataAdapter,
DataReader,orfactory.TheConnection object also includes logic that
optimizes the use of connections within the distributed application
environment.
ProviderFactory Implemented in ADO.NET 2.0. Each .NET provider implements a
ProviderFactory class, each of which derives from the common base class
DBProviderFactory. The factory class includes methods for creation of
provider-specific ADO.NET components in a generic code style. The idea
behind the ProviderFactory is to enable the developer to write generic
code that can use a provider determined at runtime. The possible providers a
factory can use are stored in the machine.config file.
Command Defines an action to perform on the DBMS, such as adding, deleting, or
updating a record. The DataAdapter includes the command objects required
to query, delete, insert, and edit records.
Parameter A parameter to a command
Error The error or warning information returned from the database. For SQL Server
this includes the error number, the severity, and the text for the error.
Exception The application exception when ADO.NET encounters an error. The Error
class is created by the Exception class. The Exception class is used in
ADO.NET try-catch error handling.
DataAdapter Translates the data from the data provider source into the in-memory DataSet
or DataReader. The DataAdapter performs all queries, translates data from
one format to another, and performs table mapping. One DataAdapter can
manage one database relation. The result collection can have any level of
complexity. The DataAdapter is also responsible for issuing requests for new

connections and terminating connections after it obtains the data.
DataReader Provides a live connection to the database. However, it only provides a means
of reading the database. In addition, the DataReader cursor works only in the
forward direction. This is the object to use to perform a fast retrieval of a local
table when there is no need to update the database. The DataReader blocks
subsequent DataAdapters and associated Connection objects, so it’s
important to close the DataReader immediately after using it.
continued
731
www.getcoolebook.com