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 }
浙公网安备 33010602011771号