Click here to Skip to main content
15,868,014 members
Articles / Web Development / ASP.NET

A Handy Code to Get Your Project Connected to Any Database by ADO.NET

Rate me:
Please Sign up or sign in to vote.
3.78/5 (10 votes)
16 Jul 2009CPOL5 min read 47.9K   340   62   15
Purely object oriented ADO code component to get your project running, no matter what database you are using at the backend. Specialised for SQL Server.

Introduction

This is an entirely rewritten FIRST article. I am thankful to all those who provided me valuable suggestions for improvement. Thank You All. I found out in the course of ADO.NET application development that developers were not at ease generalizing their database utility classes so that they can work with any database provided at the backend. Very useful when you are implementing SOA. So, here we will create a generic set of classes that you can use to get your code up and running on any provided database that ADO.net supports.

Background

This utility uses the concepts of basic OOP such as Dynamic Method Dispatching (DMD), Operator Overloading, Inheritance, structs, enums, etc., and also the concept of custom exceptions and collections. The core concept behind this utility is the use of core Database Classes from System.Data.Common, which I have manipulated and inherited in order to get the functional classes running.

Using the Code

Although understanding this code is a bit complex, using it is really simple (a matter of just two lines of code). I have tested this code on SQL Server, Access, Excel, Oracle, XML and some databases that can be connected via OLE DB.

The code consists of a governor class, DataAccess, which helps wrap up data access logic. The supported databases except XML, have a class alloted to them, SQLFactory, ProviderAgnosticFactory which implements same features. As the features to be implemented are same, so I have put all the common methods in their base class, FactoryGoverner, which specifies dummy functions (virtual) which are overriden by its derived classes. Let us call the class Factory Governer, the governer. The governor class uses DMD to dynamically call the appropriate method from the appropriate derived class. This class was made for use with ASP.NET, but is made fit for Windows applications by adding another constructor in DataAccess, like:

C#
public DataAccess(string conStr, string providerName)
{
    connectionString = conStr;
    provider = providerName;
}

You may add more such specialized classes to the utility as I have added for SQL Server (SQLFactory). The specialized classes are far better performing than general classes (ProviderAgnosticFactory). Note that since I have used DMD, while adding specialized classes, you need to implement the same methods as specified in FactoryGoverner. Another point worth noting is that in the case of ASP.NET, you need to use another constructor of the wrapper class DataAccess, in which you have to provide the configuration name that you have specified in the web.config for the connection string.

C#
public DataAccess(string configSectionName) 
{ 
    connectionString = 
        WebConfigurationManager.ConnectionStrings[configSectionName].ToString(); 
    try 
    { 
        provider = DbProviderFactories.GetFactory(
            WebConfigurationManager.ConnectionStrings[configSectionName].ProviderName); 
    } 
    catch (DbException ex) 
    { 
    } 
}

Note that the utility distinguishes the function to call by viewing the provider, so always use standard provider names such as: System.Data.SqlClient, System.Data.OracleClient, System.Data.OleDb, and System.Data.Odbc. In case none of these providers are used, a custom exception UnsupportedFactoryException is thrown, which uses a derived class of Exception.

C#
[Serializable]
public class _Exception : Exception
{
    public string ErrorMessage
    {
        get
        {
            return base.Message.ToString();
        }
    }

    public _Exception(string errorMessage)
        : base(errorMessage)
    {
    }

    public _Exception(string errorMessage, Exception innerEx)
        : base(errorMessage, innerEx)
    {
    }
}

Other structures and enums are self explanatory. But, I would like to emphasize on a particular structure that is very necessary to simplify things. AgnosticParameter is the structure that carries your query or Stored Procedure parameters. You may supply any number of parameters you wish to supply to your query or Stored Procedure. The parameters may be supplied sequentially or as an array of AgnosticParameters.

C#
public struct AgnosticParameter
{
    #region Public Member Variables
    public string ParameterName;
    public object ParameterValue;
    #endregion

    #region Constructors
    // Parameterized Constructor
    public AgnosticParameter(string ParameterName, 
                             object ParameterValue)
    {
        this.ParameterName = ParameterName;
        this.ParameterValue = ParameterValue;
    }

    // Copy Constructor
    public AgnosticParameter(AgnosticParameter sourceParameter)
    {
        this.ParameterName = sourceParameter.ParameterName;
        this.ParameterValue = sourceParameter.ParameterValue;
    }
    #endregion

    #region Overloaded Operators
    public static bool operator ==(AgnosticParameter lValue, AgnosticParameter rValue)
    {
	    return (lValue.ParameterName.Equals(rValue.ParameterName) 
	    	&& lValue.ParameterValue.Equals(rValue.ParameterValue));
    }	

    public static bool operator !=(AgnosticParameter lValue, 
				AgnosticParameter rValue)
    {
        return (lValue.ParameterName != rValue.ParameterName || 
		lValue.ParameterValue != rValue.ParameterValue);
    }

    public override bool Equals(object obj)
    {
        return base.Equals(obj);
    }

    public override int GetHashCode()
    {
        return base.GetHashCode();
    }
    public override string ToString()
    {
        return base.ToString();
    }
    #endregion
}

The overloaded operators would help you compare two objects of this structure.

Now let us analyse the class CustomConnection and its use. First of all creation of this class was all due to the comments provided by Mr. Paulo. The class custom connection is used for instantiating DbProviderFactory object, and comparing it to known database providers to know the database involved. This also helps us in instantiating DbProviderFactory to work with our custom XML factory. We will shortly see how. Lets first analyse the code:

C#
public class CustomConnection : DbProviderFactory
{
    private enum SetCustomFactory
    {
        Xml_Factory = 0
    }

    SetCustomFactory selectedFactory;

    public CustomConnection()
    {
        //No Constructor Logic Needed.
    }

    public CustomConnection(bool isXmlSource)
        : base()
    {
        //To Instantiate DbProviderFactory
        selectedFactory = SetCustomFactory.Xml_Factory;
    }

    public FactoryCodes TypeOfConnection(DbProviderFactory provider)
    {
        if (provider is System.Data.SqlClient.SqlClientFactory)
            return FactoryCodes.SQLClient;
        else if (provider is System.Data.OracleClient.OracleClientFactory)
            return FactoryCodes.OracleClient;
        else if (provider is System.Data.Odbc.OdbcFactory)
            return FactoryCodes.ODBC;
        else if (provider is System.Data.OleDb.OleDbFactory)
            return FactoryCodes.OleDB;
        //For Future Enhacements Both Clauses Return XML
        else if (provider is CustomConnection)
            return (selectedFactory == 
                SetCustomFactory.Xml_Factory) ? FactoryCodes.XML : FactoryCodes.XML;
        throw new UnsupportedFactoryException("Database Is Unsupported");
    }
}

See that this class has a method that compares the type of provider and helps in invoking the appropriate method of the factory classes involved. You may now instantiate an object of DbProviderFactory for use with XML just by using one more lines of code, which was only possible by use of DbProviderFactories earlier.

C#
DbProviderFactory xmlProvider = new CustomConnection(true);

Do note that you have to do this kind of instantiation only when you are going to use XML data source. After writing this line you are going to follow the same procedure as is true for any other databse.  Now, coming to the usability part for non XML (& XML after writing the above given piece of code). After adding all these classes in your project, querying any database is a matter of supplying a configuration and passing the query. E.g., say you have two configs as:

XML
<connectionStrings>
    <add name="TestPool" 
     connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=
                       |DataDirectory|\TestPool.mdf;Integrated Security=True;
                       User Instance=True"
     providerName="System.Data.SqlClient" />

    <add name="OleDB"
     connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
                       |DataDirectory|\AccessFile.accdb;
                       Persist Security Info=True"
     providerName="System.Data.OleDb" />
</connectionStrings>

And, if you want to query the DBs, then you may do so by providing a query or Stored Procedure as the case may be, as (I have written dummy code for various possibilities, use any one):

C#
DbProviderFactory objDF = new CustomConnection(true);
DataAccess objD1 = new DataAccess("Path To XML", objDF);
//OR
DataAccess objD2 = new DataAccess("Web Config Section Name");
// OR
DataAccess objD3 = new DataAccess("Connection String",
    DbProviderFactories.GetFactory("System.Data.SqlClient"));

Followed by your query result whatever you wish the output to be as,

C#
DataSet d = new DataSet();
d = objD2.GetDataSet(QueryType.Text, "SELECT * FROM Table1");

Just keep in mind that if you try to use non implimentable functions anywhere you would get UnsupportedFactoryException, for example using CreateXMLDocument when you are using SQL Server as provider.

Easy-Peasy.

Points of Interest

I once spent two days wondering why Excel was not working with my code, and I learnt one thing. Use proper connection strings!!

I ask for nothing but appreciation if you like this utility. Do let me know how I can help you. As this is my first article, please don't mind my terse explanations.

Possible Additions

You may add specialised factory classes to the code anytime..

History

  • 2nd July, 2009: Initial post
  • 7th July, 2009: Modifications to article, updated source code
  • 10th July, 2009: Modifications to article, updated source code

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer HCL Technologies Limited
India India
Rahul has worked on several technologies in Microsoft's technology stack. He has been in field of software developemnt for two years. He started with ASP.net and graduated to Windows application develeopment. His technology experience includes C#, LINQ, T-SQL,T-SQL, XML, PL\SQL, C, C++, VB, WebServices..

Apart from all these he is working on some of the most recent technology offerings such as Windows Azure, SQL Azure, SQL Azure Sync framework, MS Dallas, Powershell etc.

His goal of knowledge is to possess knowledge that is sky high and earth deep.

Comments and Discussions

 
Generalnice Pin
ali_reza_zareian16-Jul-09 22:05
ali_reza_zareian16-Jul-09 22:05 
General[My vote of 2] writing style Pin
Md. Marufuzzaman11-Jul-09 6:50
professionalMd. Marufuzzaman11-Jul-09 6:50 
GeneralRe: [My vote of 2] writing style Pin
MeraMBA.com12-Jul-09 1:46
MeraMBA.com12-Jul-09 1:46 
GeneralI liked the article Pin
CalvinHobbies10-Jul-09 6:48
CalvinHobbies10-Jul-09 6:48 
GeneralMy vote of 2 Pin
Paulo Zemek9-Jul-09 2:25
mvaPaulo Zemek9-Jul-09 2:25 
GeneralRe: My vote of 2 Pin
MeraMBA.com10-Jul-09 1:53
MeraMBA.com10-Jul-09 1:53 
GeneralRe: My vote of 2 Pin
Paulo Zemek10-Jul-09 2:37
mvaPaulo Zemek10-Jul-09 2:37 
GeneralRe: My vote of 2 Pin
MeraMBA.com11-Jul-09 2:09
MeraMBA.com11-Jul-09 2:09 
GeneralAgnosticParameter Pin
Paulo Zemek9-Jul-09 2:07
mvaPaulo Zemek9-Jul-09 2:07 
GeneralGood Article Pin
Viral Upadhyay9-Jul-09 2:04
Viral Upadhyay9-Jul-09 2:04 
GeneralIn my opinion... Pin
Paulo Zemek9-Jul-09 1:55
mvaPaulo Zemek9-Jul-09 1:55 
Generaloperator != logic incorrect Pin
rer1456-Jul-09 9:29
rer1456-Jul-09 9:29 
GeneralRe: operator != logic incorrect Pin
Tom Spink6-Jul-09 10:36
Tom Spink6-Jul-09 10:36 
GeneralRe: operator != logic incorrect Pin
MeraMBA.com6-Jul-09 20:01
MeraMBA.com6-Jul-09 20:01 
GeneralRe: operator != logic incorrect Pin
MeraMBA.com6-Jul-09 19:36
MeraMBA.com6-Jul-09 19:36 
Dear Rer145,

Thanks for observing the code so keenly, and taking time to comment on this article. I shall make the said changes accordingly. Thank you once again, for giving me feedback on my first article. Please do comment if you liked my article.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.