The.NET Data Providers are responsible for connecting your .NET application to a data source. The .NET Framework comes with four built-in .NET Data Providers. Each of the .NET Data Providers is maintained in its own namespace within the .NET Framework.

Namespaces for the .NET Data Providers

Four .NET Data Providers are delivered with the .NET Framework: the .NET Data Provider for SQL Server, the .NET Data Provider for Oracle, the .NET Data Provider for OLE DB, and the .NET Data Provider for ODBC. The .NET Data Provider for SQL Server is contained in the System.Data.SqlClient namespace. The .NET Data Provider for Oracle is contained in the System.Data.OracleClient namespace. The .NET Data Provider for OLE DB is contained in the System.Data.OleDbClient namespace. And the .NET Data Provider for ODBC is contained in the System.Data. Odbc namespace.

System.Data.SqlClient

The System.Data.SqlClient is the .NET managed data provider for SQL Server. The System.Data.SqlClient namespace uses SQL Server’s native TDS (Tabular Data Stream) protocol to connect to the SQL Server system. Using the native TDS protocol makes the .NET Data Provider for SQL Server the fastest possible connection between a client application and SQL Server.

System.Data.OleDb

The System.Data.OleDb namespace is the .NET managed data provider for OLE DB data sources. Whereas the System.Data.SqlClient namespace can be used to access SQL Server 7, 2000, or 2005 databases, the System.Data.OleDb namespace is used to access SQL Server 6.5 databases or earlier, as well as Oracle and Access databases. Theoretically, the .NET Data Provider for OLE DB can access any database where there’s an OLE DB Provider—with the exception of the Microsoft OLE DB Provider for ODBC. Microsoft purposely restricted the capability to access ODBC from the .NET Data Provider for OLE DB.

System.Data.OracleClient

The System.Data.OracleClient namespace is the .NET managed data provider for Oracle databases. The .NET Data Provider for Oracle requires that the Oracle 8 or higher client be installed on the system. The System.Data.OracleClient namespace uses Oracle’s native OCI (Oracle Call Interface) to connect to Oracle 8 and higher databases.

System.Data.Odbc

The System.Data.Odbc namespace is the .NET managed data provider for ODBC data sources. Microsoft designed the .NET Data Provider for ODBC to be able to access any ODBC-compliant database. However, Microsoft officially supports only connections using the Microsoft SQL Server ODBC driver, the Microsoft ODBC driver for Oracle, and the Microsoft Jet ODBC driver. However, we have successfully used this provider to connect to DB2 databases as well.

Core Classes for the .NET Data Providers

All of the.NET Data Providers included in the .NET Framework are essentially architected the same. In other words, the classes contained in each namespace have nearly identical methods, properties, and events. However, the classes each use a slightly different naming convention. For instance, all of the classes in the .NET Data Provider for SQL Server, found in the System.Data.SqlClient namespace, begin with a prefix of “Sql”; the classes that are part of the .NET Provider for OLE DB, found in the System.Data.OleDb namespace, all begin with the prefix of “OleDb”. Both namespaces contain classes that are used to initiate a connection to a target data source. For the System.Data.SqlClient namespace, this class is named SqlConnection. For the System.Data.OleDb namespace, this class is named OleDbConnection. In each case, the methods that are provided and their parameters are basically the same. Because the function and usage of these classes are basically the same, they are grouped together in the following section under their generic function names. The following section presents an overview of the primary classes contained in the .NET Data Provider namespaces.

Connection

The Connection class is used to open a connection to a target data source. A Connection object is required in order to populate either the DataReader object or the DataSet object with data from the target data source. Likewise, an active Connection object is required in order to execute any commands or stored procedures that exist on the database from the client .NET applications. Unlike most other .NET objects, Connection objects are not automatically destroyed when they go out of scope. This means that you must explicitly close any open ADO.NET Connection objects in your applications. If multiple Connection objects are opened that use the same connection string, they will be automatically added to the same connection pool.

NOTE The actual functionality provided by the OleDbConnection class and the OdbcConnection class is dependent on the capabilities of the underlying OLE DB Provider and ODBC driver. Not all providers and drivers will necessarily support the same functionality.

Command

The Command class is used to execute either a stored procedure or a SQL statement on the data source that’s associated with the active Connection object. Three types of commands are supported: ExecuteReader, ExecuteNonQuery, and ExecuteScalar. ExecuteReader commands return a result set. ExecuteNonQuery commands are used to execute SQL action queries like Insert, Update, and Delete statements that do not return any rows. ExecuteScalar commands are used to execute stored procedures or SQL queries that return a single value.

Parameter

The Parameter class is used to represent a parameter that’s passed to a Command object. Parameter objects have properties that define their attributes. For instance, the different properties of a Parameter object specify the parameter’s name, its direction, its data type, its size, and its value. Parameter names are not case-sensitive, but when naming Parameter objects that represent stored procedure parameters, naming the parameter the same as the stored procedure parameter is typically a good idea. For instance, if the Parameter object represents a stored procedure parameter named @CustomerID, using that same name when instantiating the Parameter object is a good practice. A Parameter object can also be mapped to a DataColumn in the DataSet.

DataReader

The DataReader class returns a forward-only stream of data from the target data source that’s associated with the active connection object. Unlike objects in most other ADO.NET classes that are instantiated by calling the constructor, objects created from the DataReader class are instantiated by calling the ExecuteReader method.

DataAdapter

The basic task of the DataAdapter class is to serve as a link between a DataSet object and the data source represented by the active Connection object. The DataAdapter class includes properties that allow you to specify the actual SQL statements that will be used to interact between the DataSet and the target database. In other words, the DataAdapter is responsible for both filling up the DataSet as well as sending changes made in the DataSet back to the data source. For example, the DataAdapter class provides the SelectCommand property, which controls the data that will be retrieved; the InsertCommand property, which indicates how new data in the DataSet will be added to the database; the UpdateCommand property, which controls how changed rows in the DataSet will be posted to the database; and the DeleteCommand property, which controls how rows deleted in the DataSet will be deleted from the database.

CommandBuilder

The CommandBuilder class provides a mechanism for automatically generating the SQL commands that will be used to update the target database with changes in an attached DataSet. The CommandBuilder uses the metadata returned by the SQL statement in the DataAdapter’s SelectCommand property to generate any required Insert, Update, and Delete statements. Changes made in the DataSet are not automatically posted to the database unless SQL commands are assigned to the DataAdapter InsertCommand, UpdateCommand, and DeleteCommand properties or unless a CommandBuilder object is created and attached to the active DataAdapter object. Only one CommandBuilder object can be associated with a given DataAdapter at one time.

Transaction

The Transaction class represents a SQL transaction. SQL transactions basically allow multiple database transactions to be treated as a unit where an entire group of database updates can either be posted to the database or all be undone as a unit. The Transaction object uses the BeginTransaction method to specify the start of a transaction and then either the Commit method to post the changes to the database or the Rollback method to undo the pending transaction. A Transaction object is attached to the active Connection object.

Error

The Error class contains error information that is generated by the target data source. The active Connection object is automatically closed when an error with a severity of greater than 20 is generated by the target database. However, the connection can be subsequently reopened.

Exception

The Exception class is created whenever the .NET Data Provider encounters an error generated by one of its members. An Exception object always contains at least one instance of the Error object. You trap exceptions in your code by using the .NET Frameworks Try-Catch structure error handling.