DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 313304 - Last Review: May 13, 2007 - Revision: 3.4

This article was previously published under Q313304

On This Page

SUMMARY

Use this step-by-step guide to learn 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 on). If you choose the wrong .NET data provider when you develop an application, you are locked into using the provider, or you may have to extensively rewrite your code. To avoid this problem, you can 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 our own class or function that uses the parent class (DataAdapter) rather than the inherited class (like 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 so that it is generic to all providers.

For additional information about Inheritance in Visual C# .NET , click the article number below to view the article in the Microsoft Knowledge Base:
307205  (http://support.microsoft.com/kb/307205/EN-US/ ) HOW TO: Use Inheritance in C#
The procedure in this article demonstrates how to use the IDataAdapter interface to accept any .NET provider specific DataAdapter.

NOTE: The preferred method for code forking is to use the IDataAdapter interface when inheriting from the DbDataAdapter class. Other preferred interfaces inclue IDBConnection, IDBCommand, and the IDataReader.

Requirements

The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs you will need:
  • Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
  • ODBC .NET Data Provider
  • Microsoft SQL Server 7.0 or later
The following items describe what you should understand prior to using the information provided in this article.
  • Microsoft Visual Studio .NET
  • Microsoft ADO.NET fundamentals and syntax

Create Project and Add Code

The following example explains how to use the IDataAdapter interface 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 C# .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:
    310988  (http://support.microsoft.com/kb/310988/EN-US/ ) HOWTO: Use the ODBC .NET Managed Provider in Visual C# .NET and Connection Strings
  5. Place a Button, a DataGrid, and three RadioButton controls on Form1.
    • Change the Name property of the button to btnTest and the Text property to Test.
    • Change the Name property of the first RadioButton to rbSqlClient and the Text property to SQL Client.
    • Change the Name property of the second RadioButton to rbOledb and the Text property to OLEDB.
    • Change the Name property of the third RadioButton to rbOdbc and the Text property to ODBC.
  6. Use the using statement on the namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the "General Declarations" section of Form1:
    using System.Data;
    using System.Data.Common;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    using Microsoft.Data.Odbc;
    					
  7. Type or paste the following code in the "General Declarations" area:
    IDataAdapter da;
    DataSet ds = new DataSet();
    					
  8. Create a DataAdapterFactory function that contains the following code:
    public IDataAdapter DataAdapterFactory()
    {
    	String myConnString; 
    	String myQuery  = "Select * From Customers";
    
    	if (rbSqlClient.Checked)
    	{
    		//Using SqlClient
    		myConnString = "server=myserver;integrated security=sspi;database=Northwind";
    		SqlConnection mycon = new SqlConnection(myConnString);
    		SqlDataAdapter daCust = new SqlDataAdapter(myQuery, mycon);
    		return daCust;
    	}
    	else if (rbOledb.Checked)
    	{
    		//Using OleDb
    		myConnString = "Provider=SqlOledb.1;Data Source=myserver;integrated security=SSPI;Database=Northwind";
    		OleDbConnection mycon = new OleDbConnection(myConnString);
    		OleDbDataAdapter daCust = new OleDbDataAdapter(myQuery, mycon);
    		return daCust;
    	}															   
    	else if (rbOdbc.Checked)
    	{
    		//Using Odbc
    		myConnString = "Driver={SQL Server};Server=myserver;trusted_connection=yes;database=Northwind";
    		Microsoft.Data.Odbc.OdbcConnection mycon = new Microsoft.Data.Odbc.OdbcConnection(myConnString);
    		OdbcDataAdapter daCust = new OdbcDataAdapter(myQuery, mycon);
    		return daCust;
    	}
    	else
    	{
    		return null;
    	}																											  
    }
    					
  9. Type or 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 to run your project.
  12. Select the managed provider you want to use for the connection and then click button (Test).

    The DataGrid displays the data returned from the query.

Troubleshooting

If you use base classes, you may lose provider-specific functionality.

REFERENCES

For more information about ADO.NET objects and syntax, see the Microsoft .NET Framework SDK Documentation or MSDN Online:
Accessing Data with ADO.NET
http://msdn.microsoft.com/en-us/library/e80y5yhx(vs.71).aspx (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 the following Microsoft Web site:
Inside .NET Managed Providers
http://msdn.microsoft.com/en-us/library/ms810268.aspx (http://msdn.microsoft.com/en-us/library/ms810268.aspx)


APPLIES TO
  • Microsoft ADO.NET 1.0
  • Microsoft ADO.NET 1.1
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
Keywords: 
kbhowtomaster kbsqlclient kbsystemdata KB313304
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support