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

• Download source - 8.36 KB

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:

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.

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.

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

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:

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.

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:

<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):

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,

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

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