Asp.net Binarysoft.Library 数据库通用操作层（Binarysoft.Library.Data）

1.什么是Binarysoft.Library

2.Binarysoft.Library.Data 类图

3.Binarysoft.Library.Data 提供的方法

4.Binarysoft.Library.Data 加快数据操作的小技巧

5.小结

什么是Binarysoft.Library

Binarysoft.Library旨在完成，通用的Asp.net开发的快速类库。并最终，用此类库来支持快速开发基于Asp.net的BS应用程序（包含Webform与MVC）。还是那句老话，小菜大三开始用.Net来开发BS应用程序，已经过去4年的时间了。接触的项目，大大小小的也有十几个了。在北京工作了一年多，感觉是这一年给了我很大的升华，不再把目光局限于功能的实现，DB的设计，不再局限于某个功能的实现Code，也不再因为百度或是Google一下，而自己独立解决了问题而激动不已。我更加注重于细节，小的变量的名称，Class的约束字，大到Code的重用，重写，设计模式，性能。Code的博大精深，与聊聊几行代码而实现的高性能，低耦合，高内聚的设计，深深地叫我震撼。感觉一个好的开发框架，将是高效，快速开发必不可少的东西。所以，小菜想完成自己的框架。沉淀自己的技术。Binarysoft.Library便是，小菜的一点小想法。为什么叫Binarysoft,呵呵这是小菜想开的一家软件公司，至今无果。但是，小菜正在为公司的开发框架做准备，在这里就博各位看官一笑了。今天给大家，带来的Binarysoft.Library.Data数据的通用操作层。

Binarysoft.Library.Data 类图

支持 SqlServer，MySql，Access，Oracle等常见的几种数据。支持，常用的38中方法，并支持原生方法，灵活运用。优化存储过程的参数化，代码整洁简便。

Binarysoft.Library.Data 提供的方法

提供的38种常用方法：

IExecuteable

 /// <summary>
    /// 摘要: 定义为Database基类执行简便化查询,所要实现的接口.
    /// </summary>
    public interface IExecuteable
    {
        /// <summary>
        /// 摘要: IExecuteable接口 打开 指定 DbConnection.
        /// </summary>
        /// <param name="dbConnection">DbConnection</param>
        void OpenConnection(DbConnection dbConnection);
        /// <summary>
        /// 摘要: IExecuteable接口 关闭 指定 DbConnection.
        /// </summary>
        /// <param name="dbConnection">DbConnection</param>
        void CloseConnection(DbConnection dbConnection);
        /// <summary>
        /// 摘要: 根据SQL返回 IDataReader
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>IDataReader</returns>
        IDataReader ExecuteDataReader(string sql);
        /// <summary>
        /// 摘要: 根据SQL，ParameterCollection返回 IDataReader
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>IDataReader</returns>
        IDataReader ExecuteDataReader(string sql, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 根据SQL，ParameterCollection，DbTransaction返回 IDataReader
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <param name="tran">DbTransaction</param>
        /// <returns>IDataReader</returns>
        IDataReader ExecuteDataReader(string sql, ParameterCollection parameters, DbTransaction tran);
        /// <summary>
        /// 摘要: 根据SQL返回 DataSet 数据集
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>DataSet</returns>
        DataSet ExecuteDataSet(string sql);
        /// <summary>
        /// 摘要: 根据SQL，ParameterCollection返回 DataSet 数据集
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>DataSet</returns>
        DataSet ExecuteDataSet(string sql, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 根据SQL，ParameterCollection，DbTransaction返回 DataSet 数据集
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <param name="tran">DbTransaction</param>
        /// <returns>DataSet</returns>
        DataSet ExecuteDataSet(string sql, ParameterCollection parameters, DbTransaction tran);
        /// <summary>
        /// 摘要: 根据SQL返回 DataTable 数据集
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>DataTable</returns>
        DataTable ExecuteDataTable(string sql);
        /// <summary>
        /// 摘要: 根据SQL，ParameterCollection返回 DataTable
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>DataTable</returns>
        DataTable ExecuteDataTable(string sql, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 根据 SQL，ParameterCollection，DbTransaction 返回 DataTable
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <param name="tran">DbTransaction</param>
        /// <returns>DataTable</returns>
        DataTable ExecuteDataTable(string sql, ParameterCollection parameters, DbTransaction tran);
        /// <summary>
        /// 摘要: 根据SQL返回影响行数.
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>int</returns>
        int NonQuery(string sql);
        /// <summary>
        /// 摘要: 根据 SQL，ParameterCollection 返回影响行数.
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>int</returns>
        int NonQuery(string sql, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 根据SQL，ParameterCollection，DbTransaction 返回影响行数
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <param name="tran">DbTransaction</param>
        /// <returns>int</returns>
        int NonQuery(string sql, ParameterCollection parameters, DbTransaction tran);
        /// <summary>
        /// 摘要: 调用存储 Procedure 
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <returns>IDataReader</returns>
        IDataReader ExecuteProcDataReader(string procedureName);
        /// <summary>
        /// 摘要: 调用存储 Procedure 
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>IDataReader</returns>
        IDataReader ExecuteProcDataReader(string procedureName, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 调用存储 Procedure 
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <param name="tran">DbTransaction</param>
        /// <returns>IDataReader</returns>
        IDataReader ExecuteProcDataReader(string procedureName, ParameterCollection parameters, DbTransaction tran);
        /// <summary>
        /// 摘要: 调用存储 Procedure 
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <returns>DataSet</returns>
        DataSet ExecuteProcDataSet(string procedureName);
        /// <summary>
        /// 摘要: 调用存储 Procedure 
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>DataSet</returns>
        DataSet ExecuteProcDataSet(string procedureName, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 调用存储 Procedure
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <param name="tran">DbTransaction</param>
        /// <returns>DataSet</returns>
        DataSet ExecuteProcDataSet(string procedureName, ParameterCollection parameters, DbTransaction tran);
        /// <summary>
        /// 摘要: 调用存储 Procedure
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <returns>DataTable</returns>
        DataTable ExecuteProcDataTable(string procedureName);
        /// <summary>
        /// 摘要: 调用存储 Procedure
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>DataTable</returns>
        DataTable ExecuteProcDataTable(string procedureName, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 调用存储 Procedure
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <param name="tran">DbTransaction</param>
        /// <returns>DataTable</returns>
        DataTable ExecuteProcDataTable(string procedureName, ParameterCollection parameters, DbTransaction tran);
        /// <summary>
        /// 摘要: 调用存储 Procedure
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <returns>int</returns>
        int ProcNonQuery(string procedureName);
        /// <summary>
        /// 摘要: 调用存储 Procedure
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>int</returns>
        int ProcNonQuery(string procedureName, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 调用存储 Procedure
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <param name="tran">DbTransaction</param>
        /// <returns>int</returns>
        int ProcNonQuery(string procedureName, ParameterCollection parameters, DbTransaction tran);
        /// <summary>
        /// 摘要: 调用存储 Procedure
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <returns>object</returns>
        object ProcScalar(string procedureName);
        /// <summary>
        /// 摘要: 调用存储 Procedure
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>object</returns>
        object ProcScalar(string procedureName, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 调用存储 Procedure
        /// </summary>
        /// <param name="procedureName">procedureName</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <param name="tran">DbTransaction</param>
        /// <returns>object</returns>
        object ProcScalar(string procedureName, ParameterCollection parameters, DbTransaction tran);
        /// <summary>
        /// 摘要: 根据SQL返回 object值
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>object</returns>
        object Scalar(string sql);
        /// <summary>
        /// 摘要: 根据 SQL，ParameterCollection返回 object值
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>object</returns>
        object Scalar(string sql, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 根据 SQL，ParameterCollection，DbTransaction 返回 object 值
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <param name="tran">DbTransaction</param>
        /// <returns>object</returns>
        object Scalar(string sql, ParameterCollection parameters, DbTransaction tran);
        /// <summary>
        /// 摘要: 根据 key，pageSize，pageIndex,sql 分页返回 DataTable 值
        /// </summary>
        /// <param name="key">key</param>
        /// <param name="pageSize">pageSize</param>
        /// <param name="pageIndex">pageIndex</param>
        /// <param name="sql">sql</param>
        /// <param name="count">count</param>
        /// <returns>DataTable</returns>
        DataTable Pager(string key, int pageSize, int pageIndex, string sql, out int count);
        /// <summary>
        /// 摘要: 根据 key，pageSize，pageIndex,sql,orderBy 分页返回 DataTable 值
        /// </summary>
        /// <param name="key">key</param>
        /// <param name="pageSize">pageSize</param>
        /// <param name="pageIndex">pageIndex</param>
        /// <param name="sql">sql</param>
        /// <param name="count">count</param>
        /// <param name="orderBy">orderBy</param>
        /// <returns>DataTable</returns>
        DataTable Pager(string key, int pageSize, int pageIndex, string sql, out int count, string orderBy);
        /// <summary>
        /// 摘要: 根据 key，pageSize，pageIndex,sql,orderBy,ParameterCollection 分页返回 DataTable 值
        /// </summary>
        /// <param name="key">key</param>
        /// <param name="pageSize">pageSize</param>
        /// <param name="pageIndex">pageIndex</param>
        /// <param name="sql">sql</param>
        /// <param name="count">count</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>DataTable</returns>
        DataTable Pager(string key, int pageSize, int pageIndex, string sql, out int count, ParameterCollection parameters);
        /// <summary>
        /// 摘要: 根据 key，pageSize，pageIndex,sql,orderBy,ParameterCollection 分页返回 DataTable 值
        /// </summary>
        /// <param name="key">key</param>
        /// <param name="pageSize">pageSize</param>
        /// <param name="pageIndex">pageIndex</param>
        /// <param name="sql">sql</param>
        /// <param name="count">count</param>
        /// <param name="orderBy">orderBy</param>
        /// <param name="parameters">ParameterCollection</param>
        /// <returns>DataTable</returns>
        DataTable Pager(string key, int pageSize, int pageIndex, string sql, out int count, string orderBy, ParameterCollection parameters);
    }

提供原生的常用方法：

ICreateMemberable

 1 /// <summary>
 2     /// 摘要: 提供基础的Ado.Net需要的操作对象.
 3     /// </summary>
 4     public interface ICreateMemberable
 5     {
 6         /// <summary>
 7         /// 摘要: 提供基础的Ado.Net需要的 DbDataAdapter 对象
 8         /// </summary>
 9         /// <returns>DbDataAdapter</returns>
10         DbDataAdapter CreateAdapter();
11         /// <summary>
12         /// 摘要: 提供基础的Ado.Net需要的 DbCommand 对象
13         /// </summary>
14         /// <returns>DbCommand</returns>
15         DbCommand CreateCommand();
16         /// <summary>
17         /// 摘要: 提供基础的Ado.Net需要的 DbConnection 对象
18         /// </summary>
19         /// <returns>DbConnection</returns>
20         DbConnection CreateConnection();
21         /// <summary>
22         /// 摘要: 提供基础的Ado.Net需要的 DbParameter 对象
23         /// </summary>
24         /// <returns>DbParameter</returns>
25         DbParameter CreateParameter();
26         /// <summary>
27         /// 摘要: 提供基础的Ado.Net需要的 DbTransaction 对象
28         /// </summary>
29         /// <returns>DbTransaction</returns>
30         DbTransaction CreateTransaction();
31     }

存储过程的参数简化：

Parameter && ParameterCollection

  1 /// <summary>
  2     /// 摘要: 为实现多数据操作提供的,DbParameter集合.
  3     /// </summary>
  4     public class ParameterCollection 
  5     {
  6         IList<Parameter> _Parameters = new List<Parameter>();
  7         /// <summary>
  8         /// 摘要: IList[Parameter]集合
  9         /// </summary>
 10         public IList<Parameter> Parameters
 11         {
 12             get { return _Parameters; }
 13         }
 14         /// <summary>
 15         /// 摘要: 获取指定 Parameter 的索引.
 16         /// </summary>
 17         /// <param name="item">Parameter</param>
 18         /// <returns>Int</returns>
 19         public int IndexOf(Parameter item)
 20         {
 21             return _Parameters.IndexOf(item);
 22         }
 23         /// <summary>
 24         /// 摘要: 在指定位置插入 Parameter.
 25         /// </summary>
 26         /// <param name="index">Index</param>
 27         /// <param name="item">Parameter</param>
 28         public void Insert(int index, Parameter item)
 29         {
 30             _Parameters.Insert(index, item);
 31         }
 32         /// <summary>
 33         /// 摘要: 移除指定索引位置的 Parameter.
 34         /// </summary>
 35         /// <param name="index"></param>
 36         public void RemoveAt(int index)
 37         {
 38             _Parameters.RemoveAt(index);
 39         }
 40         /// <summary>
 41         /// 摘要: 根据索引值获得 Parameter.
 42         /// </summary>
 43         /// <param name="index">Index</param>
 44         /// <returns>Parameter</returns>
 45         public Parameter this[int index]
 46         {
 47             get
 48             {
 49                 return _Parameters[index];
 50             }
 51             set
 52             {
 53                 _Parameters[index] = value;
 54             }
 55         }
 56         /// <summary>
 57         /// 摘要: 向集合添加 Parameter.
 58         /// </summary>
 59         /// <param name="item">Parameter</param>
 60         public void Add(Parameter item)
 61         {
 62             _Parameters.Add(item);
 63         }
 64         /// <summary>
 65         /// 摘要: 向集合添加 Parameter.
 66         /// </summary>
 67         /// <param name="parameterName">parameterName</param>
 68         /// <param name="parameterValue">parameterValue</param>
 69         public void Add(string parameterName, object parameterValue)
 70         {
 71             Parameter Parameter = new Parameter(parameterName,parameterValue);
 72             _Parameters.Add(Parameter);
 73         }
 74         /// <summary>
 75         /// 摘要: 清除 ParameterCollection.
 76         /// </summary>
 77         public void Clear()
 78         {
 79             _Parameters.Clear();
 80         }
 81         /// <summary>
 82         /// 摘要: 是否存在指定 Parameters
 83         /// </summary>
 84         /// <param name="item">Parameter</param>
 85         /// <returns>bool</returns>
 86         public bool Contains(Parameter item)
 87         {
 88             return _Parameters.Contains(item);
 89         }
 90         /// <summary>
 91         /// 摘要: 指定位置批量插入 Parameter[].
 92         /// </summary>
 93         /// <param name="array">Parameter[]</param>
 94         /// <param name="arrayIndex">Index</param>
 95         public void CopyTo(Parameter[] array, int arrayIndex)
 96         {
 97             _Parameters.CopyTo(array, arrayIndex);
 98         }
 99         /// <summary>
100         /// 摘要: Parameter数量.
101         /// </summary>
102         public int Count
103         {
104             get { return _Parameters.Count; }
105         }
106         /// <summary>
107         /// 摘要: 是否只读.
108         /// </summary>
109         public bool IsReadOnly
110         {
111             get { return _Parameters.IsReadOnly; }
112         }
113         /// <summary>
114         /// 摘要: 移除指定的Parameter.
115         /// </summary>
116         /// <param name="item">Parameter</param>
117         /// <returns>bool</returns>
118         public bool Remove(Parameter item)
119         {
120             return _Parameters.Remove(item);
121         }
122     }
123 /// <summary>
124     /// 摘要: 继承 IDataParameter 的抽象参数类.
125     /// </summary>
126     public class Parameter : IDataParameter
127     {
128         private ParameterDirection _Direction = ParameterDirection.Input;
129         private string _ParameterName;
130         private object _Value;
131         private string _SourceColumn;
132         private DataRowVersion _SourceVersion;
133         private bool _IsNullable = true;
134         private DbType _DbType;
135         /// <summary>
136         /// 摘要: ODBParameter 构造函数.
137         /// </summary>
138         /// <param name="parameterName">parameterName</param>
139         /// <param name="parameterValue">parameterValue</param>
140         public Parameter(string parameterName, object parameterValue)
141         {
142             this._ParameterName = parameterName;
143             this._Value = parameterValue;
144         }
145         /// <summary>
146         /// 摘要: ODBParameter 构造函数. 
147         /// </summary>
148         /// <param name="parameterName">parameterName</param>
149         /// <param name="parameterValue">parameterValue</param>
150         /// <param name="direction">ParameterDirection</param>
151         public Parameter(string parameterName, object parameterValue, ParameterDirection direction)
152         {
153             this._ParameterName = parameterName;
154             this._Value = parameterValue;
155             this._Direction = direction;
156         }
157         /// <summary>
158         /// 摘要: ODBParameter 构造函数. 
159         /// </summary>
160         /// <param name="parameterName">parameterName</param>
161         /// <param name="parameterValue">parameterValue</param>
162         /// <param name="direction">ParameterDirection</param>
163         /// <param name="dbType">DbType</param>
164         /// <param name="sourceColumn">SourceColumn</param>
165         /// <param name="sourceVersion">DataRowVersion</param>
166         public Parameter(string parameterName, object parameterValue, ParameterDirection direction, DbType dbType, string sourceColumn, DataRowVersion sourceVersion)
167         {
168             this._ParameterName = parameterName;
169             this._Value = parameterValue;
170             this._Direction = direction;
171             this._DbType = dbType;
172             this._SourceColumn = sourceColumn;
173             this._SourceVersion = sourceVersion;
174         }
175         /// <summary>
176         /// 摘要: DbType
177         /// </summary>
178         public DbType DbType
179         {
180             get
181             {
182                 return _DbType;
183             }
184             set
185             {
186                 _DbType = value;
187             }
188         }
189         /// <summary>
190         /// 摘要: ParameterDirection
191         /// </summary>
192         public ParameterDirection Direction
193         {
194             get
195             {
196                 return this._Direction;
197             }
198             set
199             {
200                 this._Direction = value;
201             }
202         }
203         /// <summary>
204         /// 摘要: IsNullable
205         /// </summary>
206         public bool IsNullable
207         {
208             get { return _IsNullable; }
209         }
210         /// <summary>
211         /// 摘要: ParameterName
212         /// </summary>
213         public string ParameterName
214         {
215             get
216             {
217                 return _ParameterName;
218             }
219             set
220             {
221                 _ParameterName = value;
222             }
223         }
224         /// <summary>
225         /// 摘要: SourceColumn
226         /// </summary>
227         public string SourceColumn
228         {
229             get
230             {
231                 return _SourceColumn;
232             }
233             set
234             {
235                 _SourceColumn = value;
236             }
237         }
238         /// <summary>
239         /// 摘要: DataRowVersion
240         /// </summary>
241         public DataRowVersion SourceVersion
242         {
243             get
244             {
245                 return _SourceVersion;
246             }
247             set
248             {
249                 _SourceVersion = value;
250             }
251         }
252         /// <summary>
253         /// 摘要: Value
254         /// </summary>
255         public object Value
256         {
257             get
258             {
259                 return _Value;
260             }
261             set
262             {
263                 _Value = value;
264             }
265         }
266     }

基类的实现：

DataBase

  1  /// <summary>
  2     /// 摘要: 定义多数据库的抽象基类,该类实现IExecuteable接口,此接口提供更为简便的 Ado.net 操作.
  3     /// </summary>
  4     public abstract class Database : IExecuteable, ICreateMemberable
  5     {
  6         private static readonly ParameterCache dbParameters = new ParameterCache();
  7         private readonly DbProviderFactory dbProviderFactory;
  8         private readonly string connectionString;
  9 
 10         #region Private methods
 11         /// <summary>
 12         /// 摘要: 创建参数数组.
 13         /// </summary>
 14         /// <param name="ParameterCollection">ParameterCollection</param>
 15         /// <param name="sql">SQL</param>
 16         /// <returns>DbParameter[]</returns>
 17         protected virtual DbParameter[] CreateParameterArray(ParameterCollection ParameterCollection, string sql)
 18         {
 19             DbParameter[] dbParameterArray = new DbParameter[ParameterCollection.Count];
 20             if (dbParameters.AlreadyCached(connectionString, sql))
 21             {
 22                 DbParameter[] dataParameters = dbParameters.GetParametersFromCached(connectionString, sql);
 23                 for (int index = 0; index < dataParameters.Length; index++)
 24                 {
 25                     dataParameters[index].Value = ParameterCollection[index].Value;
 26                 }
 27                 return dataParameters;
 28             }
 29             else
 30             {
 31                 int indexParameter = 0;
 32                 foreach (Parameter parameter in ParameterCollection.Parameters)
 33                 {
 34                     dbParameterArray[indexParameter] = this.CreateParameter();
 35                     dbParameterArray[indexParameter].ParameterName = parameter.ParameterName;
 36                     dbParameterArray[indexParameter].Value = parameter.Value;
 37                     dbParameterArray[indexParameter].Direction = parameter.Direction;
 38                     indexParameter++;
 39                 }
 40                 dbParameters.AddParameterInCache(connectionString, sql, dbParameterArray);
 41                 return dbParameterArray;
 42             }
 43         }
 44         /// <summary>
 45         /// 摘要: 给参数赋值.
 46         /// </summary>
 47         /// <param name="dbCommand">DbCommand</param>
 48         /// <param name="ParameterCollection">ParameterCollection</param>
 49         protected virtual void SetParameters(DbCommand dbCommand, ParameterCollection ParameterCollection)
 50         {
 51             foreach (Parameter parameter in ParameterCollection.Parameters)
 52             {
 53                 if (dbCommand.Parameters[parameter.ParameterName].Direction != ParameterDirection.Input)
 54                 {
 55                     parameter.Value = dbCommand.Parameters[parameter.ParameterName].Value;
 56                 }
 57             }
 58         }
 59         #endregion
 60 
 61         #region ICreateDataBaseMemberable Implements
 62         /// <summary>
 63         /// 摘要: 实例化数据库操作基类
 64         /// </summary>
 65         /// <param name="connectionString">指定数据库连接字符串</param>
 66         /// <param name="dbProviderFactory">提供DbProviderFactory</param>
 67         public Database(string connectionString, DbProviderFactory dbProviderFactory)
 68         {
 69             this.dbProviderFactory = dbProviderFactory;
 70             this.connectionString = connectionString;
 71         }
 72         /// <summary>
 73         /// 摘要: 创建连接字符传基类 DbConnection
 74         /// </summary>
 75         /// <returns></returns>
 76         public DbConnection CreateConnection()
 77         {
 78             DbConnection dbConnection = dbProviderFactory.CreateConnection();
 79             dbConnection.ConnectionString = this.connectionString;
 80             return dbConnection;
 81         }
 82         /// <summary>
 83         /// 摘要: 根据数据库支持类创建变量 DbParameter
 84         /// </summary>
 85         /// <returns></returns>
 86         public DbParameter CreateParameter()
 87         {
 88             return this.dbProviderFactory.CreateParameter();
 89         }
 90         /// <summary>
 91         /// 摘要: 根据 DbProviderFactory 创建 DbCommand 实例
 92         /// </summary>
 93         /// <returns></returns>
 94         public DbCommand CreateCommand()
 95         {
 96             return this.CreateConnection().CreateCommand();
 97         }
 98         /// <summary>
 99         /// 摘要: 根据 DbProviderFactory 创建 DbDataAdapter 实例
100         /// </summary>
101         /// <returns></returns>
102         public DbDataAdapter CreateAdapter()
103         {
104             DbDataAdapter dbDataAdapter = this.dbProviderFactory.CreateDataAdapter();
105             dbDataAdapter.SelectCommand = this.CreateCommand();
106             return dbDataAdapter;
107         }
108         /// <summary>
109         /// 摘要: 根据 DbProviderFactory 创建 DbTransaction 实例
110         /// </summary>
111         /// <returns></returns>
112         public DbTransaction CreateTransaction()
113         {
114             return CreateConnection().BeginTransaction();
115         }
116         #endregion
117 
118         #region IExecuteable Implements
119 
120         #region Connection Management
121 
122         /// <summary>
123         /// 摘要: 打开数据库操作实例现有连接
124         /// </summary>
125         /// <param name="dbConnection"></param>
126         public void OpenConnection(DbConnection dbConnection)
127         {
128             if (dbConnection != null && dbConnection.State != ConnectionState.Open)
129             {
130                 dbConnection.Open();
131             }
132         }
133         /// <summary>
134         /// 摘要: 关闭数据库操作实例现有连接
135         /// </summary>
136         /// <param name="dbConnection"></param>
137         public void CloseConnection(DbConnection dbConnection)
138         {
139             if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
140             {
141                 dbConnection.Close();
142             }
143             dbConnection.Dispose();
144         }
145 
146         #endregion
147 
148         #region Execute Custom
149 
150         #region ExecuteDataReader
151         /// <summary>
152         /// 摘要: 根据SQL返回 IDataReader
153         /// </summary>
154         /// <param name="sql">SQL</param>
155         /// <returns>IDataReader</returns>
156         public IDataReader ExecuteDataReader(string sql)
157         {
158             DbCommand dbCommand = this.CreateCommand();
159             dbCommand.CommandText = sql;
160             OpenConnection(dbCommand.Connection);
161             return dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
162         }
163         /// <summary>
164         /// 摘要: 根据SQL，ParameterCollection返回 IDataReader
165         /// </summary>
166         /// <param name="sql">SQL</param>
167         /// <param name="parameters">ParameterCollection</param>
168         /// <returns>IDataReader</returns>
169         public IDataReader ExecuteDataReader(string sql, ParameterCollection parameters)
170         {
171             DbCommand dbcommand = this.CreateCommand();
172             dbcommand.CommandText = sql;
173             dbcommand.Parameters.AddRange(CreateParameterArray(parameters, sql));
174             return dbcommand.ExecuteReader(CommandBehavior.CloseConnection);
175         }
176         /// <summary>
177         /// 摘要: 根据SQL，ParameterCollection，DbTransaction返回 IDataReader
178         /// </summary>
179         /// <param name="sql">SQL</param>
180         /// <param name="parameters">ParameterCollection</param>
181         /// <param name="tran">DbTransaction</param>
182         /// <returns>IDataReader</returns>
183         public IDataReader ExecuteDataReader(string sql, ParameterCollection parameters, DbTransaction tran)
184         {
185             DbCommand dbcommand = this.CreateCommand();
186             dbcommand.CommandText = sql;
187             dbcommand.Transaction = tran;
188             dbcommand.Parameters.AddRange(CreateParameterArray(parameters, sql));
189             return dbcommand.ExecuteReader(CommandBehavior.CloseConnection);
190         }
191 
192         #endregion
193 
194         #region ExecuteDataSet
195         /// <summary>
196         /// 摘要: 根据SQL返回 DataSet数据集
197         /// </summary>
198         /// <param name="sql">SQL</param>
199         /// <returns>DataSet</returns>
200         public DataSet ExecuteDataSet(string sql)
201         {
202             DataSet dataSet = new DataSet();
203             DbDataAdapter dataAdapter = this.CreateAdapter();
204             dataAdapter.SelectCommand.CommandText = sql;
205             dataAdapter.Fill(dataSet);
206             return dataSet;
207         }
208         /// <summary>
209         /// 摘要: 根据SQL，ParameterCollection返回 DataSet数据集
210         /// </summary>
211         /// <param name="sql">SQL</param>
212         /// <param name="parameters">ParameterCollection</param>
213         /// <returns>DataSet</returns>
214         public DataSet ExecuteDataSet(string sql, ParameterCollection parameters)
215         {
216             DataSet dataSet = new DataSet();
217             DbDataAdapter dataAdapter = this.CreateAdapter();
218             dataAdapter.SelectCommand.CommandText = sql;
219             dataAdapter.SelectCommand.Parameters.AddRange(CreateParameterArray(parameters, sql));
220             dataAdapter.Fill(dataSet);
221             SetParameters(dataAdapter.SelectCommand, parameters);
222             return dataSet;
223         }
224         /// <summary>
225         /// 摘要: 根据SQL，ParameterCollection，DbTransaction返回 DataSet数据集
226         /// </summary>
227         /// <param name="sql">SQL</param>
228         /// <param name="parameters">ParameterCollection</param>
229         /// <param name="tran">DbTransaction</param>
230         /// <returns>DataSet</returns>
231         public DataSet ExecuteDataSet(string sql, ParameterCollection parameters, DbTransaction tran)
232         {
233             DataSet dataSet = new DataSet();
234             DbDataAdapter dataAdapter = this.CreateAdapter();
235             dataAdapter.SelectCommand.CommandText = sql;
236             dataAdapter.SelectCommand.Parameters.AddRange(CreateParameterArray(parameters, sql));
237             dataAdapter.SelectCommand.Transaction = tran;
238             dataAdapter.Fill(dataSet);
239             SetParameters(dataAdapter.SelectCommand, parameters);
240             return dataSet;
241         }
242 
243         #endregion
244 
245         #region ExecuteDataTable
246         /// <summary>
247         /// 摘要: 根据SQL返回 DataTable数据集
248         /// </summary>
249         /// <param name="sql">SQL</param>
250         /// <returns>DataTable</returns>
251         public DataTable ExecuteDataTable(string sql)
252         {
253             DataTable dataTable = new DataTable();
254             DbDataAdapter dataAdapter = this.CreateAdapter();
255             dataAdapter.SelectCommand.CommandText = sql;
256             dataAdapter.Fill(dataTable);
257             return dataTable;
258         }
259         /// <summary>
260         /// 摘要: 根据SQL，ParameterCollection返回 DataTable数据集
261         /// </summary>
262         /// <param name="sql">SQL</param>
263         /// <param name="parameters">ParameterCollection</param>
264         /// <returns>DataTable</returns>
265         public DataTable ExecuteDataTable(string sql, ParameterCollection parameters)
266         {
267             DataTable dataTable = new DataTable();
268             DbDataAdapter dataAdapter = this.CreateAdapter();
269             dataAdapter.SelectCommand.CommandText = sql;
270             dataAdapter.SelectCommand.Parameters.AddRange(CreateParameterArray(parameters, sql));
271             dataAdapter.Fill(dataTable);
272             SetParameters(dataAdapter.SelectCommand, parameters);
273             return dataTable;
274         }
275         /// <summary>
276         /// 摘要: 根据SQL，ParameterCollection，DbTransaction返回 DataTable数据集
277         /// </summary>
278         /// <param name="sql">SQL</param>
279         /// <param name="parameters">ParameterCollection</param>
280         /// <param name="tran">DbTransaction</param>
281         /// <returns>DataTable</returns>
282         public DataTable ExecuteDataTable(string sql, ParameterCollection parameters, DbTransaction tran)
283         {
284             DataTable dataTable = new DataTable();
285             DbDataAdapter dataAdapter = this.CreateAdapter();
286             dataAdapter.SelectCommand.CommandText = sql;
287             dataAdapter.SelectCommand.Parameters.AddRange(CreateParameterArray(parameters, sql));
288             dataAdapter.SelectCommand.Transaction = tran;
289             dataAdapter.Fill(dataTable);
290             SetParameters(dataAdapter.SelectCommand, parameters);
291             return dataTable;
292         }
293         /// <summary>
294         /// 摘要: 根据 key,pageSize,pageIndex,sql 分页返回 DataTable 值
295         /// </summary>
296         /// <param name="key">key</param>
297         /// <param name="pageSize">pageSize</param>
298         /// <param name="pageIndex">pageIndex</param>
299         /// <param name="sql">sql</param>
300         /// <param name="count">count</param>
301         /// <returns>DataTable</returns>
302         public DataTable Pager(string key, int pageSize, int pageIndex, string sql, out int count)
303         {
304             return Pager(key, pageSize, pageIndex, sql, out count, key);
305         }
306         /// <summary>
307         /// 摘要: 根据 key,pageSize,pageIndex,sql,orderBy 分页返回 DataTable 值
308         /// </summary>
309         /// <param name="key">key</param>
310         /// <param name="pageSize">pageSize</param>
311         /// <param name="pageIndex">pageIndex</param>
312         /// <param name="sql">sql</param>
313         /// <param name="count">count</param>
314         /// <param name="orderBy">orderBy</param>
315         /// <returns>DataTable</returns>
316         public DataTable Pager(string key, int pageSize, int pageIndex, string sql, out int count, string orderBy)
317         {
318             string sqlCount = "SELECT COUNT(*) FROM ({0}) AS T";
319             count = (int)Scalar(string.Format(sqlCount, sql));
320             string sqlString = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS ROWINDEX,* FROM ({1}) AS A) AS B WHERE ROWINDEX > {2} and ROWINDEX <={3} Order BY {4}";
321             int eIndex = pageIndex * pageSize;
322             int sIndex = pageSize * (pageIndex - 1);
323             return ExecuteDataTable(string.Format(sqlString, key, sql, sIndex, eIndex, orderBy));
324         }
325         /// <summary>
326         /// 摘要: 根据 key,pageSize,pageIndex,sql,orderBy,ParameterCollection 分页返回 DataTable 值
327         /// </summary>
328         /// <param name="key">key</param>
329         /// <param name="pageSize">pageSize</param>
330         /// <param name="pageIndex">pageIndex</param>
331         /// <param name="sql">sql</param>
332         /// <param name="count">count</param>
333         /// <param name="parameters">ParameterCollection</param>
334         /// <returns>DataTable</returns>
335         public DataTable Pager(string key, int pageSize, int pageIndex, string sql, out int count, ParameterCollection parameters)
336         {
337             return Pager(key, pageSize, pageIndex, sql, out count, key, parameters);
338         }
339         /// <summary>
340         /// 摘要: 根据 key,pageSize,pageIndex,sql,orderBy,ParameterCollection 分页返回 DataTable 值
341         /// </summary>
342         /// <param name="key">key</param>
343         /// <param name="pageSize">pageSize</param>
344         /// <param name="pageIndex">pageIndex</param>
345         /// <param name="sql">sql</param>
346         /// <param name="count">count</param>
347         /// <param name="orderBy">orderBy</param>
348         /// <param name="parameters">ParameterCollection</param>
349         /// <returns>DataTable</returns>
350         public DataTable Pager(string key, int pageSize, int pageIndex, string sql, out int count, string orderBy, ParameterCollection parameters)
351         {
352             string sqlCount = "SELECT COUNT(*) FROM ({0}) AS T";
353             count = (int)Scalar(string.Format(sqlCount, sql), parameters);
354             string sqlString = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS ROWINDEX,* FROM ({1}) AS A) AS B WHERE ROWINDEX > {2} and ROWINDEX <={3} Order BY {4} ";
355             int eIndex = pageIndex * pageSize;
356             int sIndex = pageSize * (pageIndex - 1);
357             return ExecuteDataTable(string.Format(sqlString, key, sql, sIndex, eIndex, orderBy), parameters);
358         }
359 
360         #endregion
361 
362         #region ExecuteNonQuery
363         /// <summary>
364         /// 摘要: 根据SQL返回影响行数.
365         /// </summary>
366         /// <param name="sql">SQL</param>
367         /// <returns>int</returns>
368         public int NonQuery(string sql)
369         {
370             DbCommand dbCommand = this.CreateCommand();
371             dbCommand.CommandText = sql;
372             OpenConnection(dbCommand.Connection);
373             int res = dbCommand.ExecuteNonQuery();
374             CloseConnection(dbCommand.Connection);
375             return res;
376         }
377         /// <summary>
378         /// 摘要: 根据SQL，ParameterCollection返回 影响行数.
379         /// </summary>
380         /// <param name="sql">SQL</param>
381         /// <param name="parameters">ParameterCollection</param>
382         /// <returns>int</returns>
383         public int NonQuery(string sql, ParameterCollection parameters)
384         {
385             DbCommand dbCommand = this.CreateCommand();
386             dbCommand.Parameters.AddRange(CreateParameterArray(parameters, sql));
387             dbCommand.CommandText = sql;
388             OpenConnection(dbCommand.Connection);
389             int res = dbCommand.ExecuteNonQuery();
390             SetParameters(dbCommand, parameters);
391             CloseConnection(dbCommand.Connection);
392             return res;
393         }
394         /// <summary>
395         /// 摘要: 根据SQL，ParameterCollection，DbTransaction返回 影响行数
396         /// </summary>
397         /// <param name="sql">SQL</param>
398         /// <param name="parameters">ParameterCollection</param>
399         /// <param name="tran">DbTransaction</param>
400         /// <returns>int</returns>
401         public int NonQuery(string sql, ParameterCollection parameters, DbTransaction tran)
402         {
403             DbCommand dbCommand = this.CreateCommand();
404             dbCommand.Parameters.AddRange(CreateParameterArray(parameters, sql));
405             dbCommand.Transaction = tran;
406             dbCommand.CommandText = sql;
407             OpenConnection(dbCommand.Connection);
408             int res = dbCommand.ExecuteNonQuery();
409             SetParameters(dbCommand, parameters);
410             CloseConnection(dbCommand.Connection);
411             return res;
412         }
413 
414         #endregion
415 
416         #region ExecuteScalar
417         /// <summary>
418         /// 摘要: 根据SQL返回 object值
419         /// </summary>
420         /// <param name="sql">SQL</param>
421         /// <returns>object</returns>
422         public object Scalar(string sql)
423         {
424             DbCommand dbCommand = this.CreateCommand();
425             dbCommand.CommandText = sql;
426             OpenConnection(dbCommand.Connection);
427             object res = dbCommand.ExecuteScalar();
428             CloseConnection(dbCommand.Connection);
429             return res;
430         }
431         /// <summary>
432         /// 摘要: 根据SQL，ParameterCollection返回 object值
433         /// </summary>
434         /// <param name="sql">SQL</param>
435         /// <param name="parameters">ParameterCollection</param>
436         /// <returns>object</returns>
437         public object Scalar(string sql, ParameterCollection parameters)
438         {
439             DbCommand dbCommand = this.CreateCommand();
440             dbCommand.CommandText = sql;
441             dbCommand.Parameters.AddRange(CreateParameterArray(parameters, sql));
442             OpenConnection(dbCommand.Connection);
443             object res = dbCommand.ExecuteScalar();
444             SetParameters(dbCommand, parameters);
445             CloseConnection(dbCommand.Connection);
446             return res;
447         }
448         /// <summary>
449         /// 摘要: 根据SQL，ParameterCollection，DbTransaction返回 object值
450         /// </summary>
451         /// <param name="sql">SQL</param>
452         /// <param name="parameters">ParameterCollection</param>
453         /// <param name="tran">DbTransaction</param>
454         /// <returns>object</returns>
455         public object Scalar(string sql, ParameterCollection parameters, DbTransaction tran)
456         {
457             DbCommand dbCommand = this.CreateCommand();
458             dbCommand.CommandText = sql;
459             dbCommand.Parameters.AddRange(CreateParameterArray(parameters, sql));
460             dbCommand.Transaction = tran;
461             OpenConnection(dbCommand.Connection);
462             object res = dbCommand.ExecuteScalar();
463             SetParameters(dbCommand, parameters);
464             CloseConnection(dbCommand.Connection);
465             return res;
466         }
467 
468         #endregion
469 
470         #endregion
471 
472         #region Execute Proc
473 
474         #region ExecuteProDataReader
475         /// <summary>
476         /// 摘要: 调用存储 Procedure 
477         /// </summary>
478         /// <param name="procedureName">procedureName</param>
479         /// <returns>IDataReader</returns>
480         public IDataReader ExecuteProcDataReader(string procedureName)
481         {
482             DbCommand dbCommand = this.CreateCommand();
483             dbCommand.CommandText = procedureName;
484             dbCommand.CommandType = CommandType.StoredProcedure;
485             OpenConnection(dbCommand.Connection);
486             return dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
487         }
488         /// <summary>
489         /// 摘要: 调用存储 Procedure 
490         /// </summary>
491         /// <param name="procedureName">procedureName</param>
492         /// <param name="parameters">ParameterCollection</param>
493         /// <returns>IDataReader</returns>
494         public IDataReader ExecuteProcDataReader(string procedureName, ParameterCollection parameters)
495         {
496             DbCommand dbCommand = this.CreateCommand();
497             dbCommand.CommandText = procedureName;
498             dbCommand.CommandType = CommandType.StoredProcedure;
499             dbCommand.Parameters.AddRange(CreateParameterArray(parameters, procedureName));
500             OpenConnection(dbCommand.Connection);
501             return dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
502         }
503         /// <summary>
504         /// 摘要: 调用存储 Procedure 
505         /// </summary>
506         /// <param name="procedureName">procedureName</param>
507         /// <param name="parameters">ParameterCollection</param>
508         /// <param name="tran">DbTransaction</param>
509         /// <returns>IDataReader</returns>
510         public IDataReader ExecuteProcDataReader(string procedureName, ParameterCollection parameters, DbTransaction tran)
511         {
512             DbCommand dbCommand = this.CreateCommand();
513             dbCommand.CommandText = procedureName;
514             dbCommand.CommandType = CommandType.StoredProcedure;
515             dbCommand.Parameters.AddRange(CreateParameterArray(parameters, procedureName));
516             dbCommand.Transaction = tran;
517             OpenConnection(dbCommand.Connection);
518             return dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
519         }
520 
521         #endregion
522 
523         #region ExecuteProDataSet
524         /// <summary>
525         /// 摘要: 调用存储 Procedure 
526         /// </summary>
527         /// <param name="procedureName">procedureName</param>
528         /// <returns>DataSet</returns>
529         public DataSet ExecuteProcDataSet(string procedureName)
530         {
531             DataSet dataSet = new DataSet();
532             DbDataAdapter dataAdapter = this.CreateAdapter();
533             dataAdapter.SelectCommand.CommandText = procedureName;
534             dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
535             dataAdapter.Fill(dataSet);
536             return dataSet;
537         }
538         /// <summary>
539         /// 摘要: 调用存储 Procedure 
540         /// </summary>
541         /// <param name="procedureName">procedureName</param>
542         /// <param name="parameters">ParameterCollection</param>
543         /// <returns>DataSet</returns>
544         public DataSet ExecuteProcDataSet(string procedureName, ParameterCollection parameters)
545         {
546             DataSet dataSet = new DataSet();
547             DbDataAdapter dataAdapter = this.CreateAdapter();
548             dataAdapter.SelectCommand.CommandText = procedureName;
549             dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
550             dataAdapter.SelectCommand.Parameters.AddRange(CreateParameterArray(parameters, procedureName));
551             dataAdapter.Fill(dataSet);
552             SetParameters(dataAdapter.SelectCommand, parameters);
553             return dataSet;
554         }
555         /// <summary>
556         /// 摘要: 调用存储 Procedure
557         /// </summary>
558         /// <param name="procedureName">procedureName</param>
559         /// <param name="parameters">ParameterCollection</param>
560         /// <param name="tran">DbTransaction</param>
561         /// <returns>DataSet</returns>
562         public DataSet ExecuteProcDataSet(string procedureName, ParameterCollection parameters, DbTransaction tran)
563         {
564             DataSet dataSet = new DataSet();
565             DbDataAdapter dataAdapter = this.CreateAdapter();
566             dataAdapter.SelectCommand.CommandText = procedureName;
567             dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
568             dataAdapter.SelectCommand.Parameters.AddRange(CreateParameterArray(parameters, procedureName));
569             dataAdapter.SelectCommand.Transaction = tran;
570             dataAdapter.Fill(dataSet);
571             SetParameters(dataAdapter.SelectCommand, parameters);
572             return dataSet;
573         }
574 
575         #endregion
576 
577         #region ExecuteProDataTable
578         /// <summary>
579         /// 摘要: 调用存储 Procedure
580         /// </summary>
581         /// <param name="procedureName">procedureName</param>
582         /// <returns>DataTable</returns>
583         public DataTable ExecuteProcDataTable(string procedureName)
584         {
585             DataTable dataTable = new DataTable();
586             DbDataAdapter dataAdapter = this.CreateAdapter();
587             dataAdapter.SelectCommand.CommandText = procedureName;
588             dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
589             dataAdapter.Fill(dataTable);
590             return dataTable;
591         }
592         /// <summary>
593         /// 摘要: 调用存储 Procedure
594         /// </summary>
595         /// <param name="procedureName">procedureName</param>
596         /// <param name="parameters">ParameterCollection</param>
597         /// <returns>DataTable</returns>
598         public DataTable ExecuteProcDataTable(string procedureName, ParameterCollection parameters)
599         {
600             DataTable dataTable = new DataTable();
601             DbDataAdapter dataAdapter = this.CreateAdapter();
602             dataAdapter.SelectCommand.CommandText = procedureName;
603             dataAdapter.SelectCommand.Parameters.AddRange(CreateParameterArray(parameters, procedureName));
604             dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
605             dataAdapter.Fill(dataTable);
606             SetParameters(dataAdapter.SelectCommand, parameters);
607             return dataTable;
608         }
609         /// <summary>
610         /// 摘要: 调用存储 Procedure
611         /// </summary>
612         /// <param name="procedureName">procedureName</param>
613         /// <param name="parameters">ParameterCollection</param>
614         /// <param name="tran">DbTransaction</param>
615         /// <returns>DataTable</returns>
616         public DataTable ExecuteProcDataTable(string procedureName, ParameterCollection parameters, DbTransaction tran)
617         {
618             DataTable dataTable = new DataTable();
619             DbDataAdapter dataAdapter = this.CreateAdapter();
620             dataAdapter.SelectCommand.CommandText = procedureName;
621             dataAdapter.SelectCommand.Parameters.AddRange(CreateParameterArray(parameters, procedureName));
622             dataAdapter.SelectCommand.Transaction = tran;
623             dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
624             dataAdapter.Fill(dataTable);
625             SetParameters(dataAdapter.SelectCommand, parameters);
626             return dataTable;
627         }
628 
629         #endregion
630 
631         #region ExecuteProcNonQuery
632         /// <summary>
633         /// 摘要: 调用存储 Procedure
634         /// </summary>
635         /// <param name="procedureName">procedureName</param>
636         /// <returns>int</returns>
637         public int ProcNonQuery(string procedureName)
638         {
639             DbCommand dbCommand = this.CreateCommand();
640             dbCommand.CommandType = CommandType.StoredProcedure;
641             dbCommand.CommandText = procedureName;
642             OpenConnection(dbCommand.Connection);
643             int res = dbCommand.ExecuteNonQuery();
644             CloseConnection(dbCommand.Connection);
645             return res;
646         }
647         /// <summary>
648         /// 摘要: 调用存储 Procedure
649         /// </summary>
650         /// <param name="procedureName">procedureName</param>
651         /// <param name="parameters">ParameterCollection</param>
652         /// <returns>int</returns>
653         public int ProcNonQuery(string procedureName, ParameterCollection parameters)
654         {
655             DbCommand dbCommand = this.CreateCommand();
656             dbCommand.CommandType = CommandType.StoredProcedure;
657             dbCommand.CommandText = procedureName;
658             dbCommand.Parameters.AddRange(CreateParameterArray(parameters, procedureName));
659             OpenConnection(dbCommand.Connection);
660             int res = dbCommand.ExecuteNonQuery();
661             SetParameters(dbCommand, parameters);
662             CloseConnection(dbCommand.Connection);
663             return res;
664         }
665         /// <summary>
666         /// 摘要: 调用存储 Procedure
667         /// </summary>
668         /// <param name="procedureName">procedureName</param>
669         /// <param name="parameters">ParameterCollection</param>
670         /// <param name="tran">DbTransaction</param>
671         /// <returns>int</returns>
672         public int ProcNonQuery(string procedureName, ParameterCollection parameters, DbTransaction tran)
673         {
674             DbCommand dbCommand = this.CreateCommand();
675             dbCommand.CommandType = CommandType.StoredProcedure;
676             dbCommand.CommandText = procedureName;
677             dbCommand.Parameters.AddRange(CreateParameterArray(parameters, procedureName));
678             dbCommand.Transaction = tran;
679             OpenConnection(dbCommand.Connection);
680             int res = dbCommand.ExecuteNonQuery();
681             SetParameters(dbCommand, parameters);
682             CloseConnection(dbCommand.Connection);
683             return res;
684         }
685 
686         #endregion
687 
688         #region ExecuteProcScalar
689         /// <summary>
690         /// 摘要: 调用存储 Procedure
691         /// </summary>
692         /// <param name="procedureName">procedureName</param>
693         /// <returns>object</returns>
694         public object ProcScalar(string procedureName)
695         {
696             DbCommand dbCommand = this.CreateCommand();
697             dbCommand.CommandType = CommandType.StoredProcedure;
698             dbCommand.CommandText = procedureName;
699             OpenConnection(dbCommand.Connection);
700             object res = dbCommand.ExecuteScalar();
701             CloseConnection(dbCommand.Connection);
702             return res;
703         }
704         /// <summary>
705         /// 摘要: 调用存储 Procedure
706         /// </summary>
707         /// <param name="procedureName">procedureName</param>
708         /// <param name="parameters">ParameterCollection</param>
709         /// <returns>object</returns>
710         public object ProcScalar(string procedureName, ParameterCollection parameters)
711         {
712             DbCommand dbCommand = this.CreateCommand();
713             dbCommand.CommandType = CommandType.StoredProcedure;
714             dbCommand.CommandText = procedureName;
715             dbCommand.Parameters.AddRange(CreateParameterArray(parameters, procedureName));
716             OpenConnection(dbCommand.Connection);
717             object res = dbCommand.ExecuteScalar();
718             SetParameters(dbCommand, parameters);
719             CloseConnection(dbCommand.Connection);
720             return res;
721         }
722         /// <summary>
723         /// 摘要: 调用存储 Procedure
724         /// </summary>
725         /// <param name="procedureName">procedureName</param>
726         /// <param name="parameters">ParameterCollection</param>
727         /// <param name="tran">DbTransaction</param>
728         /// <returns>object</returns>
729         public object ProcScalar(string procedureName, ParameterCollection parameters, DbTransaction tran)
730         {
731             DbCommand dbCommand = this.CreateCommand();
732             dbCommand.CommandType = CommandType.StoredProcedure;
733             dbCommand.CommandText = procedureName;
734             dbCommand.Parameters.AddRange(CreateParameterArray(parameters, procedureName));
735             dbCommand.Transaction = tran;
736             OpenConnection(dbCommand.Connection);
737             object res = dbCommand.ExecuteScalar();
738             SetParameters(dbCommand, parameters);
739             CloseConnection(dbCommand.Connection);
740             return res;
741         }
742 
743         #endregion
744 
745         #endregion
746 
747         #endregion
748     }

继承此基类的实现（以SqlServer为例）：

SqlServerClient

 1   /// <summary>
 2     ///  摘要: 根据 providerName 提供 System.Data.SqlClient 操作对象.
 3     /// </summary>
 4     public class SqlServerClient : Database
 5     {
 6         /// <summary>
 7         ///  摘要: 根据 connectionString 提供 System.Data.OracleClient 操作对象.
 8         /// </summary>
 9         /// <param name="connectionString">connectionString</param>
10         public SqlServerClient(string connectionString)
11             : base(connectionString, SqlClientFactory.Instance)
12         {
13 
14         }
15     }

数据库工厂的实现：

DatabaseFactory

 1 /// <summary>
 2     /// 摘要: 提供创建 Database 的工厂类.
 3     /// </summary>
 4     public static class DatabaseFactory
 5     {
 6         /// <summary>
 7         /// 摘要: 创建 Database 用于提供数据库操作类.
 8         /// </summary>
 9         /// <param name="connectionString">connectionString</param>
10         /// <param name="providerName">providerName</param>
11         /// <returns>Database</returns>
12         public static Database CreateDatabase(string connectionString, string providerName)
13         {
14             return (Database)Assembly.Load((typeof(Database).Assembly.FullName)).CreateInstance(providerName, false, System.Reflection.BindingFlags.Default, null, new object[] { connectionString }, null, null);
15         }
16         /// <summary>
17         /// 摘要: 创建 Database 用于提供数据库操作类.
18         /// </summary>
19         /// <param name="connectionName">connectionName</param>
20         /// <returns>Database</returns>
21         public static Database CreateDatabase(string connectionName)
22         {
23             string connectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
24             string providerName = ConfigurationManager.ConnectionStrings[connectionName].ProviderName;
25             return CreateDatabase(connectionString, ProviderFilter(providerName));
26         }
27         /// <summary>
28         /// 摘要: 根据系统默认生成的 ProviderName 映射为自定义的 ProviderName.
29         /// </summary>
30         /// <param name="providerName">providerName</param>
31         /// <returns>Binarysoft.Library.Data.providerName</returns>
32         private static string ProviderFilter(string providerName)
33         {
34             switch (providerName)
35             {
36                 case "System.Data.Odbc": providerName = "Binarysoft.Library.Data.MySql.MySqlClient"; break;
37                 case "System.Data.OleDb": providerName = "Binarysoft.Library.Data.OleDb.OleDbClient"; break;
38                 case "System.Data.SqlClient": providerName = "Binarysoft.Library.Data.SqlServer.SqlServerClient"; break;
39                 case "System.Data.OracleClient": providerName = "Binarysoft.Library.Data.Oracle.OracleClient"; break;
40                 default: break;
41             }
42             return providerName;
43         }
44     }

Binarysoft.Library 加快数据操作的小技巧

参数的缓存：

CachingMechanism && ParameterCache

 1  internal class CachingMechanism
 2     {
 3         private Dictionary<string, DbParameter[]> parameterCache = new Dictionary<string, DbParameter[]>();
 4 
 5         private static string CreateKey(string connectionString, string commandString)
 6         {
 7             return connectionString + ":" + commandString;
 8         }
 9 
10         public void Clear()
11         {
12             this.parameterCache.Clear();
13         }
14 
15         public void AddParameterInCache(string connectionString, string commandString, DbParameter[] parameters)
16         {
17             string key = CreateKey(connectionString, commandString);
18             this.parameterCache.Add(key, parameters);
19         }
20 
21         public DbParameter[] GetParameterFormCache(string connectionString, string commandString)
22         {
23             string key = CreateKey(connectionString, commandString);
24             DbParameter[] parameters = this.parameterCache[key];
25             DbParameter[] parametersClone = new DbParameter[parameters.Length];
26             for (int index = 0; index < parameters.Length; index++)
27             {
28                 parametersClone[index] = (DbParameter)((ICloneable)parameters[index]).Clone();
29             }
30             return parametersClone;
31         }
32 
33         public bool IsParameterSetCached(string connectionString, string commandString)
34         {
35             string key = CreateKey(connectionString, commandString);
36             return this.parameterCache.ContainsKey(key);
37         }
38     }
39  /// <summary>
40     /// 摘要: 用于缓存参数的类.
41     /// </summary>
42     public class ParameterCache
43     {
44         private CachingMechanism cache = new CachingMechanism();
45         /// <summary>
46         /// 摘要: 判断是否缓存参数.
47         /// </summary>
48         /// <param name="connectiongString"></param>
49         /// <param name="commandString"></param>
50         /// <returns></returns>
51         public bool AlreadyCached(string connectiongString, string commandString)
52         {
53             return cache.IsParameterSetCached(connectiongString, commandString);
54         }
55         /// <summary>
56         /// 摘要: 获得参数缓存数组.
57         /// </summary>
58         /// <param name="connectiongString">connectiongString</param>
59         /// <param name="commandString">commandString</param>
60         /// <returns>DbParameter[]</returns>
61         public DbParameter[] GetParametersFromCached(string connectiongString, string commandString)
62         {
63             return cache.GetParameterFormCache(connectiongString, commandString);
64         }
65         /// <summary>
66         /// 摘要: 将参数缓存.
67         /// </summary>
68         /// <param name="connectiongString">connectiongString</param>
69         /// <param name="commandString">commandString</param>
70         /// <param name="parameters">DbParameter[]</param>
71         public void AddParameterInCache(string connectiongString, string commandString, DbParameter[] parameters)
72         {
73             cache.AddParameterInCache(connectiongString, commandString,parameters);
74         }
75         /// <summary>
76         /// 摘要: 清除缓存.
77         /// </summary>
78         protected internal void Clear()
79         {
80             this.cache.Clear();
81         } 
82     }

小结

至此，Binarysoft.Library.Data,的介绍完成。小菜，的数据返回集合，不是实体，而是采用了通用性高的DataTable或DataSet。现在EF横行，的确有它的优点。但是，小菜认为技术无止境，真真正正的自己用的数据，并且高效的代码，才是王道。欢迎大家，来批评指正。

posted @ 2013-04-08 22:06 E=mc² 阅读(1235) 评论(4) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

E=mc²

Asp.net Binarysoft.Library 数据库通用操作层（Binarysoft.Library.Data）

公告