Microsoft KB Archive/313304

Knowledge Base

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

Article ID: 313304

Article Last Modified on 5/13/2007

---

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

---

This article was previously published under Q313304

For a Microsoft Visual Basic .NET version of this article, see 308046.

This article refers to the following Microsoft .NET Framework Class Library namespaces:

• System.Data.Common
• System.Data.OleDb
• System.Data.SqlClient
• Microsoft.Data.Odbc

SUMMARY

• Description of the Technique
• Requirements
• Create Project and Add Code
• Troubleshooting

REFERENCES

SUMMARY

Use this step-by-step guide to learn 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 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 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.

back to the top

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

back to the top

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

back to the top

Troubleshooting

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

back to the top

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://msdn2.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/library/default.asp?url=/library/en-us/dndive/html/data010112001.asp

back to the top

Keywords: kbhowtomaster kbsqlclient kbsystemdata KB313304

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.