Singleton Data Access Object (Dao) & SQL Helper Instance, Is here any Performance drawback?

Question

I need comments from Experts. I write my own SQL Helper Class to Communicate with DB.

Why I use it as because I try to

Encapsulate the Ado.Net logic.
Try to set a common standard for my developer in terms of DAL coding.
Flexible & Easy to use.
Same kind of code block for SQL Server/ Oracle / Access / Excel / Generic Database code block approach (SQL Server & Oracle) e.t.c.
Plug & Play or Reusable approach.

In terms of code optimization

This helper class or Assembly is CLS Compliant.
It pass Successfully by FxCop / Static Code Analysis.

I give you the sample Code Block (DAO). Please check bellow

                   using System;
        using System.Collections.Generic;
        using System.Linq;
        using System.Text;
        using System.Data;

        #region include SQL Helper Namespace
        using DBManager;
        #endregion

        #region include SQL Helper Namespace
        using DBManager;
        #endregion


namespace ContentManagementSystem.DataAccessLayer
{
/// <summary>
/// 
/// </summary>
public sealed class DaoContentOwner : DataAccessBase
{
    #region CONSTRUCTOR
    /// <summary>
    /// 
    /// </summary>
    private DaoContentOwner()
    {
        GetInstance = new DaoContentOwner();
    }
    #endregion

    //############################################# M E T H O D S ##########################

    #region Retrieve Content Owner
    /// <summary>
    /// Retrieve Content Owner
    /// </summary>
    /// <returns></returns>
    public static DataTable RetrieveContentOwner()
    {
        DataTable dt = null;

        try
        {
            using (DBQuery dq = new DBQuery("stpGetContentOwner"))
            {

                dt = dq.ResultSetAsDataTable();

                return dt;
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }
    #endregion


    //############################################# P R O P E R T Y ########################

    #region READ ONLY PROPERTY
    /// <summary>
    /// GetInstance of DaoContentOwner Class
    /// </summary>
    public static DaoContentOwner GetInstance { get; private set; }
    #endregion
}

}

Justification:

"DataAccessBase" It is a Abstract class. Implement IDisposable Interface.

"DBQuery" is SQL Helper for SQL Server Only. It is a Sealed class. It is takes T-SQL / SP according to needs. Open an close database connection. No need to handel any thing by developer.

Why I make DAO Singleton Class, Because my all the methods withing DAO is a static method. I order to memory optimization I make it Singleton. It is also a design Principal.

Note: Needs comments. Whether need to change in the design or some thing is wrong that need to correct.

If all the methods on your class are static then you don't need to instantiate it... so no need to have a constructor or make it a singleton. I suspect your base class isn't entirely static so if you can't have multiple instances of that class or there are performance benefits then by all means follow the singleton pattern. Only you can really say whether it's the best solution as only you have a complete view of the problem space. — Lazarus, Jun 10 '11 at 14:52

score 0 · Answer 1 · answered Jun 10 '11 at 15:15

Personally I would go with:

DALFileItem dalF = new DALFileItem(DSN);
List<FileItem> list = dalF.GetAll("");

Allows accessing multiple databases, without making DSN static member.
In multithreaded environment only one thread will have access to the static method.
More object oriented: you can add interfaces, base classes and generics easier that way.

Singleton Data Access Object (Dao) & SQL Helper Instance, Is here any Performance drawback?

In terms of code optimization

I give you the sample Code Block (DAO). Please check bellow

1 Answers1