Microsoft enterprise database 6 Data Access Application Block Extension, enterprise database block

Microsoft enterprise database 6 Data Access Application Block Extension, enterprise database block

Although the title is an extension of 6, it is also applicable to 4 and 5, because the Enterprise Library has not changed much in these versions.

This extension is mainly used for DataAccessor <T>. When this class is created, Several Interfaces must be passed: IParameterMapper, IRowMapper <T>, IResultSetMapper <T>, the IRowMapper <T> enterprise database provides a MapBuilder <T> static class to help you create corresponding mappings. However, no ready-made classes are provided for IParameterMapper and IResultSetMapper <T> (maybe, but I did not find it. After all, I did not study its source code)

The following is the specific code. The design philosophy is: IParameterMapper and IResultSetMapper <T> are both methods to implement a convention. For this method, I directly set a general class, this class accepts the specific implementation delegate, which is determined by the coder, without the coder repeatedly creating the relevant class, reducing the amount of code

PS: DbCommand. Parameters. AddRange accepts the DbParameter parameter, so you can directly pass the specific DbParameter

1. GeneralParameterMapper

/// <Summary> /// General IParameterMapper // If the DbParameter to be passed before Execute has been used by other dbcommands, an exception will occur. // Therefore, you must call DbCommand. parameters. clear method releases the usage of other dbcommands on DbParameter // </summary> public class GeneralParameterMapper: IParameterMapper {private Action <DbCommand, object []> _ act; /// <summary> /// GeneralParameterMapper // </summary> /// <param name = "act"> /// defines how to assign parameterValues to the DbCommand delegate /// if not, will use By default, the Delegate does not verify whether the parameterValues to be passed has been defined in DbCommand. // executing Execute repeatedly will cause an exception, to ensure that repeated query </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 member /// <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> /// General IResultSetMapper // </summary> /// <typeparam name = "T"> </typeparam> public class GeneralResultSetMapper <T>: IResultSetMapper <T> {private Func <IDataReader, T> _ func; /// <summary> /// GeneralResultSetMapper // </summary> /// <param name = "func"> how to return T Delegation Based on IDataReader </param> public GeneralResultSetMapper (Func <IDataReader, t> func) {if (func = null) {throw new ArgumentNullException ();} this. _ func = func ;}# region IResultSetMapper <T> member /// <summary> // IResultSetMapper. mapSet /// </summary> /// <param name = "reader"> IDataReader </param> /// <returns> List set. If Count is 0, indicates no data and no null value is returned. </returns> public IEnumerable <T> MapSet (IDataReader reader) {List <T> list = new List <T> (); while (reader. read () {list. add (this. _ func (reader);} return list;} # endregion}

On the basis of these two classes, an extension class is encapsulated for the expansion of DataAccessor in the Microsoft enterprise database. The following code is specific. due to personal preferences, the extension method corresponding to Proc is not encapsulated, in fact, the Code is the same

/// <Summary> /// help class related to Microsoft enterprise database data /// </summary> public static class EntLibDbHelper {// <summary> // query SQL statements in batches and automatically fills in the object, if you do not need to pass DbParameter, we recommend that you do not use this method. You should use the Database. executeSqlStringAccessor method /// </summary> /// <typeparam name = "T"> </typeparam> /// <param name = "db"> </param>/ // <param name = "SQL"> </param> // <param name = "parameterMapperAction"> specify how the delegate confirms the processing of the SQL parameters to be passed, this value can be null. If it is null, the default delegate is used. This delegate directly transmits parameterValues to cmd. parameters. addRange method </param> /// <param name = "rowMapper"> indicates how to fill T with EntLib. If not, assign a value based on the T attribute by default </param> // <param name = "parameterValues"> the SQL parameter to be passed, this part corresponds to 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> /// query SQL statements in batches and automatically fill in the entity. If DbParameter is not required, we recommend that you do not use this method. executeSqlStringAccessor method /// </summary> /// <typeparam name = "T"> </typeparam> /// <param name = "db"> </param>/ // <param name = "SQL"> </param> // <param name = "parameterMapperAction"> specify how the delegate confirms the processing of the SQL parameters to be passed, this value can be null. If it is null, the default delegate is used. This delegate directly transmits parameterValues to cmd. parameters. addRange method </param> /// <param name = "resultSetMapperFunc"> indicates how EntLib fills T </param> /// <param name = "parameterValues"> SQL parameters, this part corresponds to 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 ();}}

Additional general Data extensions

/// <Summary> /// data-related help class /// </summary> public static class DbHelper {/// <summary> /// read from DataReader empty object // </summary> /// <typeparam name = "T"> generic 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> // when the DataReader reads an empty object, return defavalue value // </summary> /// <typeparam name = "T"> generic T </typeparam> /// <param name = "dr"> IDataReader </param> /// <param name = "key"> Key </param> /// <param name = "defaultValue"> when the obtained data is DBNull, default Value to be returned </param> /// <returns> </returns> public static T GetDefaultWhileNullable <T> (this IDataRecord dr, string key, T defaultValue = defa Ult (T) {return dr [key] = null | dr [key] = DBNull. Value? DefaultValue: (T) dr [key];} /// <summary> /// read the string from DataReader and remove the leading and trailing spaces. /// </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 ();}}

The Code is as follows:

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());            }