DAL design

Question

I'm using .Net enterprise library data access application block for my Data Access layer design.

In my Category DAL class, I've methods like :

GetProductsInCategory(int CatId), GetAllProducts, GetCategories, etc.

My question is: where do i put this line of code ?

DatabaseFactory.CreateDatabase("MyDB");

shall i put it in every method above or shall i have a base class which would return Database object to my DAL class.

Also, shall i keep these DAL class methods as static?

Thanks.

Answer 1

thanks for ur tips..i will have all my DAL classes derived from a base class DBManager.This class will have a protected method called GetDatabase() which will have code: return DatabaseFactory.CreateDatabase("MyDB"); And my method in derived class would look like:..

public DataSet GetProductsInCategory(int Category) 
{
Database db = base.GetDatabase(); 
DbCommand dbCommand = db.GetStoredProcComman("GetProductsByCategory"); 
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category); 
return db.ExecuteDataSet(dbCommand);
}

does this DAL design look ok?

Answer 2

I would suggest looking at using SubSonic for DAL and object generation. It implements Microsoft's Application Block but gives you simple (and robust) query capabilites like;

SubSonic.Query qry = new SubSonic.Query(Usr.Schema);
qry.SetSelectList(Usr.Columns.UserPkid);
qry.QueryType = SubSonic.QueryType.Select;
qry.AddWhere(Usr.UsernameColumn.ColumnName, SubSonic.Comparison.Equals, Username);

using (IDataReader reader = qry.ExecuteReader())
{
    while (reader.Read())
    {
        Trace.WriteLine("Fetched User Pkid [" + reader[Usr.Columns.UserPkid].ToString() + "]");
    }
}

And of course it implements the ActiveRecord pattern so the object classes have the .Delete(), .Add(), .Get() type methods.