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.

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 ();
}

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 ();
}