What are the possible issues of using single static instance of the de EnterpriseLibrary Database class for a DataLayer library?

Question

I am doing a refactoring of many projects and I want to group together all the methods that access the database into a DataLayer library (instead of each proyect having its own class that access the database)

This is the approach that I am doing right now

private static Database DB = null ;
private static EventLog LOG = null ;

public static void Initialize()
{
    string logName;

    if (LOG == null)
    {
        logName = "DataLayer";

        LOG = new EventLog();

        if (!EventLog.Exists(logName))
        {
            //can fail because lack of permissions or other errors
            try
            {
                EventLog.CreateEventSource(logName, logName);
            }
            catch (Exception)
            {

            }
        }

        LOG.Source = logName;
    }
    if (DB == null)
    {
        DB = DatabaseFactory.CreateDatabase("Name");
    }

}

public static DataSet GetInfoFromDB(String param1, bool automatic)
{
    DataSet resp;
    DbCommand dbCommand;
    object[] pars;

    resp = null;
    dbCommand = null ;

    try
    {
        //prepare the parameters
        pars = new object[2];
        pars[0] = param1;
        pars[1] = automatic;

        dbCommand = DB.GetStoredProcCommand("StoredProcedureName", pars);
        dbCommand.CommandTimeout = 1200;

        resp = DB.ExecuteDataSet(dbCommand);
    }
    catch (Exception ex)
    {
        LOG.WriteEntry(string.Format("Error al ejecutar el StoredProcedure \"GetInfoFromDB\" {0}", ex.Message), EventLogEntryType.Error, 2004);
    }
    finally
    {
        if (dbCommand!= null)
        {
            dbCommand.Dispose();
        }
    }

    return resp;
}

What are the posible issues of using single static instance of the de EnterpriseLibrary Database class for a DataLayer library ?

By issues I mean leaked connections, or if one mehod can somehow affect the others when multiple process start calling those methods or anyother

I would usually solve this by trial an error but I want to have an answer based on concepts and techinical/official information

ADDED

The main goal of the DataLayer and the refactor that I am planning is to take all the DB utility/common methods like GetAllParameters and many others that are replicated over and over on each project to avoid having to mantain and test them in every single project.

What I want is a unique method GetAllParameters that is called/reused in all the other projects

Therefore all the Common methods that can be used by many projects and are not part of a certain bussiness logic will be placed in DataLayer.Common like DataLayer.Common.GetAllParameters

And each logic that is specific for certiain funcionality will go inside its own DataLayer.BusinessLogicA.GetSpecificInfo

Is important to mention that this DataLayer will be used from projects like this

Website-->WebService-->BusinessLogic-->Datalayer
Winservice-->Datalayer
Winform-->Datalayer

Answer 1

By all your projects I assume all the projects in the same solution, its mad to mix DB access from different solutions in the same place, unless you are a DB/.Net guru.

1) you lose parallel access to the DB, depending on your project it might not be a problem, if it's a web project don't do it as all the calls to the server will clog up.

2) with the proposed approach you will mix business logic in each project and in the data access this is bad, makes it difficult to maintain, try to look into Entity Framework and other libraries to automate the access to the DB

3) Use the Unity from Enterprise Library to inject the DB context into each project, it will make it much more testable, I wont go into details as there are hundreds of examples on the WEB, but it might be the solution to why you are doing the refactoring.

It all sums up to probably its a bad decision to do it and it all depends on why you are doing it.

[Edit]

What you want is an extension method like the System.Linq library to do this you need to create a static class that implements a static method with the first parameter equal to this Database DB

public static MyClass
{
    public static DataSet GetAllParameters(this DataBase DB, int someParam, ...)
    {
        <your DB function to get them>
    }
}

Then tu use it you just add a using MyClass and in any DB object do var dataSet = DB.GetAllParameters(someParam, ...)

this will have a performance hit and if you pass the wrong DB it will blow up but its the fast way to do it, I strongly recommend you look into the IOC option with Automated DAL generator this will change the DataBase object to some specific table object and that make lots of verifications on compile time that this solution does not do.

Answer 2

Its ok to have your logger as a static class, but not for the conennection to the database.

Its recomended that you use using statement instead of a static function so it will be disposed automatically after its been used.

See this: Is the database connection in this class "reusable"?

This practice avoids having memory leaks,

Answer 3

Anytime you make a class static, you make it harder to test. Specifically you can't run it through a framework like Moq. That alone is reason enough to make it instanced.

As stated by Pedro.The.Kid in his answer, Injecting with UnityContainer (which would imply an interface to be implemented by your data access object) will go a long way toward making this a well managed object.

Answer 4

I would suggest to use a IOC container and let the container to handle the lifecycle of your DataClass you can use any container you want, if you want to follow the microsoft libraries then use Unity which is part of the enterprise library.

you can try to use Singletons or transients test them and see what would you prefer.

you can use something like this in you composite root:

 public static void Register(IUnityContainer container)
{
    container
        .RegisterType<Database>(
        new ContainerControlledLifetimeManager(),
        new InjectionFactory(c => DatabaseFactory.CreateDatabase("Name")));
}

take a look to this answer for a more detailed explanation.