ADO.NET Overview, Page 5
Currently, there are two key sets of managed provider components — onedesigned 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 theSystem.Data.Common namespace.
So, what are the key managed-provider components?
Connection — OleDbConnection + SQLConnection — Like classicADO, this object implements properties such as the connection string and state.We also have the typical .Open and .Close,plus .BeginTransaction returning anobject to control a database transaction. Note that you no longer have a .Executemethod on the Connection object.
Command — OleDbCommand + SqlCommand — This is the pipelineto the backend data. You can use the command to either .ExecuteNonQuery, whichwill 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 objectessentially takes a stream of data from the Command object and allows you toread it. It's like a forward-only Recordset and is very efficient. Howeverthis uses a server-side cursor, so you should avoid it too much as it naturallyrequires an open connection.
Data Adapter — OleDbDataAdapter + SqlDataAdapter — The DataAdapter object provides an all-in-one stop for your data. It essentially servesas a middle man, going through your connection to retrieve data, then passingthat 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 arespecified in the InsertCommand, UpdateCommand, InsertCommand and DeleteCommandproperties.
Sample code using each of these objects will be given later.