微軟企業庫6 Data Access Application Block 擴充,企業庫block

來源:互聯網
上載者:User

微軟企業庫6 Data Access Application Block 擴充,企業庫block

雖然標題是對6的擴充,其實對於4、5同樣適用,因為企業庫在這幾個版本中沒太大變化

該擴充主要針對DataAccessor<T>,該類在建立時要傳遞幾種介面:IParameterMapper,IRowMapper<T>,IResultSetMapper<T>,其中IRowMapper<T>企業庫提供了MapBuilder<T>靜態類來輔助建立相應的對應關係,但對於IParameterMapper和IResultSetMapper<T>沒提供現成的類(也許有,但我沒找到,畢竟沒去研究它的源碼)

以下是具體相關的代碼,設計思路是:IParameterMapper和IResultSetMapper<T>其實都是要實現一個約定的方法,而對於該方法,我直接設定通用類,該類接受具體的實現委託,該委託由coder決定,而無需coder反覆去建立相關類,減少代碼量

PS:DbCommand.Parameters.AddRange接受DbParameter參數,所以可以直接傳遞具體的DbParameter

1、GeneralParameterMapper

    /// <summary>    /// 通用IParameterMapper    /// 如果在Execute前要傳遞的DbParameter已被其它DbCommand使用,將產生異常    /// 所以應該要調用DbCommand.Parameters.Clear方法釋放其它DbCommand對DbParameter的佔用    /// </summary>    public class GeneralParameterMapper : IParameterMapper    {        private Action<DbCommand, object[]> _act;        /// <summary>        /// GeneralParameterMapper        /// </summary>        /// <param name="act">        /// 定義如何將parameterValues賦給DbCommand的委託        /// 如果不傳,將使用預設委託,該委託不校正要傳遞的parameterValues在DbCommand是否已定義        /// 此時反覆執行Execute將會導致異常,以保證不會因為編碼問題導致反覆查詢</param>        public GeneralParameterMapper(Action<DbCommand, object[]> act = null)        {            if (act != null)            {                this._act = act;            }            else            {                this._act = (cmd, paramters) =>                {                    cmd.Parameters.AddRange(paramters);                };            }        }        #region IParameterMapper 成員        /// <summary>        /// IParameterMapper.AssignParameters        /// </summary>        /// <param name="command"></param>        /// <param name="parameterValues"></param>        public void AssignParameters(DbCommand command, object[] parameterValues)        {            if (parameterValues != null && parameterValues.Length > 0)            {                this._act(command, parameterValues);            }        }        #endregion    }
2、GeneralResultSetMapper

    /// <summary>    /// 通用IResultSetMapper    /// </summary>    /// <typeparam name="T"></typeparam>    public class GeneralResultSetMapper<T> : IResultSetMapper<T>    {        private Func<IDataReader, T> _func;        /// <summary>        /// GeneralResultSetMapper        /// </summary>        /// <param name="func">根據IDataReader如何返回T委託</param>        public GeneralResultSetMapper(Func<IDataReader, T> func)        {            if (func == null)            {                throw new ArgumentNullException();            }            this._func = func;        }        #region IResultSetMapper<T> 成員        /// <summary>        /// IResultSetMapper.MapSet        /// </summary>        /// <param name="reader">IDataReader</param>        /// <returns>List集合,如果Count為0,表示無資料,不會返回null值</returns>        public IEnumerable<T> MapSet(IDataReader reader)        {            List<T> list = new List<T>();            while (reader.Read())            {                list.Add(this._func(reader));            }            return list;        }        #endregion    }

在這兩個類的基礎上,又封裝了個擴充類,專門用於微軟企業庫DataAccessor的擴充,以下是具體代碼,因個人喜好問題,沒封裝對應Proc的擴充方法,其實代碼都一樣

    /// <summary>    /// 微軟企業庫資料相關的協助類    /// </summary>    public static class EntLibDbHelper    {        /// <summary>        /// 批量查詢Sql並自動填滿實體,如果不需要傳遞DbParameter,建議不要使用該方法,應使用Database.ExecuteSqlStringAccessor方法        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="db"></param>        /// <param name="sql"></param>        /// <param name="parameterMapperAction">指定委託確認對要傳遞的Sql參數如何處理,該值可以傳遞null,為null時使用預設委託,該委託直接將parameterValues傳遞給cmd.Parameters.AddRange方法</param>        /// <param name="rowMapper">指示EntLib如何填充T,如果不傳,將預設按T的屬性進行對應賦值</param>        /// <param name="parameterValues">要傳遞的Sql參數,此部分對應parameterMapperAction</param>        /// <returns></returns>        public static IList<T> ExecuteBySqlString<T>(this Database db, string sql, Action<DbCommand, object[]> parameterMapperAction, IRowMapper<T> rowMapper = null, params object[] parameterValues)            where T : new()        {            if (rowMapper == null)            {                rowMapper = MapBuilder<T>.BuildAllProperties();            }            IParameterMapper parameterMapper = new GeneralParameterMapper(parameterMapperAction);            return db.CreateSqlStringAccessor<T>(sql, parameterMapper, rowMapper).Execute(parameterValues).ToList();        }        /// <summary>        /// 批量查詢Sql並自動填滿實體,如果不需要傳遞DbParameter,建議不要使用該方法,應使用Database.ExecuteSqlStringAccessor方法        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="db"></param>        /// <param name="sql"></param>        /// <param name="parameterMapperAction">指定委託確認對要傳遞的Sql參數如何處理,該值可以傳遞null,為null時使用預設委託,該委託直接將parameterValues傳遞給cmd.Parameters.AddRange方法</param>        /// <param name="resultSetMapperFunc">指示EntLib如何填充T</param>        /// <param name="parameterValues">要傳遞的Sql參數,此部分對應parameterMapperAction</param>        /// <returns></returns>        public static IList<T> ExecuteBySqlString<T>(this Database db, string sql, Action<DbCommand, object[]> parameterMapperAction, Func<IDataReader, T> resultSetMapperFunc, params object[] parameterValues)        {            IParameterMapper parameterMapper = new GeneralParameterMapper(parameterMapperAction);            IResultSetMapper<T> resultSetMapper = new GeneralResultSetMapper<T>(resultSetMapperFunc);            return db.CreateSqlStringAccessor<T>(sql, parameterMapper, resultSetMapper).Execute(parameterValues).ToList();        }    }
順帶補充個通用的Data擴充

    /// <summary>    /// 資料相關的協助類    /// </summary>    public static class DbHelper    {        /// <summary>        /// 從DataReader中讀取可Null 物件        /// </summary>        /// <typeparam name="T">泛型T</typeparam>        /// <param name="dr">IDataReader</param>        /// <param name="key">Key</param>        /// <returns></returns>        public static Nullable<T> GetNullable<T>(this IDataRecord dr, string key)            where T : struct        {            return dr[key] == null || dr[key] == DBNull.Value ? (Nullable<T>)null : (T)dr[key];        }        /// <summary>        /// 當DataReader讀取對象為空白時,返回defaultValue        /// </summary>        /// <typeparam name="T">泛型T</typeparam>        /// <param name="dr">IDataReader</param>        /// <param name="key">Key</param>        /// <param name="defaultValue">當取到的資料為DBNull時,應當返回的預設值</param>        /// <returns></returns>        public static T GetDefaultWhileNullable<T>(this IDataRecord dr, string key, T defaultValue = default(T))        {            return dr[key] == null || dr[key] == DBNull.Value ? defaultValue : (T)dr[key];        }        /// <summary>        /// 從DataReader中讀取字串併除前後空白字元後        /// </summary>        /// <param name="dr">IDataReader</param>        /// <param name="key">Key</param>        /// <returns></returns>        public static string GetTrimedString(this IDataRecord dr, string key)        {            return dr[key].ToString().Trim();        }    }

具體使用代碼如下:

string sql = @"SELECT [pdate]      ,[pbegtime]      ,[pendtime]      ,[pid]      ,[pdateid] FROM tbltime WITH(NOLOCK)WHERE pid=@PID";            IRowMapper<Product> rowMapper = MapBuilder<Product>.MapNoProperties()                .Map(p => p.ID).ToColumn("pdateid")                .Map(p => p.Name).WithFunc((dr) =>                {                    return string.Format("{0}-{1}", dr.GetTrimedString("pbegtime"), dr.GetTrimedString("pendtime"));                })                .Build();            var list = db.ExecuteBySqlString(sql, null, rowMapper, new SqlParameter("@PID", 12345) { DbType = DbType.Int32 });            MessageBox.Show(list.Count.ToString());            IParameterMapper paramterMapper = new GeneralParameterMapper();            var _productAccessor = db.CreateSqlStringAccessor(sql, paramterMapper, rowMapper);            var products = _productAccessor.Execute(new SqlParameter("@PID", 12345) { DbType = DbType.Int32 }).ToList();            if (products != null && products.Count > 0)            {                MessageBox.Show(products.Count.ToString());            }


相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.