ADO.NET   is Microsoft's latest data access technology. As an integral part of the .NET Framework it is far more than simply an upgrade of previous incarnations of ActiveX^® Data Objects ADO. ADO.NET provides an extensive set of .NET classes that facilitate efficient access to data from a large variety of sources also enabling sophisticated manipulation and sorting of data, and forming an important framework within which to implement inter-application communication and XML Web Services.

ADO.NET is essentially a collection of classes that expose methods and attributes used to manage communications between an application and a data store. Being an integral part of the .NET Framework, ADO.NET simplifies integration of data sharing in distributed ASP.NET applications.

ADO.NET is an expansion of ADO with some of the key concepts retained. It provides access to structured data from diverse sources, which are all accommodated in a consistent, standardized programming model.

ADO.NET can be used in any consumer application that needs to connect to, and communicate with, data sources such as Microsoft SQL Server™, as well as data sources exposed via OLE DB and XML.

XML is used to exchange data between programs or Web pages, which requires a disconnected or remote access to data. Transmitting information in XML-formatted data sets enables programmers to easily separate the data processing and user interface components of a data-sharing application onto separate servers. This can greatly improve both performance and maintainability for systems supporting many users.

ADO.NET also supports the scalability required by Web-based data-sharing applications. Web applications must serve tens, hundreds, or even thousands of users. ADO.NET does not retain lengthy database locks or active connections that monopolize limited resources. This allows the number of users to grow with only minimal increase in the demands on the resources of a system.

ADO.NET can work either through Web protocols, using XML, or in a more traditional client/server architecture.

The ADO.NET interface is readily accessible using any of the .NET- compliant languages, including C#, Visual Basic .Net, and JScript.Net.

ADO.NET exposes an object model that conforms to the standards prescribed by the World Wide Web Consortium (W3C). It is designed to support the developer in accessing and writing to a wide variety of data sources. This can be anything from a product list in a database used on an e-commerce web site to a collection of sales figures collected in Excel and saved to an XML file and used as the basis for a table being displayed on an internal Intranet site.

The ADO.NET Object Model

The ADO.NET object model is made up

• Data View
• Data Set
• Data Provider

DataView

The DataView speaks to the DataSet and is a special class designed for UI objects to bind to and can provide customized views of the DataSet. It provides methods and properties that enable UI objects such as a DataGrid to bind to a DataSet and contains properties such as AllowEdit and Count that allow the UI object to work with the data in meaningful ways. A DataView is only used in conjunction with a DataSet and never with a DataReader.

DataSet

The DataSet is the core component of the disconnected architecture of ADO.NET that caches data locally on the client. The DataSet is explicitly designed for data access independent of any data source. As a result it can be used with multiple and differing data sources, XML data, or used to manage data local to the application. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint and relation information about the data in the DataTable objects.

Another feature of the DataSet is that it tracks changes that are made to the data it holds before updating the source data. DataSet are also fully XML-featured. They contain methods such as GetXml and WriteXml that respectively produce and consume XML data easily. In an XML scenario where there is no database, these methods enable use of ADO.NET without the Data Provider being involved.

The DataSet object provides a consistent programming model that works with all current models of data storage: flat, relational, and hierarchical. It represents the data that it holds as collections and data types. The data within a DataSet is manipulated via the set of standard APIs exposed through the DataSet and its child objects regardless of its data source.

The DataSet is similar to a Recordset with CursorLocation = adUseClient, CursorType = adOpenStatic, and LockType = adLockOptimistic. However, the DataSet has extended capabilities over the Recordset for managing application data.

Data Provider

The DataProvider at the bottom of the diagram speaks to the database.  The DataProvider connects to the database on behalf of ADO.NET. It encapsulates all connections to a database. The blue section at the bottom of the diagram describes a DataProvider with several key objects in it:

• Connection
• Command
• DataAdaptor
• DataReader

.NET Beta2 provides two distinct Data Providers and only one is used at a time, based on the type of database being connected to.

The first Data Provider, SQL Server .NET Data Provider uses a special protocol called TDS (Tabular Data Stream) to communicate directly with SQL Server without adding the overhead of OLE DB or ODBC. This Data Provider is represented in the .NET namespace as System.Data.SqlClient.

The other Data Provider is the OLE DB .NET Provider. Using .NET’s COM interoperability features provides native OLE DB to enable data access. According to the .NET documentation, one important difference between the two is that the OLE DB .NET Provider supports both manual and automatic transactions. For automatic transactions, the OLE DB .NET Data Provider automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services. Component Services will be covered in a future article. This Data Provider is represented in the .NET namespace as System.Data.OleDb.

Connections

Connections are part of a Data Provider and the Connection object provides connectivity to a data source.

Connections can be opened in two ways:

1. Explicitly by calling the Open method on the connection;
2. Implicitly when using a DataAdapter.

The following examples demonstrate how to create and open connections to SQL Server™ (SqlClient) and OLE DB (OleDb) databases.

SqlClient

SqlConnection myConn = new SqlConnection

    ("Data Source=localhost;Integrated Security=SSPI;"+"Initial Catalog=northwind");

myConn.Open();

Connection String Format – SqlConnection

The SQL Server™ .NET Data Provider supports a connection string format that is similar to the OLE DB (ADO) connection string format. For valid string format names and values, see the SQLConnection.ConnectionString Property.

OleDb

OleDbConnection myConn = new OleDbConnection

   ("Provider=SQLOLEDB;Data Source=localhost;" +   "Integrated Security=SSPI;Initial Catalog=northwind");
myConn.Open();

Closing the Connection

You must always close the Connection when you are finished using it. This can be done using either the Close or Dispose methods of the Connection object. Connections are not implicitly released when the Connection object falls out of scope or is reclaimed by garbage collection.

Commands

The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. Commands contain information that is submitted to a database as a query, and, like connections, are represented by the provider-specific classes SqlCommand and OleDbCommand. Functionally, once the Connections are established and the Commands are executed the results are in the form of streams. These resultant streams can be accessed either by DataReader object, or passed into a DataSet object via a DataAdapter.

The SqlCommand class provides four different methods to execute a command. They are: ExecuteReader, ExecuteNonQuery, ExecuteScalar and, newest but not least, ExecuteXmlReader. Essentially, such methods differentiate only for the type of input they expect, and consequently for the result they return. In general, once you know the operation to accomplish, determining the right method to use is rather straightforward.

Incidentally, an OleDbCommand object does not support ExecuteXmlReader.

ExecuteReader expects to run a query command or a stored procedure that selects records. It expects to have one or more resultsets to return.

cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
// process the resultset(s) here
cmd.Connection.Close();

You access the selected records using the SqlDataReader object and use the method Read to loop through them. You move to the next resultset using the NextResults method.

ExecuteNonQuery expects to run a command, or a stored procedure, that affects the state of the specified table. This means anything but a query command. You normally use this method to issue an INSERT, UPDATE, DELETE, CREATE, and SET statement.

ExecuteNonQuery returns only the number of rows affected by the command execution, or –1 should this information be unavailable. It doesn't give you a chance to access any resultset generated by the statement or the stored procedure. Actually, there's really nothing to prevent you from using this method for a query command, but in this case you get neither the resultset nor the number of the affected rows.

cmd.Connection.Open();
nRecsAffected = cmd.ExecuteNonQuery();
cmd.Connection.Close();
// check the record(s) affected here

The number of affected rows is also made available through the RecordsAffected property of the SqlCommand object. This property equals –1 in case of errors or if a query command is executed.

ExecuteScalar expects to run a query command, or more likely a stored procedure, that returns data. However, this method is different from ExecuteReader in that it just makes available, as a scalar value, the first column on the first row of the selected resultset.

cmd.Connection.Open();
Object o = cmd.ExecuteScalar(); cmd.Connection.Close();
// work on the scalar here

The method returns the value as a boxed object. It's then up to you to unbox or cast that value to the proper, expected type.

ExecuteScalar turns out to be particularly useful when you have statistical or aggregate operations to accomplish on a certain amount of data. In these and similar circumstances, there is just one value that you might want to return back to the caller. Because of its use cases, you normally use this method on more or less complex stored procedures rather than on single SQL statements.

ExecuteXmlReader builds up and returns an XmlReader object after a SELECT command that exploits XML features in SQL Server 2000 has been issued.

DataAdapters

The DataAdapter provides a set of methods and properties to retrieve and save data between a DataSet and its source data store. It does the actual work of putting returned data from a database into a DataSet. It also manages reconciling how data should be updated against a database.

Connections and Commands whose properties are set early on in code are often passed into DataAdapters for use when their action methods are invoked.

The DataAdapter object encapsulates a set of data commands and a database connection, which are used to fill the DataSet and update the data source. The Fill method of the DataAdapter calls the SELECT command while Update method calls INSERT, UPDATE or DELETE command for each changed row. Simple and straightforward, huh?

One of the great features about the DataAdapter object is that these commands can be set explicitly in order to control the statements used at runtime to resolve changes, including the use of stored procedures.

The .NET documentation notes that the CommandBuilder object can be used to generate these commands at run-time based upon a select statement for "ad-hoc queries". However, this requires an extra round-trip to the server in order to gather required metadata, so explicitly providing the INSERT, UPDATE, and DELETE commands at design time will always result in better run-time performance.

The DataAdapter is the object that connects to the database to fill the DataSet. It also connects to the database in order to update the data, and this is based on the operations that take place while the DataSet holds the data.

However, there are cases where the DataAdapter and DataSet objects are bypassed. In these cases, the DataReader object is used.

DataReaders

When dealing with large read only data, e.g. reading 5,000 rows of data, the .NET framework includes the DataReader object, which is a read-only, forward-only stream returned from the database. Only one record at a time is ever present in memory.

The DataReader is similar to a Recordset with CursorType = adOpenForwardOnly and LockType = adLockReadOnly.

ADO.NET is an evolutionary improvement on ADO. One way to quickly understand the advantages of ADO.NET is to compare its features to those of ADO.

Feature	ADO	ADO.NET
Memory-resident data representation	Uses the RecordSet object, which looks like a single table.	Uses the DataSet object, which can contain one or more tables represented by DataTable objects.
Relationships between multiple tables	Requires the JOIN query to assemble data from multiple database tables in a single result table.	Supports the DataRelation object to associate rows in one DataTable object with rows in another DataTable object.
Data visitation	Scans RecordSet rows sequentially.	Uses a navigation paradigm for non-sequential access to rows in a table. Follows relationships to navigate from rows in one table to corresponding rows in another table.
Disconnected access	Provided by the RecordSet but typically supports connected access, represented by the Connection object. You communicate to a database with calls to an OLE DB provider.	Communicates to a database with standardized calls to the DataAdapter object, which communicates to an OLE DB provider, or directly to SQL Server.
Cursors	Utilizes server-side and client-side cursors.	The architecture is disconnected so cursors are not applicable.
Programmability	Uses the Connection object to transmit commands that address underlying data structure of a data source.	Uses the strongly typed programming characteristic of XML. Data is self-describing because names for code items correspond to the "real world" problem solved by the code. Underlying data constructs such as tables, rows, and columns do not appear, making code easier to read and to write.
Sharing disconnected data between tiers or components	Uses COM marshalling to transmit a disconnected record set. This supports only those data types defined by the COM standard. Requires type conversions, which demand system resources.	Transmits a DataSet as XML. The XML format places no restrictions on data types and requires no type conversions.
Transmitting data through firewalls	Problematic, because firewalls are typically configured to prevent system-level requests such as COM marshalling.	Supported, because ADO.NET DataSet objects use XML, which can pass through firewalls.
Scalability	Database locks and active database connections for long durations contend for limited database resources.	Disconnected access to database data without retaining database locks or active database connections for lengthy periods limits contention for limited database resources.

Summary

ADO.NET is designed to build on the strength of the ADO programming model, while providing an evolution of data access technology to meet the changing needs of the developer. It is designed to leverage your existing knowledge of ADO, while giving you much finer control over the components, resources, and behavior of your applications when accessing and working with data.