A Tale of Two Providers

A Tale of Two Providers

The very first thing that every developer should know about ADO.NET is that it has a split personality. ADO.NET database accesses go through software modules known as data providers. Version 1.0 of the .NET Framework ships with two data providers:

• The SQL Server .NET provider, which interfaces to Microsoft SQL Server databases without any help from unmanaged providers

• The OLE DB .NET provider, which interfaces to databases through unmanaged OLE DB providers

OLE DB is a data access technology that originated in the heyday of COM. OLE DB providers layer a uniform object-oriented API over disparate databases, just as Open Database Connectivity (ODBC) drivers provide a procedural front end to different kinds of databases. OLE DB providers are available for a variety of non SQL Server databases. The .NET Framework s OLE DB .NET provider lets you leverage existing OLE DB providers by calling out to them from managed code. Microsoft has tested the following OLE DB providers and deemed them compatible with the framework s OLE DB .NET provider:

• The SQLOLEDB provider, which interfaces with SQL Server databases

• The MSDAORA provider, which interfaces with Oracle databases

• The Microsoft.Jet.OLEDB.4.0 provider, which interfaces with databases driven by the Microsoft Jet database engine

In the past, some developers used the MSDASQL OLE DB provider to access databases using ODBC. MSDASQL was a generic solution that permitted databases without an OLE DB provider of their own but that had ODBC drivers available to be accessed using the OLE DB API. MSDASQL is not compatible with the .NET Framework, but you can download an ODBC .NET provider that is compatible with the framework from Microsoft s Web site.

So what does all this mean for the developer? For starters, you should decide on a provider before you write the first line of code in a project that relies on a database. Here are your choices:

• If your application will employ Microsoft SQL Server version 7.0 or later, use the SQL Server .NET provider. It s faster than the OLE DB .NET provider because it doesn t use OLE DB. It goes all the way to the database without leaving the realm of managed code. The OLE DB .NET provider, by contrast, uses the .NET Framework s Platform Invoke (P/Invoke) mechanism to call out to unmanaged OLE DB providers.

• If your application will use Microsoft SQL Server 6.5 or earlier, use the OLE DB .NET provider paired with the SQLOLEDB OLE DB provider. The SQL Server .NET provider requires SQL Server 7.0 or later.

• If your application will use a database other than SQL Server say, an Oracle 8i database use the OLE DB .NET provider.

If the database is neither Oracle nor Jet but an OLE DB provider is available for it, the provider might work. Then again, it might not. It depends on whether the database s unmanaged OLE DB provider is compatible with the .NET Framework s managed OLE DB .NET provider. Not all are. If the OLE DB driver isn t compatible with the .NET Framework (or if it doesn t exist), but an ODBC driver is available for the database in question, download Microsoft s ODBC .NET driver and use it to talk to the database.

The System.Data.SqlClient and System.Data.OleDb Namespaces

Your choice of provider directly impacts the code that you write. Some ADO.NET classes work with all providers. DataSet is a good example. Defined in the System.Data namespace, DataSet works equally well with SQL Server .NET and OLE DB .NET. But many ADO.NET classes target a specific provider. For example, DataAdapter comes in two flavors: SqlDataAdapter for the SQL Server .NET provider and OleDbDataAdapter for the OLE DB .NET provider. Sql DataAdapter and other SQL Server .NET classes belong to the System.Data.SqlClient namespace. OleDbDataAdapter is defined in System.Data.OleDb.

How does this affect the code that you write? Here s a short sample that uses the SQL Server .NET provider to list all the book titles contained in the Titles table of the Pubs database that comes with SQL Server:

using System.Data.SqlClient; . . . SqlConnection conn = new SqlConnection ("server=localhost;database=pubs;uid=sa;pwd="); try { conn.Open (); SqlCommand cmd = new SqlCommand ("select * from titles", conn); SqlDataReader reader = cmd.ExecuteReader (); while (reader.Read ()) Console.WriteLine (reader["title"]); } catch (SqlException ex) { Console.WriteLine (ex.Message); } finally { conn.Close (); }

And here s the equivalent code rewritten to use the OLE DB .NET provider (via the unmanaged OLE DB provider for SQL Server). Changes are highlighted in bold:

using System.Data.OleDb; . . . OleDbConnection conn = new OleDbConnection ("provider=sqloledb;server=localhost;database=pubs;uid=sa;pwd="); try { conn.Open (); OleDbCommand cmd = new OleDbCommand ("select * from titles", conn); OleDbDataReader reader = cmd.ExecuteReader (); while (reader.Read ()) Console.WriteLine (reader["title"]); } catch (OleDbException ex) { Console.WriteLine (ex.Message); } finally { conn.Close (); }

Notice that SqlConnection, SqlCommand, SqlDataReader, and SqlException became OleDbConnection, OleDbCommand, OleDbDataReader, and OleDbException, and that the database connection string changed too. ADO.NET provides a common API for various types of databases, but the details of that API differ slightly depending on the managed provider that you choose.

The good news is that other than class names and connection strings, few differences distinguish the SQL Server .NET and OLE DB .NET providers. The SqlDataAdapter and OleDbDataAdapter classes, for example, implement the same set of methods, properties, and events. Converting SqlDataAdapter code to use OleDbDataAdapter instead is mostly a matter of using find-and-replace to change the class names. That s good to know if you originally design your software around a SQL Server database and later decide to switch to Oracle (or vice versa).

In general, I ll use the Sql classes for the code samples in this chapter. Unless I say otherwise, you can assume that changing Sql to OleDb in the class names is sufficient to switch providers. Provider-specific class names without Sql or OleDb prefixes refer generically to classes of both types. For example, when I use the term DataReader, I m referring to both SqlDataReader and OleDbDataReader.