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

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: 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 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.

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