In my last post about the DAL I presented a very basic DAL based on a static class. This is fine for small applications, but for larger applications the limitations will soon start to show themselves.
In this post I’ll convert the DAL to a series of singletons so that each singleton operates on a different part of the database.
First of all, I should explain what a singleton is. A singleton is a class that has only one instance. The way that single instance is generated is controlled from the class itself.
When some code wants to use the singleton it calls a static property called Instance to get the one and only instance of the class. The property checks to see if there is already an instance available and if not creates it. This instance is stored in a private static field on the class. To ensure that only one instance of the class is ever created
The following is the DAL with just the parts that make it a singleton
public class InvoiceDal
{
/// <summary>
/// A private field to hold the only instance of this class
/// </summary>
private static InvoiceDal instance;
/// <summary>
/// A private constructor so that the only thing that can
/// construct this class is itself
/// </summary>
private InvoiceDal()
{
// Do initialisation stuff here.
}
/// <summary>
/// A public property that retrieves the only instance of the
/// class
/// </summary>
public static InvoiceDal Instance
{
get
{
if (instance == null)
instance = new InvoiceDal();
return instance;
}
}
}
Now the methods from the original static DAL (see previous post) can be carried over almost as they are. It is just the static keyword that has to be removed.
But, the point of this post is to refactor the old static DAL into a number of DAL classes where each class represents some component. The part given already represents the invoicing system. The next part will be for the stock inventory system.
It should, hopefully, be obvious that part of the DAL given already will be needed again. The lazy way is to just copy and paste the methods into a new DAL class. This is wasteful and the poor sod that has to maintain the code will not thank you. In practice what tends to happen is that, in your absence, you will be the butt of all jokes and your name will be dragged through the mud. This is something you don’t want to happen to you.
So, instead an abstract base class will be created which contains the common parts of the DAL. Certain decisions need to be made about goes in the base class. For example, is the base class responsible for figuring out what connection string to use? If you are only going to connect to one database then it certainly does seem to be a good choice. What if you need to (or expect to) connect to multiple databases? In those situations the derived class may be the best place to figure out the connection string. For this example, I’ll assume that multiple database are in use, but all from the same type of database system (e.g. Different databases running on SQL Server). I’ll come on to different database systems later in the series.
The abstract base class looks like this:
public abstract class DalBase
{
private static string connectionString;
protected DalBase()
{
}
/// <summary>
/// Builds the command object with an appropriate connection and sets
/// the stored procedure name.
/// </summary>
/// <param name="storedProcedureName">The name of the stored
/// procedure</param>
/// <returns>The command object</returns>
protected SqlCommand BuildCommand(string storedProcedureName)
{
// Create a connection to the database.
SqlConnection connection = new SqlConnection(connectionString);
// Create the command object - The named stored procedure
// will be called when needed.
SqlCommand result = new SqlCommand(storedProcedureName,
connection);
result.CommandType = CommandType.StoredProcedure;
return result;
}
/// <summary>
/// Builds a DataAdapter that can be used for retrieving the results
/// of queries
/// </summary>
/// <param name="storedProcedureName">The name of the stored
/// procedure</param>
/// <returns>A data adapter</returns>
protected SqlDataAdapter BuildBasicQuery(string storedProcedureName)
{
SqlCommand cmd = BuildCommand(storedProcedureName);
// Set up the data adapter to use the command already setup.
SqlDataAdapter result = new SqlDataAdapter(cmd);
return result;
}
}
And the derived class looks like this:
public class InvoiceDal : DalBase
{
/// <summary>
/// A private field to hold the only instance of this class
/// </summary>
private static InvoiceDal instance;
/// <summary>
/// A private constructor so that the only thing that can
/// construct this class is itself
/// </summary>
private InvoiceDal()
{
connectionString =
ConfigurationManager.AppSettings["InvoiceConnectionString"];
}
/// <summary>
/// A public property that retrieves the only instance of the class
/// </summary>
public static InvoiceDal Instance
{
get
{
if (instance == null)
instance = new InvoiceDal();
return instance;
}
}
/// <summary>
/// A sample public method. There are no parameters, it simply calls
/// a stored procedure that retrieves all the products
/// </summary>
/// <returns>A DataTable containing the product data</returns>
public DataTable GetAllProducts()
{
SqlDataAdapter dataAdapter = BuildBasicQuery("GetAllProducts");
// Get the result set from the database and return it
DataTable result = new DataTable();
dataAdapter.Fill(result);
return result;
}
/// <summary>
/// A sample public method. It takes one parameter which is passed
/// to the database
/// </summary>
/// <param name="invoiceNumber">A number which identifies the
/// invoice</param>
/// <returns>A dataset containing the details of the required
/// invoice</returns>
public DataSet GetInvoice(int invoiceNumber)
{
SqlDataAdapter dataAdapter = BuildBasicQuery("GetInvoice");
dataAdapter.SelectCommand.Parameters.AddWithValue(
"@invoiceNumber", invoiceNumber);
DataSet result = new DataSet();
dataAdapter.Fill(result);
return result;
}
/// <summary>
/// A sample public method. Creates an invoice in the database and
/// returns the invoice number to the calling code.
/// </summary>
/// <param name="customerId">The id of the customer</param>
/// <param name="billingAddressId">The id of the billing
/// address</param>
/// <param name="date">The date of the invoice</param>
/// <returns>The invoice number</returns>
public int CreateInvoice(int customerId, int billingAddressId,
DateTime date)
{
SqlCommand cmd = BuildCommand("CreateInvoice");
cmd.Parameters.AddWithValue("@customerId", customerId);
cmd.Parameters.AddWithValue("@billingAddressId",
billingAddressId);
cmd.Parameters.AddWithValue("@date", date);
cmd.Connection.Open();
int result = (int)cmd.ExecuteScalar();
cmd.Connection.Close();
return result;
}
}
It is now possible to create more of these derived classes for various parts of the application all sharing the same common code in the base class.
dal
The Blog of Colin Mackay 
Hi Colin,Good article, though your implimentation of the Singleton Pattern isn’t thread safe. You can do this two ways; firstly by using a lock (which is expensive) like so (formatting may be dodgey here)public sealed class Singleton{ static Singleton instance=null; static readonly object padlock = new object(); Singleton() { } public static Singleton Instance { get { lock (padlock) { if (instance==null) { instance = new Singleton(); } return instance; } } }}Secondly, you can use a fully lazy instantiation which is more performant, like so…public sealed class Singleton{ Singleton() { } public static Singleton Instance { get { return Nested.instance; } } class Nested { // Explicit static constructor to tell C# compiler // not to mark type as beforefieldinit static Nested() { } internal static readonly Singleton instance = new Singleton(); }}
Good comment Gary. In this mordern world of multi-core processors and multiple threaded applications it is better to think ahead and design for multiple threads.As for formatting, if you keep the code line length to less than 70-80 characters you can put it in a <pre> block.
Hi Colin,Thanks for the post. But I am not getting the advantage of using singleton here ? Can you please explain that ?Navaneeth
@Navaneeth: The advantage of the singleton is that there will only ever be one DAL object (or one instance of each type of DAL). So rather than store multiple instances of a DAL the singleton pattern is used. This is helpful if you want to set anything up at the start (like get the connection string from the config) as it means the setup is only ever done once.The advantage of using a singleton over a static class is that if you want to separate your DAL into components (e.g. one DAL class for invoicing, one DAL class for inventory control) then you can create a base class with the common stuff in it. Each specific DAL can inherit from the common base (something you can’t do with static classes) thus it is able to reuse that functionality.Does that answer your question?
Hi Colin,Thanks for explaining singleton. I am bit confused with the layered design pattern. Usually when you do an ASP.NET application with several pages (say 100), will you create separate BAL and DAL for all these pages ? Or use a common DAL and separate BAL’s ? I used provider model and all my DAL inherits from the Provider class which is inherited from a data access class which does all database operations. So in my scenario I will be having multiple DAL’s which is derived from the provider class. Is it the correct design ? So if I need to change the underlying datastore, I can change the provider which is used in the DAL.And how do you pass data from DAL to BAL ? I heard passing custom class objects filled with data is more good than passing a DataSet/DataTables. What is your opinion on this ?
@Navaneeth The Business Layer and DAL can be split up into functional areas. For example, your application may have several high level functions. e.g. Take something like Amazon (note: I know nothing of their application design) You can Browse their stock, you can manage wishlists, you can manage your shopping cart, you can rent DVDs, you can manage your orders and so on. Each of these is broadly a functional area. So for all the pages that deail with managing your DVD Rental you may have one DAL and one set of business objects. Obviously there will be some overlap in the front end. While browsing a DVD, for example, you will find a button to add to your shopping cart, add to your wishlist or add to your rental list. These will activate functions in the relevant business layer and in turn the relevant DAL.I’m not too sure on your middle paragraph. You might want to read Parts 3 and 4 of this series also. There is also parts 5 and 6 on the way which might help you there. (I’m not sure when I’ll be finishing them, both are partially completed)Finally, I’m not a fan of using DataSets. I used them here because it was nice and easy to demonstrate other concepts without adding too much all in one go. Parts 5 and 6 will discuss the Active Record pattern and the use of DTOs (Data Transfer Objects) which will probably answer the questions in your last paragraph.
I’m really trying to get a grasp on the Singleton approach to Data Layers. You mentioned:”The advantage of using a singleton over a static class is that if you want to separate your DAL into components (e.g. one DAL class for invoicing, one DAL class for inventory control) then you can create a base class with the common stuff in it. Each specific DAL can inherit from the common base (something you can’t do with static classes) thus it is able to reuse that functionality.”I’ve always used public static methods on non-static classes, allowing me to split up my DAL into subject-area component (e.g. Invoicing / Inventory Control) and allowing me to inherit from a base class (which has Static base methods). This also allows me to call methods without having to reference the “instance” property on the class, which reduces calls from:DataLayer.Instance.Method() toDataLayer.Method()When you are setting up a class to allow for the Singleton pattern, you’re basically just taking the Static keyword off the method, then creating and holding an instance of the class in memory, which seems like an extra unnecessary step (at least from my limited knowledge on the subject).So why go the extra step and implement the Singleton instance at all if you can use Static methods? I’m not familiar with compiler optimizations or anything, is there an execution penalty for accessing a static method on an instance class?Thanks,Damon Armstrong
an easy way to ensure thread safety with the singleton pattern is illustrated by the following:public class MySingleton{ private static MySingleton instance = new MySingleton(); private MySingleton(){ } public getInstance(){ return instance; }}at least that’s how they do it in Head First Design Patterns
Hi,Can anyone tell me what r the advantages of using multiple DAL Classes (derived from Base class) for my application, where we have multiple functionalities
HiCan anyone tell me the advantage of using Multiple classes in DAL for each functionality derived from the base class in my application or can we have one common class which contains methods for each functionality which is derived from base class.
Hi Colin, if I am declaring the connctionstring variable as private then it is not accessible in the derived class, therefore I declared it as public. Although the program is running. Please advise is it following the Singleton pattern.
The connection string is not required in any derived class. The
BuildCommand()method in the base class creates the connection.Also, if you make it
publicyou make it accessible to anyone. If you only want it available to derived classes you should make itprotected.Finally, since I wrote this almost 5 years ago, I’d suggest looking at Entity Framework these days as it provides much better ways of working with the database. If you need something closer to the metal, then Dapper is a good choice.