Frequently in the world of software development we are dealing with hazy ideas and something that is known as the "generic approach." Naturally it has nothing to do with well defined project goals, but if you don't have anything better you will have to play along. So, imagine that you are to design a DAL which should work with MS SQL, MS Access and Oracle to start and at some later stage you must be capable of adding modules which will use other database systems. You should use the best possible solution for each of them, so using an ODBC provider for all of them is out of the question, SQL must use a SQL provider and Oracle its own provider and so on. If there is no release version of some provider yet, ODBC or OleDb may be used temporarily. Certainly deployment and redeployment of new versions must be simple and easy and you can't stop and uninstall already existing applications to deploy a new version. In plain English there will be a production version which is deployed and at the same time you must proceed with development and extend the application adding new features and support for different DB systems. This can be done relatively easily in .NET. We will use reflection and a few OO patterns. First, we must know how to do late binding and to dynamically load an assembly. If you are familiar with this go straight to "Two Patterns." These two patterns are Strategy and Template, if you happen to be familiar with them proceed to "DAL Design."

Late Binding in .NET

It is possible to do late binding in .NET but it is not very easy, also there is a performance penalty to be paid. In the old days of VB5/6 it was a very frequent way of accessing MS Office objects or some other COM objects. .NET does late binding through reflection which is let's say an exotic area for most users. I will try to briefly cover how to do late binding using reflection. So I will first create a library which we may use for late binding.

using System;
namespace LBL
{
	public class utils
	{
		public string SayHello(string name)
		{
			return "Hello " + name;
		}
		public int AddTwo(int first, int second)
		{
			return first + second;
		}
		public bool IsItBigger(double first, double second)
		{
			return first > second;
		}
	}
}

I saved it in LBLib.cs and compiled it from command line using "csc /t:library LBLib.cs". Now when we have assembly to practice late binding on it we will take a look at System.Reflection, to be more precise only classes and methods which we will need. First is Assembly and method of interest is Load and LoadFrom.

Using System;
using System.Reflection;
using System.IO;
class Q
{
	static void Main()
	{
		Assembly a = null;
		try
		{
			a = Assembly.Load("LBLib");
//			a = Assembly.LoadFrom("..\\LBLib.dll");
			Console.WriteLine("Done {0} loaded",a.Location);
		}
		catch(FileNotFoundException e)
		{
			Console.WriteLine(e.Message);
		}
	}
}

If you are not sure what is the assembly name, open it with ILDASM and see manifest. Assembly must be in same directory to be loaded with Load. If you are using LoadFrom it is not necessary but specify a full path. Can you load some of system assemblies like this? Yes, use LoadFrom:

	a = Assembly.LoadFrom(@"C:\WINNT\Microsoft.NET\Framework\v1.0.3705\System.Windows.Forms.dll");

Once when assembly is loaded we need type-class from that assembly, for that we use GetType method. To create instance of that type we must use Activator.CreateInstance. Consult help files to find out what is Activator. Next are parameters needed to call method and finally we get hold of method via MethodInfo and Type.GetMethod.

Using System;
using System.Reflection;
using System.IO;
class Q
{
	static void Main()
	{
		Assembly a = null;
		try
		{
			a = Assembly.Load("LBLib");
			Type utilType = a.GetType("LBL.utils");
         		object obj = Activator.CreateInstance(utilType);
         		object[] paramArray = new object[1];		
         		paramArray[0] = "Luke";
         		MethodInfo m = utilType.GetMethod("SayHello");
         		Console.WriteLine(m.Invoke(obj, paramArray));
		}
		catch(FileNotFoundException e)
		{
			Console.WriteLine(e.Message);
		}
	}
}

VB.NET has a bit of extra support for late binding. It is located in Microsoft.VisualBasic.dll and allows VB coder to use implicit reflection.

Imports System
Module Hello
   Sub Main()
      Dim obj As Object
      obj = new LBL.utils()
      Console.WriteLine(obj.SayHello("VB Luke"))
   End Sub
End Module

To compile it add reference to library "vbc /r:LBLib.dll test.vb". But if the VB coder can't add reference he is in the same situation as C# coder. So we can conclude that late binding is a somewhat complicated story but if we need it, it is there. If we decide that we can have a common interface or set of interfaces for all that we are going to dynamically load and use, then we can use coding against that interface to avoid MethodInfo.Invoke syntax, that will be illustrated in "DAL Design."

Two Patterns

These two are called Strategy and Template patterns. I will describe them very briefly and after that we will go to the construction of DAL. Template pattern is one very frequently encountered pattern in everyday development. It consists of base class where some method or methods are implemented and others will be implemented in specialized child class. So there is a defined algorithm but some parts of it can't be known unless we specialize implementation for a concrete situation. Here is a very simple example:

using System;
public abstract class Template
{
	public void get_flowers_and_use_them()
	{
		buy();
		give();
	}
	void buy()
	{
		Console.Write("I will buy some flowers, ");
	}
	protected abstract void give();
}
public class NiceGuy:Template
{
	protected override void give()
	{
		Console.WriteLine("and I will give flowers to my mommy.");
	}
}
public class BadGuy:Template
{
	protected override void give()
	{
		Console.WriteLine("and I will eat all flowers alone. HA HA HA!");
	}
}
class test
{
	static void Main()
	{
		Template t=new NiceGuy();
		t.get_flowers_and_use_them();
		t=new BadGuy();
		t.get_flowers_and_use_them();
	}
}

We don't know how specialized classes may use flowers so we are postponing implementation of that until it is known. Another pattern-Strategy is about having driver class-Context and few interchangeable algorithms. Who decides what strategy will be used is up to you. In the final solution we will store in a configuration file a description of available dB sources and do selection of best strategy from Context, in this example I made the decision in client application:

using System;

abstract class ConnectionStrategy 
{
	abstract public void Connect();
}

class AccessStrategy : ConnectionStrategy 
{
	override public void Connect()
	{
		Console.WriteLine("Connecting to Access.");			
	}
}

class SQLStrategy : ConnectionStrategy 
{
	override public void Connect()
	{
		Console.WriteLine("Connecting to SQL.");			
	}
}

class Context 
{
	ConnectionStrategy c;
	public Context(ConnectionStrategy cnn)
	{
		c = CNN;			
	}

	public void GetConnected()
	{
		c.Connect();
	}
}

public class Client
{
    static void Main()
{	
		AccessStrategy jet = new AccessStrategy();
		Context c = new Context(jet);
		c.GetConnected();
		SQLStrategy sql = new SQLStrategy();
		c = new Context(SQL);
		c.GetConnected();
    	}
}

DAL Design

Now we have almost all that we need. But first one piece of advice: if jour job is to write DAL stay out of BLL or GUI or you will end up writing the whole application for the same money. To avoid cumbersome reflection syntax we will do programming against interface or base class, whatever is available and whenever possible-we will see that it is always possible. Error handling is omitted mostly due to my laziness and in one smaller part to make the article smaller. To have the best possible flexibility we will have to use plug-in approach, you remember currently we are using OleDb provider for some dB and as soon as its .NET provider is out we will have to add it to the solution. For plug-in theme take a look at "LiveCode.NET" by NETMaster (http://www.codeproject.com/useritems/livecodedotnet.asp), also check "Creating an Extensible User Interface with .Net" by Keith Jacquemin (http://www.codeproject.com/useritems/ExtensibleUI.asp). Here I will use base class, if you prefer interface-use interface, there is nothing wrong with it. First we will implement appropriate Template, like this :

public abstract class DBTemplate 
{
	protected string connectionString;
	protected string commandString;
	protected DBTemplate(){}
	public string ConnectionString
	{
		get{return connectionString;}
		set{connectionString=value;}
	}
	public string CommandString
	{
		get{return commandString;}
		set{commandString=value;}
	}
    	public abstract IDbConnection GetConnection();
    	public abstract IDbDataAdapter GetDataAdapter();
	public DataSet GetResult()
	{
		IDbConnection cnn = GetConnection();
		IDbDataAdapter da = GetDataAdapter();
		DataSet ds = new DataSet();
		cnn.Open();
		da.Fill(ds);
		cnn.Close();
		return ds;
	}
}

To compile it we need reference to System.Data and place all that in namespace DAL. Specialized child class which uses SQL provider and will be in a separate assembly and looks like this:

using System.Data;
using System.Data.SqlClient;
namespace DAL
{
        public class DBSpecializedSQL : DBTemplate
        {
            	public override IDbConnection GetConnection()
        	{
        		IDbConnection temp = new SqlConnection(connectionString);
        		return temp;
        	}
            	public override IDbDataAdapter GetDataAdapter()
        	{
        		IDbDataAdapter temp = new SqlDataAdapter(commandString, connectionString);
        		return temp;
        	}
      
        }
}

In a similar fashion we may implement OleDb or some other provider. Here is the OleDb provider implementation:

using System.Data;
using System.Data.OleDb;
namespace DAL
{
        public class DBSpecializedAccess : DBTemplate
        {
            	public override IDbConnection GetConnection()
        	{
        		IDbConnection temp = new OleDbConnection(connectionString);
        		return temp;
        	}
            	public override IDbDataAdapter GetDataAdapter()
        	{
        		IDbDataAdapter temp = new OleDbDataAdapter(commandString, connectionString);
        		return temp;
        	}
        }
}

Now we should see how we are going to implement Context. For context we will need a configuration file and a way to read that file. Since we are  already dealing with System.Data we will use DataSet to read XML configuration file and find if there is any specialized provider to use. Priority of providers may be left to the writer of configuration file or to the final user to select, but in this primitive example I will avoid any of these options. To have the greatest possible flexibility you may use administrative GUI which will allow the administrator to configure connection strings for different user groups, but that is already deeply in BLL team area of work. Our simple configuration file will look like this:

<?xml version="1.0" encoding="utf-8"?>
<Tables>
    <Provider Assembly="DALSQL.dll" Name="DAL.DBSpecializedSQL" Type="Specialized" TypeID="SQL">
    </Provider>
    <Provider Assembly="DALOLEDB.dll" Name="DAL.DBSpecializedAccess" Type="General_OLEDB" TypeID="Any">
    </Provider>
    <Connection Text="Data Source=(local)\NetSDK;Initial Catalog=Northwind;user id=sa;password=;
" Type="Specialized" TypeID="SQL">
    </Connection>
    <Connection Text="Provider=Microsoft.Jet.OLEDB.4.0; 
Data Source=e:\NWIND.MDB" Type="General_OLEDB" TypeID="Access">
    </Connection>
    <Connection Text="Provider=SQLOLEDB;Data Source=(local)\NetSDK;Integrated Security=SSPI;
Initial Catalog=northwind" Type="General_OLEDB" TypeID="SQL">
    </Connection>
    <Command Text="SELECT * FROM Employees">
    </Command>
</Tables>

If you like, you may improve (complicate) that further and add relationships between tables, i.e. connection to query via QueryID and so on. That comes in handy if you like to give the user the option of selecting source, query and so on. If I elaborate this further I will break the golden rule, so back to DAL. Context now has something to read from and we can create it and at the same time implement Strategy pattern on top of Template. First we will check if there is any specialized provider and if it doesn't exist we will use OleDb provider. We will also prefer to use SQL server to Access. To avoid long stories here is Context:

    public class Context
    {
        DBTemplate target;
        public Context()
        {
        	DataSet ds = new DataSet();
        	string targetAssembly=null;
        	string targetName=null;
        	string targetType=null;
        	string targetTypeID=null;
        	string connection=null;
        	string command=null;
        	ds.ReadXml("config.xml");
		DataRelation dr = new DataRelation("ParentChild", 
			ds.Tables["Provider"].Columns["Type"],		
			ds.Tables["Connection"].Columns["Type"]);
		ds.Relations.Add(dr);
        	string fltr0="Type = 'Specialized'";
        	DataRow[] foundRows = ds.Tables[0].Select(fltr0);
        	if(foundRows.Length!=0)
        	{
        		targetAssembly=foundRows[0][0].ToString();
        		targetName=foundRows[0][1].ToString();
        		targetType=foundRows[0][2].ToString();
        		targetTypeID=foundRows[0][3].ToString();
        		DataRow[] childRows = foundRows[0].GetChildRows(dr);
        		if(childRows.Length!=0)
        		{
        			connection = childRows[0][0].ToString();
        		}
        		else
        			throw new Exception("No corresponding connection");
        	}
        	else
        	{
        		fltr0="Type = 'General_OLEDB'";
        		foundRows = ds.Tables[0].Select(fltr0);		
                	if(foundRows.Length!=0)
                	{
                		targetAssembly=foundRows[0][0].ToString();
                		targetName=foundRows[0][1].ToString();
                		targetType=foundRows[0][2].ToString();
                		targetTypeID=foundRows[0][3].ToString();
                		DataRow[] childRows = foundRows[0].GetChildRows(dr);
                		if(childRows.Length!=0)
                		{
                			connection = childRows[0][0].ToString();
        				if(childRows[0][2].ToString()!="SQL")
        					foreach(DataRow test in childRows)
        						if(test[2].ToString()=="SQL")
        							connection = test[0].ToString();
                		}
                		else
                			throw new Exception("No corresponding connection.");
                	}
        		else
        			throw new Exception("No provider found.");
        	}
        	Assembly a=Assembly.LoadFrom(targetAssembly);
        	Type DBType = a.GetType(targetName);
        	target = (DBTemplate)Activator.CreateInstance(DBType);
        	target.ConnectionString=connection;
		command = "SELECT * FROM Employees";// OK I know it should come from config.xml
        	target.CommandString = command;
        }
        public DataSet LoadDataSet()
        {
        	return target.GetResult();
        }
    }

Beside the ADO.NET exercise which looks quite ugly, class is very simple. Naturally it will also be in DAL namespace. To compile store Context and DBTemplate in DALBase.cs and use "csc /t:library DALBase.cs /debug" from command line. Store DBSpecializedAccess in DALOLEDB.cs and DBSpecializedSQL in DALSQL.cs, to compile use "csc /t:library /r: DALBase.dll <filename>" again from command line. To test it open new Windows Application, add reference to DALBase.dll, place DataGrid on Form and in Form1_Load place the following code:

	DAL.Context temp=new DAL.Context();
	dataGrid1.DataSource=temp.LoadDataSet();

Before you run it copy in Debug directory DALOLEDB.dll, DALSQL.dll and config.xml. Don't forget to adjust connection strings. To check how it works with Access make config.xml to look like this:

<?xml version="1.0" encoding="utf-8"?>
<Tables>
    <Provider Assembly="DALOLEDB.dll" Name="DAL.DBSpecializedAccess" Type="General_OLEDB" TypeID="Any">
    </Provider>
    <Connection Text="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\NWIND.MDB" Type="General_OLEDB" 
TypeID="Access">
    </Connection>
    <Command Text="SELECT * FROM Employees">
    </Command>
</Tables>

This example is simple but it illustrates how to handle the situation where you must take part in development of something that will change during development process. It is a good alternative to quick fix approach-you know: one additional case statement and stored procedure will fix a problem.

Finally

In the same way you can handle BLL or user interface "generic" design. As you noted, the configuration file and its structure is quite important and it depends on it how flexible the solution may be. In that way you can change the configuration file, paste the library to the application directory and deploy new provider. Can you make it a framework which will be capable of handling stored procedures, transactions, updates and other interesting stuff? Yes, check Microsofts Data Access Application Block, it looks to me as a good starting point. If you don't know how, I am currently available for any contract or permanent assignments. Is the solution where users select what provider, connection and query will be used superior to this one? Certainly, but that involves design of user interface and BLL. It is possible to load TreeView or hierarchical menu with all available combinations provider-DB-query (or only DB-query and Context will decide what provider to use), so that user who knows nothing about SQL can perform queries. BLL should determine what is available to some category of users.