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

This article was previously published under Q308046
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Visual C# .NET version of this article, see 313304.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data
  • System.Data.Common

IN THIS TASK

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

back to the top

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.

back to the top

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
back to the top

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 SystemImports System.DataImports System.Data.CommonImports System.Data.OleDbImports System.Data.SqlClientImports 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.
back to the top

Pitfalls

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

back to the top

REFERENCES

For more information about ADO.NET objects and syntax, browse to the following MSDN Online Web site: For more information about .NET managed providers, refer to the .NET Developer's Center or browse to the following Microsoft Web site: back to the top
Properties

Article ID: 308046 - Last Review: 02/27/2014 21:10:09 - Revision: 3.2

  • 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
  • kbnosurvey kbarchive kbhowtomaster kbinfo kbsystemdata KB308046
Feedback