Какую стратегию DAL Вы используете или предлагаете?

Мой опыт работы с NHibernate показывает, что, несмотря на то, что он обладает множеством функций и обладает очень высокой производительностью, вам в конечном итоге потребуется стать экспертом по NHibernate, чтобы исправить некоторые неожиданные ошибки. Прочитав ответы про-NHibernate и увидев

Хмм, возможно, он использует длительный бег Сеансы (сеансов на бизнес Модель транзакции), и в таком подход, использование идентичности обескуражен, так как это нарушает ваш unitofwork (нужно промывать напрямую после вставки нового объекта). А решением могло бы быть отбросить идентичность и использовать идентичность HiLo 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".