Using ADO.NET Data Providers

This article has been excerpted from the book "A Programmer's Guide to ADO.NET in C#".

In this article, you'll examine ADO.NET data provider-specific classes.

First of all, what are the ADO.NET data providers? Technically, an ADO.NET data provider is a set of classes that enables you to connect to a data source to read and write data from a data source. A data provider also has components that serve as a conduit between the data source and the DataSet. In this way, the architecture isolates the manipulation of data from the source of the data. Currently, providers utilize the APIs of three main technologies that support database connectivity: the OLE-DB API, the ODBC API, and SQL server API.

ADO.NET has a number of data providers. Some of them are the OleDb data provider for manipulating databases supporting OleDb connectivity, the Sql data provider for connecting to SQL server 7 or later databases, and the ODBC data provider to connect to any ODBC data sources.

Figure 5-20 shows a generic class model of a data provider. The same component model applies to all of the Data providers. All data providers implement the same class model, so once you're comfortable with one data provider, you can easily manipulate other data providers in no time. It's just a matter of changing class names and the connection string.

Figure 5-20. A generic data provider

You'll begin your understanding of the ADO.NET data provides by examining the different data provider namespaces contained in ADO.NET. The namespaces contain the classes necessary to manipulate the data providers.

As you've seen in the ADO.NET class hierarchy section of this article, ADO.NET implements n number of data providers. Some of them are OleDb, SQL, and ODBC.

The System.Data.OleDb namespace defines classes that work with OLEDB data sources. These data providers use the native OLEDB API to connect with OLEDB data sources, such as Jet databases, XML files, and Microsoft Access databases. To work with a specific data source, you must have an OLEDB provider for that data source. Table 5-15 describes most of the OleDb data provider classes. I'll use these classes throughout this article.

Table 5-15. OleDb Data Provider Classes

CLASS	DESCRIPTION
OleDbCommand	Represents an SQL statement or stored procedure Data Can be returned in a DataReader by Calling its ExecuteReader method.
OleDbCommandBuilder	Providers a mechanism to generate automatic commands for a table.
OleDbConnection	Represents a connection object. This is the first-class need to use to connect to data sources.
OleDbAdapter	A set of commands provides a link between OleDbConnection and DataSet.
OleDbError	Collects error and warnings returned by the Data source.
OleDbErrorCollection	Collects errors generated by OLE DB data providers.
OleDbException	Exception handling class designed for OLE DB data sources.
OleDbParameter	This class represents a parameter used in OleDbCommand.
OleDbParameterCollection	A collection of a parameter.
OleDbPermission	Ensures that a user has enough security to access OLE DB data sources.
OleDbPermissionAttributes	Custom attributes can be added to security action.
OleDbTransaction	A transaction that can be processed at a data source.

Similar to the OleDb data providers, SQL and ODBC data providers provide the same class hierarchy model as in figure 5-5. The only difference is the prefix begins with OleDb, the SQL server provider namespace. For example, the OleDb provider classes begin with OleDb, the SQL Server provider namespace classes start with Sql, and the ODBC provider classes begin with ODBC. This is fairly logical for a change! The System.Data classes have no prefix, of course, because they're classes used in memory and shared by all the providers.

The way ADO.NET works is that once you've connected to a database and dumped the data into the in-memory object (known as the DataSet), you can disconnect from the database and manipulate the data until you're ready to write it back to the database. The data provider classes serve as a bridge for moving data back and forth between memory and the database. In the next section, I'll discuss how to initially connect to a database using these providers and then use the data provider classes to allow data to utilize this efficient bridge for your data.

In the section that follows I'll describe how to connect to various databases with the data providers. Then I'll show you how to use the data provider classes to execute queries and stored procedures. Also, you'll see how these powerful classes work together and how the data providers differ in certain capabilities. You'll also learn how to do transaction locking batch transactions, and transaction rollbacks. Finally, you'll learn how to capture database exceptions and information messages as well as learn how to interpret these errors.

Conclusion

Hope this article would have helped you in understanding using ADO.NET Data Providers. See my other articles on the website on ADO.NET.