Managed Providers in ADO.NET

Managed Providers in ADO.NET

To get our data from the data source (a relational database, a text file, an e-mail message, and so on), we need a managed provider. Managed providers include a collection of classes for accessing various data sources.

A .NET Framework data provider serves as a bridge between an application and a data source. It is used for connecting to a data source, executing commands, and retrieving results. Those results can then be either processed directly or placed in an ADO.NET DataSet. When the data is placed in a DataSet, it can be exposed to the user in an ad-hoc manner, combined with data from multiple sources, or remoted between tiers, as we saw in Figure 10-2. A data provider is also used to reconcile changes to the data back to the data source.

The .NET data provider is designed to be lightweight, creating a minimal layer between the data source and your code, increasing performance while not sacrificing functionality. ADO.NET includes two .NET data providers:

• SQL Server .NET data provider, for Microsoft SQL Server 7 or later.

• OLE DB .NET data provider, for data sources exposed via OLE DB.

The managed providers have similar objects, as shown in Table 10-6. The only difference is the prefix SQL or OLEDB; otherwise, the programming is essentially the same. The providers abstract the functionality and handle all the heavy lifting under the hood. Both of the providers live in the System.Data namespace.

Table 10-6  .NET Data Providers

SQL Server .NET Data Provider	OLE DB .NET Data Provider
SqlCommand	OleDbCommand
SqlConnection	OleDbConnection
SqlDataAdapter	OleDbDataAdapter
SqlDataReader	OleDbDataReader
SqlParameter	OleDbParameter

As managed providers, these provider objects themselves contain objects that represent the core elements of the .NET provider model: the Connection, Command, DataReader, and DataAdapter objects. Table 10-7 describes each of these objects.

Table 10-7  .NET Data Provider Objects

Provider Object	Description
Connection	Establishes a connection to a specific data source.
Command	Executes a command at a data source. Exposes parameters and can enlist a transaction from a connection.
DataReader	Reads a forward-only, read-only stream of data from a data source.
DataAdapter	Populates a DataSet and resolves updates with the data source.

You can see in Figure 10-4 that the DataSetCommand object of a managed provider gets the data from the data source and passes it to the data set. Likewise, when the data set is modified and the user is ready to update the original data source with the changes, a DataSetCommand object from the managed provider accomplishes this operation as well.

Figure 10-4

The DataSetCommand is a bridge between a data set and a data source.

In the past, data processing relied primarily on a two-tier architecture and was connection based. As data processing increasingly uses multitier architectures, programmers are switching to a disconnected approach to data processing to provide better scalability for their applications. The DataSetCommand provides an important tool in this approach for ADO.NET. As Figure 10-4 illustrates, a DataSetCommand loads a DataSet object and provides the bridge between the DataSet object and its data source for retrieving and saving data. It accomplishes this by invoking the appropriate SQL commands against the data source.

As I noted earlier, the .NET Framework includes the SQL Server .NET data provider and the more generic OLE DB .NET data provider. In our examples, we'll use the SQL Server data provider. However, the syntax for the OLE DB provider is almost identical. Many beginners will be scratching their heads wondering which provider to use. Table 10-8 lists some tips on which managed provider to choose for your task at hand.

Table 10-8  Recommended Uses of the ADO.NET Managed Data Providers

Provider	Recommended Uses
SQL Server .NET data provider	Middle-tier applications using Microsoft SQL Server 7 or later. Single-tier applications using Microsoft Data Engine (MSDE) or Microsoft SQL Server 7 or later. Recommended over the OLE DB Provider for SQL Server (SQLOLEDB) with the OLE DB .NET data provider. Note: For Microsoft SQL Server 6.5 and earlier, you must use the OLE DB Provider for SQL Server with the OLE DB .NET data provider.
OLE DB .NET data provider	Middle-tier applications using Microsoft SQL Server 6.5 or earlier or Oracle. Single-tier applications using Microsoft Access databases. Use of the OLE DB .NET data provider with a Microsoft Access database for a middle-tier application is not recommended. Note: Support for the OLE DB Provider for ODBC (MSDASQL) is disabled.

A Common Provider Model

As should be evident to you, ADO.NET exposes a common model for .NET data provider objects. A single set of code can be used regardless of the .NET data provider you choose. For example, the following code, shown only for purposes of illustration, will work with either the SQL Server .NET or the OLE DB .NET data provider. The code shows how you could implement a Command object by creating a class that implements IDbCommand. The IDataReader interface allows an inheriting class to implement a DataReader class, which provides a means of reading one or more forward-only streams of result sets.

Dim cmdCommand As IDbCommand = myConn.CreateCommand() cmdCommand.CommandText = "SELECT * FROM Customers" Dim drReader As IDataReader = cmdCommand.ExecuteReader() Do While drReader.Read() Console.WriteLine("{0}" & vbTab & "{1}", & _ drReader.GetString(0), drReader.GetString(1)) Loop

As I noted, this example is for illustration only. We will be using the SQL Server data provider in our examples in this chapter.