Implementing DAL in .NET with DB-first

Question

I'm working on a project that has a DB of about 30 tables and about 100 stored procedures (MSSQL).

1. All the DAL code is implemented using Data Access Application Block. I believe that this approach takes too much time even if I just need to add a new field to an existent table. First, I have to update the DB scripts, then I have to check the SP wrappers and/or queries we have in the code to reflect my changes.

2. Most of DAL (90%) is like this:

   // "idiom #1" for my project
   public static DataSet GetSomeData(int a, int b)
   {
      return SqlHelper.ExecuteDataSet(
        connection, 
        "select x, y from tab1 where a = " + 
        a.ToString() + 
        " and b = " + 
        b.ToString());
   }
   

   Then, when this is called from somewhere:

   // "idiom #2"
   var ds = DAL.GetSomeData(123, 456);
   var t = ds.Tables[0];
   var x = t.Rows[0][0];
   var y = t.Rows[0][1];
   

   I do firmly believe that this idea is really awful, but I'm not exactly sure what the right approach is. I'm absolutely sure that what I'd like to see is at least typed objects instead of DataRows and collections of these objects instead of DataTables. I'm also sure that I don't want to implement all this stuff by myself.

3. A couple of days ago, I've found BLToolkit which seems to solve this problem, but I'm not sure if it's good idea to use it, since I don't have enough experience with it. I really liked this example:

   public abstract class PersonAccessor : DataAccessor
   {
     [SqlText(@"SELECT * FROM Person WHERE FirstName = @firstName")]
     public abstract List<Person> GetPersonListByFirstName(string @firstName);
   
     [SprocName("sp_GetPersonListByLastName")]
     public abstract List<Person> GetPersonListByLastName(string @lastName);
     ...
   

   Though I can't call it ORM, this approach is abstract-enough for me to make me want it instead of what I currently have, but on the other side, it's still quite low-level.

4. I've also tried Fluent NHibernate and it seems to be really slow, though I really like its features.

What's the right solution here? The priorities are:

1. Easy to reflect DB structure changes
2. Typed data instead of int.Parse(row[0][3].ToString())
3. High performance

Answer 1

I would suggest that you use a .NET ORM. You can learn more about choosing an ORM here:

NHibernate, Entity Framework, active records or linq2sql

Executing "SELECT * FROM Person WHERE FirstName = @FirstName" is not going to be noticeably slower using an ORM and your development speed has the potential to be much faster.