Using OleDb, SqlXml and Odbc member classes

Give the other managed data providers -- System.Data.OleDb, System.Data.Odbc and Microsoft.Data.SqlXml -- a test drive.

The following tip was excerpted from Chapter 1, 'Migrating from ADO to ADO.NET,' of the book Expert One-on-one Visual Basic 2005 Database Programming by Roger Jennings, courtesy of Wiley Publishers. Click here for the complete collection of book excerpts.

Most data-centric VB 2005 demonstration projects connect to a SQL Server instance with SqlClient objects while developers gain familiarity with .NET's panoply of System.Data classes. Thus, the preceding examples use the SqlClient data provider. You should, however, give the other managed providers -- System.Data.OleDb, System.Data.Odbc, and Microsoft.Data.SqlXml -- a test drive with the OleDbDataProjects.sln project in your VB2005DBChapter01 OleDbDataProjects folder. Figure 1-4 shows OleDbDataProject's form with list boxes and a text box that display data generated by each of the three providers. Marking the Use OdbcDataReader checkbox substitutes the Odbc for the OleDb data provider to fill the Rowset 1 (Shippers) list box.


Figure 1-4

You can take advantage of ADO.NET 2.0's new DbProviderFactories.GetFactory ("System.Data.Provider") method and the DbProviderFactory.CreateConnection and CreateCommand methods to generate a connection to and commands for any available managed data provider. Chapter 2's "Use the DbProviderFactories to Create Database-Agnostic Projects" section shows you how to write applications that accommodate multiple relational database management systems.

Each sample procedure has its own connection string. You must modify each connection string to point to your Microsoft Access, SQL Server or SQL Express instance.

The SQLXML Managed Classes (Microsoft.Data.SqlXml) native data provider for SQL Server 2000 isn't a member of the .NET Framework 2.0. It's a component of Microsoft SQLXML 4.0, which VS 2005 and VB Express install as Microsoft.Data.SqlXml.dll.

Substitute OleDb for SqlClient Objects

The OleDb data provider is your best bet for connecting to Access (Jet 4.0) database files or database servers for which you don't have a native .NET data provider. The OleDb provider also lets you create applications that might work with the user's choice of database servers. In most cases, you can replace Imports System.Data.SqlServer with Imports System.Data.OleDb, substitute the appropriate OLE DB connection string, and replace the prefix of data objects from Sql to OleDb. In some cases, you might need to alter the SQL statement for a specific database back end's SQL dialect. For example, the Jet query engine recognizes the semicolon as an SQL statement terminator but won't return additional resultsets from another SQL statement that follows the semicolon. Thus, the code for Northwind.mdb in the following OpenOleDbDataReader listing reuses the OleDbCommand with a second SQL statement:

     Private Sub OpenOleDbDataReader()
          'Define and open the OleDbConnection object
          Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
            "Data Source=C:Program FilesMicrosoft OfficeOFFICE11" + _
            "SAMPLESNorthwind.mdb;Persist Security Info=False"
          'Substitute the following if you don't have Northwind.mdb available
          'Dim strConn As String = "Provider=SQLOLEDB;" + _
          ' "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"

          Dim cnnNwind As OleDbConnection = New OleDbConnection(strConn)
          cnnNwind.Open()

          'Define the OleDbCommand
          Dim strSQL As String = "SELECT * FROM Shippers"
          'strSQL += ";SELECT EmployeeID, FirstName, LastName FROM Employees"
          Dim cmdReader As OleDbCommand = New OleDbCommand(strSQL, cnnNwind)
          cmdReader.CommandType = CommandType.Text

         'Define, create, and traverse the OleDbDataReader
         'Don't close the connection when closing the OleDbDataReader
         Dim odbReader As OleDbDataReader = _
               cmdReader.ExecuteReader(CommandBehavior.Default)
         lstShippers.Items.Clear()
         With odbReader
              If .HasRows Then
                 While .Read
                       'Process the rows
                       lstShippers.Items.Add(.Item(0).ToString + _
                           " - " + .Item(1).ToString)
                      End While
                     .Close()
              End If
         End With
         lstEmployees.Items.Clear()
         cmdReader.CommandText = "SELECT EmployeeID, FirstName, LastName FROM Employees"
         odbReader = cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
         'Process additional resultsets
         With odbReader
              If .HasRows Then
                While .Read
                      'Process additional rows
                      lstEmployees.Items.Add(.Item(0).ToString + " - " + _
                          .Item(1).ToString + " " + .Item(2).ToString)
               End While
             End If
             'Close the OleDbDataReader and the OleDbConnection
             .Close()
       End With
   End Sub

You must close the first DataReader before you change the CommandText property to reuse the OleDbCommand object.

Replace SqlConnection and SqlCommand with SqlXmlCommand

Returning XmlReader objects with the OleDb data provider requires adding a project reference to Microsoft.Data.SqlXml. Adding an Imports Microsoft.Data.SqlXml statement to your form's class file simplifies references to its classes. An interesting feature of the SqlXmlCommand object is that it doesn't require an SqlConnection object, as illustrated by the following listing for the OpenSqlXmlReader procedure:

     Private Sub OpenSqlXmlReader()
          'This procedure requires installing SQLXML 3.0 SP-2 or later
          'and a project reference to Microsoft.Data.SqlXml

          'Define OleDb connection string
          Dim strConn As String = "Provider=SQLOLEDB;Data Source=localhost;" + _
                "Initial Catalog=Northwind;Integrated Security=SSPI"

          'Define the SqlXmlCommand
          Dim strSQL As String = "SELECT * FROM Shippers FOR XML AUTO, Elements"
          Dim cmdXml As SqlXmlCommand = New SqlXmlCommand(strConn)
          cmdXml.CommandText = strSQL
          Dim xrShippers As System.Xml.XmlReader = cmdXml.ExecuteXmlReader
          With xrShippers
               .Read()
               Do While .ReadState <> Xml.ReadState.EndOfFile
                     txtXML.Text += .ReadOuterXml
               Loop
               'Format the result
               txtXML.Text = Replace(txtXML.Text, "><", ">" + vbCrLf + "<")
              .Close()
        End With
  End Sub

Test the Odbc Data Provider

You're not likely to use an Odbc data provider unless you're working with a legacy database server for which an OLE DB data provider isn't available. The following OpenOdbcDataReader procedure listing is present for completeness only:

     Private Sub OpenOdbcDataReader()
          'Define and open the OdbcConnection object
          Dim strConn As String = "DRIVER={SQL Server};SERVER=localhost;" + _
                "Trusted_connection=yes;DATABASE=Northwind;"

          Dim cnnNwind As OdbcConnection = New OdbcConnection(strConn)
          cnnNwind.Open()

          'Define the OdbcCommand
          Dim strSQL As String = "SELECT * FROM Shippers"
          Dim cmdReader As OdbcCommand = New OdbcCommand(strSQL, cnnNwind)
          cmdReader.CommandType = CommandType.Text


          'Define, create, and traverse the OdbcDataReader
          'Close the connection when closing the OdbcDataReader
         Dim sdrReader As OdbcDataReader = _
           cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
         If chkUseOdbc.Checked Then
            lstShippers.Items.Clear()
         End If
         With sdrReader
              If .HasRows Then
                While .Read
                      'Process the rows
                      Dim intShipperID As Integer = .GetInt32(0)
                      Dim strCompany As String = .GetString(1)
                      Dim strPhone As String = .GetString(2)
                      If chkUseOdbc.Checked Then
                         lstShippers.Items.Add(.Item(0).ToString + _
                             " - " + .Item(1).ToString)
                     End If
                End While
           End If
           'Close the OdbcDataReader and the OdbcConnection
           .Close()
    End With
End Sub

The above tip was excerpted from Chapter 1, 'Migrating from ADO to ADO.NET,' of the book Expert One-on-one Visual Basic 2005 Database Programming by Roger Jennings, courtesy of Wiley Publishers. Click here for the complete collection of book excerpts.

This was first published in May 2006

Dig deeper on SQL Server Migration Strategies and Planning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close