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 for DataAccessorT, this class must pass several interfaces during creation: IParameterMapper, IRowMapperT, and IResultSetMapperT. The IRowMapperT Enterprise Library provides a MapBuilderT static class to help you create
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 for DataAccessorT, this class must pass several interfaces during creation: IParameterMapper, IRowMapperT, and IResultSetMapperT. The IRowMapperT Enterprise Library provides a MapBuilderT static class to help you create
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 for DataAccessor This class must pass several interfaces during creation: IParameterMapper and IRowMapper , IResultSetMapper , Where IRowMapper The Enterprise Library provides MapBuilder Static class to help create corresponding mappings, but for IParameterMapper and IResultSetMapper No ready-made classes provided (maybe there are, but I didn't find them. After all, I didn't study its source code)
The following code is used to design IParameterMapper and IResultSetMapper. In fact, they all need to implement an agreed method. For this method, I directly set a general class that accepts the specific implementation delegate, which is determined by the coder, without the need for coder to repeatedly create related classes, reduce the amount of code
PS: DbCommand. Parameters. AddRange accepts the DbParameter parameter, so you can directly pass the specific DbParameter
1. GeneralParameterMapper
////// General IParameterMapper // If the DbParameter to be passed before Execute has been used by other dbcommands, an exception will occur. // you should call DbCommand. parameters. clear method to release the usage of other dbcommands on DbParameter ///Public class GeneralParameterMapper: IParameterMapper {private Action
_ Act ;///
/// GeneralParameterMapper //////
/// Defines how to assign parameterValues to the DbCommand delegate // if not passed, the default delegate will be used, this 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 queries will not be performed due to Encoding Problems.Public GeneralParameterMapper (Action
Act = null) {if (act! = Null) {this. _ act = act;} else {this. _ act = (cmd, paramters) => {cmd. parameters. addRange (paramters) ;}}# region IParameterMapper member ///
/// IParameterMapper. AssignParameters //////
///
Public void AssignParameters (DbCommand command, object [] parameterValues) {if (parameterValues! = Null & parameterValues. Length> 0) {this. _ act (command, parameterValues) ;}# endregion}
2. GeneralResultSetMapper
////// General IResultSetMapper //////
Public class GeneralResultSetMapper
: IResultSetMapper
{Private Func
_ Func ;///
/// GeneralResultSetMapper //////
According to how IDataReader returns T delegatePublic GeneralResultSetMapper (Func
Func) {if (func = null) {throw new ArgumentNullException ();} this. _ func = func ;}# region IResultSetMapper
Member ///
/// IResultSetMapper. MapSet //////
IDataReader///
List set. If Count is 0, it indicates no data and no null value is returned.
Public IEnumerable
MapSet (IDataReader reader) {List
List = new List
(); 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 the Microsoft enterprise database DataAccessor and Other Execute extensions are included. The following is the specific code, the extension method corresponding to Proc is not encapsulated, but the code is actually the same
PS: Extended here because DbCommand can be accessed in the delegate, parameters such as CommandTimeOut are not passed.
////// Help class related to Microsoft enterprise database data ///Public static class EntLibDbHelper {////// Query SQL statements in batches and automatically fill in the entities. If DbParameter is not required, we recommend that you do not use this method. You should use the Database. ExecuteSqlStringAccessor method //////
/////////Specify the delegate to confirm how to handle the SQL parameter to be passed. This value can be passed null. If it is null, the default delegate is used. The delegate directly passes parameterValues to cmd. Parameters. AddRange method.///Indicates how to fill T in EntLib. If T is not passed, the corresponding value is assigned based on the T attribute by default.///The SQL parameter to be passed. This part corresponds to parameterMapperAction.///
Public static IList
ExecuteBySqlString
(This Database db, string SQL, Action
ParameterMapperAction, IRowMapper
RowMapper = null, params object [] parameterValues) where T: new () {if (rowMapper = null) {rowMapper = MapBuilder
. BuildAllProperties ();} IParameterMapper parameterMapper = new GeneralParameterMapper (parameterMapperAction); return db. CreateSqlStringAccessor
(SQL, parameterMapper, rowMapper). Execute (parameterValues). ToList ();}///
/// Query SQL statements in batches and automatically fill in the entities. If DbParameter is not required, we recommend that you do not use this method. You should use the Database. ExecuteSqlStringAccessor method //////
///
///
///
Specify the delegate to confirm how to handle the SQL parameter to be passed. This value can be passed null. If it is null, the default delegate is used. The delegate directly passes parameterValues to cmd. Parameters. AddRange method.///
Indicates how EntLib fills T///
The SQL parameter to be passed. This part corresponds to parameterMapperAction.///
Public static IList
ExecuteBySqlString
(This Database db, string SQL, Action
ParameterMapperAction, Func
ResultSetMapperFunc, params object [] parameterValues) {IParameterMapper parameterMapper = new GeneralParameterMapper (parameterMapperAction); IResultSetMapper
ResultSetMapper = new GeneralResultSetMapper
(ResultSetMapperFunc); return db. CreateSqlStringAccessor
(SQL, parameterMapper, resultSetMapper). Execute (parameterValues). ToList ();}///
/// Execute the SQL text and return the number of affected rows //////
///
///
///
Public static int ExecuteNonQueryBySqlString (this Database db, string SQL, Action
ParameterSetAction = null) {return db. ExecuteBySqlString (SQL, (cmd) =>{ return db. ExecuteNonQuery (cmd) ;}, parameterSetAction );}///
/// Execute the SQL text and return DataReader //////
///
///
///
Public static IDataReader ExecuteReaderBySqlString (this Database db, string SQL, Action
ParameterSetAction = null) {return db. ExecuteBySqlString (SQL, (cmd) =>{ return db. ExecuteReader (cmd) ;}, parameterSetAction );}///
/// Execute the SQL text and return the first column of data in the first row //////
///
///
///
Public static object ExecuteScalarBySqlString (this Database db, string SQL, Action
ParameterSetAction = null) {return db. ExecuteBySqlString (SQL, (cmd) =>{ return db. ExecuteScalar (cmd) ;}, parameterSetAction);} private static T ExecuteBySqlString
(This Database db, string SQL, Func
ResultFunc, Action
ParameterSetAction = null) {var cmd = db. GetSqlStringCommand (SQL); if (parameterSetAction! = Null) {parameterSetAction (cmd);} return resultFunc (cmd );}}
Additional general Data extensions
////// Data-related help classes ///Public static class DbHelper {////// Read an empty object from DataReader //////
Generic T
///IDataReader///Key///
Public static Nullable
GetNullable
(This IDataRecord dr, string key) where T: struct {return dr [key] = null | dr [key] = DBNull. Value? (Nullable
) Null: (T) dr [key];} //
/// Defavalue value is returned when the DataReader reads an empty object //////
Generic T
///
IDataReader///
Key///
When the retrieved data is DBNull, the default value should be returned.///
Public static T GetDefaultWhileNullable
(This IDataRecord dr, string key, T defaultValue = default (T) {return dr [key] = null | dr [key] = DBNull. Value? DefaultValue: (T) dr [key];} //
/// Read the string from DataReader and remove the leading and trailing spaces //////
IDataReader///
Key///
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
rowMapper = MapBuilder
.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()); }