The previous example used one of the two managed providers initially 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.

You can rewrite Example 11-3 to work with theBugs 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 OLE DB version of Example 11-3.

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
   }
}

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.