Database Provider-Independent Data Access Layer Using ADO.NET 2.0

In .NET, the way of writing code for data access has changed. There no longer are different data provider Namespaces and Classes. For example, to connect Microsoft SQL Server 2000, Microsoft currently recommends that you use System.Data.SqlClient Namespace. This namespace has the following classes:

  • SqlConnection
  • SqlCommand
  • SqlDataAdapter
  • SqlDataReader
  • SqlParameter

The above classes take advantage of the better performance available through SQL Server's native Tabular Data Stream (TDS) interfaces. TDS is the protcol that MS SQL Server 2000 uses to connect between Client and DataSource.

Suppose you have written Data Acces Layer code based on the above SqlClient provider that only talks to MS SQL 2000 Server. Now, your application wants to connect to a different database—say, Oracle or MS SQL Server 6.5 (this is an OLE-DB Compliant DB). The DAL won't work with this new database. So, you need to change the Provider from System.Data.SqlClient to System.Data.OleDb provider and corresponding classes that support OleDb and rewrite the DAL code.

ADO.NET 2.0 will help you avoid this kind of problem, where you need not re-write DAL code when you change the data source. In ADO.NET 2.0, the Namespace System.Data.Common namespace has added some new classes that directly enable the implementation of provider-independent data access classes in the form of Factory Method Patterns.

Factory Patterns

A Factory pattern is one that returns an instance of one of several possible classes depending on the data provided to it. Or, you can say that, based on input, the Factory method creates the class object and returns. Usually, all of the classes it returns have a common parent class and common methods, but each of them performs a task differently. ADO.NET 2.0 provides a factory class to create Data access objects based on the provider that you pass.

In ADO.NET, the System.Data.Common namespace has the following base classes:

  • DbConnection
  • DbCommand
  • DbDataAdapter
  • DbParameter
  • DbTransaction
  • DbCommandBuilder
  • DbException

DbProviderFactory is the core class that you use to generate instances of the classes you need; for example, a connection, command, parameter, and so forth.

DbProviderFactories is a class that enables you to create specific instances of the DbProviderFactory class, each of which represents one of the managed code providers that are installed and available on the machine; for example, System.Data.SqlClient, System.Data.OleDb, ODBC, and so forth.

Here, I will use SqlClient as my Data Provider. I will write a Data Access code based on this SqlClient Provider.

#region Using directives

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common ;


#endregion

namespace DataProvider
{
   class Program
   {
      static void Main(string[] args)
      {
         string DataProviderName="System.Data.SqlClient";
         DbProviderFactory dpf =
            DbProviderFactories.GetFactory(DataProviderName);

         DbConnection dbConn= dpf.CreateConnection();
         dbConn.ConnectionString = "Database=AM;uid=XXXXX;pwd=XXXXX";
            dbConn.Open();

         DbCommand dbcmd = dpf.CreateCommand();
         dbcmd.CommandText = "Select * from  tDevelopers";
            dbcmd.Connection = dbConn;


         DbDataReader dr = dbcmd.ExecuteReader();

         while (dr.Read())
         {
            Console.WriteLine("{0}, {1} , {2}",dr["Name"].ToString(),
                              dr["EmailAddress"].ToString(),
                              dr["Phone"].ToString());
         }
         Console.Read();
      }
   }
}

Suppose I wanted to change my Data source to an OleDB-based Data provider. I just need to change string DataProviderName=" System.Data.OleDb";. This value also can be changed from the configuration.

For example, the configuration file entry for Data Provider will look like this:

Configuring a specific provider type

<configuration>
   <appSettings>
      <add key="provider-type" value="System.Data.SqlClient" />
   </appSettings>
</configuration>

You may want to use it if your application needs to be configurable to run against different database systems on a regular basis.



About the Author

SeenivasaRagavan Ramadurai

Seenivasaragavan Ramadurai is a .NET consultant, He has been working with .NET technology since pre beta releases. Seenivasa background includes Master's in Computer Science and B.Sc. Mathematics. He has over 9 years of software development experience with Microsoft technologies and has extensive experience developing client-server, distributed, Web services, and component based applications using Visual Studio .NET. Before moving to .NET, Seenivasa has worked on MFC, COM, ATL, and Visual C++ based applications. If you are looking for a consulting help, contact him at skbbaba23@gmail.com

Comments

  • about oracle

    Posted by wagnerp on 11/18/2009 05:16am

    what about the oracle that returns back a ref cursor ?

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Adaptation and evolution are fundamental requirements of survival -- not only in nature, but also in business. Our world has changed dramatically in a short amount of time. Many businesses are fueling and capitalizing on this change, while others are desperately clinging to a bygone era. Who is left standing in the years and decades ahead should come as no surprise. This edition of Unleashing IT highlights the companies that are embracing new circumstances, new methods, and new opportunities. By downloading …

  • Email is the most common communication vehicle used by organizations of all shapes and sizes. Among the billions of email messages sent every day are sensitive information, critical requests, and other essential business data. IT staff bear the burden of ensuring the confidentiality, integrity, and availability of the information contained within the communication. This white paper explores the email security landscape, an assessment of the threats organizations face,  and the building blocks of an effective …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds