ADO.NET is an evolution of the ADO data access model that directly addresses
user requirements for developing scalable applications. It was designed specifically
for the web with scalability, statelessness, and XML in mind.
ADO.NET uses some ADO objects, such as the Connection and Command objects,
and also introduces new objects. Key new ADO.NET objects include the DataSet, DataReader, and DataAdapter.
The important distinction between this evolved stage of ADO.NET and previous
data architectures is that there exists an object -- the DataSet -- that is
separate and distinct from any data stores. Because of that, the DataSet functions as a standalone entity.
You can think of the DataSet as an always disconnected recordset that knows nothing about the source or destination of the data it contains. Inside a DataSet, much like in a database, there are
tables, columns, relationships, constraints, views, and so forth.
A DataAdapter is the object that connects to the database to fill the DataSet. Then, it
connects back to the database to update the data there, based on operations performed while the DataSet held the data. In the past, data processing has been primarily connection-based. Now, in an effort to
make multi-tiered apps more efficient, data processing is turning to a
message-based approach that revolves around chunks of information. At the
center of this approach is the DataAdapter, which provides a bridge to
retrieve and save data between a DataSet and its source data store. It
accomplishes this by means of requests to the appropriate SQL commands made against the
The XML-based DataSet object provides a
consistent programming model that works with all models of data storage: flat, relational, and hierarchical.
It does this by having no 'knowledge' of the source of its data, and by
representing the data that it holds as collections and data types. No
matter what the source of the data within the DataSet is, it is manipulated
through the same set of standard APIs exposed through the DataSet and its
While the DataSet has no knowledge of the source of its data, the managed
provider has detailed and specific information. The role of the managed
provider is to connect, fill, and persist the DataSet to and from data stores.
The OLE DB and SQL Server .NET Data Providers (System.Data.OleDb and System.Data.SqlClient) that are part of the .Net Framework provide four basic objects: the Command, Connection, DataReader and DataAdapter.
In the remaining sections of this document, we'll walk through each
part of the DataSet and the OLE DB/SQL Server .NET Data Providers explaining what they
are, and how to program against them.
The following sections will introduce you to some objects that have evolved,
and some that are new. These objects are:
- Connections. For connection to and managing transactions against a database.
- Commands. For issuing SQL commands against a database.
- DataReaders. For reading a forward-only stream of data records from a SQL Server data source.
- DataSets. For storing, remoting and programming against flat data, XML data and relational data.
- DataAdapters. For pushing data into a DataSet, and reconciling data against a database.
Note When dealing with connections to a database, there are two different
options: SQL Server .NET Data Provider (System.Data.SqlClient) and OLE DB .NET Data Provider (System.Data.OleDb).
In these samples we will use the SQL Server .NET Data Provider.
These are written to talk directly to Microsoft SQL Server. The OLE DB .NET Data Provider
is used to talk to any OLE DB provider (as it uses OLE DB underneath).
Connections are used to 'talk to' databases, and are represented by provider-specific classes such as SQLConnection.
Commands travel over connections and resultsets are returned in the form of streams which can be read by a
DataReader object, or pushed into a DataSet object.
The example below shows how to create a connection object. Connections can be opened
explicitly by calling the Open method on the connection, or will be opened
implicitly when using a DataAdapter.
Commands contain the information that is submitted to a database, and are represented by provider-specific classes such as
SQLCommand. A command can be a stored procedure call, an UPDATE statement, or a statement that
returns results. You can also use input and output parameters, and return
values as part of your command syntax. The example below shows how to issue an INSERT statement
against the Northwind database.
The DataReader object is somewhat synonymous with a read-only/forward-only cursor
over data. The DataReader API supports flat as well as hierarchical data. A DataReader object is returned
after executing a command against a database. The format of the returned DataReader object is different from a recordset.For example, you might use the DataReader to show the
results of a search list in a web page.
DataSets and DataAdapters
The DataSet object is similar to the ADO Recordset object, but more powerful, and with one other important distinction: the DataSet is always disconnected. The DataSet object represents a cache of data, with
database-like structures such as tables, columns, relationships, and
constraints. However, though a DataSet can and does behave much
like a database, it is important to remember that DataSet objects do not interact directly
with databases, or other source data. This allows the developer to work with a programming model that is always consistent,
regardless of where the source data resides. Data coming from a database, an XML file, from code, or user input can all
be placed into DataSet objects. Then, as changes are made to the DataSet they can be tracked and verified before updating the source data. The GetChanges method of the DataSet object actually creates a second DataSet that contains only the changes to the data. This DataSet is then used by a DataAdapter (or other
objects) to update the original data source.
The DataSet has many XML characteristics, including the ability to produce
and consume XML data and XML schemas. XML schemas can be used to describe
schemas interchanged via XML Web services. In fact, a DataSet with a schema can
actually be compiled for type safety and statement completion.
The DataAdapter object works as a bridge between the DataSet and the source data. Using the provider-specific SqlDataAdapter (along with its associated SqlCommand and SqlConnection) can increase overall performance when working with a Microsoft SQL Server databases. For other OLE DB-supported databases, you would use the OleDbDataAdapter object and its associated OleDbCommand and OleDbConnection objects.
The DataAdapter object uses commands to update the data source after changes have been made to the DataSet. Using the Fill method of the DataAdapter calls the SELECT command; using the Update method calls the INSERT, UPDATE or DELETE command for each changed row. You can explicitly set these commands in order to control the statements used at runtime to resolve changes, including the use of stored procedures. For ad-hoc scenarios, a CommandBuilder object can generate these at run-time based upon a select statement. However, this run-time
generation 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 result in
better run-time performance.
Dim myConnection As SqlConnection = New SqlConnection("server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind")
Dim mySqlDataAdapter As SqlDataAdapter = New SqlDataAdapter("select * from customers", myConnection)
mySqlDataAdapter.InsertCommand.CommandText = "sp_InsertCustomer"
mySqlDataAdapter.InsertCommand.CommandType = CommandType.StoredProcedure
mySqlDataAdapter.DeleteCommand.CommandText = "sp_DeleteCustomer"
mySqlDataAdapter.DeleteCommand.CommandType = CommandType.StoredProcedure
mySqlDataAdapter.UpdateCommand.CommandText = "sp_UpdateCustomers"
mySqlDataAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure
The records are appropriately mapped to the given commands accordingly.
Figure: DataAdapters and DataSets
The sample below illustrates loading a DataAdapter via a SELECT statement.
Then it updates, deletes and adds some records within the DataSet. Finally, it
returns those updates to the source database via the DataAdapter. The constructed
DeleteCommand, InsertCommand and UpdateCommand are shown in the page. It also
illustrates using multiple DataAdapter objects to load multiple tables (Customers and Orders) into the
- ADO.NET is the next evolution of ADO for the .Net Framework.
- ADO.NET was created with n-Tier, statelessness and XML in the forefront. Two new
objects, the DataSet and DataAdapter, are provided for these scenarios.
- ADO.NET can be used to get data from a stream, or to store data in a cache for updates.
- There is a lot more information about ADO.NET in the documentation.
- Remember, you can execute a command directly against the database
in order to do inserts, updates, and deletes. You don't need to first put data into a DataSet in order
to insert, update, or delete it.
- Also, you can use a DataSet to bind to the data, move through the data, and navigate data relationships.
Microsoft .NET Framework SDK QuickStart Tutorials Version 2.0
Copyright � 2005 Microsoft Corporation. All rights reserved.