11.4 Managed Providers

The previous example used one of the four managed providers currently available with ADO.NET: the SQL Managed provider. The SQL Managed provider is optimized for SQL Server, but it 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. There is also a managed provider for Oracle and on for ODBC-compliant databases.

You can rewrite Example 11-3 to work with the Bugs database using Access rather than SQL Server with just a few small changes. First, of course, you need to create a new Access database. Name the new database SimpleBugListBoxAccessDB. Example 11-4 assumes you will save your database to the root directory on your C drive, but you may save it anywhere else that is convenient for you as long as you adjust the connection string.

Use the File Import menu option in Access to import the data from the SQL database. This will create tables in Access that reflect the structure and content of the data in the SQL database. Notice that the Bugs database is now named dbo_Bugs in Access.

Create a new ASP web application project named SimpleBugListBoxAccess and once again drag a list box onto the form and name it lbBugs. Copy the code from Example 11-3, but make the following changes:

This design pattern continues throughout working with the two managed providers; for every object whose class name begins with Sql, there is a corresponding class beginning with OleDb. Example 11-4 is the complete C# OLE DB version of Example 11-3.

Example 11-4. Using ADO.NET with Access in C#

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace SimpleBugListBoxAccess
{
   /// <summary>
   /// Summary description for WebForm1.
   /// </summary>
   public class WebForm1 : System.Web.UI.Page
   {
      protected System.Web.UI.WebControls.ListBox lbBugs;
   
      public WebForm1(  )
      {
         Page.Init += new System.EventHandler(Page_Init);
      }

      private void Page_Load(object sender, System.EventArgs e)
      {
         // connect to the Bugs database
       string connectionString = 
          "provider=Microsoft.JET.OLEDB.4.0; " +
          "data source = c:\\SimpleBugListAccessDB.mdb";

         // get records from the Bugs table
         string commandString = 
            "Select BugID, Description from dbo_Bugs";

         // create the dataset command object 
       // and the DataSet
       OleDbDataAdapter dataAdapter = 
          new OleDbDataAdapter (
          commandString, connectionString);

         DataSet dataSet = new DataSet(  );

         // fill the dataset  object
         dataAdapter.Fill(dataSet,"Bugs");

         // 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)
         {
            lbBugs.Items.Add(
               dataRow["BugID"] + 
               ": " + dataRow["Description"]  );
         }      
      }

      private void Page_Init(object sender, EventArgs e)
      {
         InitializeComponent(  );
      }

      #region Web Form Designer generated code
      private void InitializeComponent(  )
      {    
         this.Load += new System.EventHandler(this.Page_Load);

      }
      #endregion
   }
}

Example 11-5 is the OLE DB VB.NET version of the Page_Load routine.

Example 11-5. Page_Load in OLE DB VB.NET

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)     
Handles MyBase.Load

    Dim connectionString As String
    connectionString = "provider=Microsoft.JET.OLEDB.4.0; " & _
        "data source = c:\SimpleBugListAccessDB.mdb"

    Dim commandString As String
    commandString = "Select BugID, Description from dbo_Bugs"

    Dim myDataAdapter As New System.Data.OleDb.OleDbDataAdapter( _
        commandString, connectionString)

    Dim myDataSet As New DataSet

    myDataAdapter.Fill(myDataSet, "Bugs")

    Dim myDataTable As DataTable
    myDataTable = myDataSet.Tables(0)

    Dim theRow As DataRow
    For Each theRow In myDataTable.Rows
      lbBugs.Items.Add(theRow("BugID") & ": " & theRow("Description"))
    Next

  End Sub

Before you run this program, edit the description of the first bug to include the word Access; this will help you ensure that you are looking at the correct data. The output, which is shown in Figure 11-7, is identical to that from the previous example (except for the change you've made to the description of the first bug).

Figure 11-7. Using the ADO 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, there will be any number of specialized managed providers available.