In this continuation of my series on the DAL I’m going to show the ability to create several DALs and have a Factory class instantiate the correct DAL based on settings in a config file.
One of the purposes of the DAL is to shield the application from the detail of accessing the database. You may have a system where you may have to talk to different databases depending on the way the software is installed. Typically this is most useful by creators of components that need to plug into many different database systems.
The DAL will no longer be a singleton, but rely on a Factory to create the correct DAL class. This is most useful when the data may be coming from different sources. It can also be useful when your application is in development and the database isn’t ready because you can then create a mock DAL and return that instead of the real thing.
UML Diagram showing multiple DALs
The above UML Diagram shows the base DAL that was introduced in the last article in this series. Again, it is an abstract class as it does not contain enough functionality to be instantiated on its own. However, it is now joined by two additional abstract classes. These add a bit more functionality specific to the target database, but still not enough to be useful as an object in its own right.
Finally at the bottom layer are the various concrete DAL classes that act as the proxies to the database.
So that the calling application doesn’t need to know what database it is dealing with the concrete classes implement interfaces. That way the application receives a reference to an IPolicyDal, for example, without having to know whether it is connected to an Oracle database or a SQL Server database (or any other future database you may need to support)
The factory class, not shown in the UML diagram above, picks up information in a config file which tells it which specialised DAL to actually create. It returns that specialised instance through a reference to the interface. This means that the calling code does not need to know what specialised class has actually been created. Nor should it need to care.
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
namespace Dal
{
/// <summary>
/// Creates and dispenses the correct DAL depending on the configuration
/// </summary>
public class DalFactory
{
private static IPolicyDal thePolicyDal = null;
private static IClaimDal theClaimDal = null;
/// <summary>
/// Gets the policy DAL
/// </summary>
/// <returns>A dal that implements IPolicyDal</returns>
public static IPolicyDal GetPolicyDal()
{
if (thePolicyDal == null)
{
string policyDalName = DalConfig.PolicyDal;
Type policyDalType = Type.GetType(policyDalName);
thePolicyDal =
(IPolicyDal)Activator.CreateInstance(policyDalType);
}
return thePolicyDal;
}
/// <summary>
/// Gets the claim DAL
/// </summary>
/// <returns>A DAL that implements IClaimDal</returns>
public static IClaimDal GetClaimDal()
{
if (theClaimDal == null)
{
string claimDalName = DalConfig.ClaimDal;
Type claimDalType = Type.GetType(claimDalName);
theClaimDal =
(IClaimDal)Activator.CreateInstance(claimDalType);
}
return theClaimDal;
}
}
}
The factory stores the each DAL class as it is created so that if a Get...() method is called repeatedly it always returns the same instance. The DAL classes should not be storing any state so this is a safe thing to do.
The DalConfig class is a very simple static class that consists of a number of property getters that retrieves the information out of the config file. The PolicyDal and ClaimDal properties return the fully qualified name of the specialised DAL class to instantiate.
Previous articles in the series:
The Blog of Colin Mackay 
Hi Colin,Thanks for making this clear. I have few doubts1 – Why you used interfaces ? Returning the base class object with a concrete DAL instance from factory class would be enough ? I guess so. If you are returning the base class, I think you can avoid the risk of using interfaces, as it is tough to modify after implementing all the classes.2 – I see your concrete DAL classes have some methods. So in this design, from BAL we can create interface reference and call the implemented class right ? So how do you pass values to this method (concrete DAL method) from BAL ? Navaneeth
@Navaneeth1. I use interfaces because there are two (or more) possible routes to get to a concrete class that does the same thing with differing internal implementations. In this example OraclePolicyDal and SqlPolicyDal. You cannot return the base class because it does not contain the relevant methods. Just some internal stuff for dealing with the database which isn’t useful to the caller.2. Yes, the Business code would have a reference to the interface which would refer to an actual concrete implementation. You pass values in the normal way. I don’t understand the question. The interface defines the method signature that the concrete class must implement.
Thanks for the article. I was looking for some design guidance on this type of implementation and this article was perfect. If you have the code for the DalConfig class, a sample business class, and sample interface class it might be good so people can see how to use this design pattern. Thanks again!
@Maldew the DalConfig class is a very simple static class that gets information from the config file. It can be as simple as this:public static class DalConfig{ public static string ConnectionString { get { return ConfigurationSettings.AppSettings[“ConnectionString”]; } }}The interface isn’t a class, but a class implements an interface. It may look something like this:public interface IPolicyDal{ int AddPolicy(/* Parameters containing the details of the policy */); void GetPolicy(int policyId); void RenewPolicy(int policyId, int policyTerm);}The business class is, however, outwith the scope of what I’m trying to explain here. However if you were to use the DAL from a business class you might have some code that looks like this in it:public class Policy{ private int policyId; private DateTime startDate; private DateTime endDate; public void Renew Policy() { TimeSpan term = endDate – startDate; DalFactory.GetPolicyDal().RenewPolicy(policyId, t.Days); }}Obviously there would be a lot more going on in the business class, but I’m only demonstrating a small interation with the DAL.
Thanks for the extra examples. One last question. I implemented your solution in a project and chose to remove the OracleDalBase and SqlDalBase classes and have only 1 DalBase class that uses the new dotnet 2.0 DbProviderFactory to create the correct object based on a config setting. Do you see any problems with this?
@Maldew Only in terms that the SQL language used by each database has differences. If you do everything with stored procedures and pass parameters I guess that would be okay. However, you would generally only be using references to the base DbCommand, DbConnection and so on. These are limited in what they can do. If you needed something specific for a particular database your code would soon become a mass spaghetti with all the casting and conditional statements in order to support specific features of a particular database or to correct for subtle differences. It is for this reason that the two levels of base class exist above.
Hello Colin,From UML diagram we can see that OracleDalBase and SqlServerDalBase both inherit from DalBase. All of them have a method called BuildCommand. I guess they are inherited from DalBase yes? So does it mean that their return type is DbCommand? Because otherwise you cannot inherit from base class. If so how would you avoid spaghetti code and other methods you mention in previous post?
@Giorgi To answer your questions.1. Yes, as the diagram shows, they are inherited from DalBase.2. Yes, they would return a DbCommand3. You avoid the spaghetti code because all the database specific stuff is held inside your DAL. Any code using it will never need to know what the final source of data is. Within the DAL itself each specific database implementation is held in its own set of classes. You should never need to have any code that has a conditional that says, if I’m running SQL Server then do this, otherwise if I’m running Oracle do that. The only bit of code that does not inherit from BaseDal has any slight knowledge of what the back end database actually is will be the Factory class.
Thank Colin,Let me ask several question. firstly, when an actual dal class (like sqlserverpolicydal or oraclepolicydal) calls BuilDCommand method of the base class, a DbCommand is returned. Do you cast it to OracleCommand or SqlCommand class or do you work with generic DbCommand class?Secondly, Let’s say you need to move data from one database or file to another database. How would you design it? I am thinking about building two dals for one class: one dal for reading data (readdal) and another for writing it in destination (writedal) and calling methods from these classes transparently to the user.Finally, let’s say you are using Olde Db to read data from excel file. In such case you can’t initialize connection string in dal using configuration manager because the file will be indicated by the user. Any elegant solution for this problem?By the way, have you posted parts 5 and 6?Thanks again.
@Giorgi Sorry for the lateness of the reply.First, the concrete DAL classes (e.g. SqlServerPolicyDal or OraclePolicyDal) would call the BuildCommand method in the immediate base, so it is still specific to the database in use. Yes, the reference is to a DbCommand, but the actual object is a SqlCommand or an OracleCommand or whatever. In the concrete DAL class I’d probably for the most part just work with what DbCommand exposes as that is sufficient in most cases. If I needed specific functionality of the database for optimisation then I’d cast it to the actual version of the class.Second question: I’d use SQL Server Integration Services for that. But, if you want to do it programatically then just have the factory instantiate the correct dal. If the data flow is two way then pass to the factory something (an enum say) that tells it which specific DAL you need. If the information flow is one-way then the factory will know which specific version to instantiate based on whether you want a ReadDal or a WriteDal.Penultimately (because you asked another question after your final question): If you need the user to indicate what the file is then pass the filename to the CreateXxxDal() method on the factory. It can pass the filename to the actual Dal object which can then plug it into the ConnectionString. Although this sounds like you are now knowing a lot more about the source of the data that you previously needed to know, the fact that you are asking the user for it means that additional information about the specific Dal is now floating around the rest of your application anyway. If you have lots of diverse datasources then you may want to wrap that up in another class so you can pass it around in a simple and consistent way without needing lots of if (isExcel){} else {} all over the place.And the real finally: No, I’ve not posted parts 5 and 6. I’m sure they are kicking around my laptop somewhere and I just need to find them and post them.
Thanks for reply ColinHope you will find parts 5 and six and also free time to post them 🙂