C#数据库操作辅助类

  1  /// <summary>
  2     /// 数据库操作辅助类
  3     /// </summary>
  4     public partial class SQLHelper
  5     {
  6         /// <summary>
  7         /// 执行对象
  8         /// </summary>
  9         private static DbCommand dbCommand { get; set; }
 10 
 11         /// <summary>
 12         /// 事物状态
 13         /// </summary>
 14         public static int TransactionState { get; set; }
 15         /// <summary>
 16         /// 事物对象
 17         /// </summary>
 18         private static DbTransaction dbTransaction { get; set; }
 19 
 20         /// <summary>
 21         /// 连接对象
 22         /// </summary>
 23         private static DbConnection dbConnection { get; set; }
 24 
 25         /// <summary>
 26         /// 抽象数据工厂  
 27         /// </summary>
 28         private static DbProviderFactory providerFactory;
 29 
 30         /// <summary>
 31         /// 数据库连接字符串
 32         /// </summary>
 33         public static string ConnectionString { get; set; }
 34 
 35         /// <summary>
 36         /// ProviderType类型
 37         /// </summary>
 38         public static ProviderType DBProviderType { get; set; }
 39 
 40         /// <summary>
 41         /// 构造函数
 42         /// </summary>
 43         static SQLHelper()
 44         {
 45             //获取数据库连接字符串
 46             ConnectionString = ConfigHelper.ConnectionString;
 47             //获取数据库类型
 48             DBProviderType = ProviderFactory.GetProviderType(ConfigHelper.ProviderTypeName);
 49             //获取providerFactory对象
 50             providerFactory = ProviderFactory.GetDbProviderFactory(DBProviderType);
 51             if (providerFactory == null)
 52             {
 53                 throw new ArgumentException("Can't load DBProviderFactory for given value of ProviderType");
 54             }
 55             dbConnection = providerFactory.CreateConnection();
 56             dbConnection.ConnectionString = ConnectionString;
 57         }
 58 
 59         #region 事务
 60         /// <summary>
 61         /// 开始事务
 62         /// </summary>
 63         public static void BeginTransaction()
 64         {
 65             if (dbConnection != null)
 66             {
 67                 dbConnection.Open();
 68                 dbTransaction = dbConnection.BeginTransaction();
 69                 TransactionState = 1;
 70             }
 71         }
 72 
 73         /// <summary>
 74         /// 提交事务
 75         /// </summary>
 76         public static void Commit()
 77         {
 78             if (dbConnection != null && dbTransaction != null)
 79             {
 80                 dbTransaction.Commit();
 81                 dbTransaction = null;
 82                 dbConnection.Close();
 83                 TransactionState = 0;
 84             }
 85         }
 86 
 87         /// <summary>
 88         /// 回滚事务
 89         /// </summary>
 90         public static void Rollback()
 91         {
 92             if (dbConnection != null && dbTransaction != null)
 93             {
 94                 dbTransaction.Rollback();
 95                 dbTransaction = null;
 96                 dbConnection.Close();
 97                 TransactionState = 0;
 98             }
 99         }
100 
101         #endregion
102 
103         #region ExecuteNonQuery
104 
105         /// <summary>    
106         /// 执行增删改操作，返回受影响的行数。    
107         /// </summary>    
108         /// <param name="commandText">要执行的增删改的SQL语句</param>    
109         /// <param name="parameters">执行增删改语句所需要的参数</param> 
110         /// <returns></returns>   
111         public static int ExecuteNonQuery(string commandText, params DbParameter[] parameters)
112         {
113             return DBExecuteNonQuery(CommandType.Text, commandText, parameters);
114         }
115 
116         /// <summary>
117         /// 执行增删改操作，返回受影响的行数。   
118         /// </summary>
119         /// <param name="commandType">执行的SQL语句的类型</param>
120         /// <param name="commandText">要执行的增删改的SQL语句</param>
121         /// <param name="parameters">执行增删改语句所需要的参数</param> 
122         /// <returns></returns>
123         public static int ExecuteNonQuery(CommandType commandType, string commandText, params DbParameter[] parameters)
124         {
125             return DBExecuteNonQuery(commandType, commandText, parameters);
126         }
127 
128         /// <summary>
129         /// 对数据库执行增删改操作，返回受影响的行数。   
130         /// </summary>
131         /// <param name="commandType">执行的SQL语句的类型</param>
132         /// <param name="commandText">要执行的增删改的SQL语句</param>
133         /// <param name="trans">事物对象</param> 
134         /// <param name="parameters">执行增删改语句所需要的参数</param> 
135         /// <returns></returns>
136         private static int DBExecuteNonQuery(CommandType commandType, string commandText, DbParameter[] parameters)
137         {
138             try
139             {
140                 using (dbCommand = CreateDbCommand(commandType, commandText, parameters))
141                 {
142                     int ExecuteRows = dbCommand.ExecuteNonQuery();
143                     dbCommand.Parameters.Clear();
144                     CloseConnection(dbCommand);
145                     return ExecuteRows;
146                 }
147             }
148             catch (Exception ex)
149             {
150                 new ExceptionHelper("SQLHELPRE_ExecuteNonQuery", ex);
151                 TransactionState = 9;
152                 return 0;
153             }
154         }
155 
156         #endregion
157 
158         #region ExecuteReader
159 
160         /// <summary>    
161         /// 执行查询语句，返回DataReader对象   
162         /// </summary>    
163         /// <param name="commandText">需要执行的查询SQL语句</param> 
164         /// <param name="parameters">执行SQL查询语句所需要的参数</param> 
165         /// <returns></returns>   
166         public static DbDataReader ExecuteReader(string commandText, params DbParameter[] parameters)
167         {
168             return DBExecuteReader(CommandType.Text, commandText, parameters);
169         }
170 
171         /// <summary>    
172         /// 执行查询语句，返回DbDataReader对象   
173         /// </summary>    
174         /// <param name="commandType">执行的SQL语句的类型</param> 
175         /// <param name="commandText">需要执行的查询SQL语句</param>    
176         /// <param name="parameters">执行SQL查询语句所需要的参数</param> 
177         /// <returns></returns>
178         public static DbDataReader ExecuteReader(CommandType commandType, string commandText, params DbParameter[] parameters)
179         {
180             return DBExecuteReader(commandType, commandText, parameters);
181         }
182 
183         /// <summary>    
184         /// 执行查询语句，返回DbDataReader对象   
185         /// </summary>    
186         /// <param name="commandType">执行的SQL语句的类型</param> 
187         /// <param name="commandText">需要执行的查询SQL语句</param>    
188         /// <param name="parameters">执行SQL查询语句所需要的参数</param> 
189         /// <param name="trans">事物对象</param> 
190         /// <returns></returns>
191         private static DbDataReader DBExecuteReader(CommandType commandType, string commandText, DbParameter[] parameters)
192         {
193             DbCommand command = CreateDbCommand(commandType, commandText, parameters);
194             try
195             {  
196                 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
197                 command.Parameters.Clear();
198                 return reader;
199             }
200             catch (Exception ex)
201             {
202                 command.Parameters.Clear();
203                 CloseConnection(command);
204                 new ExceptionHelper("SQLHELPRE_ExecuteReader", ex);
205                 return null;
206             }
207         }
208 
209         #endregion
210 
211         #region ExecuteScalar
212 
213         /// <summary>    
214         /// 执行查询语句，返回查询结果的第一行第一列    
215         /// </summary>    
216         /// <param name="commandText">要执行的SQL查询语句</param>
217         /// <param name="parameters">执行SQL查询语句所需要的参数</param>    
218         /// <returns></returns>   
219         public static object ExecuteScalar(string commandText, params DbParameter[] parameters)
220         {
221             return DBExecuteScalar(CommandType.Text, commandText, parameters);
222         }
223 
224         /// <summary>    
225         /// 执行查询语句，返回查询结果的第一行第一列    
226         /// </summary>   
227         /// <param name="commandType">执行的SQL语句的类型</param> 
228         /// <param name="commandText">要执行的SQL查询语句</param>    
229         /// <param name="parameters">执行SQL查询语句所需要的参数</param>    
230         /// <returns></returns>  
231         public static object ExecuteScalar(CommandType commandType, string commandText, params DbParameter[] parameters)
232         {
233             return DBExecuteScalar(commandType, commandText, parameters);
234         }
235 
236         /// <summary>    
237         /// 执行查询语句，返回查询结果的第一行第一列    
238         /// </summary>   
239         /// <param name="commandType">执行的SQL语句的类型</param> 
240         /// <param name="commandText">要执行的SQL查询语句</param> 
241         /// <param name="trans">事物对象</param>    
242         /// <param name="parameters">执行SQL查询语句所需要的参数</param>    
243         /// <returns></returns> 
244         private static object DBExecuteScalar(CommandType commandType, string commandText, DbParameter[] parameters)
245         {
246             try
247             {
248                 using (DbCommand command = CreateDbCommand(commandType, commandText, parameters))
249                 {
250                     object result = command.ExecuteScalar();
251                     command.Parameters.Clear();
252                     CloseConnection(command);
253                     return result;
254                 }
255             }
256             catch (Exception ex)
257             {
258                 new ExceptionHelper("SQLHELPRE_ExecuteScalar", ex);
259                 return null;
260             }
261         }
262 
263         #endregion
264 
265         #region ExecuteDataTable
266 
267         /// <summary>    
268         /// 执行查询语句，返回查询结果的DataTable    
269         /// </summary>  
270         /// <param name="commandText">要执行的SQL查询语句</param>    
271         /// <param name="parameters">执行SQL查询语句所需要的参数</param> 
272         /// <returns></returns> 
273         public static DataTable ExecuteDataTable(string commandText, params DbParameter[] parameters)
274         {
275             return DBExecuteDataTable(CommandType.Text, commandText, parameters);
276         }
277 
278         /// <summary>    
279         /// 执行查询语句，返回查询结果的DataTable    
280         /// </summary>  
281         /// <param name="commandType">执行的SQL语句的类型</param> 
282         /// <param name="commandText">要执行的SQL查询语句</param>    
283         /// <param name="parameters">执行SQL查询语句所需要的参数</param> 
284         /// <returns></returns> 
285         public static DataTable ExecuteDataTable(CommandType commandType, string commandText, params DbParameter[] parameters)
286         {
287             return DBExecuteDataTable(commandType, commandText, parameters);
288         }
289 
290         /// <summary>    
291         /// 执行查询语句，返回查询结果的DataTable    
292         /// </summary>  
293         /// <param name="commandType">执行的SQL语句的类型</param> 
294         /// <param name="commandText">要执行的SQL查询语句</param>    
295         /// <param name="trans">事物对象</param> 
296         /// <param name="parameters">执行SQL查询语句所需要的参数</param> 
297         /// <returns></returns> 
298         private static DataTable DBExecuteDataTable(CommandType commandType, string commandText, DbParameter[] parameters)
299         {
300             try
301             {
302                 using (DbCommand command = CreateDbCommand(commandType, commandText, parameters))
303                 {
304                     using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
305                     {
306                         adapter.SelectCommand = command;
307                         DataTable datatab = new DataTable();
308                         adapter.Fill(datatab);
309                         command.Parameters.Clear();
310                         CloseConnection(command);
311                         return datatab;
312                     }
313                 }
314             }
315             catch (Exception ex)
316             {
317                 new ExceptionHelper("SQLHELPRE_ExecuteDataTable", ex);
318                 return null;
319             }
320         }
321 
322    
323         #endregion
324 
325         #region CreateDBParameter
326         /// <summary>
327         /// 创建DbParameter参数
328         /// </summary>
329         /// <param name="name">参数名</param>
330         /// <param name="value">参数值</param>
331         /// <returns></returns>
332         public static DbParameter CreateDbParameter(string parameterName, object parameterValue)
333         {
334             return CreateDbParameter(parameterName, ParameterDirection.Input, parameterValue);
335         }
336 
337         /// <summary>
338         /// 创建DbParameter参数
339         /// </summary>
340         /// <param name="name">参数名</param>
341         /// <param name="parameterDirection">参数类型</param>
342         /// <param name="value">参数值</param>
343         /// <returns></returns>
344         public static DbParameter CreateDbParameter(string parameterName, ParameterDirection parameterDirection, object parameterValue)
345         {
346             DbParameter parameter = providerFactory.CreateParameter();
347             parameter.ParameterName = parameterName;
348             parameter.Value = parameterValue;
349             parameter.Direction = parameterDirection;
350             return parameter;
351         }
352 
353         #endregion
354 
355         /// <summary>
356         /// 关闭连接
357         /// </summary>
358         private static void CloseConnection(DbCommand command = null)
359         {
360             if (command != null)
361             {
362                 if (dbTransaction == null)
363                 {
364                     command.Connection.Close();
365                 }
366             }
367         }
368 
369         /// <summary> 
370         /// 创建一个DbCommand对象 
371         /// </summary> 
372         /// <param name="sql">要执行的查询语句</param>    
373         /// <param name="parameters">执行SQL查询语句所需要的参数</param> 
374         /// <param name="commandType">执行的SQL语句的类型</param> 
375         /// <returns></returns> 
376         private static DbCommand CreateDbCommand(CommandType commandType, string commandText, params     DbParameter[] parameters)
377         {
378             DbCommand command = dbConnection.CreateCommand();
379             if (dbConnection == null)
380             {
381                 dbConnection = providerFactory.CreateConnection();
382                 dbConnection.ConnectionString = ConnectionString;
383             }
384             command.Connection = dbConnection;
385             switch (dbConnection.State)
386             {
387                 case ConnectionState.Closed:
388                     dbConnection.Open();
389                     break;
390                 case ConnectionState.Broken:
391                     dbConnection.Close();
392                     dbConnection.Open();
393                     break;
394             }
395             command.CommandText = commandText;
396             command.CommandType = commandType;
397             if (dbTransaction != null)
398             {
399                 command.Transaction = dbTransaction;
400             }
401             if (parameters != null)
402             {
403                 command.Parameters.AddRange(parameters);
404             }
405             return command;
406         }
407 
408     }