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

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.

Start Visual Studio .NET.
Create a new Windows application in Visual C# .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:
310988 HOWTO: Use the ODBC .NET Managed Provider in Visual C# .NET and Connection Strings
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.
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;
					
```
Type or paste the following code in the "General Declarations" area:
```
IDataAdapter da;
DataSet ds = new DataSet();
					
```

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;
	}																											  
}

Type or paste the following code in the btnTest Click event:

da = DataAdapterFactory();
da.Fill(ds);
dataGrid1.DataSource = ds ;

Modify the connection strings as appropriate for your environment.
Save your project. On the Debug menu, click Start to run your project.
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.