What DAL strategy do you use or suggest?

Question

My situation is that I screwed up essentially. I inherited my code base about 1.5 years ago when I took this position and rather than reinventing the wheel, even though I know now that I should have, I kept the DAL in pretty much the same structure as the previous developer.

Essentially there is one file (now at 15k lines of code) that serves as a go between to a bunch of DAO's that use DataSets and TableAdapters to retrieve data. My xsd files have grown to such size that they cause R# to crash visual studio every time it opens and the intermediary class that is now 15k lines also takes forever for R# to analyze. Not to mention it is ugly, it works but not well, and is an absolute nightmare to debug.

What I have tried thus far is switching to NHibernate. NHibernate is a great library, but unfortunately it was not adaptable enough to work with my application, from what the lead developer says (Fabio Maulo) it is pretty much a combination of my application requirements and the restrictions upon NHibernate when using identity as a database PK strategy.

So now I am back to essentially designing my own DAL. I am looking at a few different patterns for this, but would like to get your DAL design strategies. There are so many ways and reasons to implement a DAL in a particular manner so if you could please explain your strategy and why it was best fit for you I would greatly appreciate it.

Thanks in advance!

Edit: Let me explain why NHibernate did not work since that seems to be the immediate response. My users create a "job" that is actually just a transient representation of my Job class. Within this job they will give it one or a list of weight factors that are also transient at the time of creation. Finally they provide a list of job details that have a particular weight factor associated to them. Because, in the DB, weight factors are unique when I go to persist the job and it cascades down to weight factor it dies when it finds a duplicate weight factor. I tried running a check before assigning the weight factor to the detail (which I didn't want to do because I don't want the extra calls to the db) but calling CreateCriteria in NH also causes a flush in the session, according to Fabio, which destroys my cache and thus kills the entire in memory representation of the job. Folks over at the NH mailing list said I should switch over to GUID, but that is not a feasible option as the conversion process would be a nightmare.

Answer 1

If your DAL is written to an interface, it would be much easier to switch to NHibernate or something comperable (I would prefer Fluent-NHibernate, but I digress). So why not spend the time instead refactoring the DAL to use an interface, and then write a new implementation using NH or your ORM of choice?

Answer 2

For me the best fit was a pretty simple concept - use DAO class definitions and with reflection create all SQL necessary to populate and save them. This way there is no mapping file, only simple classes. My DAO's require an Entity base class so it is not a POCO but that doesn't bother me. It does support any type of primary key, be it single identity column or multi column.

Answer 3

In recent projects we have stopped programming a separate DAL.

Instead we use an Object Relational Mapper (in our case Entity Framework). We then let the business layer program directly against the ORM.

This has saved us over 90% of development effort in some cases.

Answer 4

My first step would be to break the code out of a 15 KLOC monster, then come up with a strategy for creating a new DAL.

Answer 5

My experience with NHibernate is that, while it is packed with features and very high-performance, you will eventually need to become an NHibernate expert in order to fix some unexpected behavior. Reading through the pro-NHibernate answers and seeing

Hmm , perhaps he uses long running Sessions (Session per Business Transaction model), and in such an approach, using identity is discouraged, since it breaks your unitofwork (it needs to flush directly after inserting a new entity). A solution could be to drop the identity, and use the HiLo identity generator.

illustrates exactly what I mean.

What I've done is create a base class modeled somewhat off of the ActiveRecord pattern, that I inherit from and mark up the inherited class with attributes that attach it to a stored procedure each for Select, Insert, Update and Delete. The base class uses Reflection to read the attributes and assign the class's property values to SP parameters, and in the case of Select(), assign the result SQLDataReader's column values to the properties of a list of generics.

This is what DataObjectBase looks like:

interface IDataObjectBase<T>
    {
        void Delete();
        void Insert();
        System.Collections.Generic.List<T> Select();
        void Update();
    }

This is an example of a data class deriving from it:

[StoredProcedure("usp_refund_CustRefundDetailInsert", OperationType.Insert)]
    [StoredProcedure("usp_refund_CustRefundDetailSelect", OperationType.Select)]
    [StoredProcedure("usp_refund_CustRefundDetailUpdate", OperationType.Update)]
    public class RefundDetail : DataObjectBase<RefundDetail>
    {

        [StoredProcedureParameter(null, OperationType.Update, ParameterDirection.Input)]
        [StoredProcedureParameter(null, OperationType.Insert, ParameterDirection.Output)]
        [StoredProcedureParameter(null, OperationType.Select, ParameterDirection.Input)]
        [ResultColumn(null)]
        public int? RefundDetailId
        { get; set; }

        [StoredProcedureParameter(null, OperationType.Update, ParameterDirection.Input)]
        [StoredProcedureParameter(null, OperationType.Insert, ParameterDirection.Input)]
        [StoredProcedureParameter(null, OperationType.Select, ParameterDirection.Input)]
        [ResultColumn(null)]
        public int? RefundId
        { get; set; }
        [StoredProcedureParameter(null, OperationType.Update, ParameterDirection.Input)]
        [StoredProcedureParameter(null, OperationType.Insert, ParameterDirection.Input)]
        [ResultColumn(null)]
        public int RefundTypeId
        { get; set; }

        [StoredProcedureParameter(null, OperationType.Update, ParameterDirection.Input)]
        [StoredProcedureParameter(null, OperationType.Insert, ParameterDirection.Input)]
        [ResultColumn(null)]
        public decimal? RefundAmount
        { get; set; }        
        [StoredProcedureParameter(null, OperationType.Update, ParameterDirection.Input)]
        [StoredProcedureParameter(null, OperationType.Insert, ParameterDirection.Input)]
        [ResultColumn(null)]
        public string ARTranId
        { get; set; }

    }

I know it seems like I'm reinventing the wheel, but all of the libraries I found either had too much dependence on other libraries (ActiveRecord + NHibernate, for instance, which was a close second) or were too complicated to use and administer.

The library I made is very lightweight (maybe a couple of hundred lines of C#) and doesn't do anything more than assign values to parameters and execute the SP. It also lends itself very well to code generation, so eventually I expect to write no data access code. I also like that it uses a class instance instead of a static class, so that I can pass data to queries without some awkward criteria collection or HQL. Select() means "get more like me".

Answer 6

Linq to SQL is nice if you are using SQL Server. There is source out there for a LinqToSQL provider to Access and MySQL. I haven't tested it though. LinqToSql follows the UnitOfWork model which is similar to the way ADO.NET functions. You make a series of changes to a local copy of the data then commit all the changes with one update call. It's pretty clean I think.

You can also extend the DataRow class yourself to provide strongly typed access to your fields. I used XSLT to generate the DataRow descendants based on the metadata of each table. I have a generic DataTable decendant. MyDataTable where T is my derived row. I know that MS's strongly-typed datasets do a similar thing but I wanted a light-weight generic version that I complete control of. Once you have this, you can write static access methods that query the db and fill the DataTable.

You would be in charge of writing the changes from the DataTable back to the DataSource. I would write a generic class or method that creates the update,inserts and deletes.

Good Luck!

Answer 7

I use mine wrapper for SPs for the fastest data retrieving and L2S when perfomance is not a goal. My DAL uses repository pattern and encapsulated logic for TDD.