How to design Database Access Layer to contain Sql libraries in one place

Question

I have a handler class for every table and I am doing all the DB work in functions, like below.

public class MyObjectHandler
{
    public bool Insert(MyObject obj)
    {
        using(SqlConnection conn = new SqlConnection(DbHandler.ConnectionString))
        {
            SqlCommand comm = new SqlCommand("OBJ_INSERT", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.Add(new SqlParameter("@Code", SqlDbType.NVarChar, 50) { Value = obj.Code });
            comm.Parameters.Add(new SqlParameter("@Desc", SqlDbType.NVarChar, 150) { Value = obj.Desc });
            comm.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int) { Value = obj.UserCreated.Id });

            conn.Open();
            int retVal = comm.ExecuteNonQuery();
            conn.Close();

            if(retVal > 0)
                return true;
            else
                return false;
        }
    }

    public bool Update(MyObject obj)
    {
        using(SqlConnection conn = new SqlConnection(DbHandler.ConnectionString))
        {
            SqlCommand comm = new SqlCommand("OBJ_UPDATE", conn);
            comm.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = obj.Id });
            comm.Parameters.Add(new SqlParameter("@Code", SqlDbType.NVarChar, 50) { Value = obj.Code });
            comm.Parameters.Add(new SqlParameter("@Desc", SqlDbType.NVarChar, 150) { Value = obj.Desc });
            comm.CommandType = CommandType.StoredProcedure;

            conn.Open();
            int retVal = comm.ExecuteNonQuery();
            conn.Close();

            if(retVal > 0)
                return true;
            else
                return false;
        }
    }

    public bool Delete(int Id)
    {
        using(SqlConnection conn = new SqlConnection(DbHandler.ConnectionString))
        {
            SqlCommand comm = new SqlCommand("OBJ_DELETE", conn);
            comm.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = Id });
            comm.CommandType = CommandType.StoredProcedure;

            conn.Open();
            int retVal = comm.ExecuteNonQuery();
            conn.Close();

            if(retVal > 0)
                return true;
            else
                return false;
        }
    }
}

Problem is, as you can see I am using SqlConnection routine all the time. I want to create a layer, which is a class that includes all the SQL libraries. Would you offer me a general solution on this? Even a general idea will be enough on this.

For example, my function for Getting the data is easy:

public static DataTable GetDataTable(string spName)
{
    DataTable resultsTable = new DataTable();
    using(SqlConnection conn = new SqlConnection(DbHandler.ConnectionString))
    {
        SqlCommand comm = new SqlCommand(spName, conn);
        comm.CommandType = CommandType.StoredProcedure;
        conn.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(comm);
        adapter.Fill(resultsTable);
        conn.Close();
    }
    return resultsTable;
}

This is good enough, but I need a better solution to send parameters easily and with more flexibility. Seems like the only option is to send parameter name, type and length one by one. Would you recommend a design on this?

Amit Joshi · Answer 1 · 2017-08-02T11:20:49.807

Consider using ORM with Repository pattern. ORMs are really great tools to build Data Access Layer. Dapper and NHibernate are my favorite; there are lot many other available.

Problem is, as you can see I am using "SqlConnection" routine all the time. I want to create a layer, which is a class includes all the Sql libraries. Would you offer me a general solution on this? Even a general idea will be enough on this.

General solution is ORM. General idea (without using ORM) could be to implement UnitOfWork pattern. Refer this answer. It is using Dapper ORM, but similar could be implemented with core ADO.NET. Repository pattern comes to rescue here. Instead of managing connection inside Repository, you can inject it from application. This way, your application gets total control over UnitOfWork and can use it the way suits for given situation.

I need a better solution to send parameters easier, and with more flexibility. Seems like the only option is to send parameter name, type and length one by one.

ORMs provide their own wrappers to accept the parameters; so it may help here. Without ORM, you can pass in Dictionay or similar and build parameter list inside your GetDataTable and pass it to ADO.NET call.

How to design Database Access Layer to contain Sql libraries in one place

1 Answers1