ADO.NET Overview

Currently, there are two key sets of managed provider components — one designed for general data access (in System.Data.OleDb) and one fine-tuned for SQL Server (in System.Data.SqlClient).

Both of these comply with the standard data implementations defined in the System.Data.Common namespace.

So, what are the key managed-provider components?

Connection — OleDbConnection + SQLConnection — Like classic ADO, this object implements properties such as the connection string and state. We also have the typical .Open and .Close, plus .BeginTransaction returning an object to control a database transaction. Note that you no longer have a .Execute method on the Connection object.

Command — OleDbCommand + SqlCommand — This is the pipeline to the backend data. You can use the command to either .ExecuteNonQuery, which will action an SQL statement (such as a DELETE command) upon the data — or .ExecuteReader, which links straight in to the Data Reader object.

Data Reader — OleDbDataReader + SqlDataReader — This object essentially takes a stream of data from the Command object and allows you to read it. It's like a forward-only Recordset and is very efficient. However this uses a server-side cursor, so you should avoid it too much as it naturally requires an open connection.

Data Adapter — OleDbDataAdapter + SqlDataAdapter — The Data Adapter object provides an all-in-one stop for your data. It essentially serves as a middle man, going through your connection to retrieve data, then passing that into a DataSet. You can then pass the DataSet back to the Data Adapter, which will go and update the database. The SQL statements for each command are specified in the InsertCommand, UpdateCommand, InsertCommand and DeleteCommand properties.

Sample code using each of these objects will be given later.