Core C# and .NET

The ADO.NET architecture is designed to make life easier for both the application developer and the database provider. To the developer, it presents a set of abstract classes that define a common set of methods and properties that can be used to access any data source. The data source is treated as an abstract entity, much like a drawing surface is to the GDI+ classes. Figure 11-1 depicts this concept.

For database providers, ADO.NET serves as a blueprint that describes the base API classes and interface specifications providers must supply with their product. Beneath the surface, the vendor implements the custom code for connecting to their database, processing SQL commands, and returning the results. Many database products, such as MySQL and Oracle, have custom .NET data provider implementations available. In addition, they have generic OLE DB versions. The .NET data provider should always be the first choice because it offers better performance and often supports added custom features. Let's look at both the OLE DB and native .NET data providers

OLE DB Data Provider in .NET

An OLE DB provider is the code that sits between the data consumer and the native API of a data source. It maps the generic OLE DB API to the data source's native APIs. It is a COM-based solution in which the data consumer and provider are COM objects that communicate through COM interfaces. Database vendors and third parties have written OLE DB providers for just about every significant data source. In contrast, far fewer .NET data providers exist. To provide a bridge to these preexisting OLE DB interfaces, .NET includes an OleDB data provider that functions as a thin wrapper to route calls into the native OLE DB. Because interoperability with COM requires switching between managed and unmanaged code, performance can be severely degraded.^[1]

^[1] Test results for .NET 1.1 have shown the SQL Client provider to be up to 10 times faster than OLE DB.

As we see in the next section, writing code to use OLE DB is essentially the same as working with a .NET data provider. In fact, new .NET classes provide a "factory" that can dynamically produce code for a selected provider. Consequently, responding to a vendor's upgrade from OLE DB to a custom provider should have no appreciable effect on program logic.

.NET Data Provider

The .NET data provider provides the same basic service to the client as the OLE DB provider: exposing a data source's API to a client. Its advantage is that it can directly access the native API of the data source, rather than relying on an intermediate data access bridge. Native providers may also include additional features to manipulate data types native to the data source and improve performance. For example, the Oracle provider, ODP.NET, includes adjustable settings to control connection pooling, the number of rows to be pre-fetched, and the size of non-scalar data being fetched.

Data Provider Objects for Accessing Data

A managed data provider exposes four classes that enable a data consumer to access the provider's data source. Although these classes are specific to the provider, they derive from abstract ADO.NET classes:

• DbConnection. Establishes a connection to the data source.

• DbCommand. Used to query or send a command to the data source.

• DbDataReader. Provides read-only and forward-only access to the data source.

• DBDataAdapter. Serves as a channel through which a DataSet connects to a provider.

Because these are abstract classes, the developer is responsible for specifying the vendor's specific implementation within the code. As we see next, the object names can be hard coded or provided generically by a provider factory class.

Provider Factories

Each data provider registers a ProviderFactory class and a provider string in the machine.config file. The available providers can be listed using the static GetFactoryClasses method of the DbProviderFactories class. As this code shows, the method returns a DataTable containing four columns of information about the provider.

DataTable tb = DbProviderFactories.GetFactoryClasses(); foreach (DataRow drow in tb.Rows ) { StringBuilder sb = new StringBuilder(""); for (int i=0; i<tb.Columns.Count; i++) { sb.Append((i+1).ToString()).Append(drow[i].ToString()); sb.Append("\n"); } Console.WriteLine(sb.ToString()); }

Running this code for ADO.NET 2.0 lists four Microsoft written providers: Odbc, OleDb, OracleClient, and SqlClient. Figure 11-2 shows output for the SqlClient provider.

To use these providers, your code must create objects specific to the provider. For example, the connection object for each would be an OdbcConnection, OleDbConnection, OracleConnection, or SqlConnection type. You can create the objects supplied by the providers directly:

SqlConnection conn = new SqlConnection(); SqlCommand cmd = new SqlCommand(); SqlDataReader dr = cmd.ExecuteReader();

However, suppose your application has to support multiple data sources. A switch/case construct could be used, but a better and more flexible approach is to use a class factory to create these objects dynamically based on the provider selected. ADO.NET provides just that a DbProviderFactory class that is used to return objects required by a specific data provider. It works quite simply. A string containing the provider name is passed to the GetFactory method of the DbProviderFactories class. The method returns a factory object that is used to create the specific objects required by the provider. Listing 11-1 demonstrates using a factory.

Listing 11-1. Using the DbProviderFactory Class

// System.Data.Common namespace is required DbProviderFactory factory ; string provider = "System.Data.SqlClient"; // data provider string connstr = "Data Source=MYSERVER;Initial Catalog=films; User Id=filmsadmin;Password=bogart;"; // Get factory object for SQL Server factory = DbProviderFactories.GetFactory(provider); // Get connection object. using ensures connection is closed. using (DbConnection conn = factory.CreateConnection()) { conn.ConnectionString = connstr; try { conn.Open(); DbCommand cmd = factory.CreateCommand(); // Command object cmd.CommandText = "SELECT * FROM movies WHERE movie_ID=8" ; cmd.Connection = conn; DbDataReader dr; dr = cmd.ExecuteReader(); dr.Read(); MessageBox.Show((string)dr["movie_Title"]); conn.Close(); } catch (DbException ex) { MessageBox.Show(ex.ToString()); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }

This approach requires only that a provider and connection string be provided. For example, we can easily switch this to an ODBC provider by changing two statements:

string provider= "System.Data.Odbc"; // The DSN (Data Source Name) is defined using an ODBC utility string connstr = "DSN=movies;Database=films";

Note that the factory class provides a series of Create methods that returns the objects specific to the data provider. These methods include CreateCommand, CreateConnection, and CreateDataAdapter.