14.4 Using OLE DB Managed Providers

There are two managed providers currently available with ADO.NET: the SQL Server Managed Provider and OLE DB Managed Provider. The previous example used the SQL Server Managed Provider, which is optimized for SQL Server and is restricted to working with SQL Server databases. The more general solution is the OLE DB Managed Provider, which will connect to any OLE DB provider, including Access.

You can rewrite Example 14-1 to work with the Northwind database using Access rather than SQL Server with just a few small changes. First, you need to change the connection string:

string connectionString = 
    "provider=Microsoft.JET.OLEDB.4.0; "
    + "data source = c:\\nwind.mdb";

This query connects to the Northwind database on the C drive. (Your exact path might be different.)

Next, change the DataAdapter object to an OLEDBDataAdapter rather than a SqlDataAdapter:

OleDbDataAdapter DataAdapter = 
    new OleDbDataAdapter (commandString, connectionString);

Also be sure to add a using statement for the OleDb namespace:

using System.Data.OleDb;

This design pattern continues throughout the two Managed Providers; for every object whose class name begins with "Sql," there is a corresponding class beginning with "OleDb." Example 14-2 illustrates the complete OLE DB version of Example 14-1.

Example 14-2. Using the ADO Managed Provider

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

using System.Data.OleDb;


namespace ProgrammingCSharpWinForm
{

   public class ADOForm1 : System.Windows.Forms.Form
   {
      private System.ComponentModel.Container components;
      private System.Windows.Forms.ListBox lbCustomers;

      public ADOForm1( )
      {
         InitializeComponent( );

         // connect to Northwind Access database
         
         string connectionString = 
            "provider=Microsoft.JET.OLEDB.4.0; "
            + "data source = c:\\nwind.mdb";



         // get records from the customers table
         string commandString = 
         "Select CompanyName, ContactName from Customers";

         // create the data set command object 
         // and the DataSet
         
         OleDbDataAdapter DataAdapter = 
         new OleDbDataAdapter(
         commandString, connectionString);


         DataSet DataSet = new DataSet( );

         // fill the data set object
         DataAdapter.Fill(DataSet,"Customers");

         // Get the one table from the DataSet
         DataTable dataTable = DataSet.Tables[0];
            
         // for each row in the table, display the info
         foreach (DataRow dataRow in dataTable.Rows)
         {
            lbCustomers.Items.Add(
               dataRow["CompanyName"] + 
               " (" + dataRow["ContactName"] + ")" );
         }

      }

      protected override void Dispose(bool disposing)
      {
         if (disposing)
         {
             if (components == null)
             {
                 components.Dispose( );
             }
         }
         base.Dispose(disposing);
      }

      private void InitializeComponent( )
      {
         this.components = 
            new System.ComponentModel.Container ( );
         this.lbCustomers = new System.Windows.Forms.ListBox ( );
         lbCustomers.Location = new System.Drawing.Point (48, 24);
         lbCustomers.Size = new System.Drawing.Size (368, 160);
         lbCustomers.TabIndex = 0;
         this.Text = "ADOFrm1";
         this.AutoScaleBaseSize = new System.Drawing.Size (5, 13);
         this.ClientSize = new System.Drawing.Size (464, 273);
         this.Controls.Add (this.lbCustomers);
      }

 
      public static void Main(string[] args) 
      {
         Application.Run(new ADOForm1( ));
      }
   }
}

The output from this is identical to that from the previous example, as shown in Figure 14-2.

Figure 14-2. Using the ADO Managed Provider

The OLE DB Managed Provider is more general than the SQL Managed Provider and can, in fact, be used to connect to SQL Server as well as to any other OLE DB object. Because the SQL Server Provider is optimized for SQL Server, it will be more efficient to use the SQL Server-specific provider when working with SQL Server. In time, any number of specialized managed providers will be available.