Connected Operations in ADO.NET
Connected operations in ADO.NET are data access scenarios that require tight binding and open connection to the data source while working with the connected subset of the ADO.NET object model. This is in contrast to the round-tripping and client manipulation of the DataSet, which is the disconnected data vehicle of choice in ADO.NET.
Those versed in data access with ADO will find the connected operations of ADO.NET familiar ground to tread upon. Microsoft intentionally kept many of the concepts from the previous data access model consistent to aid the programmer in making the transition.
The specific classes discussed in this article are Connection, Command, and DataReader. They work in a very familiar manner to the ADO Connection, Command, and "firehose" cursor Recordset.
To download a zip file containing the source files for this article, click here.
Choosing a Provider
The first thing you must do when working with ADO.NET is select the .NET data provider to use for working with your database or data source. If you choose SQL Server for your database, the recommendation is the SQL Server .NET data provider and the classes from the System.Data.SqlClient namespace. If compatibility with OLEDB is required to access a source such as Oracle, the best choice is the OLEDB .NET data provider and the System.Data.OleDb namespace.
For the most part, the two data providers have complementary features. I'll focus mainly on the SQL provider in the following code examples, but will point out differences between the two providers' implementations when necessary. At the end of this article, I'll discuss how to write code that's neutral to either provider.