ADO.NET OVERVIEW
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 data store.
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 subordinate objects.
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.
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:
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.
Commands:
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.
DataReaders:
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:
DataSets
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 DatSet 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 WebServices. In fact, a DataSet
with a schema can actually be compiled for type safety and statement
completion.
DATA ADAPTERS (OLEDB/SQL)
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.
1. ADO.NET is the next evolution of ADO for the .Net Framework.
2. 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.
3. ADO.NET can be used to get data from a stream, or to store data in
a cache for updates.
4. There is a lot more information about ADO.NET in the
documentation.
5. 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
