Managed Providers

The previous examples used one of the managed providers initially available with ADO.NET: the SQL managed provider. The SQL managed provider is optimized for Microsoft SQL Server, but it is restricted to working with SQL Server databases. The more general solution is the OLE DB managed provider, which connects to any OLE DB provider, including Microsoft Access and MSDE (which is provided for free).

You can rewrite Example 19-1 to work with the Bugs database using Microsoft Access rather than Microsoft SQL Server with just a few small changes. First, you need to create a new Access database. Name the new database SimpleBugListBoxAccessDB. Example 19-6 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 accordingly.

Use the File Get External Data 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 Application project named SimpleBugListboxAccessCS, and once again drag a ListBox onto the form, resize it, and name it lbBugs. Copy the code from Example 19-2, but make the following changes:

1. Change the connection string to:

   

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

   This will connect to the database you just created. Note the space between data and source in the connection string. Be sure to substitute the correct path for the mdb file on your machine.

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

   

   OleDbDataAdapter DataAdapter =
    new OleDbDataAdapter (commandString, connectionString);

3. Replace the using statement (in C#) or the imports statement (in VB.NET) System.Data.SqlClient with the corresponding statement for the OleDb namespace:

   

   using System.Data.OleDb;

   

   imports System.Data.OleDb

   This design pattern continues while you work with the two managed providers; for every object whose class name begins with Sql, a corresponding class begins with OleDb. Example 19-6 is the complete OLE DB version of Example 19-2 in C#, while Example 19-7 is the complete OLE DB version in VB.NET.

Example 19-6. Using ADO.NET with Access (in C#)

using System;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
 
 
namespace SimpleBugListboxAccessCS
{
 /// 

/// Summary description for Form1. ///

public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.ListBox lbBugs; ///

/// Required designer variable. ///

private System.ComponentModel.Container components = null; public Form1( ) { // // Required for Windows Form Designer support // InitializeComponent( ); // connect to the Bugs database string connectionString = "provider=Microsoft.JET.OLEDB.4.0; " + "data source = c:\simpleBugListBoxAccessDB.mdb"; // get records from the Bugs table string commandString = "Select BugID, Description from dbo_Bugs"; // 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) { lbBugs.Items.Add( dataRow["BugID"] + ": " + dataRow["Description"] ); } } ///

/// Clean up any resources being used. ///

protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose( ); } } base.Dispose( disposing ); } #region Windows Form Designer generated code #endregion ///

/// The main entry point for the application. ///

[STAThread] static void Main( ) { Application.Run(new Form1( )); } } }

Example 19-7. Using ADO.NET with Access (in VB.NET)

Public Class Form1
 Inherits System.Windows.Forms.Form
 
#Region " Windows Form Designer generated code "
 
 Public Sub New( )
 MyBase.New( )
 
 'This call is required by the Windows Form Designer.
 InitializeComponent( )
 
 Dim connectionString As String
 connectionString = _
 "provider=Microsoft.JET.OLEDB.4.0; data source = _
 c:\simpleBugListBoxAccessDB.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
 
 
#End Region
 
End Class

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, shown in Figure 19-19, is identical to that from the previous example (except for the change you've made to the description of the first bug).

Figure 19-19. 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 Microsoft 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.