The Fundamental ADO.NET Classes | Professional ASP.NET MVC 1.0 (Wrox Programmer to Programmer)

Traditional data access with ADO revolves around one fundamental data storage object “ the Recordset . The technique used here is to create a connection to a data store using either an OLEDB provider or an ODBC through OLEDB driver (depending on the data store and the availability of the provider) and then execute commands against that connection to return a Recordset object containing the appropriate data. This can be done using a Command object or directly against the Connection object. Alternatively, to insert or update the data, just execute a SQL statement or a stored procedure within the data store using the Connection object or Command object directly, without returning a Recordset object.

Data access in .NET follows a broadly similar principle, but uses a different set of objects. So, switching to .NET does not involve learning a completely different technique. However, the objects used are quite different underneath, providing much better performance with more flexibility and usability.

The .NET data access object model is based around two fundamental objects “ the DataReader and the DataSet . Together, they replace the Recordset from traditional ADO, providing many new features that make complex data access techniques much more efficient, while remaining as easy to use as the Recordset object. The main differences are that a DataReader provides forward-only and read-only access to data (like a firehose cursor in ADO), while the DataSet object can hold more than one table (in other words, more than one rowset) from the same data source as well as the relationships between them.

You can create a DataSet from existing data in a data store, or fill it directly with data one row at a time using code. It also allows you to manipulate the data held in the DataSet 's tables, and build as well as modify the relationships between the tables within it.

Each table within a DataSet maintains details of the original values of the data as you work with it, and any changes to the data can be pushed back into the data store at a later date.

The DataSet also contains metadata describing the table contents, such as the columns types, rules, and keys. Remember that the whole ethos with a DataSet is to be able to work accurately and efficiently in a disconnected environment.

The DataSet object can also persist its contents, including more than one data table or rowset, directly as XML, and load data from an XML document that contains structured data in the correct format. In fact, XML is the only persistence format for data in .NET “ bringing it more into line with the needs of disconnected and remote clients .

Comparison of Techniques in ADO and ADO.NET

As we expect most of our readers to be at least partly familiar with traditional ADO programming techniques, we will start with a quick overview of how the new ADO.NET classes and techniques relate to the traditional approach:

Traditional ADO approach	ADO.NET equivalent
Connected access to data using a Connection (and possibly a Command as well) to fill a Recordset then iterate through the Recordset .	Use a Connection and a Command to connect a DataReader object to the data store and read the results iteratively from the data store.
Updating a data store using a Connection and Command object to execute a SQL statement or stored procedure.	Use a Connection and a Command to connect to the data store and execute the SQL statement or stored procedure.
Disconnected access to data using a Connection (and possibly a Command as well) to fill a Recordset then remove the connection to the data source.	Use a Connection and a Command to connect a DataAdapter to the data source and then fill a DataSet with the results.
Updating a data store from a disconnected Recordset by reconnecting and using the Update or UpdateBatch method.	Use a Connection and a Command to connect a DataAdapter and DataSet to the data source and then call the Update method of the DataAdapter .

The major differences are:

There is no direct equivalent of a Recordset class. Depending on the task you want to achieve, you use a DataReader or a DataSet instead.
Client-side and server-side (database) cursors are not used in ADO.NET. The disconnected model means that they are not applicable .
Database locking is not supported or required. Again, due to the disconnected model, it is not applicable.
All data persistence is as XML. There are no MIME-encoded or binary representations of rowsets or other data structures.

Let's look at the new ADO.NET classes in more detail.

The Connection Classes

These classes are similar to the ADO Connection class, with similar properties. They are used to connect a data store to a Command instance.

The OleDbConnection class is used with an OLE-DB provider.
The SqlConnection class uses Tabular Data Services (TDS) with MS SQL Server.
The OdbcConnection class is used with an ODBC driver.
The OracleConnection class is used to connect to an Oracle database.

In traditional ADO, it was common to use the Connection to directly execute a SQL statement against the data source or to open a Recordset . This cannot be done with the .NET Connection classes. However, they do provide access to transactions that are in progress against a data store.

The Commonly Used Methods of the Connection Classes

The most commonly used methods for the OleDbConnection , OdbcConnection , OracleConnection , and SqlConnection classes are shown in the following table:

Method	Description
Open	Opens a connection to the data source using the current settings for the properties, such as ConnectionString that specifies the connection information to use
Close	Closes the connection to the data source
BeginTransaction	Starts a data source transaction and returns a Transaction instance that can be used to commit or abort the transaction.

Note

An excellent reference to all the properties, methods, and events of the classes discussed here is included within the .NET SDK that is provided with the framework. Simply open the Class Library topic within the Reference section, or search for the class by name using the Index or Search feature of the SDK. Many of the common ones have been demonstrated, including those shown in the preceding table.

Remember that there are at least two implementations of some of the .NET data access classes, each one being specific to the data store you are connecting to.

Classes prefixed with OleDb or Odbc are used with a managed code OLEDB provider or ODBC driver against any database that has a suitable provider or driver. The classes prefixed with Sql are used only with Microsoft SQL Server (we'll concentrate on just these three types of data store connection).

Other than that, the classes are identical as far as programming with them is concerned . However, you must use the appropriate one depending on which data store you connect to, so your code must be rewritten to use the correct ones if you change from one set of classes to the other.

This is generally only a matter of changing the prefixes in the class declarations. For this reason, you may prefer to avoid including the prefix in your variable and method names , and in comments within your code.

Note

As an aside, it is possible to use the .NET Activator class's CreateInstance method to create an instance of a class using a variable to specify the class name. This would allow generic code routines to be created that instantiate the correct class type ( OleDb or Sql ) depending on some external condition you specify. The details of this topic can be found in the SDK.

The Command Classes

These classes are similar to the equivalent ADO Command , and have similar properties. They are used to connect the Connection class to a DataReader or a DataAdapter instance:

The OleDbCommand class is used with an OLE-DB provider.
The SqlCommand class uses Tabular Data Services with MS SQL Server.
The OdbcCommand class is used with an ODBC driver.
The OracleCommand class is used to access an Oracle database.

The Command class allows you to execute a SQL statement or stored procedure against a data source. This includes returning a rowset (in which case you use another class such as a DataReader or a DataAdapter to access the data), returning a single value (a singleton ), or returning a count of the number of records affected for queries that do not return a rowset.

The Commonly Used Methods of the Command Classes

The most commonly used methods for the OleDbCommand , OdbcCommand , OracleCommand , and SqlCommand classes are shown in the following table:

Method	Description
ExecuteNonQuery	Executes the command defined in the CommandText property against the connection defined in the Connection property for a query that does not return any rows (an UPDATE , DELETE , or INSERT ). Returns an Integer indicating the number of rows affected by the query.
ExecuteReader	Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns a "reader" instance that is connected to the resulting rowset within the database, allowing the rows to be retrieved. The derivative ExecuteXmlReader method can be used with the SQL Server 7.0 SQLXML technology to return an XML document fragment in an XmlReader instance. We look at the various "reader" classes later.
ExecuteScalar	Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns only a single value (effectively the first column of the first row of the resulting rowset). Any other returned columns and rows are discarded. Fast and efficient when only a "singleton" value is required.

The DataAdapter Classes

Some classes in the framework connect one or more Command instances to a Dataset . They provide the pipeline and logic that fetches the data from the data store and populates the tables in the DataSet , or pushes the changes in the DataSet back into the data store.

The OleDbDataAdapter class is used with an OLE-DB provider.
The SqlDataAdapter class uses Tabular Data Services with MS SQL Server.
The OdbcDataAdapter class is used with an ODBC driver.
The OracleDataAdapter class is used to access an Oracle database.

These classes provide four properties that define the commands used to manipulate the data in a data store: SelectCommand , InsertCommand , UpdateCommand , and DeleteCommand .

Each one of these properties is a reference to a Command instance (these Command instances can all share the same Connection instance). Figure 8-4 shows how these classes are related :

Figure 8-4:

The Commonly Used Methods of the DataAdapter Classes

The OleDbDataAdapter , OdbcDataAdapter , OracleDataAdapter , and SqlDataAdapter classes provide a series of methods for working with the DataSet that they apply to. The three most commonly used methods are shown in the following table:

Method	Description
Fill	Executes the SelectCommand to fill the DataSet with data from the data source. Can also be used to update (refresh) an existing table in a DataSet with changes made to the data in the original data source if there is a primary key in the table in the DataSet .
FillSchema	Uses the SelectCommand to extract just the schema for a table from the data source, and creates an empty table in the DataSet with all the corresponding constraints.
Update	Calls the respective InsertCommand , UpdateCommand , or DeleteCommand for each inserted, updated, or deleted row in the DataSet so as to update the original data source with the changes made to the content of the DataSet . This is a little like the UpdateBatch method provided by the ADO Recordset , but in the DataSet it can be used to update more than one table.

The DataSet Class

The DataSet provides the basis for disconnected storage and manipulation of relational data. You can fill it from a data store, work with it while disconnected from that data store, then reconnect and flush changes back to the data store as required. The main differences between a DataSet and the ADO Recordset are:

The DataSet class can hold more than one table (more than one rowset in other words), as well as the relationships between them.
The DataSet class automatically provides disconnected access to data.

Consider the following schematic:

Figure 8-5:

Figure 8-5 shows a schematic view of the relationship between all the classes discuss now. Each table in a DataSet is a DataTable instance within the Tables collection. Each DataTable contains a collection of DataRow instances and a collection of DataColumn instances. There are also collections for the primary keys, constraints, and default values used in this table (the Constraints collection), and the parent and child relationships between the tables.

There is also a DefaultView instance for each table. This is used to create a DataView based on the table, so that the data can be searched, filtered or otherwise manipulated “ or bound to a control for display (we look at the DataTable and DataView classes later).

The Commonly Used Methods of the DataSet Class

The DataSet class exposes a series of methods that can be used to work with the contents of the tables, or the relationships between them. For example, you can clear the DataSet , or merge data from a separate DataSet into this one. The following table summarizes the methods available:

Method	Description
Clear	Removes all data stored in the DataSet by emptying all of the tables it contains. However, it is often more efficient to destroy the instance and create a new one unless you need to hold a reference to the existing one.
Merge	Takes the contents of a DataSet and merges it with another DataSet so that it contains all the data from both of the source DataSet instances.

We mentioned earlier that the default persistence format in .NET is XML. The following table shows the methods provided by the DataSet class for reading and writing this XML data.

Method	Description
ReadXml and ReadXmlSchema	Takes an XML document or an XML schema and reads it into the DataSet .
GetXml and GetXmlSchema	Returns a String containing an XML document or an XML schema that represents the data in the DataSet .
WriteXml and WriteXmlSchema	Writes the XML document or XML schema that represents the data in the DataSet to a disk file, to a "reader/writer" instance, or to a Stream . We look at the "reader/writer" classes later.

The DataSet class, together with all the DataTable instances it contains, keeps a record of the values for the content when it was originally created and loaded (filled with data). This is a fundamental requirement to allow the changes to be pushed back into the original data store in a multi- user scenario.

There are four methods provided that allow you to control when and how the original values are stored, as shown in the following table:

Method	Description
AcceptChanges	Commits all the changes made to the tables or relations within the DataSet since it was loaded, or since the last time AcceptChanges was executed.
GetChanges	Returns a DataSet containing some or all of the changes made since it was loaded, or since the last time AcceptChanges was executed.
HasChanges	Indicates if any changes have been made to the contents of the DataSet since it was loaded, or since the last time AcceptChanges was executed.
RejectChanges	Abandons all the changes made to values in the tables within the DataSet since it was loaded, or since the last time AcceptChanges was executed. Returns it to the original state and removes all stored changes information.

The DataTable Class

Each of the tables or rowsets stored within a DataSet class is exposed through a DataTable class instance, as was shown in Figure 8-5. Each DataTable has a Rows property that references a DataRowCollection class instance. This is a collection of DataRow class instances.

The Commonly Used Methods of the DataTable Class

The DataTable class exposes a series of properties and methods that allow you to interact with each table individually while it is stored in the DataSet . The most commonly used methods are Clear , AcceptChanges , and RejectChanges . These are fundamentally the same as the methods just described for the DataSet class, but operate only on the specific table to which the DataTable class refers.

The following methods allow you to manipulate the contents of the table:

Method	Description
NewRow	Creates a new row for the table. The values can then be inserted into it using code, and the new row added to the table.
Select	Returns the set of rows that match a filter, in the order specified. Used to create subsets of rows.

The Commonly Used Methods of the DataRowCollection Class

This is a collection of all the rows in a DataTable , as referenced by the Rows property of the table. It provides methods to add and remove rows, and to find a row based on a value for the primary key (or more than one value for a multiple-column primary key). These methods are summarized in the following table:

Method	Description
Add	Adds a new row created with the NewRow method of the DataTable to the table
Remove	Permanently removes the specified DataRow class from the table
RemoveAt	Permanently removes a row specified by its index position from the table
Find	Takes an array of primary key values and returns the matching row as a DataRow instance

The Commonly Used Methods of the DataRow Class

This class represents the row itself within the table, and within the DataRowCollection . It has the AcceptChanges and RejectChanges methods, which work the same way as for the DataTable class.

The DataRow class also has methods that are used to manipulate individual rows in a table, as shown in the following table:

Methods	Description
BeginEdit , EndEdit , and CancelEdit	Used to switch the row into "edit mode" and save or abandon the changes made in this mode.
Delete	Marks the row as being deleted, though it is not removed from the table until the Update or AcceptChanges method is executed.
GetChildRows	Returns a collection of rows from another table that is related to this row as child rows.
SetColumnError and GetColumnsInError	Used to set and return the error status for this row. In conjunction with the HasErrors and RowError properties, this allows bulk edit errors to be reported separately afterwards.

The DataView Class

As shown in the earlier schematic, you can retrieve a DataView containing the data from a table within a DataSet . The DataView class exposes a complete table or a subset of the rows from a table. It can be created using the DefaultView of the table, or from a DataTable instance that selects a subset of rows from a table.

The Commonly Used Methods of the DataView Class

In general, to manipulate the contents of a table within a DataSet , it's best to create a DataView from the table and use the methods it provides. The most commonly used methods are shown in the following table:

Method	Description
AddNew	Adds a new row to the DataView . The values can then be inserted into it using code.
Delete	Removes the current or specified row from the DataView .
Find	Takes a single value or an array of values, and returns the index of the row that matches these value(s).
FindRows	Takes a single value or an array of values, and returns a collection of DataRow instances that match these value(s).

The DataReader Classes

While the DataSet provides a comprehensive platform for disconnected data access, there are many occasions when you just want a fast and efficient way to access a data store without actually extracting data that will be remoted (disconnected). This might be to extract one or a few records or specific field values, or to execute a simple INSERT , UPDATE , or DELETE SQL statement. Or, it might be where there is too much data to fit into a DataSet and to remote sensibly. It's also the ideal solution for server-side data binding in most cases, as mentioned in the previous chapter. For all these tasks you can use a DataReader class.

The OleDbDataReader class is used with an OLEDB provider.
The SqlDataReader class uses Tabular Data Services with MS SQL Server.
The OdbcDataReader class is used with an ODBC driver.
The OracleDataReader class is used to access an Oracle database.

As Figure 8-6 suggests, the DataReader provides the equivalent of a firehose cursor for direct connected access and retrieval of data from a data store. It's somewhat like the way an ADO Recordset is used to extract data and then iterate through it.

Figure 8-6:

We execute a SQL statement or stored procedure to get a set of data rows that are referenced by a DataReader , and then iterate through them “ while all the time remaining connected to the data store.

The important points to bear in mind with the DataReader are:

It provides a partial equivalent of a cursor against a data store, using a SQL statement or stored procedure to extract a rowset.
It provides the ability to execute a SQL statement or stored procedure to update the data store content.
It does not provide disconnected access to data.
Access to the rowset referenced by a DataReader is read-only and forward-only.

Note
You can extract XML formatted data fragments directly from MS SQL Server 2000 using a reader instance (in this case an XmlReader ) together with the in-built SQL-XML technology.

The Commonly Used Methods and Properties of the DataReader Classes

To use a DataReader class, create a Command class and then use this to execute your SQL statement or stored procedure and return a DataReader . You can then iterate through the rows and columns, using the DataReader to extract the results from the data store.

The following table shows the most commonly used methods exposed by the DataReader classes:

Method	Description
Read	Advances the current row pointer to the next row so that the values of the columns can be accessed using the column name or ordinal position. Returns False when there are no more rows to read.
GetValue	Returns one value from the current row in its native format (as the native data type in the data source) by specifying the integer column index. The simpler but less efficient alternative to using the column index is to specify the column name directly as: value = DataReader(" column-name ").
GetValues	Gets one or more values from the current row in their native format (as the native data type in the data source) into an array.
Getxxxxxx	Returns a value from the current row as the data type specific to each method, by specifying the integer column index. Examples are GetBoolean , GetInt16 , and GetChars .
NextResult	Moves the current row pointer to the next set of results when the statement is a SQL stored procedure or a batch SQL statement that returns more than one result set. Note that this is not a MoveNext operation like that of an ADO Recordset “ it moves the current row pointer from one rowset to the first row in the next rowset .
Close	Closes the DataReader and releases the reference to the rowset.

The DataReader classes also expose some useful properties that allow you to discover details about the rowset that it is referencing, as shown in the following table:

Method	Description
FieldCount	Returns the number of columns (fields) in the rowset returned by the query or stored procedure.
HasRows	Returns a Boolean value of True if the execution of the query or stored procedure returned any rows, and False if there are no rows in the resulting rowset. This method was added in version 1.1.
IsClosed	Returns a Boolean value that is True if the DataReader has been closed, or False if it is still open following execution of the query or stored procedure.
RecordsAffected	Returns an Integer value that is the number of rows in the result set referenced by the DataReader . Only valid after all the rows have been read from the DataReader by a server control such as a DataGrid , or after iterating through until the Read method returns False .

Should I Use a DataReader or a DataSet?

When you start building applications that access a data store, think about what kind of access you actually need, and how the data will be used. It should be obvious from the descriptions of the classes that the DataSet carries a noticeable overhead in terms of complexity when compared to a DataReader , with the corresponding negative effect on performance and memory usage.

So, wherever it's possible, aim to use a DataReader rather than a DataSet . The kinds of occasions that require a DataSet are:

When you need to remote the data (disconnect from the data store and pass the data to another tier in the application) to a client application, store it ready for use in a process, edit the data, or in some similar scenario.
When you need to store, transport, or access more than one table (more than one DataTable instance), and optionally the relationships between these tables.
When you need to update data in the source database using the in-built methods of the DataSet and DataAdapter rather than executing individual SQL UPDATE statements or stored procedures. The DataSet also stores the original (as well as the current) values of each column in each row, so it better manages a situation where multiple users are concurrently updating the data.
When you need to take advantage of the synchronization between an XML document and the equivalent "relational" rowset. This topic is discussed in Chapter 11.
In certain data binding scenarios, such as binding the same data to several controls or using automatic record paging in a DataGrid control, you cannot use a DataReader as the data source. In such cases, it's usual to use a DataView created from a table in a DataSet .
If you are iterating through the data rows, and need the freedom to be able to move backwards and forwards in the rowset. You can't use a DataReader for this, as it is a forward-only data source.

Relational Data Providers for .NET

.NET uses managed code data providers to connect to a data store. The following table shows the .NET Data Providers that ship with version 1.1 of the .NET Framework:

Provider Name	Description
SQLOLEDB	OLEDB provider SQL Server
MSDAORA	OLEDB provider for Oracle
Microsoft.Jet.OLEDB.4.0	OLEDB provider for Access and other Jet data sources
SQL Server	ODBC driver for SQL Server
Microsoft ODBC for Oracle	ODBC driver for Oracle
Microsoft Access Driver ( *.mdb )	ODBC driver for Microsoft Access
Oracle	Microsoft provider for Oracle (requires the Oracle client software version 8.1.7 or later to be installed)

Only the first three of the providers listed were included with.NET Framework version 1.0. A managed provider for ODBC was developed as a beta product during the version 1.0 timeframe, and can still be obtained from the Microsoft Data web site at http://www.microsoft.com/data/. More managed providers are planned, such as those for Microsoft Exchange, Active Directory, and other data stores. The existing unmanaged OLEDB providers for these data stores cannot be used in .NET.

Note	The beta version of the ODBC driver installs in a different namespace from the driver included in version 1.1 of the .NET Framework. The current namespace is System.Data.Odbc , whereas the beta version was installed as Microsoft.Data.Odbc .