14.4 Using ADO Managed Providers

Example 14-1 used one of the managed providers currently available with ADO.NET: the SQL Managed Provider, the OLE DB Managed Provider, etc. The SQL Managed Provider 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:

Dim connectionString As String = _    
"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 ADODataAdapter rather than a SqlDataAdapter:

Dim myDataAdapter As New OleDbDataAdapter( _
  commandString, connectionString)

Also be sure to add an Imports statement for the OleDb namespace:

Imports 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 "ADO." Example 14-2 illustrates the complete OLE DB version of Example 14-1.

Example 14-2. Using the OLE DB Managed Provider

Option Strict On
Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb

Public Class ADOForm1
    Inherits System.Windows.Forms.Form

    Private components As System.ComponentModel.Container
    Private lbCustomers As System.Windows.Forms.ListBox

    Public Sub New( )
        InitializeComponent( )

        ' connect to my local server, northwind db
        Dim connectionString As String = _
            "provider=Microsoft.JET.OLEDB.4.0; " & _
             "data source = c:\\nwind.mdb"

        ' get records from the customers table
        Dim commandString As String = _
          "Select CompanyName, ContactName from Customers"

        ' create the data set command object 
        ' and the myDataSet
        Dim myDataAdapter As New OleDbDataAdapter( _
          commandString, connectionString)

        Dim myDataSet As New DataSet( )

        ' fill the data set object
        myDataAdapter.Fill(myDataSet, "Customers")

        ' Get the one table from the myDataSet
        Dim myDataTable As DataTable = myDataSet.Tables(0)

        ' for each row in the table, display the info
        Dim tempRow As DataRow
        For Each tempRow In myDataTable.Rows
            lbCustomers.Items.Add((tempRow("CompanyName") & _
              " (" & tempRow("ContactName") & ")"))
        Next

    End Sub 'New

    Private Sub InitializeComponent( )
        Me.components = New System.ComponentModel.Container( )
        Me.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
        Me.Text = "ADOFrm1"
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(464, 273)
        Me.Controls.Add(lbCustomers)
    End Sub 'InitializeComponent

    Public Overloads Shared Sub Main(ByVal args( ) As String)
        Application.Run(New ADOForm1( ))
    End Sub 'Main
End Class 'ADOForm1

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

Figure 14-2. Output from Example 14-2

The ADO 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.