HOW TO: Use Base Classes to Reduce Code Forking with Managed Providers in Visual Basic .NET

Article translations Article translations
Article ID: 308046 - View products that this article applies to.
This article was previously published under Q308046
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SUMMARY

This article explains how to use base classes to reduce code forking with managed providers.

Description of the Technique

ADO.NET has different types of data providers (for example, SqlClient, OleDb, ODBC, and so forth). If you choose the wrong .NET data provider for developing your application, you are then locked in to that provider, or you are faced with the prospect of a large code rewrite. One of the ways to avoid this issue is to use base classes.

For example, the SqlDataAdapter, OleDbDataAdapter, and OdbcDataAdapter classes all inherit from the DbDataAdapter class, which in turn inherits from the System.Data.Common.DataAdapter class. You can create your own class or function that uses the parent class (DataAdapter) instead of the inherited class (such as SqlDataAdapter, OleDbDataAdapter, and OdbcDataAdapter). The function returns a common object or interface that is provider-independent. In this way, you can isolate the provider-specific code to a common function or class and write your application generic to all providers.

For additional information about how to use the IDataAdapter interface to accept any .NET provider-specific DataAdapter, click the article number below to view the article in the Microsoft Knowledge Base:
307222 HOW TO: Use Inheritance in Visual Basic .NET
NOTE: The preferred method for code forking is to use the IDataAdapter interface when you inherit from the DbDataAdapter class. Other preferred interfaces include IDBConnection, IDBCommand, and IDataReader.

Requirements

The following is a summary of the recommended hardware, software, network infrastructure, skills and knowledge, and service packs that you will need to perform the procedures described in this article:
  • A Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server-based system
  • Microsoft Visual Studio .NET
  • ODBC .NET Data Provider
  • Microsoft SQL Server 7.0 or later
You should be familiar with the following before you use the information provided in this article:
  • Visual Studio .NET
  • ADO.NET fundamentals and syntax

Create Project and Add Code

The following steps use the Microsoft.Data.Odbc.DataAdapter base class to reduce code forking from System.Data.OleDb.OledbDataAdapter, System.Data.SqlClient.SqlDataAdapter and Microsoft.Data.Odbc.OdbcDataAdapter.
  1. Start Visual Studio .NET.
  2. Create a new Windows application in Visual Basic .NET.
  3. Make sure that your project contains a reference to the System.Data namespace; add a reference if it does not.
  4. Add references to Microsoft.Data.Odbc.dll.For additional information about the ODBC .NET Managed Provider, click the article number below to view the article in the Microsoft Knowledge Base:
    310985 HOW TO: Use the ODBC .NET Managed Provider in Visual Basic .NET and Connection Strings
  5. Place a CommandButton, a DataGrid, and three RadioButtons on Form1.

    Then, do the following:
    • Change the Name property of the CommandButton to btnTest, and then change the Text property to Test.
    • Change the Name property of the first RadioButton to rbSqlClient, and then change the Text property to SQL Client.
    • Change the Name property of the second RadioButton to rbOledb, and then change the Text property to OLEDB.
    • Change the Name property of the third RadioButton to rbOdbc, and then change the Text property to ODBC.

  6. Use the Imports statement on the namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add this to General Declarations section in Form1, as follows:
    Imports System
    Imports System.Data
    Imports System.Data.Common
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    Imports Microsoft.Data.Odbc
    					
  7. Copy and paste the following code in the General Declarations section:
        Dim da As IDataAdapter
        Dim ds As New DataSet()
    					
  8. Create a DataAdapterFactory function by copying and pasting the following code:
     Public Function DataAdapterFactory() As IDataAdapter
            Dim myConnString As String
            Dim myQuery As String = "Select * From Customers"
    
            If Me.rbSqlClient.Checked = True Then
                'Using SqlClient
                myConnString = "server=myserver;integrated security=sspi;database=Northwind"
                Dim mycon As New SqlConnection(myConnString)
                Dim daCust As New SqlDataAdapter(myQuery, mycon)
                Return daCust
            ElseIf Me.rbOledb.Checked = True Then
                'Using OleDb
                myConnString = "Provider=SqlOledb.1;Data Source=myserver;integrated security=SSPI;Database=Northwind"
                Dim mycon As New OleDbConnection(myConnString)
                Dim daCust As New OleDbDataAdapter(myQuery, mycon)
                Return daCust
            ElseIf Me.rbOdbc.Checked = True Then
                'Using Odbc
                myConnString = "Driver={SQL Server};Server=myserver;trusted_connection=yes;database=Northwind"
                Dim mycon As New Microsoft.Data.Odbc.OdbcConnection(myConnString)
                Dim daCust As New OdbcDataAdapter(myQuery, mycon)
                Return daCust
            End If
      End Function
    					
  9. Copy and paste the following code in the btnTest Click event:
    da = DataAdapterFactory()
    da.Fill(ds)
    DataGrid1.DataSource = ds
    					
  10. Modify the Connection strings as appropriate for your environment.
  11. Save your project. On the Debug menu, click Start, and then run your project.
  12. Select the managed provider to use for the connection, and then click the button (Test).

    Note that the DataGrid displays the data returned from the query.

Pitfalls

Note that after performing these steps, you lose the provider-specific functionalities if you are using the base classes.

REFERENCES

For more information about ADO.NET objects and syntax, browse to the following MSDN Online Web site:
http://msdn.microsoft.com/en-us/library/e80y5yhx(VS.71).aspx
For more information about .NET managed providers, refer to the .NET Developer's Center or browse to the following Microsoft Web site:
Inside .NET Managed Providers
http://msdn.microsoft.com/en-us/library/ms810268.aspx

Properties

Article ID: 308046 - Last Review: February 27, 2014 - Revision: 3.2
APPLIES TO
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 1.1
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
Keywords: 
kbnosurvey kbarchive kbhowtomaster kbinfo kbsystemdata KB308046

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com