微軟企業庫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()); }