![]()
class="code_img_closed" src="/Upload/Images/2017062105/0015B68B3C38AA5B.gif" alt="">
1 /// <summary>
2 /// 数据库的通用访问代码
3 /// 此类为抽象类，
4 /// 不允许实例化，在应用时直接调用即可
5 /// </summary>
6 public abstract class SqlHelper
7 {
8 /// <summary>
9 /// 数据库连接字符串
10 /// </summary>
11
12 public static string connectionString = "";
13
14
15 // Hashtable to store cached parameters
16 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
17
18 #region ExecteNonQuery操作方法集合
19 /// <summary>
20 ///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。
21 /// 使用参数数组形式提供参数列表
22 /// </summary>
23 /// <param name="connectionString">一个有效的数据库连接字符串</param>
24 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
25 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
26 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
27 /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
28 public static bool ExecteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
29 {
30 try
31 {
32 SqlCommand cmd = new SqlCommand();
33 using (SqlConnection conn = new SqlConnection(connectionString))
34 {
35 //通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中
36 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
37 int val = cmd.ExecuteNonQuery();
38 //清空SqlCommand中的参数列表
39 cmd.Parameters.Clear();
40 return val > 0 ? true : false;
41 }
42 }
43 catch
44 {
45 return false;
46 }
47
48 }
49
50 /// <summary>
51 ///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。
52 /// 使用参数数组形式提供参数列表
53 /// </summary>
54 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
55 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
56 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
57 /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
58 public static bool ExecteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
59 {
60 try
61 {
62 return ExecteNonQuery(connectionString, cmdType, cmdText, commandParameters);
63 }
64 catch
65 {
66 return false;
67 }
68
69 }
70
71 /// <summary>
72 ///存储过程专用
73 /// </summary>
74 /// <param name="cmdText">存储过程的名字</param>
75 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
76 /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
77 public static bool ExecteNonQueryProducts(string cmdText, params SqlParameter[] commandParameters)
78 {
79 try
80 {
81 return ExecteNonQuery(CommandType.StoredProcedure, cmdText, commandParameters);
82
83 }
84 catch
85 {
86 return false;
87 }
88 }
89
90 /// <summary>
91 ///Sql语句专用
92 /// </summary>
93 /// <param name="cmdText">T_Sql语句</param>
94 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
95 /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
96 public static bool ExecteNonQueryText(string cmdText, params SqlParameter[] commandParameters)
97 {
98 try
99 {
100 return ExecteNonQuery(CommandType.Text, cmdText, commandParameters);
101 }
102 catch
103 {
104 return false;
105 }
106
107 }
108
109 #endregion
110
111
112 #region GetTable操作方法集合
113
114 /// <summary>
115 /// 执行一条返回结果集的SqlCommand，通过一个已经存在的数据库连接
116 /// 使用参数数组提供参数
117 /// </summary>
118 /// <param name="connecttionString">一个现有的数据库连接</param>
119 /// <param name="cmdTye">SqlCommand命令类型</param>
120 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
121 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
122 /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>
123 public static DataTableCollection GetTable(string connecttionString, CommandType cmdTye, string cmdText, SqlParameter[] commandParameters)
124 {
125 try
126 {
127 SqlCommand cmd = new SqlCommand();
128 DataSet ds = new DataSet();
129 using (SqlConnection conn = new SqlConnection(connecttionString))
130 {
131 PrepareCommand(cmd, conn, null, cmdTye, cmdText, commandParameters);
132 SqlDataAdapter adapter = new SqlDataAdapter();
133 adapter.SelectCommand = cmd;
134 adapter.Fill(ds);
135 }
136 DataTableCollection table = ds.Tables;
137 return table;
138 }
139 catch (Exception ex)
140 {
141 return null;
142 }
143
144 }
145
146 /// <summary>
147 /// 执行一条返回结果集的SqlCommand，通过一个已经存在的数据库连接
148 /// 使用参数数组提供参数
149 /// </summary>
150 /// <param name="cmdTye">SqlCommand命令类型</param>
151 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
152 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
153 /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>
154 public static DataTableCollection GetTable(CommandType cmdTye, string cmdText, SqlParameter[] commandParameters)
155 {
156 try
157 {
158 return GetTable(SqlHelper.connectionString, cmdTye, cmdText, commandParameters);
159 }
160 catch
161 {
162 return null;
163
164 }
165
166 }
167
168 /// <summary>
169 /// 存储过程专用
170 /// </summary>
171 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
172 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
173 /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>
174 public static DataTableCollection GetTableProducts(string cmdText, SqlParameter[] commandParameters)
175 {
176 try
177 {
178 return GetTable(CommandType.StoredProcedure, cmdText, commandParameters);
179 }
180 catch
181 {
182 return null;
183 }
184 }
185
186 /// <summary>
187 /// Sql语句专用
188 /// </summary>
189 /// <param name="cmdText"> T-SQL 语句</param>
190 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
191 /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>
192 public static DataTableCollection GetTableText(string cmdText, SqlParameter[] commandParameters)
193 {
194 try
195 {
196 return GetTable(CommandType.Text, cmdText, commandParameters);
197 }
198 catch
199 {
200 System.Windows.Forms.MessageBox.Show("查询后台出现错误，请重试！");
201 return null;
202 }
203 }
204
205 #endregion
206
207
208 #region 检查是否存在
209 /// <summary>
210 /// 检查是否存在 存在:true
211 /// </summary>
212 /// <param name="strSql">Sql语句</param>
213 /// <param name="cmdParms">参数</param>
214 /// <returns>bool结果</returns>
215 public static bool Exists(string strSql, params SqlParameter[] cmdParms)
216 {
217 try
218 {
219 int cmdresult = Convert.ToInt32(ExecuteScalar(connectionString, CommandType.Text, strSql, cmdParms));
220 if (cmdresult == 0)
221 {
222 return false;
223 }
224 else
225 {
226 return true;
227 }
228 }
229 catch (Exception ex)
230 {
231 return false;
232 }
233
234 }
235 #endregion
236
237
238 #region 各方法SqlParameter参数处理
239 /// <summary>
240 /// 为执行命令准备参数
241 /// </summary>
242 /// <param name="cmd">SqlCommand 命令</param>
243 /// <param name="conn">已经存在的数据库连接</param>
244 /// <param name="trans">数据库事物处理</param>
245 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
246 /// <param name="cmdText">Command text，T-SQL语句 例如 Select * from Products</param>
247 /// <param name="cmdParms">返回带参数的命令</param>
248 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
249 {
250 try
251 {
252 //判断数据库连接状态
253 if (conn.State != ConnectionState.Open)
254 conn.Open();
255 cmd.Connection = conn;
256 cmd.CommandText = cmdText;
257 //判断是否需要事物处理
258 if (trans != null)
259 cmd.Transaction = trans;
260 cmd.CommandType = cmdType;
261 if (cmdParms != null)
262 {
263 foreach (SqlParameter parm in cmdParms)
264 cmd.Parameters.Add(parm);
265 }
266 }
267 catch(Exception ex)
268 {
269 MessageBox.Show("连接服务器发生错误,请检查！", "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
270 System.Environment.Exit(0);
271 }
272
273 }
274
275 #endregion
276
277
278 #region 其他查询方法集合
279
280 /// <summary>
281 /// 执行命令，返回一个在连接字符串中指定的数据库结果集
282 /// 使用所提供的参数。
283 /// </summary>
284 /// <param name="connectionString">一个有效的数据库连接字符串</param>
285 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
286 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
287 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
288 /// <returns>A SqlDataReader containing the results</returns>
289 public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
290 {
291 SqlCommand cmd = new SqlCommand();
292 SqlConnection conn = new SqlConnection(connectionString);
293 // we use a try/catch here because if the method throws an exception we want to
294 // close the connection throw code, because no datareader will exist, hence the
295 // commandBehaviour.CloseConnection will not work
296 try
297 {
298 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
299 SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
300 cmd.Parameters.Clear();
301 return rdr;
302 }
303 catch
304 {
305 conn.Close();
306 throw;
307 }
308 }
309
310 #region//ExecuteDataSet方法
311
312 /// <summary>
313 /// return a dataset
314 /// </summary>
315 /// <param name="connectionString">一个有效的数据库连接字符串</param>
316 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
317 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
318 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
319 /// <returns>return a dataset</returns>
320 public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
321 {
322 SqlCommand cmd = new SqlCommand();
323 try
324 {
325 using (SqlConnection conn = new SqlConnection(connectionString))
326 {
327 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
328 SqlDataAdapter da = new SqlDataAdapter();
329 DataSet ds = new DataSet();
330 da.SelectCommand = cmd;
331 da.Fill(ds);
332 return ds;
333 }
334 }
335 catch
336 {
337 throw;
338 }
339 }
340
341 /// <summary>
342 /// 返回一个DataSet
343 /// </summary>
344 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
345 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
346 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
347 /// <returns>return a dataset</returns>
348 public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
349 {
350 return ExecuteDataSet(connectionString, cmdType, cmdText, commandParameters);
351 }
352
353 /// <summary>
354 /// 返回一个DataSet
355 /// </summary>
356 /// <param name="cmdText">存储过程的名字</param>
357 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
358 /// <returns>return a dataset</returns>
359 public static DataSet ExecuteDataSetProducts(string cmdText, params SqlParameter[] commandParameters)
360 {
361 return ExecuteDataSet(connectionString, CommandType.StoredProcedure, cmdText, commandParameters);
362 }
363
364 /// <summary>
365 /// 返回一个DataSet
366 /// </summary>
367 /// <param name="cmdText">T-SQL 语句</param>
368 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
369 /// <returns>return a dataset</returns>
370 public static DataSet ExecuteDataSetText(string cmdText, params SqlParameter[] commandParameters)
371 {
372 return ExecuteDataSet(connectionString, CommandType.Text, cmdText, commandParameters);
373 }
374
375 public static DataView ExecuteDataSet(string connectionString, string sortExpression, string direction, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
376 {
377 SqlCommand cmd = new SqlCommand();
378 try
379 {
380 using (SqlConnection conn = new SqlConnection(connectionString))
381 {
382 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
383 SqlDataAdapter da = new SqlDataAdapter();
384 DataSet ds = new DataSet();
385 da.SelectCommand = cmd;
386 da.Fill(ds);
387 DataView dv = ds.Tables[0].DefaultView;
388 dv.Sort = sortExpression + " " + direction;
389 return dv;
390 }
391 }
392 catch
393 {
394
395 throw;
396 }
397 }
398 #endregion
399
400 #region // ExecuteScalar方法
401
402 /// <summary>
403 /// 返回第一行的第一列
404 /// </summary>
405 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
406 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
407 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
408 /// <returns>返回一个对象</returns>
409 public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
410 {
411 return ExecuteScalar(SqlHelper.connectionString, cmdType, cmdText, commandParameters);
412 }
413
414 /// <summary>
415 /// 返回第一行的第一列存储过程专用
416 /// </summary>
417 /// <param name="cmdText">存储过程的名字</param>
418 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
419 /// <returns>返回一个对象</returns>
420 public static object ExecuteScalarProducts(string cmdText, params SqlParameter[] commandParameters)
421 {
422 return ExecuteScalar(SqlHelper.connectionString, CommandType.StoredProcedure, cmdText, commandParameters);
423 }
424
425 /// <summary>
426 /// 返回第一行的第一列Sql语句专用
427 /// </summary>
428 /// <param name="cmdText">者 T-SQL 语句</param>
429 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
430 /// <returns>返回一个对象</returns>
431 public static object ExecuteScalarText(string cmdText, params SqlParameter[] commandParameters)
432 {
433 return ExecuteScalar(SqlHelper.connectionString, CommandType.Text, cmdText, commandParameters);
434 }
435
436 /// <summary>
437 /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
438 /// using the provided parameters.
439 /// </summary>
440 /// <remarks>
441 /// e.g.:
442 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
443 /// </remarks>
444 /// <param name="connectionString">一个有效的数据库连接字符串</param>
445 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
446 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
447 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
448 /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
449 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
450 {
451 SqlCommand cmd = new SqlCommand();
452
453 using (SqlConnection connection = new SqlConnection(connectionString))
454 {
455 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
456 object val = cmd.ExecuteScalar();
457 cmd.Parameters.Clear();
458 return val;
459 }
460 }
461
462 /// <summary>
463 /// Execute a SqlCommand that returns the first column of the first record against an existing database connection
464 /// using the provided parameters.
465 /// </summary>
466 /// <remarks>
467 /// e.g.:
468 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
469 /// </remarks>
470 /// <param name="connectionString">一个有效的数据库连接字符串</param>
471 /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
472 /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
473 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
474 /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
475 public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
476 {
477 SqlCommand cmd = new SqlCommand();
478 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
479 object val = cmd.ExecuteScalar();
480 cmd.Parameters.Clear();
481 return val;
482 }
483
484 #endregion
485
486 /// <summary>
487 /// add parameter array to the cache
488 /// </summary>
489 /// <param name="cacheKey">Key to the parameter cache</param>
490 /// <param name="cmdParms">an array of SqlParamters to be cached</param>
491 public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
492 {
493 parmCache[cacheKey] = commandParameters;
494 }
495
496 #endregion
497
498
499
500 }
logs_code_collapse">SQLHelper
1 using System;
2 using System.Collections;
3 using System.Collections.Specialized;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Configuration;
7 using System.Data.Common;
8 using System.Collections.Generic;
9 namespace Maticsoft.DBUtility
10 {
11 /// <summary>
12 /// 数据访问抽象基础类
13 /// </summary>
14 public abstract class DbHelperSQL
15 {
16 //数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现.
17 public static string connectionString = "连接字符串";
18 public DbHelperSQL()
19 {
20 }
21
22 #region 公用方法
23 /// <summary>
24 /// 判断是否存在某表的某个字段
25 /// </summary>
26 /// <param name="tableName">表名称</param>
27 /// <param name="columnName">列名称</param>
28 /// <returns>是否存在</returns>
29 public static bool ColumnExists(string tableName, string columnName)
30 {
31 string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
32 object res = GetSingle(sql);
33 if (res == null)
34 {
35 return false;
36 }
37 return Convert.ToInt32(res) > 0;
38 }
39 public static int GetMaxID(string FieldName, string TableName)
40 {
41 string strsql = "select max(" + FieldName + ")+1 from " + TableName;
42 object obj = GetSingle(strsql);
43 if (obj == null)
44 {
45 return 1;
46 }
47 else
48 {
49 return int.Parse(obj.ToString());
50 }
51 }
52 public static bool Exists(string strSql)
53 {
54 object obj = GetSingle(strSql);
55 int cmdresult;
56 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
57 {
58 cmdresult = 0;
59 }
60 else
61 {
62 cmdresult = int.Parse(obj.ToString());
63 }
64 if (cmdresult == 0)
65 {
66 return false;
67 }
68 else
69 {
70 return true;
71 }
72 }
73 /// <summary>
74 /// 表是否存在
75 /// </summary>
76 /// <param name="TableName"></param>
77 /// <returns></returns>
78 public static bool TabExists(string TableName)
79 {
80 string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
81 //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
82 object obj = GetSingle(strsql);
83 int cmdresult;
84 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
85 {
86 cmdresult = 0;
87 }
88 else
89 {
90 cmdresult = int.Parse(obj.ToString());
91 }
92 if (cmdresult == 0)
93 {
94 return false;
95 }
96 else
97 {
98 return true;
99 }
100 }
101 public static bool Exists(string strSql, params SqlParameter[] cmdParms)
102 {
103 object obj = GetSingle(strSql, cmdParms);
104 int cmdresult;
105 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
106 {
107 cmdresult = 0;
108 }
109 else
110 {
111 cmdresult = int.Parse(obj.ToString());
112 }
113 if (cmdresult == 0)
114 {
115 return false;
116 }
117 else
118 {
119 return true;
120 }
121 }
122 #endregion
123
124 #region 执行简单SQL语句
125
126 /// <summary>
127 /// 执行SQL语句，返回影响的记录数
128 /// </summary>
129 /// <param name="SQLString">SQL语句</param>
130 /// <returns>影响的记录数</returns>
131 public static int ExecuteSql(string SQLString)
132 {
133 using (SqlConnection connection = new SqlConnection(connectionString))
134 {
135 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
136 {
137 try
138 {
139 connection.Open();
140 int rows = cmd.ExecuteNonQuery();
141 return rows;
142 }
143 catch (System.Data.SqlClient.SqlException e)
144 {
145 connection.Close();
146 throw e;
147 }
148 }
149 }
150 }
151
152 public static int ExecuteSqlByTime(string SQLString, int Times)
153 {
154 using (SqlConnection connection = new SqlConnection(connectionString))
155 {
156 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
157 {
158 try
159 {
160 connection.Open();
161 cmd.CommandTimeout = Times;
162 int rows = cmd.ExecuteNonQuery();
163 return rows;
164 }
165 catch (System.Data.SqlClient.SqlException e)
166 {
167 connection.Close();
168 throw e;
169 }
170 }
171 }
172 }
173
174 /// <summary>
175 /// 执行Sql和Oracle滴混合事务
176 /// </summary>
177 /// <param name="list">SQL命令行列表</param>
178 /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
179 /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
180 public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
181 {
182 using (SqlConnection conn = new SqlConnection(connectionString))
183 {
184 conn.Open();
185 SqlCommand cmd = new SqlCommand();
186 cmd.Connection = conn;
187 SqlTransaction tx = conn.BeginTransaction();
188 cmd.Transaction = tx;
189 try
190 {
191 foreach (CommandInfo myDE in list)
192 {
193 string cmdText = myDE.CommandText;
194 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
195 PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
196 if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
197 {
198 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
199 {
200 tx.Rollback();
201 throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");
202 //return 0;
203 }
204
205 object obj = cmd.ExecuteScalar();
206 bool isHave = false;
207 if (obj == null && obj == DBNull.Value)
208 {
209 isHave = false;
210 }
211 isHave = Convert.ToInt32(obj) > 0;
212 if (isHave)
213 {
214 //引发事件
215 myDE.OnSolicitationEvent();
216 }
217 }
218 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
219 {
220 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
221 {
222 tx.Rollback();
223 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
224 //return 0;
225 }
226
227 object obj = cmd.ExecuteScalar();
228 bool isHave = false;
229 if (obj == null && obj == DBNull.Value)
230 {
231 isHave = false;
232 }
233 isHave = Convert.ToInt32(obj) > 0;
234
235 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
236 {
237 tx.Rollback();
238 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
239 //return 0;
240 }
241 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
242 {
243 tx.Rollback();
244 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
245 //return 0;
246 }
247 continue;
248 }
249 int val = cmd.ExecuteNonQuery();
250 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
251 {
252 tx.Rollback();
253 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
254 //return 0;
255 }
256 cmd.Parameters.Clear();
257 }
258 string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
259 bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
260 if (!res)
261 {
262 tx.Rollback();
263 throw new Exception("Oracle执行失败");
264 // return -1;
265 }
266 tx.Commit();
267 return 1;
268 }
269 catch (System.Data.SqlClient.SqlException e)
270 {
271 tx.Rollback();
272 throw e;
273 }
274 catch (Exception e)
275 {
276 tx.Rollback();
277 throw e;
278 }
279 }
280 }
281 /// <summary>
282 /// 执行多条SQL语句，实现数据库事务。
283 /// </summary>
284 /// <param name="SQLStringList">多条SQL语句</param>
285 public static int ExecuteSqlTran(List<String> SQLStringList)
286 {
287 using (SqlConnection conn = new SqlConnection(connectionString))
288 {
289 conn.Open();
290 SqlCommand cmd = new SqlCommand();
291 cmd.Connection = conn;
292 SqlTransaction tx = conn.BeginTransaction();
293 cmd.Transaction = tx;
294 try
295 {
296 int count = 0;
297 for (int n = 0; n < SQLStringList.Count; n++)
298 {
299 string strsql = SQLStringList[n];
300 if (strsql.Trim().Length > 1)
301 {
302 cmd.CommandText = strsql;
303 count += cmd.ExecuteNonQuery();
304 }
305 }
306 tx.Commit();
307 return count;
308 }
309 catch
310 {
311 tx.Rollback();
312 return 0;
313 }
314 }
315 }
316 /// <summary>
317 /// 执行带一个存储过程参数的的SQL语句。
318 /// </summary>
319 /// <param name="SQLString">SQL语句</param>
320 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
321 /// <returns>影响的记录数</returns>
322 public static int ExecuteSql(string SQLString, string content)
323 {
324 using (SqlConnection connection = new SqlConnection(connectionString))
325 {
326 SqlCommand cmd = new SqlCommand(SQLString, connection);
327 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
328 myParameter.Value = content;
329 cmd.Parameters.Add(myParameter);
330 try
331 {
332 connection.Open();
333 int rows = cmd.ExecuteNonQuery();
334 return rows;
335 }
336 catch (System.Data.SqlClient.SqlException e)
337 {
338 throw e;
339 }
340 finally
341 {
342 cmd.Dispose();
343 connection.Close();
344 }
345 }
346 }
347 /// <summary>
348 /// 执行带一个存储过程参数的的SQL语句。
349 /// </summary>
350 /// <param name="SQLString">SQL语句</param>
351 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
352 /// <returns>影响的记录数</returns>
353 public static object ExecuteSqlGet(string SQLString, string content)
354 {
355 using (SqlConnection connection = new SqlConnection(connectionString))
356 {
357 SqlCommand cmd = new SqlCommand(SQLString, connection);
358 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
359 myParameter.Value = content;
360 cmd.Parameters.Add(myParameter);
361 try
362 {
363 connection.Open();
364 object obj = cmd.ExecuteScalar();
365 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
366 {
367 return null;
368 }
369 else
370 {
371 return obj;
372 }
373 }
374 catch (System.Data.SqlClient.SqlException e)
375 {
376 throw e;
377 }
378 finally
379 {
380 cmd.Dispose();
381 connection.Close();
382 }
383 }
384 }
385 /// <summary>
386 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
387 /// </summary>
388 /// <param name="strSQL">SQL语句</param>
389 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
390 /// <returns>影响的记录数</returns>
391 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
392 {
393 using (SqlConnection connection = new SqlConnection(connectionString))
394 {
395 SqlCommand cmd = new SqlCommand(strSQL, connection);
396 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
397 myParameter.Value = fs;
398 cmd.Parameters.Add(myParameter);
399 try
400 {
401 connection.Open();
402 int rows = cmd.ExecuteNonQuery();
403 return rows;
404 }
405 catch (System.Data.SqlClient.SqlException e)
406 {
407 throw e;
408 }
409 finally
410 {
411 cmd.Dispose();
412 connection.Close();
413 }
414 }
415 }
416
417 /// <summary>
418 /// 执行一条计算查询结果语句，返回查询结果（object）。
419 /// </summary>
420 /// <param name="SQLString">计算查询结果语句</param>
421 /// <returns>查询结果（object）</returns>
422 public static object GetSingle(string SQLString)
423 {
424 using (SqlConnection connection = new SqlConnection(connectionString))
425 {
426 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
427 {
428 try
429 {
430 connection.Open();
431 object obj = cmd.ExecuteScalar();
432 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
433 {
434 return null;
435 }
436 else
437 {
438 return obj;
439 }
440 }
441 catch (System.Data.SqlClient.SqlException e)
442 {
443 connection.Close();
444 throw e;
445 }
446 }
447 }
448 }
449 public static object GetSingle(string SQLString, int Times)
450 {
451 using (SqlConnection connection = new SqlConnection(connectionString))
452 {
453 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
454 {
455 try
456 {
457 connection.Open();
458 cmd.CommandTimeout = Times;
459 object obj = cmd.ExecuteScalar();
460 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
461 {
462 return null;
463 }
464 else
465 {
466 return obj;
467 }
468 }
469 catch (System.Data.SqlClient.SqlException e)
470 {
471 connection.Close();
472 throw e;
473 }
474 }
475 }
476 }
477 /// <summary>
478 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
479 /// </summary>
480 /// <param name="strSQL">查询语句</param>
481 /// <returns>SqlDataReader</returns>
482 public static SqlDataReader ExecuteReader(string strSQL)
483 {
484 SqlConnection connection = new SqlConnection(connectionString);
485 SqlCommand cmd = new SqlCommand(strSQL, connection);
486 try
487 {
488 connection.Open();
489 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
490 return myReader;
491 }
492 catch (System.Data.SqlClient.SqlException e)
493 {
494 throw e;
495 }
496
497 }
498 /// <summary>
499 /// 执行查询语句，返回DataSet
500 /// </summary>
501 /// <param name="SQLString">查询语句</param>
502 /// <returns>DataSet</returns>
503 public static DataSet Query(string SQLString)
504 {
505 using (SqlConnection connection = new SqlConnection(connectionString))
506 {
507 DataSet ds = new DataSet();
508 try
509 {
510 connection.Open();
511 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
512 command.Fill(ds, "ds");
513 }
514 catch (System.Data.SqlClient.SqlException ex)
515 {
516 throw new Exception(ex.Message);
517 }
518 return ds;
519 }
520 }
521 public static DataSet Query(string SQLString, int Times)
522 {
523 using (SqlConnection connection = new SqlConnection(connectionString))
524 {
525 DataSet ds = new DataSet();
526 try
527 {
528 connection.Open();
529 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
530 command.SelectCommand.CommandTimeout = Times;
531 command.Fill(ds, "ds");
532 }
533 catch (System.Data.SqlClient.SqlException ex)
534 {
535 throw new Exception(ex.Message);
536 }
537 return ds;
538 }
539 }
540
541
542
543 #endregion
544
545 #region 执行带参数的SQL语句
546
547 /// <summary>
548 /// 执行SQL语句，返回影响的记录数
549 /// </summary>
550 /// <param name="SQLString">SQL语句</param>
551 /// <returns>影响的记录数</returns>
552 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
553 {
554 using (SqlConnection connection = new SqlConnection(connectionString))
555 {
556 using (SqlCommand cmd = new SqlCommand())
557 {
558 try
559 {
560 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
561 int rows = cmd.ExecuteNonQuery();
562 cmd.Parameters.Clear();
563 return rows;
564 }
565 catch (System.Data.SqlClient.SqlException e)
566 {
567 throw e;
568 }
569 }
570 }
571 }
572
573
574 /// <summary>
575 /// 执行多条SQL语句，实现数据库事务。
576 /// </summary>
577 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
578 public static void ExecuteSqlTran(Hashtable SQLStringList)
579 {
580 using (SqlConnection conn = new SqlConnection(connectionString))
581 {
582 conn.Open();
583 using (SqlTransaction trans = conn.BeginTransaction())
584 {
585 SqlCommand cmd = new SqlCommand();
586 try
587 {
588 //循环
589 foreach (DictionaryEntry myDE in SQLStringList)
590 {
591 string cmdText = myDE.Key.ToString();
592 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
593 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
594 int val = cmd.ExecuteNonQuery();
595 cmd.Parameters.Clear();
596 }
597 trans.Commit();
598 }
599 catch
600 {
601 trans.Rollback();
602 throw;
603 }
604 }
605 }
606 }
607 /// <summary>
608 /// 执行多条SQL语句，实现数据库事务。
609 /// </summary>
610 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
611 public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
612 {
613 using (SqlConnection conn = new SqlConnection(connectionString))
614 {
615 conn.Open();
616 using (SqlTransaction trans = conn.BeginTransaction())
617 {
618 SqlCommand cmd = new SqlCommand();
619 try
620 { int count = 0;
621 //循环
622 foreach (CommandInfo myDE in cmdList)
623 {
624 string cmdText = myDE.CommandText;
625 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
626 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
627
628 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
629 {
630 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
631 {
632 trans.Rollback();
633 return 0;
634 }
635
636 object obj = cmd.ExecuteScalar();
637 bool isHave = false;
638 if (obj == null && obj == DBNull.Value)
639 {
640 isHave = false;
641 }
642 isHave = Convert.ToInt32(obj) > 0;
643
644 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
645 {
646 trans.Rollback();
647 return 0;
648 }
649 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
650 {
651 trans.Rollback();
652 return 0;
653 }
654 continue;
655 }
656 int val = cmd.ExecuteNonQuery();
657 count += val;
658 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
659 {
660 trans.Rollback();
661 return 0;
662 }
663 cmd.Parameters.Clear();
664 }
665 trans.Commit();
666 return count;
667 }
668 catch
669 {
670 trans.Rollback();
671 throw;
672 }
673 }
674 }
675 }
676 /// <summary>
677 /// 执行多条SQL语句，实现数据库事务。
678 /// </summary>
679 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
680 public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
681 {
682 using (SqlConnection conn = new SqlConnection(connectionString))
683 {
684 conn.Open();
685 using (SqlTransaction trans = conn.BeginTransaction())
686 {
687 SqlCommand cmd = new SqlCommand();
688 try
689 {
690 int indentity = 0;
691 //循环
692 foreach (CommandInfo myDE in SQLStringList)
693 {
694 string cmdText = myDE.CommandText;
695 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
696 foreach (SqlParameter q in cmdParms)
697 {
698 if (q.Direction == ParameterDirection.InputOutput)
699 {
700 q.Value = indentity;
701 }
702 }
703 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
704 int val = cmd.ExecuteNonQuery();
705 foreach (SqlParameter q in cmdParms)
706 {
707 if (q.Direction == ParameterDirection.Output)
708 {
709 indentity = Convert.ToInt32(q.Value);
710 }
711 }
712 cmd.Parameters.Clear();
713 }
714 trans.Commit();
715 }
716 catch
717 {
718 trans.Rollback();
719 throw;
720 }
721 }
722 }
723 }
724 /// <summary>
725 /// 执行多条SQL语句，实现数据库事务。
726 /// </summary>
727 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
728 public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
729 {
730 using (SqlConnection conn = new SqlConnection(connectionString))
731 {
732 conn.Open();
733 using (SqlTransaction trans = conn.BeginTransaction())
734 {
735 SqlCommand cmd = new SqlCommand();
736 try
737 {
738 int indentity = 0;
739 //循环
740 foreach (DictionaryEntry myDE in SQLStringList)
741 {
742 string cmdText = myDE.Key.ToString();
743 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
744 foreach (SqlParameter q in cmdParms)
745 {
746 if (q.Direction == ParameterDirection.InputOutput)
747 {
748 q.Value = indentity;
749 }
750 }
751 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
752 int val = cmd.ExecuteNonQuery();
753 foreach (SqlParameter q in cmdParms)
754 {
755 if (q.Direction == ParameterDirection.Output)
756 {
757 indentity = Convert.ToInt32(q.Value);
758 }
759 }
760 cmd.Parameters.Clear();
761 }
762 trans.Commit();
763 }
764 catch
765 {
766 trans.Rollback();
767 throw;
768 }
769 }
770 }
771 }
772 /// <summary>
773 /// 执行一条计算查询结果语句，返回查询结果（object）。
774 /// </summary>
775 /// <param name="SQLString">计算查询结果语句</param>
776 /// <returns>查询结果（object）</returns>
777 public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
778 {
779 using (SqlConnection connection = new SqlConnection(connectionString))
780 {
781 using (SqlCommand cmd = new SqlCommand())
782 {
783 try
784 {
785 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
786 object obj = cmd.ExecuteScalar();
787 cmd.Parameters.Clear();
788 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
789 {
790 return null;
791 }
792 else
793 {
794 return obj;
795 }
796 }
797 catch (System.Data.SqlClient.SqlException e)
798 {
799 throw e;
800 }
801 }
802 }
803 }
804
805 /// <summary>
806 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
807 /// </summary>
808 /// <param name="strSQL">查询语句</param>
809 /// <returns>SqlDataReader</returns>
810 public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
811 {
812 SqlConnection connection = new SqlConnection(connectionString);
813 SqlCommand cmd = new SqlCommand();
814 try
815 {
816 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
817 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
818 cmd.Parameters.Clear();
819 return myReader;
820 }
821 catch (System.Data.SqlClient.SqlException e)
822 {
823 throw e;
824 }
825 // finally
826 // {
827 // cmd.Dispose();
828 // connection.Close();
829 // }
830
831 }
832
833 /// <summary>
834 /// 执行查询语句，返回DataSet
835 /// </summary>
836 /// <param name="SQLString">查询语句</param>
837 /// <returns>DataSet</returns>
838 public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
839 {
840 using (SqlConnection connection = new SqlConnection(connectionString))
841 {
842 SqlCommand cmd = new SqlCommand();
843 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
844 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
845 {
846 DataSet ds = new DataSet();
847 try
848 {
849 da.Fill(ds, "ds");
850 cmd.Parameters.Clear();
851 }
852 catch (System.Data.SqlClient.SqlException ex)
853 {
854 throw new Exception(ex.Message);
855 }
856 return ds;
857 }
858 }
859 }
860
861
862 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
863 {
864 if (conn.State != ConnectionState.Open)
865 conn.Open();
866 cmd.Connection = conn;
867 cmd.CommandText = cmdText;
868 if (trans != null)
869 cmd.Transaction = trans;
870 cmd.CommandType = CommandType.Text;//cmdType;
871 if (cmdParms != null)
872 {
873
874
875 foreach (SqlParameter parameter in cmdParms)
876 {
877 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
878 (parameter.Value == null))
879 {
880 parameter.Value = DBNull.Value;
881 }
882 cmd.Parameters.Add(parameter);
883 }
884 }
885 }
886
887 #endregion
888
889 #region 存储过程操作
890
891 /// <summary>
892 /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
893 /// </summary>
894 /// <param name="storedProcName">存储过程名</param>
895 /// <param name="parameters">存储过程参数</param>
896 /// <returns>SqlDataReader</returns>
897 public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
898 {
899 SqlConnection connection = new SqlConnection(connectionString);
900 SqlDataReader returnReader;
901 connection.Open();
902 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
903 command.CommandType = CommandType.StoredProcedure;
904 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
905 return returnReader;
906
907 }
908
909
910 /// <summary>
911 /// 执行存储过程
912 /// </summary>
913 /// <param name="storedProcName">存储过程名</param>
914 /// <param name="parameters">存储过程参数</param>
915 /// <param name="tableName">DataSet结果中的表名</param>
916 /// <returns>DataSet</returns>
917 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
918 {
919 using (SqlConnection connection = new SqlConnection(connectionString))
920 {
921 DataSet dataSet = new DataSet();
922 connection.Open();
923 SqlDataAdapter sqlDA = new SqlDataAdapter();
924 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
925 sqlDA.Fill(dataSet, tableName);
926 connection.Close();
927 return dataSet;
928 }
929 }
930 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
931 {
932 using (SqlConnection connection = new SqlConnection(connectionString))
933 {
934 DataSet dataSet = new DataSet();
935 connection.Open();
936 SqlDataAdapter sqlDA = new SqlDataAdapter();
937 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
938 sqlDA.SelectCommand.CommandTimeout = Times;
939 sqlDA.Fill(dataSet, tableName);
940 connection.Close();
941 return dataSet;
942 }
943 }
944
945
946 /// <summary>
947 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
948 /// </summary>
949 /// <param name="connection">数据库连接</param>
950 /// <param name="storedProcName">存储过程名</param>
951 /// <param name="parameters">存储过程参数</param>
952 /// <returns>SqlCommand</returns>
953 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
954 {
955 SqlCommand command = new SqlCommand(storedProcName, connection);
956 command.CommandType = CommandType.StoredProcedure;
957 foreach (SqlParameter parameter in parameters)
958 {
959 if (parameter != null)
960 {
961 // 检查未分配值的输出参数,将其分配以DBNull.Value.
962 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
963 (parameter.Value == null))
964 {
965 parameter.Value = DBNull.Value;
966 }
967 command.Parameters.Add(parameter);
968 }
969 }
970
971 return command;
972 }
973
974 /// <summary>
975 /// 执行存储过程，返回影响的行数
976 /// </summary>
977 /// <param name="storedProcName">存储过程名</param>
978 /// <param name="parameters">存储过程参数</param>
979 /// <param name="rowsAffected">影响的行数</param>
980 /// <returns></returns>
981 public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
982 {
983 using (SqlConnection connection = new SqlConnection(connectionString))
984 {
985 int result;
986 connection.Open();
987 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
988 rowsAffected = command.ExecuteNonQuery();
989 result = (int)command.Parameters["ReturnValue"].Value;
990 //Connection.Close();
991 return result;
992 }
993 }
994
995 /// <summary>
996 /// 创建 SqlCommand 对象实例(用来返回一个整数值)
997 /// </summary>
998 /// <param name="storedProcName">存储过程名</param>
999 /// <param name="parameters">存储过程参数</param>
1000 /// <returns>SqlCommand 对象实例</returns>
1001 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
1002 {
1003 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
1004 command.Parameters.Add(new SqlParameter("ReturnValue",
1005 SqlDbType.Int, 4, ParameterDirection.ReturnValue,
1006 false, 0, 0, string.Empty, DataRowVersion.Default, null));
1007 return command;
1008 }
1009 #endregion
1010
1011 }
1012
1013 }
DBHelperSQL （注意：需引用类库DBUtility.dll，下载地址：http://download.csdn.net/detail/airrose/3275957）
1 using System;
2 using System.Collections;
3 using System.Collections.Specialized;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Configuration;
7 using System.Data.Common;
8 using System.Collections.Generic;
9 namespace Maticsoft.DBUtility
10 {
11 /// <summary>
12 /// 数据访问类，可用于访问不同数据库
13 /// </summary>
14 public class DbHelperSQLP
15 {
16 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
17 public string connectionString = "连接字符串";
18 public DbHelperSQLP()
19 {
20 }
21 public DbHelperSQLP(string ConnectionString)
22 {
23 connectionString = ConnectionString;
24 }
25
26 #region 公用方法
27 /// <summary>
28 /// 判断是否存在某表的某个字段
29 /// </summary>
30 /// <param name="tableName">表名称</param>
31 /// <param name="columnName">列名称</param>
32 /// <returns>是否存在</returns>
33 public bool ColumnExists(string tableName, string columnName)
34 {
35 string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
36 object res = GetSingle(sql);
37 if (res == null)
38 {
39 return false;
40 }
41 return Convert.ToInt32(res) > 0;
42 }
43 public int GetMaxID(string FieldName, string TableName)
44 {
45 string strsql = "select max(" + FieldName + ")+1 from " + TableName;
46 object obj = GetSingle(strsql);
47 if (obj == null)
48 {
49 return 1;
50 }
51 else
52 {
53 return int.Parse(obj.ToString());
54 }
55 }
56 public bool Exists(string strSql)
57 {
58 object obj = GetSingle(strSql);
59 int cmdresult;
60 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
61 {
62 cmdresult = 0;
63 }
64 else
65 {
66 cmdresult = int.Parse(obj.ToString());
67 }
68 if (cmdresult == 0)
69 {
70 return false;
71 }
72 else
73 {
74 return true;
75 }
76 }
77 /// <summary>
78 /// 表是否存在
79 /// </summary>
80 /// <param name="TableName"></param>
81 /// <returns></returns>
82 public bool TabExists(string TableName)
83 {
84 string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
85 //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
86 object obj = GetSingle(strsql);
87 int cmdresult;
88 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
89 {
90 cmdresult = 0;
91 }
92 else
93 {
94 cmdresult = int.Parse(obj.ToString());
95 }
96 if (cmdresult == 0)
97 {
98 return false;
99 }
100 else
101 {
102 return true;
103 }
104 }
105 public bool Exists(string strSql, params SqlParameter[] cmdParms)
106 {
107 object obj = GetSingle(strSql, cmdParms);
108 int cmdresult;
109 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
110 {
111 cmdresult = 0;
112 }
113 else
114 {
115 cmdresult = int.Parse(obj.ToString());
116 }
117 if (cmdresult == 0)
118 {
119 return false;
120 }
121 else
122 {
123 return true;
124 }
125 }
126 #endregion
127
128 #region 执行简单SQL语句
129
130 /// <summary>
131 /// 执行SQL语句，返回影响的记录数
132 /// </summary>
133 /// <param name="SQLString">SQL语句</param>
134 /// <returns>影响的记录数</returns>
135 public int ExecuteSql(string SQLString)
136 {
137 using (SqlConnection connection = new SqlConnection(connectionString))
138 {
139 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
140 {
141 try
142 {
143 connection.Open();
144 int rows = cmd.ExecuteNonQuery();
145 return rows;
146 }
147 catch (System.Data.SqlClient.SqlException e)
148 {
149 connection.Close();
150 throw e;
151 }
152 }
153 }
154 }
155
156 public int ExecuteSqlByTime(string SQLString, int Times)
157 {
158 using (SqlConnection connection = new SqlConnection(connectionString))
159 {
160 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
161 {
162 try
163 {
164 connection.Open();
165 cmd.CommandTimeout = Times;
166 int rows = cmd.ExecuteNonQuery();
167 return rows;
168 }
169 catch (System.Data.SqlClient.SqlException e)
170 {
171 connection.Close();
172 throw e;
173 }
174 }
175 }
176 }
177
178 /// <summary>
179 /// 执行Sql和Oracle滴混合事务
180 /// </summary>
181 /// <param name="list">SQL命令行列表</param>
182 /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
183 /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
184 public int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
185 {
186 using (SqlConnection conn = new SqlConnection(connectionString))
187 {
188 conn.Open();
189 SqlCommand cmd = new SqlCommand();
190 cmd.Connection = conn;
191 SqlTransaction tx = conn.BeginTransaction();
192 cmd.Transaction = tx;
193 try
194 {
195 foreach (CommandInfo myDE in list)
196 {
197 string cmdText = myDE.CommandText;
198 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
199 PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
200 if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
201 {
202 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
203 {
204 tx.Rollback();
205 throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");
206 //return 0;
207 }
208
209 object obj = cmd.ExecuteScalar();
210 bool isHave = false;
211 if (obj == null && obj == DBNull.Value)
212 {
213 isHave = false;
214 }
215 isHave = Convert.ToInt32(obj) > 0;
216 if (isHave)
217 {
218 //引发事件
219 myDE.OnSolicitationEvent();
220 }
221 }
222 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
223 {
224 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
225 {
226 tx.Rollback();
227 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
228 //return 0;
229 }
230
231 object obj = cmd.ExecuteScalar();
232 bool isHave = false;
233 if (obj == null && obj == DBNull.Value)
234 {
235 isHave = false;
236 }
237 isHave = Convert.ToInt32(obj) > 0;
238
239 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
240 {
241 tx.Rollback();
242 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
243 //return 0;
244 }
245 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
246 {
247 tx.Rollback();
248 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
249 //return 0;
250 }
251 continue;
252 }
253 int val = cmd.ExecuteNonQuery();
254 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
255 {
256 tx.Rollback();
257 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
258 //return 0;
259 }
260 cmd.Parameters.Clear();
261 }
262 string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
263 bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
264 if (!res)
265 {
266 tx.Rollback();
267 throw new Exception("Oracle执行失败");
268 // return -1;
269 }
270 tx.Commit();
271 return 1;
272 }
273 catch (System.Data.SqlClient.SqlException e)
274 {
275 tx.Rollback();
276 throw e;
277 }
278 catch (Exception e)
279 {
280 tx.Rollback();
281 throw e;
282 }
283 }
284 }
285 /// <summary>
286 /// 执行多条SQL语句，实现数据库事务。
287 /// </summary>
288 /// <param name="SQLStringList">多条SQL语句</param>
289 public int ExecuteSqlTran(List<String> SQLStringList)
290 {
291 using (SqlConnection conn = new SqlConnection(connectionString))
292 {
293 conn.Open();
294 SqlCommand cmd = new SqlCommand();
295 cmd.Connection = conn;
296 SqlTransaction tx = conn.BeginTransaction();
297 cmd.Transaction = tx;
298 try
299 {
300 int count = 0;
301 for (int n = 0; n < SQLStringList.Count; n++)
302 {
303 string strsql = SQLStringList[n];
304 if (strsql.Trim().Length > 1)
305 {
306 cmd.CommandText = strsql;
307 count += cmd.ExecuteNonQuery();
308 }
309 }
310 tx.Commit();
311 return count;
312 }
313 catch
314 {
315 tx.Rollback();
316 return 0;
317 }
318 }
319 }
320 /// <summary>
321 /// 执行带一个存储过程参数的的SQL语句。
322 /// </summary>
323 /// <param name="SQLString">SQL语句</param>
324 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
325 /// <returns>影响的记录数</returns>
326 public int ExecuteSql(string SQLString, string content)
327 {
328 using (SqlConnection connection = new SqlConnection(connectionString))
329 {
330 SqlCommand cmd = new SqlCommand(SQLString, connection);
331 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
332 myParameter.Value = content;
333 cmd.Parameters.Add(myParameter);
334 try
335 {
336 connection.Open();
337 int rows = cmd.ExecuteNonQuery();
338 return rows;
339 }
340 catch (System.Data.SqlClient.SqlException e)
341 {
342 throw e;
343 }
344 finally
345 {
346 cmd.Dispose();
347 connection.Close();
348 }
349 }
350 }
351 /// <summary>
352 /// 执行带一个存储过程参数的的SQL语句。
353 /// </summary>
354 /// <param name="SQLString">SQL语句</param>
355 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
356 /// <returns>影响的记录数</returns>
357 public object ExecuteSqlGet(string SQLString, string content)
358 {
359 using (SqlConnection connection = new SqlConnection(connectionString))
360 {
361 SqlCommand cmd = new SqlCommand(SQLString, connection);
362 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
363 myParameter.Value = content;
364 cmd.Parameters.Add(myParameter);
365 try
366 {
367 connection.Open();
368 object obj = cmd.ExecuteScalar();
369 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
370 {
371 return null;
372 }
373 else
374 {
375 return obj;
376 }
377 }
378 catch (System.Data.SqlClient.SqlException e)
379 {
380 throw e;
381 }
382 finally
383 {
384 cmd.Dispose();
385 connection.Close();
386 }
387 }
388 }
389 /// <summary>
390 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
391 /// </summary>
392 /// <param name="strSQL">SQL语句</param>
393 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
394 /// <returns>影响的记录数</returns>
395 public int ExecuteSqlInsertImg(string strSQL, byte[] fs)
396 {
397 using (SqlConnection connection = new SqlConnection(connectionString))
398 {
399 SqlCommand cmd = new SqlCommand(strSQL, connection);
400 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
401 myParameter.Value = fs;
402 cmd.Parameters.Add(myParameter);
403 try
404 {
405 connection.Open();
406 int rows = cmd.ExecuteNonQuery();
407 return rows;
408 }
409 catch (System.Data.SqlClient.SqlException e)
410 {
411 throw e;
412 }
413 finally
414 {
415 cmd.Dispose();
416 connection.Close();
417 }
418 }
419 }
420
421 /// <summary>
422 /// 执行一条计算查询结果语句，返回查询结果（object）。
423 /// </summary>
424 /// <param name="SQLString">计算查询结果语句</param>
425 /// <returns>查询结果（object）</returns>
426 public object GetSingle(string SQLString)
427 {
428 using (SqlConnection connection = new SqlConnection(connectionString))
429 {
430 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
431 {
432 try
433 {
434 connection.Open();
435 object obj = cmd.ExecuteScalar();
436 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
437 {
438 return null;
439 }
440 else
441 {
442 return obj;
443 }
444 }
445 catch (System.Data.SqlClient.SqlException e)
446 {
447 connection.Close();
448 throw e;
449 }
450 }
451 }
452 }
453 public object GetSingle(string SQLString, int Times)
454 {
455 using (SqlConnection connection = new SqlConnection(connectionString))
456 {
457 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
458 {
459 try
460 {
461 connection.Open();
462 cmd.CommandTimeout = Times;
463 object obj = cmd.ExecuteScalar();
464 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
465 {
466 return null;
467 }
468 else
469 {
470 return obj;
471 }
472 }
473 catch (System.Data.SqlClient.SqlException e)
474 {
475 connection.Close();
476 throw e;
477 }
478 }
479 }
480 }
481 /// <summary>
482 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
483 /// </summary>
484 /// <param name="strSQL">查询语句</param>
485 /// <returns>SqlDataReader</returns>
486 public SqlDataReader ExecuteReader(string strSQL)
487 {
488 SqlConnection connection = new SqlConnection(connectionString);
489 SqlCommand cmd = new SqlCommand(strSQL, connection);
490 try
491 {
492 connection.Open();
493 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
494 return myReader;
495 }
496 catch (System.Data.SqlClient.SqlException e)
497 {
498 throw e;
499 }
500
501 }
502 /// <summary>
503 /// 执行查询语句，返回DataSet
504 /// </summary>
505 /// <param name="SQLString">查询语句</param>
506 /// <returns>DataSet</returns>
507 public DataSet Query(string SQLString)
508 {
509 using (SqlConnection connection = new SqlConnection(connectionString))
510 {
511 DataSet ds = new DataSet();
512 try
513 {
514 connection.Open();
515 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
516 command.Fill(ds, "ds");
517 }
518 catch (System.Data.SqlClient.SqlException ex)
519 {
520 throw new Exception(ex.Message);
521 }
522 return ds;
523 }
524 }
525 public DataSet Query(string SQLString, int Times)
526 {
527 using (SqlConnection connection = new SqlConnection(connectionString))
528 {
529 DataSet ds = new DataSet();
530 try
531 {
532 connection.Open();
533 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
534 command.SelectCommand.CommandTimeout = Times;
535 command.Fill(ds, "ds");
536 }
537 catch (System.Data.SqlClient.SqlException ex)
538 {
539 throw new Exception(ex.Message);
540 }
541 return ds;
542 }
543 }
544
545
546
547 #endregion
548
549 #region 执行带参数的SQL语句
550
551 /// <summary>
552 /// 执行SQL语句，返回影响的记录数
553 /// </summary>
554 /// <param name="SQLString">SQL语句</param>
555 /// <returns>影响的记录数</returns>
556 public int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
557 {
558 using (SqlConnection connection = new SqlConnection(connectionString))
559 {
560 using (SqlCommand cmd = new SqlCommand())
561 {
562 try
563 {
564 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
565 int rows = cmd.ExecuteNonQuery();
566 cmd.Parameters.Clear();
567 return rows;
568 }
569 catch (System.Data.SqlClient.SqlException e)
570 {
571 throw e;
572 }
573 }
574 }
575 }
576
577
578 /// <summary>
579 /// 执行多条SQL语句，实现数据库事务。
580 /// </summary>
581 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
582 public void ExecuteSqlTran(Hashtable SQLStringList)
583 {
584 using (SqlConnection conn = new SqlConnection(connectionString))
585 {
586 conn.Open();
587 using (SqlTransaction trans = conn.BeginTransaction())
588 {
589 SqlCommand cmd = new SqlCommand();
590 try
591 {
592 //循环
593 foreach (DictionaryEntry myDE in SQLStringList)
594 {
595 string cmdText = myDE.Key.ToString();
596 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
597 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
598 int val = cmd.ExecuteNonQuery();
599 cmd.Parameters.Clear();
600 }
601 trans.Commit();
602 }
603 catch
604 {
605 trans.Rollback();
606 throw;
607 }
608 }
609 }
610 }
611 /// <summary>
612 /// 执行多条SQL语句，实现数据库事务。
613 /// </summary>
614 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
615 public int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
616 {
617 using (SqlConnection conn = new SqlConnection(connectionString))
618 {
619 conn.Open();
620 using (SqlTransaction trans = conn.BeginTransaction())
621 {
622 SqlCommand cmd = new SqlCommand();
623 try
624 { int count = 0;
625 //循环
626 foreach (CommandInfo myDE in cmdList)
627 {
628 string cmdText = myDE.CommandText;
629 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
630 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
631
632 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
633 {
634 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
635 {
636 trans.Rollback();
637 return 0;
638 }
639
640 object obj = cmd.ExecuteScalar();
641 bool isHave = false;
642 if (obj == null && obj == DBNull.Value)
643 {
644 isHave = false;
645 }
646 isHave = Convert.ToInt32(obj) > 0;
647
648 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
649 {
650 trans.Rollback();
651 return 0;
652 }
653 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
654 {
655 trans.Rollback();
656 return 0;
657 }
658 continue;
659 }
660 int val = cmd.ExecuteNonQuery();
661 count += val;
662 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
663 {
664 trans.Rollback();
665 return 0;
666 }
667 cmd.Parameters.Clear();
668 }
669 trans.Commit();
670 return count;
671 }
672 catch
673 {
674 trans.Rollback();
675 throw;
676 }
677 }
678 }
679 }
680 /// <summary>
681 /// 执行多条SQL语句，实现数据库事务。
682 /// </summary>
683 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
684 public void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
685 {
686 using (SqlConnection conn = new SqlConnection(connectionString))
687 {
688 conn.Open();
689 using (SqlTransaction trans = conn.BeginTransaction())
690 {
691 SqlCommand cmd = new SqlCommand();
692 try
693 {
694 int indentity = 0;
695 //循环
696 foreach (CommandInfo myDE in SQLStringList)
697 {
698 string cmdText = myDE.CommandText;
699 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
700 foreach (SqlParameter q in cmdParms)
701 {
702 if (q.Direction == ParameterDirection.InputOutput)
703 {
704 q.Value = indentity;
705 }
706 }
707 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
708 int val = cmd.ExecuteNonQuery();
709 foreach (SqlParameter q in cmdParms)
710 {
711 if (q.Direction == ParameterDirection.Output)
712 {
713 indentity = Convert.ToInt32(q.Value);
714 }
715 }
716 cmd.Parameters.Clear();
717 }
718 trans.Commit();
719 }
720 catch
721 {
722 trans.Rollback();
723 throw;
724 }
725 }
726 }
727 }
728 /// <summary>
729 /// 执行多条SQL语句，实现数据库事务。
730 /// </summary>
731 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
732 public void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
733 {
734 using (SqlConnection conn = new SqlConnection(connectionString))
735 {
736 conn.Open();
737 using (SqlTransaction trans = conn.BeginTransaction())
738 {
739 SqlCommand cmd = new SqlCommand();
740 try
741 {
742 int indentity = 0;
743 //循环
744 foreach (DictionaryEntry myDE in SQLStringList)
745 {
746 string cmdText = myDE.Key.ToString();
747 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
748 foreach (SqlParameter q in cmdParms)
749 {
750 if (q.Direction == ParameterDirection.InputOutput)
751 {
752 q.Value = indentity;
753 }
754 }
755 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
756 int val = cmd.ExecuteNonQuery();
757 foreach (SqlParameter q in cmdParms)
758 {
759 if (q.Direction == ParameterDirection.Output)
760 {
761 indentity = Convert.ToInt32(q.Value);
762 }
763 }
764 cmd.Parameters.Clear();
765 }
766 trans.Commit();
767 }
768 catch
769 {
770 trans.Rollback();
771 throw;
772 }
773 }
774 }
775 }
776 /// <summary>
777 /// 执行一条计算查询结果语句，返回查询结果（object）。
778 /// </summary>
779 /// <param name="SQLString">计算查询结果语句</param>
780 /// <returns>查询结果（object）</returns>
781 public object GetSingle(string SQLString, params SqlParameter[] cmdParms)
782 {
783 using (SqlConnection connection = new SqlConnection(connectionString))
784 {
785 using (SqlCommand cmd = new SqlCommand())
786 {
787 try
788 {
789 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
790 object obj = cmd.ExecuteScalar();
791 cmd.Parameters.Clear();
792 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
793 {
794 return null;
795 }
796 else
797 {
798 return obj;
799 }
800 }
801 catch (System.Data.SqlClient.SqlException e)
802 {
803 throw e;
804 }
805 }
806 }
807 }
808
809 /// <summary>
810 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
811 /// </summary>
812 /// <param name="strSQL">查询语句</param>
813 /// <returns>SqlDataReader</returns>
814 public SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
815 {
816 SqlConnection connection = new SqlConnection(connectionString);
817 SqlCommand cmd = new SqlCommand();
818 try
819 {
820 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
821 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
822 cmd.Parameters.Clear();
823 return myReader;
824 }
825 catch (System.Data.SqlClient.SqlException e)
826 {
827 throw e;
828 }
829 // finally
830 // {
831 // cmd.Dispose();
832 // connection.Close();
833 // }
834
835 }
836
837 /// <summary>
838 /// 执行查询语句，返回DataSet
839 /// </summary>
840 /// <param name="SQLString">查询语句</param>
841 /// <returns>DataSet</returns>
842 public DataSet Query(string SQLString, params SqlParameter[] cmdParms)
843 {
844 using (SqlConnection connection = new SqlConnection(connectionString))
845 {
846 SqlCommand cmd = new SqlCommand();
847 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
848 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
849 {
850 DataSet ds = new DataSet();
851 try
852 {
853 da.Fill(ds, "ds");
854 cmd.Parameters.Clear();
855 }
856 catch (System.Data.SqlClient.SqlException ex)
857 {
858 throw new Exception(ex.Message);
859 }
860 return ds;
861 }
862 }
863 }
864
865
866 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
867 {
868 if (conn.State != ConnectionState.Open)
869 conn.Open();
870 cmd.Connection = conn;
871 cmd.CommandText = cmdText;
872 if (trans != null)
873 cmd.Transaction = trans;
874 cmd.CommandType = CommandType.Text;//cmdType;
875 if (cmdParms != null)
876 {
877
878
879 foreach (SqlParameter parameter in cmdParms)
880 {
881 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
882 (parameter.Value == null))
883 {
884 parameter.Value = DBNull.Value;
885 }
886 cmd.Parameters.Add(parameter);
887 }
888 }
889 }
890
891 #endregion
892
893 #region 存储过程操作
894
895 /// <summary>
896 /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
897 /// </summary>
898 /// <param name="storedProcName">存储过程名</param>
899 /// <param name="parameters">存储过程参数</param>
900 /// <returns>SqlDataReader</returns>
901 public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
902 {
903 using (SqlConnection connection = new SqlConnection(connectionString))
904 {
905 SqlDataReader returnReader;
906 connection.Open();
907 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
908 command.CommandType = CommandType.StoredProcedure;
909 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
910 return returnReader;
911 }
912 }
913
914
915 /// <summary>
916 /// 执行存储过程
917 /// </summary>
918 /// <param name="storedProcName">存储过程名</param>
919 /// <param name="parameters">存储过程参数</param>
920 /// <param name="tableName">DataSet结果中的表名</param>
921 /// <returns>DataSet</returns>
922 public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
923 {
924 using (SqlConnection connection = new SqlConnection(connectionString))
925 {
926 DataSet dataSet = new DataSet();
927 connection.Open();
928 SqlDataAdapter sqlDA = new SqlDataAdapter();
929 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
930 sqlDA.Fill(dataSet, tableName);
931 connection.Close();
932 return dataSet;
933 }
934 }
935 public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
936 {
937 using (SqlConnection connection = new SqlConnection(connectionString))
938 {
939 DataSet dataSet = new DataSet();
940 connection.Open();
941 SqlDataAdapter sqlDA = new SqlDataAdapter();
942 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
943 sqlDA.SelectCommand.CommandTimeout = Times;
944 sqlDA.Fill(dataSet, tableName);
945 connection.Close();
946 return dataSet;
947 }
948 }
949
950
951 /// <summary>
952 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
953 /// </summary>
954 /// <param name="connection">数据库连接</param>
955 /// <param name="storedProcName">存储过程名</param>
956 /// <param name="parameters">存储过程参数</param>
957 /// <returns>SqlCommand</returns>
958 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
959 {
960 SqlCommand command = new SqlCommand(storedProcName, connection);
961 command.CommandType = CommandType.StoredProcedure;
962 foreach (SqlParameter parameter in parameters)
963 {
964 if (parameter != null)
965 {
966 // 检查未分配值的输出参数,将其分配以DBNull.Value.
967 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
968 (parameter.Value == null))
969 {
970 parameter.Value = DBNull.Value;
971 }
972 command.Parameters.Add(parameter);
973 }
974 }
975
976 return command;
977 }
978
979 /// <summary>
980 /// 执行存储过程，返回影响的行数
981 /// </summary>
982 /// <param name="storedProcName">存储过程名</param>
983 /// <param name="parameters">存储过程参数</param>
984 /// <param name="rowsAffected">影响的行数</param>
985 /// <returns></returns>
986 public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
987 {
988 using (SqlConnection connection = new SqlConnection(connectionString))
989 {
990 int result;
991 connection.Open();
992 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
993 rowsAffected = command.ExecuteNonQuery();
994 result = (int)command.Parameters["ReturnValue"].Value;
995 //Connection.Close();
996 return result;
997 }
998 }
999
1000 /// <summary>
1001 /// 创建 SqlCommand 对象实例(用来返回一个整数值)
1002 /// </summary>
1003 /// <param name="storedProcName">存储过程名</param>
1004 /// <param name="parameters">存储过程参数</param>
1005 /// <returns>SqlCommand 对象实例</returns>
1006 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
1007 {
1008 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
1009 command.Parameters.Add(new SqlParameter("ReturnValue",
1010 SqlDbType.Int, 4, ParameterDirection.ReturnValue,
1011 false, 0, 0, string.Empty, DataRowVersion.Default, null));
1012 return command;
1013 }
1014 #endregion
1015
1016 }
1017
1018 }
DBHelperSQLP （注意：同上）
1 using System;
2 using System.Collections;
3 using System.Collections.Specialized;
4 using System.Data;
5 using System.Configuration;
6 using System.Data.SQLite;
7 namespace Maticsoft.DBUtility
8 {
9 /// <summary>
10 /// 数据访问基础类(基于SQLite)
11 /// 可以用户可以修改满足自己项目的需要。
12 /// </summary>
13 public abstract class DbHelperSQLite
14 {
15 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
16 public static string connectionString = "连接字符串";
17 public DbHelperSQLite()
18 {
19 }
20
21
22 #region 公用方法
23
24 public static int GetMaxID(string FieldName, string TableName)
25 {
26 string strsql = "select max(" + FieldName + ")+1 from " + TableName;
27 object obj = GetSingle(strsql);
28 if (obj == null)
29 {
30 return 1;
31 }
32 else
33 {
34 return int.Parse(obj.ToString());
35 }
36 }
37 public static bool Exists(string strSql)
38 {
39 object obj = GetSingle(strSql);
40 int cmdresult;
41 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
42 {
43 cmdresult = 0;
44 }
45 else
46 {
47 cmdresult = int.Parse(obj.ToString());
48 }
49 if (cmdresult == 0)
50 {
51 return false;
52 }
53 else
54 {
55 return true;
56 }
57 }
58 public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)
59 {
60 object obj = GetSingle(strSql, cmdParms);
61 int cmdresult;
62 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
63 {
64 cmdresult = 0;
65 }
66 else
67 {
68 cmdresult = int.Parse(obj.ToString());
69 }
70 if (cmdresult == 0)
71 {
72 return false;
73 }
74 else
75 {
76 return true;
77 }
78 }
79
80 #endregion
81
82 #region 执行简单SQL语句
83
84 /// <summary>
85 /// 执行SQL语句，返回影响的记录数
86 /// </summary>
87 /// <param name="SQLString">SQL语句</param>
88 /// <returns>影响的记录数</returns>
89 public static int ExecuteSql(string SQLString)
90 {
91 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
92 {
93 using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
94 {
95 try
96 {
97 connection.Open();
98 int rows = cmd.ExecuteNonQuery();
99 return rows;
100 }
101 catch (System.Data.SQLite.SQLiteException E)
102 {
103 connection.Close();
104 throw new Exception(E.Message);
105 }
106 }
107 }
108 }
109
110 /// <summary>
111 /// 执行多条SQL语句，实现数据库事务。
112 /// </summary>
113 /// <param name="SQLStringList">多条SQL语句</param>
114 public static void ExecuteSqlTran(ArrayList SQLStringList)
115 {
116 using (SQLiteConnection conn = new SQLiteConnection(connectionString))
117 {
118 conn.Open();
119 SQLiteCommand cmd = new SQLiteCommand();
120 cmd.Connection = conn;
121 SQLiteTransaction tx = conn.BeginTransaction();
122 cmd.Transaction = tx;
123 try
124 {
125 for (int n = 0; n < SQLStringList.Count; n++)
126 {
127 string strsql = SQLStringList[n].ToString();
128 if (strsql.Trim().Length > 1)
129 {
130 cmd.CommandText = strsql;
131 cmd.ExecuteNonQuery();
132 }
133 }
134 tx.Commit();
135 }
136 catch (System.Data.SQLite.SQLiteException E)
137 {
138 tx.Rollback();
139 throw new Exception(E.Message);
140 }
141 }
142 }
143 /// <summary>
144 /// 执行带一个存储过程参数的的SQL语句。
145 /// </summary>
146 /// <param name="SQLString">SQL语句</param>
147 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
148 /// <returns>影响的记录数</returns>
149 public static int ExecuteSql(string SQLString, string content)
150 {
151 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
152 {
153 SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
154 SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
155 myParameter.Value = content;
156 cmd.Parameters.Add(myParameter);
157 try
158 {
159 connection.Open();
160 int rows = cmd.ExecuteNonQuery();
161 return rows;
162 }
163 catch (System.Data.SQLite.SQLiteException E)
164 {
165 throw new Exception(E.Message);
166 }
167 finally
168 {
169 cmd.Dispose();
170 connection.Close();
171 }
172 }
173 }
174 /// <summary>
175 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
176 /// </summary>
177 /// <param name="strSQL">SQL语句</param>
178 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
179 /// <returns>影响的记录数</returns>
180 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
181 {
182 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
183 {
184 SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
185 SQLiteParameter myParameter = new SQLiteParameter("@fs", DbType.Binary);
186 myParameter.Value = fs;
187 cmd.Parameters.Add(myParameter);
188 try
189 {
190 connection.Open();
191 int rows = cmd.ExecuteNonQuery();
192 return rows;
193 }
194 catch (System.Data.SQLite.SQLiteException E)
195 {
196 throw new Exception(E.Message);
197 }
198 finally
199 {
200 cmd.Dispose();
201 connection.Close();
202 }
203 }
204 }
205
206 /// <summary>
207 /// 执行一条计算查询结果语句，返回查询结果（object）。
208 /// </summary>
209 /// <param name="SQLString">计算查询结果语句</param>
210 /// <returns>查询结果（object）</returns>
211 public static object GetSingle(string SQLString)
212 {
213 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
214 {
215 using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
216 {
217 try
218 {
219 connection.Open();
220 object obj = cmd.ExecuteScalar();
221 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
222 {
223 return null;
224 }
225 else
226 {
227 return obj;
228 }
229 }
230 catch (System.Data.SQLite.SQLiteException e)
231 {
232 connection.Close();
233 throw new Exception(e.Message);
234 }
235 }
236 }
237 }
238 /// <summary>
239 /// 执行查询语句，返回SQLiteDataReader
240 /// </summary>
241 /// <param name="strSQL">查询语句</param>
242 /// <returns>SQLiteDataReader</returns>
243 public static SQLiteDataReader ExecuteReader(string strSQL)
244 {
245 SQLiteConnection connection = new SQLiteConnection(connectionString);
246 SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
247 try
248 {
249 connection.Open();
250 SQLiteDataReader myReader = cmd.ExecuteReader();
251 return myReader;
252 }
253 catch (System.Data.SQLite.SQLiteException e)
254 {
255 throw new Exception(e.Message);
256 }
257
258 }
259 /// <summary>
260 /// 执行查询语句，返回DataSet
261 /// </summary>
262 /// <param name="SQLString">查询语句</param>
263 /// <returns>DataSet</returns>
264 public static DataSet Query(string SQLString)
265 {
266 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
267 {
268 DataSet ds = new DataSet();
269 try
270 {
271 connection.Open();
272 SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
273 command.Fill(ds, "ds");
274 }
275 catch (System.Data.SQLite.SQLiteException ex)
276 {
277 throw new Exception(ex.Message);
278 }
279 return ds;
280 }
281 }
282
283
284 #endregion
285
286 #region 执行带参数的SQL语句
287
288 /// <summary>
289 /// 执行SQL语句，返回影响的记录数
290 /// </summary>
291 /// <param name="SQLString">SQL语句</param>
292 /// <returns>影响的记录数</returns>
293 public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
294 {
295 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
296 {
297 using (SQLiteCommand cmd = new SQLiteCommand())
298 {
299 try
300 {
301 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
302 int rows = cmd.ExecuteNonQuery();
303 cmd.Parameters.Clear();
304 return rows;
305 }
306 catch (System.Data.SQLite.SQLiteException E)
307 {
308 throw new Exception(E.Message);
309 }
310 }
311 }
312 }
313
314
315 /// <summary>
316 /// 执行多条SQL语句，实现数据库事务。
317 /// </summary>
318 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>
319 public static void ExecuteSqlTran(Hashtable SQLStringList)
320 {
321 using (SQLiteConnection conn = new SQLiteConnection(connectionString))
322 {
323 conn.Open();
324 using (SQLiteTransaction trans = conn.BeginTransaction())
325 {
326 SQLiteCommand cmd = new SQLiteCommand();
327 try
328 {
329 //循环
330 foreach (DictionaryEntry myDE in SQLStringList)
331 {
332 string cmdText = myDE.Key.ToString();
333 SQLiteParameter[] cmdParms = (SQLiteParameter[])myDE.Value;
334 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
335 int val = cmd.ExecuteNonQuery();
336 cmd.Parameters.Clear();
337
338 trans.Commit();
339 }
340 }
341 catch
342 {
343 trans.Rollback();
344 throw;
345 }
346 }
347 }
348 }
349
350
351 /// <summary>
352 /// 执行一条计算查询结果语句，返回查询结果（object）。
353 /// </summary>
354 /// <param name="SQLString">计算查询结果语句</param>
355 /// <returns>查询结果（object）</returns>
356 public static object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)
357 {
358 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
359 {
360 using (SQLiteCommand cmd = new SQLiteCommand())
361 {
362 try
363 {
364 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
365 object obj = cmd.ExecuteScalar();
366 cmd.Parameters.Clear();
367 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
368 {
369 return null;
370 }
371 else
372 {
373 return obj;
374 }
375 }
376 catch (System.Data.SQLite.SQLiteException e)
377 {
378 throw new Exception(e.Message);
379 }
380 }
381 }
382 }
383
384 /// <summary>
385 /// 执行查询语句，返回SQLiteDataReader
386 /// </summary>
387 /// <param name="strSQL">查询语句</param>
388 /// <returns>SQLiteDataReader</returns>
389 public static SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)
390 {
391 SQLiteConnection connection = new SQLiteConnection(connectionString);
392 SQLiteCommand cmd = new SQLiteCommand();
393 try
394 {
395 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
396 SQLiteDataReader myReader = cmd.ExecuteReader();
397 cmd.Parameters.Clear();
398 return myReader;
399 }
400 catch (System.Data.SQLite.SQLiteException e)
401 {
402 throw new Exception(e.Message);
403 }
404
405 }
406
407 /// <summary>
408 /// 执行查询语句，返回DataSet
409 /// </summary>
410 /// <param name="SQLString">查询语句</param>
411 /// <returns>DataSet</returns>
412 public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)
413 {
414 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
415 {
416 SQLiteCommand cmd = new SQLiteCommand();
417 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
418 using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
419 {
420 DataSet ds = new DataSet();
421 try
422 {
423 da.Fill(ds, "ds");
424 cmd.Parameters.Clear();
425 }
426 catch (System.Data.SQLite.SQLiteException ex)
427 {
428 throw new Exception(ex.Message);
429 }
430 return ds;
431 }
432 }
433 }
434
435
436 private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
437 {
438 if (conn.State != ConnectionState.Open)
439 conn.Open();
440 cmd.Connection = conn;
441 cmd.CommandText = cmdText;
442 if (trans != null)
443 cmd.Transaction = trans;
444 cmd.CommandType = CommandType.Text;//cmdType;
445 if (cmdParms != null)
446 {
447 foreach (SQLiteParameter parm in cmdParms)
448 cmd.Parameters.Add(parm);
449 }
450 }
451
452 #endregion
453
454
455
456 }
457 }
DBHelperSQLite
二、Oracle 相关
1 using System;
2 using System.Collections;
3 using System.Collections.Specialized;
4 using System.Data;
5 using System.Data.OracleClient;
6 using System.Configuration;
7
8 namespace Maticsoft.DBUtility
9 {
10 /// <summary>
11 /// 数据访问基础类(基于Oracle)
12 /// 可以用户可以修改满足自己项目的需要。
13 /// </summary>
14 public abstract class DbHelperOra
15 {
16 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
17 public static string connectionString = "连接字符串";
18 public DbHelperOra()
19 {
20 }
21
22 #region 公用方法
23
24 public static int GetMaxID(string FieldName, string TableName)
25 {
26 string strsql = "select max(" + FieldName + ")+1 from " + TableName;
27 object obj = GetSingle(strsql);
28 if (obj == null)
29 {
30 return 1;
31 }
32 else
33 {
34 return int.Parse(obj.ToString());
35 }
36 }
37 public static bool Exists(string strSql)
38 {
39 object obj = GetSingle(strSql);
40 int cmdresult;
41 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
42 {
43 cmdresult = 0;
44 }
45 else
46 {
47 cmdresult = int.Parse(obj.ToString());
48 }
49 if (cmdresult == 0)
50 {
51 return false;
52 }
53 else
54 {
55 return true;
56 }
57 }
58
59 public static bool Exists(string strSql, params OracleParameter[] cmdParms)
60 {
61 object obj = GetSingle(strSql, cmdParms);
62 int cmdresult;
63 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
64 {
65 cmdresult = 0;
66 }
67 else
68 {
69 cmdresult = int.Parse(obj.ToString());
70 }
71 if (cmdresult == 0)
72 {
73 return false;
74 }
75 else
76 {
77 return true;
78 }
79 }
80
81 #endregion
82
83 #region 执行简单SQL语句
84
85 /// <summary>
86 /// 执行SQL语句，返回影响的记录数
87 /// </summary>
88 /// <param name="SQLString">SQL语句</param>
89 /// <returns>影响的记录数</returns>
90 public static int ExecuteSql(string SQLString)
91 {
92 using (OracleConnection connection = new OracleConnection(connectionString))
93 {
94 using (OracleCommand cmd = new OracleCommand(SQLString,connection))
95 {
96 try
97 {
98 connection.Open();
99 int rows=cmd.ExecuteNonQuery();
100 return rows;
101 }
102 catch(System.Data.OracleClient.OracleException E)
103 {
104 connection.Close();
105 throw new Exception(E.Message);
106 }
107 }
108 }
109 }
110
111 /// <summary>
112 /// 执行多条SQL语句，实现数据库事务。
113 /// </summary>
114 /// <param name="SQLStringList">多条SQL语句</param>
115 public static void ExecuteSqlTran(ArrayList SQLStringList)
116 {
117 using (OracleConnection conn = new OracleConnection(connectionString))
118 {
119 conn.Open();
120 OracleCommand cmd = new OracleCommand();
121 cmd.Connection=conn;
122 OracleTransaction tx=conn.BeginTransaction();
123 cmd.Transaction=tx;
124 try
125 {
126 for(int n=0;n<SQLStringList.Count;n++)
127 {
128 string strsql=SQLStringList[n].ToString();
129 if (strsql.Trim().Length>1)
130 {
131 cmd.CommandText=strsql;
132 cmd.ExecuteNonQuery();
133 }
134 }
135 tx.Commit();
136 }
137 catch(System.Data.OracleClient.OracleException E)
138 {
139 tx.Rollback();
140 throw new Exception(E.Message);
141 }
142 }
143 }
144 /// <summary>
145 /// 执行带一个存储过程参数的的SQL语句。
146 /// </summary>
147 /// <param name="SQLString">SQL语句</param>
148 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
149 /// <returns>影响的记录数</returns>
150 public static int ExecuteSql(string SQLString,string content)
151 {
152 using (OracleConnection connection = new OracleConnection(connectionString))
153 {
154 OracleCommand cmd = new OracleCommand(SQLString,connection);
155 System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@content", OracleType.NVarChar);
156 myParameter.Value = content ;
157 cmd.Parameters.Add(myParameter);
158 try
159 {
160 connection.Open();
161 int rows=cmd.ExecuteNonQuery();
162 return rows;
163 }
164 catch(System.Data.OracleClient.OracleException E)
165 {
166 throw new Exception(E.Message);
167 }
168 finally
169 {
170 cmd.Dispose();
171 connection.Close();
172 }
173 }
174 }
175 /// <summary>
176 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
177 /// </summary>
178 /// <param name="strSQL">SQL语句</param>
179 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
180 /// <returns>影响的记录数</returns>
181 public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
182 {
183 using (OracleConnection connection = new OracleConnection(connectionString))
184 {
185 OracleCommand cmd = new OracleCommand(strSQL,connection);
186 System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@fs", OracleType.LongRaw);
187 myParameter.Value = fs ;
188 cmd.Parameters.Add(myParameter);
189 try
190 {
191 connection.Open();
192 int rows=cmd.ExecuteNonQuery();
193 return rows;
194 }
195 catch(System.Data.OracleClient.OracleException E)
196 {
197 throw new Exception(E.Message);
198 }
199 finally
200 {
201 cmd.Dispose();
202 connection.Close();
203 }
204 }
205 }
206
207 /// <summary>
208 /// 执行一条计算查询结果语句，返回查询结果（object）。
209 /// </summary>
210 /// <param name="SQLString">计算查询结果语句</param>
211 /// <returns>查询结果（object）</returns>
212 public static object GetSingle(string SQLString)
213 {
214 using (OracleConnection connection = new OracleConnection(connectionString))
215 {
216 using(OracleCommand cmd = new OracleCommand(SQLString,connection))
217 {
218 try
219 {
220 connection.Open();
221 object obj = cmd.ExecuteScalar();
222 if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
223 {
224 return null;
225 }
226 else
227 {
228 return obj;
229 }
230 }
231 catch(System.Data.OracleClient.OracleException e)
232 {
233 connection.Close();
234 throw new Exception(e.Message);
235 }
236 }
237 }
238 }
239 /// <summary>
240 /// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
241 /// </summary>
242 /// <param name="strSQL">查询语句</param>
243 /// <returns>OracleDataReader</returns>
244 public static OracleDataReader ExecuteReader(string strSQL)
245 {
246 OracleConnection connection = new OracleConnection(connectionString);
247 OracleCommand cmd = new OracleCommand(strSQL,connection);
248 try
249 {
250 connection.Open();
251 OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
252 return myReader;
253 }
254 catch(System.Data.OracleClient.OracleException e)
255 {
256 throw new Exception(e.Message);
257 }
258
259 }
260 /// <summary>
261 /// 执行查询语句，返回DataSet
262 /// </summary>
263 /// <param name="SQLString">查询语句</param>
264 /// <returns>DataSet</returns>
265 public static DataSet Query(string SQLString)
266 {
267 using (OracleConnection connection = new OracleConnection(connectionString))
268 {
269 DataSet ds = new DataSet();
270 try
271 {
272 connection.Open();
273 OracleDataAdapter command = new OracleDataAdapter(SQLString,connection);
274 command.Fill(ds,"ds");
275 }
276 catch(System.Data.OracleClient.OracleException ex)
277 {
278 throw new Exception(ex.Message);
279 }
280 return ds;
281 }
282 }
283
284
285 #endregion
286
287 #region 执行带参数的SQL语句
288
289 /// <summary>
290 /// 执行SQL语句，返回影响的记录数
291 /// </summary>
292 /// <param name="SQLString">SQL语句</param>
293 /// <returns>影响的记录数</returns>
294 public static int ExecuteSql(string SQLString,params OracleParameter[] cmdParms)
295 {
296 using (OracleConnection connection = new OracleConnection(connectionString))
297 {
298 using (OracleCommand cmd = new OracleCommand())
299 {
300 try
301 {
302 PrepareCommand(cmd, connection, null,SQLString, cmdParms);
303 int rows=cmd.ExecuteNonQuery();
304 cmd.Parameters.Clear();
305 return rows;
306 }
307 catch(System.Data.OracleClient.OracleException E)
308 {
309 throw new Exception(E.Message);
310 }
311 }
312 }
313 }
314
315
316 /// <summary>
317 /// 执行多条SQL语句，实现数据库事务。
318 /// </summary>
319 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
320 public static void ExecuteSqlTran(Hashtable SQLStringList)
321 {
322 using (OracleConnection conn = new OracleConnection(connectionString))
323 {
324 conn.Open();
325 using (OracleTransaction trans = conn.BeginTransaction())
326 {
327 OracleCommand cmd = new OracleCommand();
328 try
329 {
330 //循环
331 foreach (DictionaryEntry myDE in SQLStringList)
332 {
333 string cmdText=myDE.Key.ToString();
334 OracleParameter[] cmdParms=(OracleParameter[])myDE.Value;
335 PrepareCommand(cmd,conn,trans,cmdText, cmdParms);
336 int val = cmd.ExecuteNonQuery();
337 cmd.Parameters.Clear();
338
339 trans.Commit();
340 }
341 }
342 catch
343 {
344 trans.Rollback();
345 throw;
346 }
347 }
348 }
349 }
350
351
352 /// <summary>
353 /// 执行一条计算查询结果语句，返回查询结果（object）。
354 /// </summary>
355 /// <param name="SQLString">计算查询结果语句</param>
356 /// <returns>查询结果（object）</returns>
357 public static object GetSingle(string SQLString,params OracleParameter[] cmdParms)
358 {
359 using (OracleConnection connection = new OracleConnection(connectionString))
360 {
361 using (OracleCommand cmd = new OracleCommand())
362 {
363 try
364 {
365 PrepareCommand(cmd, connection, null,SQLString, cmdParms);
366 object obj = cmd.ExecuteScalar();
367 cmd.Parameters.Clear();
368 if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
369 {
370 return null;
371 }
372 else
373 {
374 return obj;
375 }
376 }
377 catch(System.Data.OracleClient.OracleException e)
378 {
379 throw new Exception(e.Message);
380 }
381 }
382 }
383 }
384
385 /// <summary>
386 /// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
387 /// </summary>
388 /// <param name="strSQL">查询语句</param>
389 /// <returns>OracleDataReader</returns>
390 public static OracleDataReader ExecuteReader(string SQLString,params OracleParameter[] cmdParms)
391 {
392 OracleConnection connection = new OracleConnection(connectionString);
393 OracleCommand cmd = new OracleCommand();
394 try
395 {
396 PrepareCommand(cmd, connection, null,SQLString, cmdParms);
397 OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
398 cmd.Parameters.Clear();
399 return myReader;
400 }
401 catch(System.Data.OracleClient.OracleException e)
402 {
403 throw new Exception(e.Message);
404 }
405
406 }
407
408 /// <summary>
409 /// 执行查询语句，返回DataSet
410 /// </summary>
411 /// <param name="SQLString">查询语句</param>
412 /// <returns>DataSet</returns>
413 public static DataSet Query(string SQLString,params OracleParameter[] cmdParms)
414 {
415 using (OracleConnection connection = new OracleConnection(connectionString))
416 {
417 OracleCommand cmd = new OracleCommand();
418 PrepareCommand(cmd, connection, null,SQLString, cmdParms);
419 using( OracleDataAdapter da = new OracleDataAdapter(cmd) )
420 {
421 DataSet ds = new DataSet();
422 try
423 {
424 da.Fill(ds,"ds");
425 cmd.Parameters.Clear();
426 }
427 catch(System.Data.OracleClient.OracleException ex)
428 {
429 throw new Exception(ex.Message);
430 }
431 return ds;
432 }
433 }
434 }
435
436
437 private static void PrepareCommand(OracleCommand cmd,OracleConnection conn,OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
438 {
439 if (conn.State != ConnectionState.Open)
440 conn.Open();
441 cmd.Connection = conn;
442 cmd.CommandText = cmdText;
443 if (trans != null)
444 cmd.Transaction = trans;
445 cmd.CommandType = CommandType.Text;//cmdType;
446 if (cmdParms != null)
447 {
448 foreach (OracleParameter parm in cmdParms)
449 cmd.Parameters.Add(parm);
450 }
451 }
452
453 #endregion
454
455 #region 存储过程操作
456
457 /// <summary>
458 /// 执行存储过程 返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
459 /// </summary>
460 /// <param name="storedProcName">存储过程名</param>
461 /// <param name="parameters">存储过程参数</param>
462 /// <returns>OracleDataReader</returns>
463 public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
464 {
465 OracleConnection connection = new OracleConnection(connectionString);
466 OracleDataReader returnReader;
467 connection.Open();
468 OracleCommand command = BuildQueryCommand( connection,storedProcName, parameters );
469 command.CommandType = CommandType.StoredProcedure;
470 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
471 return returnReader;
472 }
473
474
475 /// <summary>
476 /// 执行存储过程
477 /// </summary>
478 /// <param name="storedProcName">存储过程名</param>
479 /// <param name="parameters">存储过程参数</param>
480 /// <param name="tableName">DataSet结果中的表名</param>
481 /// <returns>DataSet</returns>
482 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
483 {
484 using (OracleConnection connection = new OracleConnection(connectionString))
485 {
486 DataSet dataSet = new DataSet();
487 connection.Open();
488 OracleDataAdapter sqlDA = new OracleDataAdapter();
489 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
490 sqlDA.Fill( dataSet, tableName );
491 connection.Close();
492 return dataSet;
493 }
494 }
495
496
497 /// <summary>
498 /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
499 /// </summary>
500 /// <param name="connection">数据库连接</param>
501 /// <param name="storedProcName">存储过程名</param>
502 /// <param name="parameters">存储过程参数</param>
503 /// <returns>OracleCommand</returns>
504 private static OracleCommand BuildQueryCommand(OracleConnection connection,string storedProcName, IDataParameter[] parameters)
505 {
506 OracleCommand command = new OracleCommand( storedProcName, connection );
507 command.CommandType = CommandType.StoredProcedure;
508 foreach (OracleParameter parameter in parameters)
509 {
510 command.Parameters.Add( parameter );
511 }
512 return command;
513 }
514
515 /// <summary>
516 /// 执行存储过程，返回影响的行数
517 /// </summary>
518 /// <param name="storedProcName">存储过程名</param>
519 /// <param name="parameters">存储过程参数</param>
520 /// <param name="rowsAffected">影响的行数</param>
521 /// <returns></returns>
522 public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
523 {
524 using (OracleConnection connection = new OracleConnection(connectionString))
525 {
526 int result;
527 connection.Open();
528 OracleCommand command = BuildIntCommand(connection,storedProcName, parameters );
529 rowsAffected = command.ExecuteNonQuery();
530 result = (int)command.Parameters["ReturnValue"].Value;
531 //Connection.Close();
532 return result;
533 }
534 }
535
536 /// <summary>
537 /// 创建 OracleCommand 对象实例(用来返回一个整数值)
538 /// </summary>
539 /// <param name="storedProcName">存储过程名</param>
540 /// <param name="parameters">存储过程参数</param>
541 /// <returns>OracleCommand 对象实例</returns>
542 private static OracleCommand BuildIntCommand(OracleConnection connection,string storedProcName, IDataParameter[] parameters)
543 {
544 OracleCommand command = BuildQueryCommand(connection,storedProcName, parameters );
545 command.Parameters.Add( new OracleParameter ( "ReturnValue",
546 OracleType.Int32, 4, ParameterDirection.ReturnValue,
547 false,0,0,string.Empty,DataRowVersion.Default,null ));
548 return command;
549 }
550 #endregion
551
552 }
553 }
DBHelperOra （注意：注意类库引用）
using System;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
using System.Collections.Generic;
namespace Maticsoft.DBUtility
{
/// <summary>
/// A helper class used to execute queries against an Oracle database
/// </summary>
public abstract class OracleHelper
{
// Read the connection strings from the configuration file
public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.AppSettings["OraConnString1"];
public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.AppSettings["OraConnString2"];
public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.AppSettings["OraConnString3"];
public static readonly string ConnectionStringProfile = ConfigurationManager.AppSettings["OraProfileConnString"];
public static readonly string ConnectionStringMembership = ConfigurationManager.AppSettings["OraMembershipConnString"];
//Create a hashtable for the parameter cached
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// Execute a database query which does not include a select
/// </summary>
/// <param name="connString">Connection string to database</param>
/// <param name="cmdType">Command type either stored procedure or SQL</param>
/// <param name="cmdText">Acutall SQL Command</param>
/// <param name="commandParameters">Parameters to bind to the command</param>
/// <returns></returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
// Create a new Oracle command
OracleCommand cmd = new OracleCommand();
//Create a connection
using (OracleConnection connection = new OracleConnection(connectionString))
{
//Prepare the command
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
//Execute the command
int val = cmd.ExecuteNonQuery();
connection.Close();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string connectionString, string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return ds;
}
}
public static DataSet Query(string connectionString, string SQLString, params OracleParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.OracleClient.OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return ds;
}
}
}
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (OracleParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string connectionString, string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
}
}
public static bool Exists(string connectionString,string strOracle)
{
object obj = OracleHelper.GetSingle(connectionString,strOracle);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// Execute an OracleCommand (that returns no resultset) against an existing database transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="trans">an existing database transaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute an OracleCommand (that returns no resultset) against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute an OracleCommand (that returns no resultset) against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, string cmdText)
{
OracleCommand cmd = new OracleCommand();
OracleConnection connection = new OracleConnection(connectionString);
PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute a select query that will return a result set
/// </summary>
/// <param name="connString">Connection string</param>
//// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns></returns>
public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
try
{
//Prepare the command to execute
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// Execute an OracleCommand that returns the first column of the first record against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
using (OracleConnection conn = new OracleConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a OracleCommand (that returns a 1x1 resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <param name="transaction">A valid SqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">An array of OracleParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (transaction == null)
throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null)
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// Execute an OracleCommand that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Add a set of parameters to the cached
/// </summary>
/// <param name="cacheKey">Key value to look up the parameters</param>
/// <param name="commandParameters">Actual parameters to cached</param>
public static void CacheParameters(string cacheKey, params OracleParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
/// <summary>
/// Fetch parameters from the cache
/// </summary>
/// <param name="cacheKey">Key to look up the parameters</param>
/// <returns></returns>
public static OracleParameter[] GetCachedParameters(string cacheKey)
{
OracleParameter[] cachedParms = (OracleParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
// If the parameters are in the cache
OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];
// return a copy of the parameters
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
/// <summary>
/// Internal function to prepare a command for execution by the database
/// </summary>
/// <param name="cmd">Existing command object</param>
/// <param name="conn">Database connection object</param>
/// <param name="trans">Optional transaction object</param>
/// <param name="cmdType">Command type, e.g. stored procedure</param>
/// <param name="cmdText">Command test</param>
/// <param name="commandParameters">Parameters for the command</param>
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
{
//Open the connection if required
if (conn.State != ConnectionState.Open)
conn.Open();
//Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
//Bind it to the transaction if it exists
if (trans != null)
cmd.Transaction = trans;
// Bind the parameters passed in
if (commandParameters != null)
{
foreach (OracleParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
}
/// <summary>
/// Converter to use boolean data type with Oracle
/// </summary>
/// <param name="value">Value to convert</param>
/// <returns></returns>
public static string OraBit(bool value)
{
if (value)
return "Y";
else
return "N";
}
/// <summary>
/// Converter to use boolean data type with Oracle
/// </summary>
/// <param name="value">Value to convert</param>
/// <returns></returns>
public static bool OraBool(string value)
{
if (value.Equals("Y"))
return true;
else
return false;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static bool ExecuteSqlTran(string conStr, List<CommandInfo> cmdList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo c in cmdList)
{
if (!String.IsNullOrEmpty(c.CommandText))
{
PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (c.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须符合select count(..的格式");
//return false;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须大于0");
//return false;
}
if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须等于0");
//eturn false;
}
continue;
}
int res = cmd.ExecuteNonQuery();
if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须有影像行");
// return false;
}
}
}
tx.Commit();
return true;
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw E;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(string conStr,List<String> SQLStringList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (string sql in SQLStringList)
{
if (!String.IsNullOrEmpty(sql))
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
}
}
OracleHelper
三、MySQL 相关
1 using System;
2 using System.Collections;
3 using System.Collections.Specialized;
4 using System.Data;
5 using MySql.Data.MySqlClient;
6 using System.Configuration;
7 using System.Data.Common;
8 using System.Collections.Generic;
9 namespace Maticsoft.DBUtility
10 {
11 /// <summary>
12 /// 数据访问抽象基础类
13 /// </summary>
14 public abstract class DbHelperMySQL
15 {
16 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
17 public static string connectionString = "连接字符串";
18 public DbHelperMySQL()
19 {
20 }
21
22 #region 公用方法
23 /// <summary>
24 /// 得到最大值
25 /// </summary>
26 /// <param name="FieldName"></param>
27 /// <param name="TableName"></param>
28 /// <returns></returns>
29 public static int GetMaxID(string FieldName, string TableName)
30 {
31 string strsql = "select max(" + FieldName + ")+1 from " + TableName;
32 object obj = GetSingle(strsql);
33 if (obj == null)
34 {
35 return 1;
36 }
37 else
38 {
39 return int.Parse(obj.ToString());
40 }
41 }
42 /// <summary>
43 /// 是否存在
44 /// </summary>
45 /// <param name="strSql"></param>
46 /// <returns></returns>
47 public static bool Exists(string strSql)
48 {
49 object obj = GetSingle(strSql);
50 int cmdresult;
51 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
52 {
53 cmdresult = 0;
54 }
55 else
56 {
57 cmdresult = int.Parse(obj.ToString());
58 }
59 if (cmdresult == 0)
60 {
61 return false;
62 }
63 else
64 {
65 return true;
66 }
67 }
68 /// <summary>
69 /// 是否存在（基于MySqlParameter）
70 /// </summary>
71 /// <param name="strSql"></param>
72 /// <param name="cmdParms"></param>
73 /// <returns></returns>
74 public static bool Exists(string strSql, params MySqlParameter[] cmdParms)
75 {
76 object obj = GetSingle(strSql, cmdParms);
77 int cmdresult;
78 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
79 {
80 cmdresult = 0;
81 }
82 else
83 {
84 cmdresult = int.Parse(obj.ToString());
85 }
86 if (cmdresult == 0)
87 {
88 return false;
89 }
90 else
91 {
92 return true;
93 }
94 }
95 #endregion
96
97 #region 执行简单SQL语句
98
99 /// <summary>
100 /// 执行SQL语句，返回影响的记录数
101 /// </summary>
102 /// <param name="SQLString">SQL语句</param>
103 /// <returns>影响的记录数</returns>
104 public static int ExecuteSql(string SQLString)
105 {
106 using (MySqlConnection connection = new MySqlConnection(connectionString))
107 {
108 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
109 {
110 try
111 {
112 connection.Open();
113 int rows = cmd.ExecuteNonQuery();
114 return rows;
115 }
116 catch (MySql.Data.MySqlClient.MySqlException e)
117 {
118 connection.Close();
119 throw e;
120 }
121 }
122 }
123 }
124
125 public static int ExecuteSqlByTime(string SQLString, int Times)
126 {
127 using (MySqlConnection connection = new MySqlConnection(connectionString))
128 {
129 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
130 {
131 try
132 {
133 connection.Open();
134 cmd.CommandTimeout = Times;
135 int rows = cmd.ExecuteNonQuery();
136 return rows;
137 }
138 catch (MySql.Data.MySqlClient.MySqlException e)
139 {
140 connection.Close();
141 throw e;
142 }
143 }
144 }
145 }
146
147 /// <summary>
148 /// 执行MySql和Oracle滴混合事务
149 /// </summary>
150 /// <param name="list">SQL命令行列表</param>
151 /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
152 /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
153 public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
154 {
155 using (MySqlConnection conn = new MySqlConnection(connectionString))
156 {
157 conn.Open();
158 MySqlCommand cmd = new MySqlCommand();
159 cmd.Connection = conn;
160 MySqlTransaction tx = conn.BeginTransaction();
161 cmd.Transaction = tx;
162 try
163 {
164 foreach (CommandInfo myDE in list)
165 {
166 string cmdText = myDE.CommandText;
167 MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
168 PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
169 if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
170 {
171 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
172 {
173 tx.Rollback();
174 throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");
175 //return 0;
176 }
177
178 object obj = cmd.ExecuteScalar();
179 bool isHave = false;
180 if (obj == null && obj == DBNull.Value)
181 {
182 isHave = false;
183 }
184 isHave = Convert.ToInt32(obj) > 0;
185 if (isHave)
186 {
187 //引发事件
188 myDE.OnSolicitationEvent();
189 }
190 }
191 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
192 {
193 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
194 {
195 tx.Rollback();
196 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
197 //return 0;
198 }
199
200 object obj = cmd.ExecuteScalar();
201 bool isHave = false;
202 if (obj == null && obj == DBNull.Value)
203 {
204 isHave = false;
205 }
206 isHave = Convert.ToInt32(obj) > 0;
207
208 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
209 {
210 tx.Rollback();
211 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
212 //return 0;
213 }
214 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
215 {
216 tx.Rollback();
217 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
218 //return 0;
219 }
220 continue;
221 }
222 int val = cmd.ExecuteNonQuery();
223 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
224 {
225 tx.Rollback();
226 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
227 //return 0;
228 }
229 cmd.Parameters.Clear();
230 }
231 string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
232 bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
233 if (!res)
234 {
235 tx.Rollback();
236 throw new Exception("执行失败");
237 // return -1;
238 }
239 tx.Commit();
240 return 1;
241 }
242 catch (MySql.Data.MySqlClient.MySqlException e)
243 {
244 tx.Rollback();
245 throw e;
246 }
247 catch (Exception e)
248 {
249 tx.Rollback();
250 throw e;
251 }
252 }
253 }
254 /// <summary>
255 /// 执行多条SQL语句，实现数据库事务。
256 /// </summary>
257 /// <param name="SQLStringList">多条SQL语句</param>
258 public static int ExecuteSqlTran(List<String> SQLStringList)
259 {
260 using (MySqlConnection conn = new MySqlConnection(connectionString))
261 {
262 conn.Open();
263 MySqlCommand cmd = new MySqlCommand();
264 cmd.Connection = conn;
265 MySqlTransaction tx = conn.BeginTransaction();
266 cmd.Transaction = tx;
267 try
268 {
269 int count = 0;
270 for (int n = 0; n < SQLStringList.Count; n++)
271 {
272 string strsql = SQLStringList[n];
273 if (strsql.Trim().Length > 1)
274 {
275 cmd.CommandText = strsql;
276 count += cmd.ExecuteNonQuery();
277 }
278 }
279 tx.Commit();
280 return count;
281 }
282 catch
283 {
284 tx.Rollback();
285 return 0;
286 }
287 }
288 }
289 /// <summary>
290 /// 执行带一个存储过程参数的的SQL语句。
291 /// </summary>
292 /// <param name="SQLString">SQL语句</param>
293 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
294 /// <returns>影响的记录数</returns>
295 public static int ExecuteSql(string SQLString, string content)
296 {
297 using (MySqlConnection connection = new MySqlConnection(connectionString))
298 {
299 MySqlCommand cmd = new MySqlCommand(SQLString, connection);
300 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
301 myParameter.Value = content;
302 cmd.Parameters.Add(myParameter);
303 try
304 {
305 connection.Open();
306 int rows = cmd.ExecuteNonQuery();
307 return rows;
308 }
309 catch (MySql.Data.MySqlClient.MySqlException e)
310 {
311 throw e;
312 }
313 finally
314 {
315 cmd.Dispose();
316 connection.Close();
317 }
318 }
319 }
320 /// <summary>
321 /// 执行带一个存储过程参数的的SQL语句。
322 /// </summary>
323 /// <param name="SQLString">SQL语句</param>
324 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
325 /// <returns>影响的记录数</returns>
326 public static object ExecuteSqlGet(string SQLString, string content)
327 {
328 using (MySqlConnection connection = new MySqlConnection(connectionString))
329 {
330 MySqlCommand cmd = new MySqlCommand(SQLString, connection);
331 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
332 myParameter.Value = content;
333 cmd.Parameters.Add(myParameter);
334 try
335 {
336 connection.Open();
337 object obj = cmd.ExecuteScalar();
338 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
339 {
340 return null;
341 }
342 else
343 {
344 return obj;
345 }
346 }
347 catch (MySql.Data.MySqlClient.MySqlException e)
348 {
349 throw e;
350 }
351 finally
352 {
353 cmd.Dispose();
354 connection.Close();
355 }
356 }
357 }
358 /// <summary>
359 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
360 /// </summary>
361 /// <param name="strSQL">SQL语句</param>
362 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
363 /// <returns>影响的记录数</returns>
364 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
365 {
366 using (MySqlConnection connection = new MySqlConnection(connectionString))
367 {
368 MySqlCommand cmd = new MySqlCommand(strSQL, connection);
369 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);
370 myParameter.Value = fs;
371 cmd.Parameters.Add(myParameter);
372 try
373 {
374 connection.Open();
375 int rows = cmd.ExecuteNonQuery();
376 return rows;
377 }
378 catch (MySql.Data.MySqlClient.MySqlException e)
379 {
380 throw e;
381 }
382 finally
383 {
384 cmd.Dispose();
385 connection.Close();
386 }
387 }
388 }
389
390 /// <summary>
391 /// 执行一条计算查询结果语句，返回查询结果（object）。
392 /// </summary>
393 /// <param name="SQLString">计算查询结果语句</param>
394 /// <returns>查询结果（object）</returns>
395 public static object GetSingle(string SQLString)
396 {
397 using (MySqlConnection connection = new MySqlConnection(connectionString))
398 {
399 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
400 {
401 try
402 {
403 connection.Open();
404 object obj = cmd.ExecuteScalar();
405 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
406 {
407 return null;
408 }
409 else
410 {
411 return obj;
412 }
413 }
414 catch (MySql.Data.MySqlClient.MySqlException e)
415 {
416 connection.Close();
417 throw e;
418 }
419 }
420 }
421 }
422 public static object GetSingle(string SQLString, int Times)
423 {
424 using (MySqlConnection connection = new MySqlConnection(connectionString))
425 {
426 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
427 {
428 try
429 {
430 connection.Open();
431 cmd.CommandTimeout = Times;
432 object obj = cmd.ExecuteScalar();
433 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
434 {
435 return null;
436 }
437 else
438 {
439 return obj;
440 }
441 }
442 catch (MySql.Data.MySqlClient.MySqlException e)
443 {
444 connection.Close();
445 throw e;
446 }
447 }
448 }
449 }
450 /// <summary>
451 /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
452 /// </summary>
453 /// <param name="strSQL">查询语句</param>
454 /// <returns>MySqlDataReader</returns>
455 public static MySqlDataReader ExecuteReader(string strSQL)
456 {
457 MySqlConnection connection = new MySqlConnection(connectionString);
458 MySqlCommand cmd = new MySqlCommand(strSQL, connection);
459 try
460 {
461 connection.Open();
462 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
463 return myReader;
464 }
465 catch (MySql.Data.MySqlClient.MySqlException e)
466 {
467 throw e;
468 }
469
470 }
471 /// <summary>
472 /// 执行查询语句，返回DataSet
473 /// </summary>
474 /// <param name="SQLString">查询语句</param>
475 /// <returns>DataSet</returns>
476 public static DataSet Query(string SQLString)
477 {
478 using (MySqlConnection connection = new MySqlConnection(connectionString))
479 {
480 DataSet ds = new DataSet();
481 try
482 {
483 connection.Open();
484 MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
485 command.Fill(ds, "ds");
486 }
487 catch (MySql.Data.MySqlClient.MySqlException ex)
488 {
489 throw new Exception(ex.Message);
490 }
491 return ds;
492 }
493 }
494 public static DataSet Query(string SQLString, int Times)
495 {
496 using (MySqlConnection connection = new MySqlConnection(connectionString))
497 {
498 DataSet ds = new DataSet();
499 try
500 {
501 connection.Open();
502 MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
503 command.SelectCommand.CommandTimeout = Times;
504 command.Fill(ds, "ds");
505 }
506 catch (MySql.Data.MySqlClient.MySqlException ex)
507 {
508 throw new Exception(ex.Message);
509 }
510 return ds;
511 }
512 }
513
514
515
516 #endregion
517
518 #region 执行带参数的SQL语句
519
520 /// <summary>
521 /// 执行SQL语句，返回影响的记录数
522 /// </summary>
523 /// <param name="SQLString">SQL语句</param>
524 /// <returns>影响的记录数</returns>
525 public static int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms)
526 {
527 using (MySqlConnection connection = new MySqlConnection(connectionString))
528 {
529 using (MySqlCommand cmd = new MySqlCommand())
530 {
531 try
532 {
533 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
534 int rows = cmd.ExecuteNonQuery();
535 cmd.Parameters.Clear();
536 return rows;
537 }
538 catch (MySql.Data.MySqlClient.MySqlException e)
539 {
540 throw e;
541 }
542 }
543 }
544 }
545
546
547 /// <summary>
548 /// 执行多条SQL语句，实现数据库事务。
549 /// </summary>
550 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
551 public static void ExecuteSqlTran(Hashtable SQLStringList)
552 {
553 using (MySqlConnection conn = new MySqlConnection(connectionString))
554 {
555 conn.Open();
556 using (MySqlTransaction trans = conn.BeginTransaction())
557 {
558 MySqlCommand cmd = new MySqlCommand();
559 try
560 {
561 //循环
562 foreach (DictionaryEntry myDE in SQLStringList)
563 {
564 string cmdText = myDE.Key.ToString();
565 MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
566 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
567 int val = cmd.ExecuteNonQuery();
568 cmd.Parameters.Clear();
569 }
570 trans.Commit();
571 }
572 catch
573 {
574 trans.Rollback();
575 throw;
576 }
577 }
578 }
579 }
580 /// <summary>
581 /// 执行多条SQL语句，实现数据库事务。
582 /// </summary>
583 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
584 public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
585 {
586 using (MySqlConnection conn = new MySqlConnection(connectionString))
587 {
588 conn.Open();
589 using (MySqlTransaction trans = conn.BeginTransaction())
590 {
591 MySqlCommand cmd = new MySqlCommand();
592 try
593 { int count = 0;
594 //循环
595 foreach (CommandInfo myDE in cmdList)
596 {
597 string cmdText = myDE.CommandText;
598 MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
599 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
600
601 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
602 {
603 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
604 {
605 trans.Rollback();
606 return 0;
607 }
608
609 object obj = cmd.ExecuteScalar();
610 bool isHave = false;
611 if (obj == null && obj == DBNull.Value)
612 {
613 isHave = false;
614 }
615 isHave = Convert.ToInt32(obj) > 0;
616
617 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
618 {
619 trans.Rollback();
620 return 0;
621 }
622 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
623 {
624 trans.Rollback();
625 return 0;
626 }
627 continue;
628 }
629 int val = cmd.ExecuteNonQuery();
630 count += val;
631 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
632 {
633 trans.Rollback();
634 return 0;
635 }
636 cmd.Parameters.Clear();
637 }
638 trans.Commit();
639 return count;
640 }
641 catch
642 {
643 trans.Rollback();
644 throw;
645 }
646 }
647 }
648 }
649 /// <summary>
650 /// 执行多条SQL语句，实现数据库事务。
651 /// </summary>
652 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
653 public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
654 {
655 using (MySqlConnection conn = new MySqlConnection(connectionString))
656 {
657 conn.Open();
658 using (MySqlTransaction trans = conn.BeginTransaction())
659 {
660 MySqlCommand cmd = new MySqlCommand();
661 try
662 {
663 int indentity = 0;
664 //循环
665 foreach (CommandInfo myDE in SQLStringList)
666 {
667 string cmdText = myDE.CommandText;
668 MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
669 foreach (MySqlParameter q in cmdParms)
670 {
671 if (q.Direction == ParameterDirection.InputOutput)
672 {
673 q.Value = indentity;
674 }
675 }
676 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
677 int val = cmd.ExecuteNonQuery();
678 foreach (MySqlParameter q in cmdParms)
679 {
680 if (q.Direction == ParameterDirection.Output)
681 {
682 indentity = Convert.ToInt32(q.Value);
683 }
684 }
685 cmd.Parameters.Clear();
686 }
687 trans.Commit();
688 }
689 catch
690 {
691 trans.Rollback();
692 throw;
693 }
694 }
695 }
696 }
697 /// <summary>
698 /// 执行多条SQL语句，实现数据库事务。
699 /// </summary>
700 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
701 public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
702 {
703 using (MySqlConnection conn = new MySqlConnection(connectionString))
704 {
705 conn.Open();
706 using (MySqlTransaction trans = conn.BeginTransaction())
707 {
708 MySqlCommand cmd = new MySqlCommand();
709 try
710 {
711 int indentity = 0;
712 //循环
713 foreach (DictionaryEntry myDE in SQLStringList)
714 {
715 string cmdText = myDE.Key.ToString();
716 MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
717 foreach (MySqlParameter q in cmdParms)
718 {
719 if (q.Direction == ParameterDirection.InputOutput)
720 {
721 q.Value = indentity;
722 }
723 }
724 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
725 int val = cmd.ExecuteNonQuery();
726 foreach (MySqlParameter q in cmdParms)
727 {
728 if (q.Direction == ParameterDirection.Output)
729 {
730 indentity = Convert.ToInt32(q.Value);
731 }
732 }
733 cmd.Parameters.Clear();
734 }
735 trans.Commit();
736 }
737 catch
738 {
739 trans.Rollback();
740 throw;
741 }
742 }
743 }
744 }
745 /// <summary>
746 /// 执行一条计算查询结果语句，返回查询结果（object）。
747 /// </summary>
748 /// <param name="SQLString">计算查询结果语句</param>
749 /// <returns>查询结果（object）</returns>
750 public static object GetSingle(string SQLString, params MySqlParameter[] cmdParms)
751 {
752 using (MySqlConnection connection = new MySqlConnection(connectionString))
753 {
754 using (MySqlCommand cmd = new MySqlCommand())
755 {
756 try
757 {
758 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
759 object obj = cmd.ExecuteScalar();
760 cmd.Parameters.Clear();
761 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
762 {
763 return null;
764 }
765 else
766 {
767 return obj;
768 }
769 }
770 catch (MySql.Data.MySqlClient.MySqlException e)
771 {
772 throw e;
773 }
774 }
775 }
776 }
777
778 /// <summary>
779 /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
780 /// </summary>
781 /// <param name="strSQL">查询语句</param>
782 /// <returns>MySqlDataReader</returns>
783 public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms)
784 {
785 MySqlConnection connection = new MySqlConnection(connectionString);
786 MySqlCommand cmd = new MySqlCommand();
787 try
788 {
789 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
790 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
791 cmd.Parameters.Clear();
792 return myReader;
793 }
794 catch (MySql.Data.MySqlClient.MySqlException e)
795 {
796 throw e;
797 }
798 // finally
799 // {
800 // cmd.Dispose();
801 // connection.Close();
802 // }
803
804 }
805
806 /// <summary>
807 /// 执行查询语句，返回DataSet
808 /// </summary>
809 /// <param name="SQLString">查询语句</param>
810 /// <returns>DataSet</returns>
811 public static DataSet Query(string SQLString, params MySqlParameter[] cmdParms)
812 {
813 using (MySqlConnection connection = new MySqlConnection(connectionString))
814 {
815 MySqlCommand cmd = new MySqlCommand();
816 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
817 using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
818 {
819 DataSet ds = new DataSet();
820 try
821 {
822 da.Fill(ds, "ds");
823 cmd.Parameters.Clear();
824 }
825 catch (MySql.Data.MySqlClient.MySqlException ex)
826 {
827 throw new Exception(ex.Message);
828 }
829 return ds;
830 }
831 }
832 }
833
834
835 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
836 {
837 if (conn.State != ConnectionState.Open)
838 conn.Open();
839 cmd.Connection = conn;
840 cmd.CommandText = cmdText;
841 if (trans != null)
842 cmd.Transaction = trans;
843 cmd.CommandType = CommandType.Text;//cmdType;
844 if (cmdParms != null)
845 {
846
847
848 foreach (MySqlParameter parameter in cmdParms)
849 {
850 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
851 (parameter.Value == null))
852 {
853 parameter.Value = DBNull.Value;
854 }
855 cmd.Parameters.Add(parameter);
856 }
857 }
858 }
859
860 #endregion
861
862
863
864 }
865
866 }
DBHelperMySQL （注意：注意类库引用）
四、其他
1 using System;
2 using System.Collections;
3 using System.Collections.Specialized;
4 using System.Data;
5 using System.Data.OleDb;
6 using System.Configuration;
7
8 namespace Maticsoft.DBUtility
9 {
10 /// <summary>
11 /// 数据访问基础类(基于OleDb)
12 /// 可以用户可以修改满足自己项目的需要。
13 /// </summary>
14 public abstract class DbHelperOleDb
15 {
16 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
17 public static string connectionString = "连接字符串";
18 public DbHelperOleDb()
19 {
20 }
21
22 #region 公用方法
23
24 public static int GetMaxID(string FieldName, string TableName)
25 {
26 string strsql = "select max(" + FieldName + ")+1 from " + TableName;
27 object obj = DbHelperSQL.GetSingle(strsql);
28 if (obj == null)
29 {
30 return 1;
31 }
32 else
33 {
34 return int.Parse(obj.ToString());
35 }
36 }
37 public static bool Exists(string strSql)
38 {
39 object obj = DbHelperSQL.GetSingle(strSql);
40 int cmdresult;
41 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
42 {
43 cmdresult = 0;
44 }
45 else
46 {
47 cmdresult = int.Parse(obj.ToString());
48 }
49 if (cmdresult == 0)
50 {
51 return false;
52 }
53 else
54 {
55 return true;
56 }
57 }
58 public static bool Exists(string strSql, params OleDbParameter[] cmdParms)
59 {
60 object obj = GetSingle(strSql, cmdParms);
61 int cmdresult;
62 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
63 {
64 cmdresult = 0;
65 }
66 else
67 {
68 cmdresult = int.Parse(obj.ToString());
69 }
70 if (cmdresult == 0)
71 {
72 return false;
73 }
74 else
75 {
76 return true;
77 }
78 }
79
80 #endregion
81
82 #region 执行简单SQL语句
83
84 /// <summary>
85 /// 执行SQL语句，返回影响的记录数
86 /// </summary>
87 /// <param name="SQLString">SQL语句</param>
88 /// <returns>影响的记录数</returns>
89 public static int ExecuteSql(string SQLString)
90 {
91 using (OleDbConnection connection = new OleDbConnection(connectionString))
92 {
93 using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
94 {
95 try
96 {
97 connection.Open();
98 int rows = cmd.ExecuteNonQuery();
99 return rows;
100 }
101 catch (System.Data.OleDb.OleDbException E)
102 {
103 connection.Close();
104 throw new Exception(E.Message);
105 }
106 }
107 }
108 }
109
110 /// <summary>
111 /// 执行多条SQL语句，实现数据库事务。
112 /// </summary>
113 /// <param name="SQLStringList">多条SQL语句</param>
114 public static void ExecuteSqlTran(ArrayList SQLStringList)
115 {
116 using (OleDbConnection conn = new OleDbConnection(connectionString))
117 {
118 conn.Open();
119 OleDbCommand cmd = new OleDbCommand();
120 cmd.Connection = conn;
121 OleDbTransaction tx = conn.BeginTransaction();
122 cmd.Transaction = tx;
123 try
124 {
125 for (int n = 0; n < SQLStringList.Count; n++)
126 {
127 string strsql = SQLStringList[n].ToString();
128 if (strsql.Trim().Length > 1)
129 {
130 cmd.CommandText = strsql;
131 cmd.ExecuteNonQuery();
132 }
133 }
134 tx.Commit();
135 }
136 catch (System.Data.OleDb.OleDbException E)
137 {
138 tx.Rollback();
139 throw new Exception(E.Message);
140 }
141 }
142 }
143 /// <summary>
144 /// 执行带一个存储过程参数的的SQL语句。
145 /// </summary>
146 /// <param name="SQLString">SQL语句</param>
147 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
148 /// <returns>影响的记录数</returns>
149 public static int ExecuteSql(string SQLString, string content)
150 {
151 using (OleDbConnection connection = new OleDbConnection(connectionString))
152 {
153 OleDbCommand cmd = new OleDbCommand(SQLString, connection);
154 System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@content", OleDbType.VarChar);
155 myParameter.Value = content;
156 cmd.Parameters.Add(myParameter);
157 try
158 {
159 connection.Open();
160 int rows = cmd.ExecuteNonQuery();
161 return rows;
162 }
163 catch (System.Data.OleDb.OleDbException E)
164 {
165 throw new Exception(E.Message);
166 }
167 finally
168 {
169 cmd.Dispose();
170 connection.Close();
171 }
172 }
173 }
174 /// <summary>
175 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
176 /// </summary>
177 /// <param name="strSQL">SQL语句</param>
178 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
179 /// <returns>影响的记录数</returns>
180 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
181 {
182 using (OleDbConnection connection = new OleDbConnection(connectionString))
183 {
184 OleDbCommand cmd = new OleDbCommand(strSQL, connection);
185 System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@fs", OleDbType.Binary);
186 myParameter.Value = fs;
187 cmd.Parameters.Add(myParameter);
188 try
189 {
190 connection.Open();
191 int rows = cmd.ExecuteNonQuery();
192 return rows;
193 }
194 catch (System.Data.OleDb.OleDbException E)
195 {
196 throw new Exception(E.Message);
197 }
198 finally
199 {
200 cmd.Dispose();
201 connection.Close();
202 }
203 }
204 }
205
206 /// <summary>
207 /// 执行一条计算查询结果语句，返回查询结果（object）。
208 /// </summary>
209 /// <param name="SQLString">计算查询结果语句</param>
210 /// <returns>查询结果（object）</returns>
211 public static object GetSingle(string SQLString)
212 {
213 using (OleDbConnection connection = new OleDbConnection(connectionString))
214 {
215 using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
216 {
217 try
218 {
219 connection.Open();
220 object obj = cmd.ExecuteScalar();
221 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
222 {
223 return null;
224 }
225 else
226 {
227 return obj;
228 }
229 }
230 catch (System.Data.OleDb.OleDbException e)
231 {
232 connection.Close();
233 throw new Exception(e.Message);
234 }
235 }
236 }
237 }
238 /// <summary>
239 /// 执行查询语句，返回OleDbDataReader
240 /// </summary>
241 /// <param name="strSQL">查询语句</param>
242 /// <returns>OleDbDataReader</returns>
243 public static OleDbDataReader ExecuteReader(string strSQL)
244 {
245 OleDbConnection connection = new OleDbConnection(connectionString);
246 OleDbCommand cmd = new OleDbCommand(strSQL, connection);
247 try
248 {
249 connection.Open();
250 OleDbDataReader myReader = cmd.ExecuteReader();
251 return myReader;
252 }
253 catch (System.Data.OleDb.OleDbException e)
254 {
255 throw new Exception(e.Message);
256 }
257
258 }
259 /// <summary>
260 /// 执行查询语句，返回DataSet
261 /// </summary>
262 /// <param name="SQLString">查询语句</param>
263 /// <returns>DataSet</returns>
264 public static DataSet Query(string SQLString)
265 {
266 using (OleDbConnection connection = new OleDbConnection(connectionString))
267 {
268 DataSet ds = new DataSet();
269 try
270 {
271 connection.Open();
272 OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
273 command.Fill(ds, "ds");
274 }
275 catch (System.Data.OleDb.OleDbException ex)
276 {
277 throw new Exception(ex.Message);
278 }
279 return ds;
280 }
281 }
282
283
284 #endregion
285
286 #region 执行带参数的SQL语句
287
288 /// <summary>
289 /// 执行SQL语句，返回影响的记录数
290 /// </summary>
291 /// <param name="SQLString">SQL语句</param>
292 /// <returns>影响的记录数</returns>
293 public static int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms)
294 {
295 using (OleDbConnection connection = new OleDbConnection(connectionString))
296 {
297 using (OleDbCommand cmd = new OleDbCommand())
298 {
299 try
300 {
301 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
302 int rows = cmd.ExecuteNonQuery();
303 cmd.Parameters.Clear();
304 return rows;
305 }
306 catch (System.Data.OleDb.OleDbException E)
307 {
308 throw new Exception(E.Message);
309 }
310 }
311 }
312 }
313
314
315 /// <summary>
316 /// 执行多条SQL语句，实现数据库事务。
317 /// </summary>
318 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OleDbParameter[]）</param>
319 public static void ExecuteSqlTran(Hashtable SQLStringList)
320 {
321 using (OleDbConnection conn = new OleDbConnection(connectionString))
322 {
323 conn.Open();
324 using (OleDbTransaction trans = conn.BeginTransaction())
325 {
326 OleDbCommand cmd = new OleDbCommand();
327 try
328 {
329 //循环
330 foreach (DictionaryEntry myDE in SQLStringList)
331 {
332 string cmdText = myDE.Key.ToString();
333 OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Value;
334 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
335 int val = cmd.ExecuteNonQuery();
336 cmd.Parameters.Clear();
337
338 trans.Commit();
339 }
340 }
341 catch
342 {
343 trans.Rollback();
344 throw;
345 }
346 }
347 }
348 }
349
350
351 /// <summary>
352 /// 执行一条计算查询结果语句，返回查询结果（object）。
353 /// </summary>
354 /// <param name="SQLString">计算查询结果语句</param>
355 /// <returns>查询结果（object）</returns>
356 public static object GetSingle(string SQLString, params OleDbParameter[] cmdParms)
357 {
358 using (OleDbConnection connection = new OleDbConnection(connectionString))
359 {
360 using (OleDbCommand cmd = new OleDbCommand())
361 {
362 try
363 {
364 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
365 object obj = cmd.ExecuteScalar();
366 cmd.Parameters.Clear();
367 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
368 {
369 return null;
370 }
371 else
372 {
373 return obj;
374 }
375 }
376 catch (System.Data.OleDb.OleDbException e)
377 {
378 throw new Exception(e.Message);
379 }
380 }
381 }
382 }
383
384 /// <summary>
385 /// 执行查询语句，返回OleDbDataReader
386 /// </summary>
387 /// <param name="strSQL">查询语句</param>
388 /// <returns>OleDbDataReader</returns>
389 public static OleDbDataReader ExecuteReader(string SQLString, params OleDbParameter[] cmdParms)
390 {
391 OleDbConnection connection = new OleDbConnection(connectionString);
392 OleDbCommand cmd = new OleDbCommand();
393 try
394 {
395 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
396 OleDbDataReader myReader = cmd.ExecuteReader();
397 cmd.Parameters.Clear();
398 return myReader;
399 }
400 catch (System.Data.OleDb.OleDbException e)
401 {
402 throw new Exception(e.Message);
403 }
404
405 }
406
407 /// <summary>
408 /// 执行查询语句，返回DataSet
409 /// </summary>
410 /// <param name="SQLString">查询语句</param>
411 /// <returns>DataSet</returns>
412 public static DataSet Query(string SQLString, params OleDbParameter[] cmdParms)
413 {
414 using (OleDbConnection connection = new OleDbConnection(connectionString))
415 {
416 OleDbCommand cmd = new OleDbCommand();
417 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
418 using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
419 {
420 DataSet ds = new DataSet();
421 try
422 {
423 da.Fill(ds, "ds");
424 cmd.Parameters.Clear();
425 }
426 catch (System.Data.OleDb.OleDbException ex)
427 {
428 throw new Exception(ex.Message);
429 }
430 return ds;
431 }
432 }
433 }
434
435
436 private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
437 {
438 if (conn.State != ConnectionState.Open)
439 conn.Open();
440 cmd.Connection = conn;
441 cmd.CommandText = cmdText;
442 if (trans != null)
443 cmd.Transaction = trans;
444 cmd.CommandType = CommandType.Text;//cmdType;
445 if (cmdParms != null)
446 {
447 foreach (OleDbParameter parm in cmdParms)
448 cmd.Parameters.Add(parm);
449 }
450 }
451
452 #endregion
453
454 }
455 }
DBHelperOleDB
相关文章
