[EntLib]微软企业库6 Data Access Application Block 扩展
- WBOYOriginal
- 2016-06-07 15:36:382070browse
虽然标题是对6的扩展，其实对于4、5同样适用，因为企业库在这几个版本中没太大变化 该扩展主要针对DataAccessorT，该类在创建时要传递几种接口：IParameterMapper，IRowMapperT，IResultSetMapperT，其中IRowMapperT企业库提供了MapBuilderT静态类来辅助创建
虽然标题是对6的扩展，其实对于4、5同样适用，因为企业库在这几个版本中没太大变化
该扩展主要针对DataAccessor
以下是具体相关的代码，设计思路是：IParameterMapper和IResultSetMapper
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将会导致异常，以保证不会因为编码问题导致反复查询
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 name="parameterValues">
public void AssignParameters(DbCommand command, object[] parameterValues)
{
if (parameterValues != null && parameterValues.Length > 0)
{
this._act(command, parameterValues);
}
}
#endregion
}</dbcommand></dbcommand>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委托
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
/// <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
}</t></t></t></t></idatareader></idatareader></t></t>
在这两个类的基础上，又封装了个扩展类，专门用于微软企业库DataAccessor的扩展，同时包含了其它的Execute扩展，以下是具体代码，因个人喜好问题，没封装对应Proc的扩展方法，其实代码都一样
PS：此处扩展因为DbCommand是可以在委托中访问到的，所以对于CommandTimeOut之类的就不做参数传递
/// <summary>
/// 微软企业库数据相关的帮助类
/// </summary>
public static class EntLibDbHelper
{
/// <summary>
/// 批量查询Sql并自动填充实体，如果不需要传递DbParameter，建议不要使用该方法，应使用Database.ExecuteSqlStringAccessor方法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db">
/// <param name="sql">
/// <param name="parameterMapperAction">指定委托确认对要传递的Sql参数如何处理，该值可以传递null，为null时使用默认委托，该委托直接将parameterValues传递给cmd.Parameters.AddRange方法
/// <param name="rowMapper">指示EntLib如何填充T，如果不传，将默认按T的属性进行对应赋值
/// <param name="parameterValues">要传递的Sql参数，此部分对应parameterMapperAction
/// <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 name="sql">
/// <param name="parameterMapperAction">指定委托确认对要传递的Sql参数如何处理，该值可以传递null，为null时使用默认委托，该委托直接将parameterValues传递给cmd.Parameters.AddRange方法
/// <param name="resultSetMapperFunc">指示EntLib如何填充T
/// <param name="parameterValues">要传递的Sql参数，此部分对应parameterMapperAction
/// <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();
}
/// <summary>
/// 执行SQL文本，并返回受影响行数
/// </summary>
/// <param name="db">
/// <param name="sql">
/// <param name="parameterSetAction">
/// <returns></returns>
public static int ExecuteNonQueryBySqlString(this Database db, string sql, Action<dbcommand> parameterSetAction = null)
{
return db.ExecuteBySqlString(sql, (cmd) => { return db.ExecuteNonQuery(cmd); }, parameterSetAction);
}
/// <summary>
/// 执行SQL文本，返回DataReader
/// </summary>
/// <param name="db">
/// <param name="sql">
/// <param name="parameterSetAction">
/// <returns></returns>
public static IDataReader ExecuteReaderBySqlString(this Database db, string sql, Action<dbcommand> parameterSetAction = null)
{
return db.ExecuteBySqlString(sql, (cmd) => { return db.ExecuteReader(cmd); }, parameterSetAction);
}
/// <summary>
/// 执行SQL文本，返回第一行第一列数据
/// </summary>
/// <param name="db">
/// <param name="sql">
/// <param name="parameterSetAction">
/// <returns></returns>
public static object ExecuteScalarBySqlString(this Database db, string sql, Action<dbcommand> parameterSetAction = null)
{
return db.ExecuteBySqlString(sql, (cmd) => { return db.ExecuteScalar(cmd); }, parameterSetAction);
}
private static T ExecuteBySqlString<t>(this Database db, string sql, Func<dbcommand t> resultFunc, Action<dbcommand> parameterSetAction = null)
{
var cmd = db.GetSqlStringCommand(sql);
if (parameterSetAction != null)
{
parameterSetAction(cmd);
}
return resultFunc(cmd);
}
}</dbcommand></dbcommand></t></dbcommand></dbcommand></dbcommand></t></t></t></idatareader></dbcommand></t></t></t></t></t></dbcommand></t></t>顺带补充个通用的Data扩展
/// <summary>
/// 数据相关的帮助类
/// </summary>
public static class DbHelper
{
/// <summary>
/// 从DataReader中读取可空对象
/// </summary>
/// <typeparam name="T">泛型T</typeparam>
/// <param name="dr">IDataReader
/// <param name="key">Key
/// <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 name="key">Key
/// <param name="defaultValue">当取到的数据为DBNull时，应当返回的默认值
/// <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 name="key">Key
/// <returns></returns>
public static string GetTrimedString(this IDataRecord dr, string key)
{
return dr[key].ToString().Trim();
}
}</t></t></t></t>
具体使用代码如下：
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());
}</product></product>
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：不可思议的每日培训（2）每日培训的庐山真面目Next article：vb.net操作数据库之ACCESS（3）