The Way I See a DAL - CodeProject

Download source - 17.34 KB

Introduction

After looking for a generic DAL class and ORM frameworks I did not find one that suited my way of seeing things. All the mappings were done on tables, but all my applications run stored procedures that receive different data from relational tables! I observed that the entities and the fields coming from the procedures were the same, so why not automatically map them into entities? So I have developed a generic DAL that does just that.

Using the Code

The code has 3 classes: a DBHelper that does all the conversions needed from the database into OO, a ConnectionHelper that actually does all the work, and a GenericDAL that wraps/masks the ConnectionHelper and exposes a clean interface for the programmer.

To use the code just copy the 3 clases into your project and inherit the GenericDAL in your own DAL.

namespace strofo.DAL {
    public class NewCitiesDal : GenericDAL<City> {
        public static List<City> GetAll() {
            SqlCommand cmd = new SqlCommand("Cities_SelectAll");
            cmd.CommandType = CommandType.StoredProcedure;

            return GetListFromCommand(cmd);
        }
    }
}

The best part of this is that if you want to add a new field, you just have to modify the entity and/or stored procedures; no modifications of the DAL or the table in the database are needed.

You can get a single entity from the GenericDAL using a code like this one:

public static City GetById(int ID) {
    SqlCommand cmd = new SqlCommand("Cities_SelectById");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(DBHelper.ToSqlParameter("@ID", ID));

    return GetItemFromCommand(cmd);
}

or just a single value:

public static int Insert(City city) {
    SqlCommand cmd = new SqlCommand("Cities_Insert");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(DBHelper.ToSqlParameter("@Name", city.Name));
    cmd.Parameters.Add(DBHelper.ToSqlParameter("@ImgPath", city.ImgPath));

    return DBHelper.ToInt32(GetValueFromCommand(cmd), -1);
}

Hopefuly you like this and use it!