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 :)