Microsoft KB Archive/308046

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

Article ID: 308046

Article Last Modified on 1/28/2006

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition

-



This article was previously published under Q308046



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
 * Description of the Technique
 * Requirements
 * Create Project and Add Code
 * Pitfalls

REFERENCES



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.  Start Visual Studio .NET. Create a new Windows application in Visual Basic .NET. Make sure that your project contains a reference to the System.Data namespace; add a reference if it does not. 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

 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.</li> Change the Name property of the second RadioButton to rbOledb, and then change the Text property to OLEDB.</li> Change the Name property of the third RadioButton to rbOdbc, and then change the Text property to ODBC.</li></ul>

</li>  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 </li>  Copy and paste the following code in the General Declarations section: Dim da As IDataAdapter Dim ds As New DataSet </li>  Create a DataAdapterFactory function by copying and pasting the following code: Public Function DataAdapterFactory As IDataAdapter Dim myConnString As String Dim myQuery As String = &quot;Select * From Customers&quot;

If Me.rbSqlClient.Checked = True Then 'Using SqlClient myConnString = &quot;server=myserver;integrated security=sspi;database=Northwind&quot; Dim mycon As New SqlConnection(myConnString) Dim daCust As New SqlDataAdapter(myQuery, mycon) Return daCust ElseIf Me.rbOledb.Checked = True Then 'Using OleDb myConnString = &quot;Provider=SqlOledb.1;Data Source=myserver;integrated security=SSPI;Database=Northwind&quot; Dim mycon As New OleDbConnection(myConnString) Dim daCust As New OleDbDataAdapter(myQuery, mycon) Return daCust ElseIf Me.rbOdbc.Checked = True Then 'Using Odbc myConnString = &quot;Driver={SQL Server};Server=myserver;trusted_connection=yes;database=Northwind&quot; Dim mycon As New Microsoft.Data.Odbc.OdbcConnection(myConnString) Dim daCust As New OdbcDataAdapter(myQuery, mycon) Return daCust End If End Function </li>  Copy and paste the following code in the btnTest Click event: da = DataAdapterFactory da.Fill(ds) DataGrid1.DataSource = ds                   </li> Modify the Connection strings as appropriate for your environment.</li> Save your project. On the Debug menu, click Start, and then run your project.</li> 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.</li></ol>

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