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:

1. Change the connection string to:

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

   This will connect to the database you just created.

2. Change the DataAdapter object to be an OleDbDataAdapter rather than a SqlDataAdapter:

   OleDbDataAdapter DataAdapter =      new OleDbDataAdapter (commandString, connectionString);

3. Replace the System.Data.SqlClient with the using statement for the OleDb namespace:

   using System.Data.OleDb;

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.