 
Introduction
The primary means of talking to a database is through
ADO.NET. To a large extent, there is overlap, but just because you already know
ADO, that doesn’t mean you know ADO.NET. It’s a very good start but you’re going
to have to be careful in your assumptions.
The namespace System.Data defines the primary types and
functionality of the ADO.NET.
In a nutshell, here’s how ADO.NET works: The application
defines an object that knows how to access the database. It copies data from
that database into a memory object and then disconnects from the database. This
memory object is normally referred to as a ‘dataset’ and may comprise not just a
single table but a set of related tables. Inside of the data set are objects
that encapsulate access to tables, columns and row. The rows contain the actual
table data. Columns define the properties of the fields in the rows that contain
the data. The program operates on the in memory data set and writes the results
back to the database. The data is encapsulated in an XML document before being
transported between the application and the database.
That last line has some ramifications. Conventional ADO
used COM for data transport. COM uses RPC to transport data between machines and
so could be stonewalled at the firewall because it is not unusual for a firewall
to block COM RPC packets. This is no longer an issue with ADO.NET.
ADO.NET comes with two managed data providers. First of
all, there’s SQL Server. ADO.NET comes with an optimized data provider mechanism
for SQL Server. The other is a generic interpreter between ADO.NET and OLEDB.
Effectively, that means that any database that has an OLEDB interface can also
work with ADO.NET.
In real simple terms, this is what happens- a database
connection object is created and told how to connect up with a database. This
database connection object creates a command object. The command object accepts
instructions from the program on what is to happen. A DataAdapter object is
created. It is responsible for moving data into and out of the database in a
manner appropriate to the command object’s instructions. A DataSet is created.
It is the mechanism that contains the data while the data is being held in
memory. The DataSet is operated on by bound controls or the program itself.
Typically, the DataSet is filled by the DataAdapter which extracted data from
the database connection in a manner specified by the command object. Once all of
the additions, modifications or deletions are complete, the DataSet is written
back to the database.
Here’s the general steps required in order to access data:
-
A new connection object is created.
-
The object is given connection values.
-
A connection to a database is opened.
-
A SQL select string is constructed.
-
A new command object is created, using that select
string.
-
The command object executes.
-
A new data reader object is constructed.
-
The data reader object is used to access the
dataset.
-
The data reader is closed.
-
The connection is closed.
There are two pre-defined managed data providers.
This brings me to a pet peeve. There are only two data
providers defined at this time. How hard could it have been for the two to have
been defined so that they were used the same? Too hard, apparently, because the
two namespaces use different calls.
The previous version of ADO only dealt with a single table.
This new ADO.NET dataset doesn’t have that limitation and could conceivably be
populated with the complete contents of an entire database.
The dataset is organized into rows and columns. A column
has certain properties, among which are the name of the column and the type of
the data it contains.
A row contains a collection of columns. A particular column
within that collection can be specified by its name.
A collection of rows can be iterated either by seeking to a
particular row by its primary key ID or by simply iterating through the
collection.
Overview of Objects
ADO.NET relies heavily upon a set of cooperating objects to
implement a client’s data access actions.
Connection Object
The Connection object is responsible for establishment of
communications between the client application and the database itself.
Command Object
The Command object is responsible for the implementation of
a client’s commands. This command may be a select, delete or any other SQL
statement.
Parameter Object
A Parameter object operates in conjunction with a Command
object. A Command object has a Parameters collection. The collection, not
surprisingly, consists of zero or more Parameter objects. Each object describes
a parameter that is fed into a command.
For example, let’s take this SQL statement:
Select * from tblConfig where ID =
‘13’
If this were to be written in a parameterized format, we’d
have
Select * from tblConfig where ID =
‘?’
The contents of the ‘?’ would be substituted by the data
contained within the first parameter object.
Parameter objects are merged into the command string such
that the order of the parameter object corresponds to the order of the command
argument.
DataReader Object
The DataReader encapsulates the in-memory database
information itself.
DataReaders have tables. Tables have constraints, row and
column collections and, additionally, may have associated views.
Transaction Object
DataAdapter object
| 
.
