Gal Segal's Blog

Thoughts of a programmer with a soul

Aug 22

My DAL Base Class

Tags: ,

utility-belt

A few days ago I wrote a small base class for the DAL layer. There are some repeating tasks involving the action of getting/setting data from a database: creating a command, establishing connection, reading the data and disposing everything in the end. In this base class I try to hide these actions and leave the programmer to deal with the important stuff.

The code

public static class DataHelper
{
	public static List<T> GetMany<T>(string connectionStringName, SqlCommand command, Func<IDataRecord, T> mapper)
	{
		using (SqlConnection con = GetConnection(connectionStringName))
		{
			command.Connection = con;
			con.Open();
			return command.MapMany(mapper).ToList<T>();
		}
	}

	public static T GetSingle<T>(string connectionStringName, SqlCommand command, Func<IDataRecord, T> mapper)
	{
		return GetMany<T>(connectionStringName, command, mapper).FirstOrDefault();
	}

	public static int ExecuteNonQuery(string connectionStringName, SqlCommand command)
	{
		using (SqlConnection con = GetConnection(connectionStringName))
		{
			command.Connection = con;
			con.Open();
			return command.ExecuteNonQuery();
		}
	}

	public static T Read<T>(object value)
	{
		if (value != DBNull.Value)
		{
			return (T)value;
		}
		return default(T);
	}

	private static SqlConnection GetConnection(string connectionStringName)
	{
		return new SqlConnection(ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString);
	}

	private static IEnumerable<T> MapMany<T>(this DbCommand command, Func<IDataRecord, T> mapper)
	{
		using (var reader = command.ExecuteReader())
		{
			if (reader != null && reader.HasRows)
			{
				while (reader.Read())
				{
					yield return mapper(reader);
				}
			}
		}
	}
}

Usage

Get a collection:

try
{
	string connectionString ="CONNECTION_STRING";
	SqlCommand command = new SqlCommand("MyStoredProcedure");
	command.CommandType = CommandType.StoredProcedure;
	command.Parameters.AddWithValue("@ID", 1);
	return DataHelper.GetMany<User>(connectionString, command, r => new Proposal()
	{
		Name = DataHelper.Read<string>(r["Name"]),
		Age = DataHelper.Read<int>(r["Age"]),
		BirthDate = DataHelper.Read<DateTime>(r["BirthDate"])
	});
}
catch (Exception ex)
{
	throw ex;
}

Get a single result:

try
{
	string connectionString ="CONNECTION_STRING";
	SqlCommand command = new SqlCommand("MyStoredProcedure");
	command.CommandType = CommandType.StoredProcedure;
	command.Parameters.AddWithValue("@ID", 1);
	return DataHelper.GetSingle<User>(connectionString, command, r => new Proposal()
	{
		Name = DataHelper.Read<string>(r["Name"]),
		Age = DataHelper.Read<int>(r["Age"]),
		BirthDate = DataHelper.Read<DateTime>(r["BirthDate"])
	});
}
catch (Exception ex)
{
	throw ex;
}

Happy Coding :)

Back to top
  • Arjun Sapkota

    This looks almost same of what my dal looks. Though I have made it a concrete and you’ve  static.
    Just a thought, you could make it to concrete so that you need not depend on concrete SQLConnection, rather you’ll inject it at the runtime.
    public class QueryExecutor
        {
            private readonly ConnectionManager connectionManager;

            public QueryExecutor(ConnectionManager connectionManager)
            {
                this.connectionManager = connectionManager;
            }

            public IEnumerable GetRecords(IDbCommand command, Func selector)
            {
                using (new ConnectionScope(this.connectionManager))
                {
                    IDataReader reader = command.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            yield return selector(reader);
                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
            }

            public T GetSingleRecord(IDbCommand command, Func selector)
            {
                T retval = default(T);
                using (new ConnectionScope(this.connectionManager))
                {
                    IDataReader reader = command.ExecuteReader();
                    try
                    {
                        if (reader.Read())
                        {
                            retval = selector(reader); //Find first entry and return
                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
                return retval;
            }

            public void ExecuteQuery(string query)
            {
                using (new ConnectionScope(this.connectionManager))
                {
                    IDbCommand dbCommand = connectionManager.CreateCommand(query);
                    dbCommand.ExecuteNonQuery();
                }
            }
        }

    • http://gal-segal.com Gal Segal

      Nice one. At the time of writing this code I was testing using static classes against singletons created by Unity IoC. Currently I see more benefits to singleton in this matter.