/// <summary>通用数据库接口 |
002 |
|
003 |
using System; |
004 |
using System.Collections; |
005 |
using System.Collections.Specialized; |
006 |
using System.Data; |
007 |
using System.Data.SqlClient; |
008 |
using System.Configuration; |
009 |
using System.Data.Common; |
010 |
using System.Collections.Generic; |
011 |
namespace Legalsoft.Wizard.DBUtility |
012 |
{ |
013 |
public enum EffentNextType |
014 |
{ |
015 |
/// <summary> |
016 |
/// 对其他语句无任何影响 |
017 |
/// </summary> |
018 |
None, |
019 |
/// <summary> |
020 |
/// 当前语句必须为"SELECT COUNT(1) FROM .."格式，如果存在则继续执行，不存在回滚事务 |
021 |
/// </summary> |
022 |
WhenHaveContine, |
023 |
/// <summary> |
024 |
/// 当前语句必须为"SELECT COUNT(1) FROM .."格式，如果不存在则继续执行，存在回滚事务 |
025 |
/// </summary> |
026 |
WhenNoHaveContine, |
027 |
/// <summary> |
028 |
/// 当前语句影响到的行数必须大于0，否则回滚事务 |
029 |
/// </summary> |
030 |
ExcuteEffectRows, |
031 |
/// <summary> |
032 |
/// 引发事件-当前语句必须为"SELECT COUNT(1) FROM .."格式，如果不存在则继续执行，存在回滚事务 |
033 |
/// </summary> |
034 |
SolicitationEvent |
035 |
} |
036 |
public class CommandInfo |
037 |
{ |
038 |
public object ShareObject = null; |
039 |
public object OriginalData = null; |
040 |
event EventHandler _solicitationEvent; |
041 |
public event EventHandler SolicitationEvent |
042 |
{ |
043 |
add |
044 |
{ |
045 |
_solicitationEvent += value; |
046 |
} |
047 |
remove |
048 |
{ |
049 |
_solicitationEvent -= value; |
050 |
} |
051 |
} |
052 |
public void OnSolicitationEvent() |
053 |
{ |
054 |
if (_solicitationEvent != null) |
055 |
{ |
056 |
_solicitationEvent(this,new EventArgs()); |
057 |
} |
058 |
} |
059 |
public string CommandText; |
060 |
public System.Data.Common.DbParameter[] Parameters; |
061 |
public EffentNextType EffentNextType = EffentNextType.None; |
062 |
public CommandInfo() |
063 |
{ |
064 |
} |
065 |
public CommandInfo(string sqlText, SqlParameter[] para) |
066 |
{ |
067 |
this.CommandText = sqlText; |
068 |
this.Parameters = para; |
069 |
} |
070 |
public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type) |
071 |
{ |
072 |
this.CommandText = sqlText; |
073 |
this.Parameters = para; |
074 |
this.EffentNextType = type; |
075 |
} |
076 |
} |
077 |
/// <summary> |
078 |
/// 数据访问抽象基础类 |
079 |
/// Copyright (C) 2004-2008 By LiTianPing |
080 |
/// </summary> |
081 |
public abstract class SQLHelper |
082 |
{ |
083 |
//数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. |
084 |
public static string connectionString = ""; |
085 |
public SQLHelper() |
086 |
{ |
087 |
connectionString = @"DATA SOURCE=(local);UID=sa;PWD=111111;DATABASE=LegalSoft"; |
088 |
} |
089 |
#region 公用方法 |
090 |
/// <summary> |
091 |
/// 读取指定图片的二进制信息 |
092 |
/// </summary> |
093 |
/// <param name="id"></param> |
094 |
/// <returns></returns> |
095 |
public object LoadImage(int id) |
096 |
{ |
097 |
SqlConnection myConnection = new SqlConnection(connectionString); |
098 |
SqlCommand myCommand = new SqlCommand("SELECT [bImage] FROM [tblCAIPIAO_NEWS_IMG] WHERE id=@id", myConnection); |
099 |
myCommand.CommandType = CommandType.Text; |
100 |
myCommand.Parameters.Add(new SqlParameter("@id", id)); |
101 |
myConnection.Open(); |
102 |
object result = myCommand.ExecuteScalar(); |
103 |
try |
104 |
{ |
105 |
return new System.IO.MemoryStream((byte[])result); |
106 |
} |
107 |
catch (ArgumentNullException e) |
108 |
{ |
109 |
return null; |
110 |
} |
111 |
finally |
112 |
{ |
113 |
myConnection.Close(); |
114 |
} |
115 |
} |
116 |
/// <summary> |
117 |
/// 判断是否存在某表的某个字段 |
118 |
/// </summary> |
119 |
/// <param name="tableName">表名称</param> |
120 |
/// <param name="columnName">列名称</param> |
121 |
/// <returns>是否存在</returns> |
122 |
public static bool ColumnExists(string tableName, string columnName) |
123 |
{ |
124 |
string sql = "select count(1) from syscolumns where [id]=object_id("" + tableName + "") and [name]="" + columnName + """; |
125 |
object res = GetSingle(sql); |
126 |
if (res == null) |
127 |
{ |
128 |
return false; |
129 |
} |
130 |
return Convert.ToInt32(res) > 0; |
131 |
} |
132 |
public static int GetMaxID(string FieldName, string TableName) |
133 |
{ |
134 |
string strsql = "select max(" + FieldName + ")+1 from " + TableName; |
135 |
object obj = SQLHelper.GetSingle(strsql); |
136 |
if (obj == null) |
137 |
{ |
138 |
return 1; |
139 |
} |
140 |
else |
141 |
{ |
142 |
return int.Parse(obj.ToString()); |
143 |
} |
144 |
} |
145 |
public static bool Exists(string strSql) |
146 |
{ |
147 |
object obj = SQLHelper.GetSingle(strSql); |
148 |
int cmdresult; |
149 |
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) |
150 |
{ |
151 |
cmdresult = 0; |
152 |
} |
153 |
else |
154 |
{ |
155 |
cmdresult = int.Parse(obj.ToString()); |
156 |
} |
157 |
if (cmdresult == 0) |
158 |
{ |
159 |
return false; |
160 |
} |
161 |
else |
162 |
{ |
163 |
return true; |
164 |
} |
165 |
} |
166 |
/// <summary> |
167 |
/// 表是否存在 |
168 |
/// </summary> |
169 |
/// <param name="TableName"></param> |
170 |
/// <returns></returns> |
171 |
public static bool TabExists(string TableName) |
172 |
{ |
173 |
string strsql = "select count(*) from sysobjects where id = object_id(N"[" + TableName + "]") and OBJECTPROPERTY(id, N"IsUserTable") = 1"; |
174 |
//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N"[dbo].[" + TableName + "]") AND type in (N"U")"; |
175 |
object obj = SQLHelper.GetSingle(strsql); |
176 |
int cmdresult; |
177 |
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) |
178 |
{ |
179 |
cmdresult = 0; |
180 |
} |
181 |
else |
182 |
{ |
183 |
cmdresult = int.Parse(obj.ToString()); |
184 |
} |
185 |
if (cmdresult == 0) |
186 |
{ |
187 |
return false; |
188 |
} |
189 |
else |
190 |
{ |
191 |
return true; |
192 |
} |
193 |
} |
194 |
public static bool Exists(string strSql, params SqlParameter[] cmdParms) |
195 |
{ |
196 |
object obj = SQLHelper.GetSingle(strSql, cmdParms); |
197 |
int cmdresult; |
198 |
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) |
199 |
{ |
200 |
cmdresult = 0; |
201 |
} |
202 |
else |
203 |
{ |
204 |
cmdresult = int.Parse(obj.ToString()); |
205 |
} |
206 |
if (cmdresult == 0) |
207 |
{ |
208 |
return false; |
209 |
} |
210 |
else |
211 |
{ |
212 |
return true; |
213 |
} |
214 |
} |
215 |
#endregion |
216 |
#region 执行简单SQL语句 |
217 |
/// <summary> |
218 |
/// 执行SQL语句，返回影响的记录数 |
219 |
/// </summary> |
220 |
/// <param name="SQLString">SQL语句</param> |
221 |
/// <returns>影响的记录数</returns> |
222 |
public static int ExecuteSql(string SQLString) |
223 |
{ |
224 |
using (SqlConnection connection = new SqlConnection(connectionString)) |
225 |
{ |
226 |
using (SqlCommand cmd = new SqlCommand(SQLString, connection)) |
227 |
{ |
228 |
try |
229 |
{ |
230 |
connection.Open(); |
231 |
int rows = cmd.ExecuteNonQuery(); |
232 |
return rows; |
233 |
} |
234 |
catch (System.Data.SqlClient.SqlException e) |
235 |
{ |
236 |
connection.Close(); |
237 |
throw e; |
238 |
} |
239 |
} |
240 |
} |
241 |
} |
242 |
public static int ExecuteSqlByTime(string SQLString, int Times) |
243 |
{ |
244 |
using (SqlConnection connection = new SqlConnection(connectionString)) |
245 |
{ |
246 |
using (SqlCommand cmd = new SqlCommand(SQLString, connection)) |
247 |
{ |
248 |
try |
249 |
{ |
250 |
connection.Open(); |
251 |
cmd.CommandTimeout = Times; |
252 |
int rows = cmd.ExecuteNonQuery(); |
253 |
return rows; |
254 |
} |
255 |
catch (System.Data.SqlClient.SqlException e) |
256 |
{ |
257 |
connection.Close(); |
258 |
throw e; |
259 |
} |
260 |
} |
261 |
} |
262 |
} |
263 |
/// <summary> |
264 |
/// 执行Sql和Oracle滴混合事务 |
265 |
/// </summary> |
266 |
/// <param name="list">SQL命令行列表</param> |
267 |
/// <param name="oracleCmdSqlList">Oracle命令行列表</param> |
268 |
/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns> |
269 |
public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList) |
270 |
{ |
271 |
using (SqlConnection conn = new SqlConnection(connectionString)) |
272 |
{ |
273 |
conn.Open(); |
274 |
SqlCommand cmd = new SqlCommand(); |
275 |
cmd.Connection = conn; |
276 |
SqlTransaction tx = conn.BeginTransaction(); |
277 |
cmd.Transaction = tx; |
278 |
try |
279 |
{ |
280 |
foreach (CommandInfo myDE in list) |
281 |
{ |
282 |
string cmdText = myDE.CommandText; |
283 |
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; |
284 |
PrepareCommand(cmd, conn, tx, cmdText, cmdParms); |
285 |
if (myDE.EffentNextType == EffentNextType.SolicitationEvent) |
286 |
{ |
287 |
if (myDE.CommandText.ToLower().IndexOf("count(") == -1) |
288 |
{ |
289 |
tx.Rollback(); |
290 |
throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式"); |
291 |
//return 0; |
292 |
} |
293 |
object obj = cmd.ExecuteScalar(); |
294 |
bool isHave = false; |
295 |
if (obj == null && obj == DBNull.Value) |
296 |
{ |
297 |
isHave = false; |
298 |
} |
299 |
isHave = Convert.ToInt32(obj) > 0; |
300 |
if (isHave) |
301 |
{ |
302 |
//引发事件 |
303 |
myDE.OnSolicitationEvent(); |
304 |
} |
305 |
} |
306 |
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) |
307 |
{ |
308 |
if (myDE.CommandText.ToLower().IndexOf("count(") == -1) |
309 |
{ |
310 |
tx.Rollback(); |
311 |
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); |
312 |
//return 0; |
313 |
} |
314 |
object obj = cmd.ExecuteScalar(); |
315 |
bool isHave = false; |
316 |
if (obj == null && obj == DBNull.Value) |
317 |
{ |
318 |
isHave = false; |
319 |
} |
320 |
isHave = Convert.ToInt32(obj) > 0; |
321 |
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) |
322 |
{ |
323 |
tx.Rollback(); |
324 |
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); |
325 |
//return 0; |
326 |
} |
327 |
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) |
328 |
{ |
329 |
tx.Rollback(); |
330 |
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); |
331 |
//return 0; |
332 |
} |
333 |
continue; |
334 |
} |
335 |
int val = cmd.ExecuteNonQuery(); |
336 |
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) |
337 |
{ |
338 |
tx.Rollback(); |
339 |
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); |
340 |
//return 0; |
341 |
} |
342 |
cmd.Parameters.Clear(); |
343 |
} |
344 |
//string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC"); |
345 |
//bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList); |
346 |
//if (!res) |
347 |
//{ |
348 |
// tx.Rollback(); |
349 |
// throw new Exception("Oracle执行失败"); |
350 |
// return -1; |
351 |
//} |
352 |
tx.Commit(); |
353 |
return 1; |
354 |
} |
355 |
catch (System.Data.SqlClient.SqlException e) |
356 |
{ |
357 |
tx.Rollback(); |
358 |
throw e; |
359 |
} |
360 |
catch (Exception e) |
361 |
{ |
362 |
tx.Rollback(); |
363 |
throw e; |
364 |
} |
365 |
} |
366 |
} |
367 |
/// <summary> |
368 |
/// 执行多条SQL语句，实现数据库事务。 |
369 |
/// </summary> |
370 |
/// <param name="SQLStringList">多条SQL语句</param> |
371 |
public static int ExecuteSqlTran(List<String> SQLStringList) |
372 |
{ |
373 |
using (SqlConnection conn = new SqlConnection(connectionString)) |
374 |
{ |
375 |
conn.Open(); |
376 |
SqlCommand cmd = new SqlCommand(); |
377 |
cmd.Connection = conn; |
378 |
SqlTransaction tx = conn.BeginTransaction(); |
379 |
cmd.Transaction = tx; |
380 |
try |
381 |
{ |
382 |
int count = 0; |
383 |
for (int n = 0; n < SQLStringList.Count; n++) |
384 |
{ |
385 |
string strsql = SQLStringList[n]; |
386 |
if (strsql.Trim().Length > 1) |
387 |
{ |
388 |
cmd.CommandText = strsql; |
389 |
count += cmd.ExecuteNonQuery(); |
390 |
} |
391 |
} |
392 |
tx.Commit(); |
393 |
return count; |
394 |
} |
395 |
catch |
396 |
{ |
397 |
tx.Rollback(); |
398 |
return 0; |
399 |
} |
400 |
} |
401 |
} |
402 |
/// <summary> |
403 |
/// 执行带一个存储过程参数的的SQL语句。 |
404 |
/// </summary> |
405 |
/// <param name="SQLString">SQL语句</param> |
406 |
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> |
407 |
/// <returns>影响的记录数</returns> |
408 |
public static int ExecuteSql(string SQLString, string content) |
409 |
{ |
410 |
using (SqlConnection connection = new SqlConnection(connectionString)) |
411 |
{ |
412 |
SqlCommand cmd = new SqlCommand(SQLString, connection); |
413 |
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); |
414 |
myParameter.Value = content; |
415 |
cmd.Parameters.Add(myParameter); |
416 |
try |
417 |
{ |
418 |
connection.Open(); |
419 |
int rows = cmd.ExecuteNonQuery(); |
420 |
return rows; |
421 |
} |
422 |
catch (System.Data.SqlClient.SqlException e) |
423 |
{ |
424 |
throw e; |
425 |
} |
426 |
finally |
427 |
{ |
428 |
cmd.Dispose(); |
429 |
connection.Close(); |
430 |
} |
431 |
} |
432 |
} |
433 |
/// <summary> |
434 |
/// 执行带一个存储过程参数的的SQL语句。 |
435 |
/// </summary> |
436 |
/// <param name="SQLString">SQL语句</param> |
437 |
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> |
438 |
/// <returns>影响的记录数</returns> |
439 |
public static object ExecuteSqlGet(string SQLString, string content) |
440 |
{ |
441 |
using (SqlConnection connection = new SqlConnection(connectionString)) |
442 |
{ |
443 |
SqlCommand cmd = new SqlCommand(SQLString, connection); |
444 |
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); |
445 |
myParameter.Value = content; |
446 |
cmd.Parameters.Add(myParameter); |
447 |
try |
448 |
{ |
449 |
connection.Open(); |
450 |
object obj = cmd.ExecuteScalar(); |
451 |
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) |
452 |
{ |
453 |
return null; |
454 |
} |
455 |
else |
456 |
{ |
457 |
return obj; |
458 |
} |
459 |
} |
460 |
catch (System.Data.SqlClient.SqlException e) |
461 |
{ |
462 |
throw e; |
463 |
} |
464 |
finally |
465 |
{ |
466 |
cmd.Dispose(); |
467 |
connection.Close(); |
468 |
} |
469 |
} |
470 |
} |
471 |
/// <summary> |
472 |
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) |
473 |
/// </summary> |
474 |
/// <param name="strSQL">SQL语句</param> |
475 |
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param> |
476 |
/// <returns>影响的记录数</returns> |
477 |
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) |
478 |
{ |
479 |
using (SqlConnection connection = new SqlConnection(connectionString)) |
480 |
{ |
481 |
SqlCommand cmd = new SqlCommand(strSQL, connection); |
482 |
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); |
483 |
myParameter.Value = fs; |
484 |
cmd.Parameters.Add(myParameter); |
485 |
try |
486 |
{ |
487 |
connection.Open(); |
488 |
int rows = cmd.ExecuteNonQuery(); |
489 |
return rows; |
490 |
} |
491 |
catch (System.Data.SqlClient.SqlException e) |
492 |
{ |
493 |
throw e; |
494 |
} |
495 |
finally |
496 |
{ |
497 |
cmd.Dispose(); |
498 |
connection.Close(); |
499 |
} |
500 |
} |
501 |
} |
502 |
/// <summary> |
503 |
/// 执行一条计算查询结果语句，返回查询结果（object）。 |
504 |
/// </summary> |
505 |
/// <param name="SQLString">计算查询结果语句</param> |
506 |
/// <returns>查询结果（object）</returns> |
507 |
public static object GetSingle(string SQLString) |
508 |
{ |
509 |
using (SqlConnection connection = new SqlConnection(connectionString)) |
510 |
{ |
511 |
using (SqlCommand cmd = new SqlCommand(SQLString, connection)) |
512 |
{ |
513 |
try |
514 |
{ |
515 |
connection.Open(); |
516 |
object obj = cmd.ExecuteScalar(); |
517 |
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) |
518 |
{ |
519 |
return null; |
520 |
} |
521 |
else |
522 |
{ |
523 |
return obj; |
524 |
} |
525 |
} |
526 |
catch (System.Data.SqlClient.SqlException e) |
527 |
{ |
528 |
connection.Close(); |
529 |
throw e; |
530 |
} |
531 |
} |
532 |
} |
533 |
} |
534 |
public static object GetSingle(string SQLString, int Times) |
535 |
{ |
536 |
using (SqlConnection connection = new SqlConnection(connectionString)) |
537 |
{ |
538 |
using (SqlCommand cmd = new SqlCommand(SQLString, connection)) |
539 |
{ |
540 |
try |
541 |
{ |
542 |
connection.Open(); |
543 |
cmd.CommandTimeout = Times; |
544 |
object obj = cmd.ExecuteScalar(); |
545 |
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) |
546 |
{ |
547 |
return null; |
548 |
} |
549 |
else |
550 |
{ |
551 |
return obj; |
552 |
} |
553 |
} |
554 |
catch (System.Data.SqlClient.SqlException e) |
555 |
{ |
556 |
connection.Close(); |
557 |
throw e; |
558 |
} |
559 |
} |
560 |
} |
561 |
} |
562 |
/// <summary> |
563 |
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) |
564 |
/// </summary> |
565 |
/// <param name="strSQL">查询语句</param> |
566 |
/// <returns>SqlDataReader</returns> |
567 |
public static SqlDataReader ExecuteReader(string strSQL) |
568 |
{ |
569 |
SqlConnection connection = new SqlConnection(connectionString); |
570 |
SqlCommand cmd = new SqlCommand(strSQL, connection); |
571 |
try |
572 |
{ |
573 |
connection.Open(); |
574 |
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); |
575 |
return myReader; |
576 |
} |
577 |
catch (System.Data.SqlClient.SqlException e) |
578 |
{ |
579 |
throw e; |
580 |
} |
581 |
} |
582 |
/// <summary> |
583 |
/// 执行查询语句，返回DataSet |
584 |
/// </summary> |
585 |
/// <param name="SQLString">查询语句</param> |
586 |
/// <returns>DataSet</returns> |
587 |
public static DataSet Query(string SQLString) |
588 |
{ |
589 |
using (SqlConnection connection = new SqlConnection(connectionString)) |
590 |
{ |
591 |
DataSet ds = new DataSet(); |
592 |
try |
593 |
{ |
594 |
connection.Open(); |
595 |
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); |
596 |
command.Fill(ds, "ds"); |
597 |
} |
598 |
catch (System.Data.SqlClient.SqlException ex) |
599 |
{ |
600 |
throw new Exception(ex.Message); |
601 |
} |
602 |
return ds; |
603 |
} |
604 |
} |
605 |
/// <summary> |
606 |
/// 查询并得到数据集DataSet |
607 |
/// </summary> |
608 |
/// <param name="SQLString"></param> |
609 |
/// <param name="Times"></param> |
610 |
/// <returns></returns> |
611 |
public static DataSet Query(string SQLString, int Times) |
612 |
{ |
613 |
using (SqlConnection connection = new SqlConnection(connectionString)) |
614 |
{ |
615 |
DataSet ds = new DataSet(); |
616 |
try |
617 |
{ |
618 |
connection.Open(); |
619 |
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); |
620 |
command.SelectCommand.CommandTimeout = Times; |
621 |
command.Fill(ds, "ds"); |
622 |
} |
623 |
catch (System.Data.SqlClient.SqlException ex) |
624 |
{ |
625 |
throw new Exception(ex.Message); |
626 |
} |
627 |
return ds; |
628 |
} |
629 |
} |
630 |
#endregion |
631 |
#region 执行带参数的SQL语句 |
632 |
/// <summary> |
633 |
/// 执行SQL语句，返回影响的记录数 |
634 |
/// </summary> |
635 |
/// <param name="SQLString">SQL语句</param> |
636 |
/// <returns>影响的记录数</returns> |
637 |
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms) |
638 |
{ |
639 |
using (SqlConnection connection = new SqlConnection(connectionString)) |
640 |
{ |
641 |
using (SqlCommand cmd = new SqlCommand()) |
642 |
{ |
643 |
try |
644 |
{ |
645 |
PrepareCommand(cmd, connection, null, SQLString, cmdParms); |
646 |
int rows = cmd.ExecuteNonQuery(); |
647 |
cmd.Parameters.Clear(); |
648 |
return rows; |
649 |
} |
650 |
catch (System.Data.SqlClient.SqlException e) |
651 |
{ |
652 |
throw e; |
653 |
} |
654 |
} |
655 |
} |
656 |
} |
657 |
/// <summary> |
658 |
/// 执行多条SQL语句，实现数据库事务。 |
659 |
/// </summary> |
660 |
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> |
661 |
public static void ExecuteSqlTran(Hashtable SQLStringList) |
662 |
{ |
663 |
using (SqlConnection conn = new SqlConnection(connectionString)) |
664 |
{ |
665 |
conn.Open(); |
666 |
using (SqlTransaction trans = conn.BeginTransaction()) |
667 |
{ |
668 |
SqlCommand cmd = new SqlCommand(); |
669 |
try |
670 |
{ |
671 |
//循环 |
672 |
foreach (DictionaryEntry myDE in SQLStringList) |
673 |
{ |
674 |
string cmdText = myDE.Key.ToString(); |
675 |
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; |
676 |
PrepareCommand(cmd, conn, trans, cmdText, cmdParms); |
677 |
int val = cmd.ExecuteNonQuery(); |
678 |
cmd.Parameters.Clear(); |
679 |
} |
680 |
trans.Commit(); |
681 |
} |
682 |
catch |
683 |
{ |
684 |
trans.Rollback(); |
685 |
throw; |
686 |
} |
687 |
} |
688 |
} |
689 |
} |
690 |
/// <summary> |
691 |
/// 执行多条SQL语句，实现数据库事务。 |
692 |
/// </summary> |
693 |
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> |
694 |
public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList) |
695 |
{ |
696 |
using (SqlConnection conn = new SqlConnection(connectionString)) |
697 |
{ |
698 |
conn.Open(); |
699 |
using (SqlTransaction trans = conn.BeginTransaction()) |
700 |
{ |
701 |
SqlCommand cmd = new SqlCommand(); |
702 |
try |
703 |
{ |
704 |
int count = 0; |
705 |
//循环 |
706 |
foreach (CommandInfo myDE in cmdList) |
707 |
{ |
708 |
string cmdText = myDE.CommandText; |
709 |
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; |
710 |
PrepareCommand(cmd, conn, trans, cmdText, cmdParms); |
711 |
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) |
712 |
{ |
713 |
if (myDE.CommandText.ToLower().IndexOf("count(") == -1) |
714 |
{ |
715 |
trans.Rollback(); |
716 |
return 0; |
717 |
} |
718 |
object obj = cmd.ExecuteScalar(); |
719 |
bool isHave = false; |
720 |
if (obj == null && obj == DBNull.Value) |
721 |
{ |
722 |
isHave = false; |
723 |
} |
724 |
isHave = Convert.ToInt32(obj) > 0; |
725 |
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) |
726 |
{ |
727 |
trans.Rollback(); |
728 |
return 0; |
729 |
} |
730 |
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) |
731 |
{ |
732 |
trans.Rollback(); |
733 |
return 0; |
734 |
} |
735 |
continue; |
736 |
} |
737 |
int val = cmd.ExecuteNonQuery(); |
738 |
count += val; |
739 |
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) |
740 |
{ |
741 |
trans.Rollback(); |
742 |
return 0; |
743 |
} |
744 |
cmd.Parameters.Clear(); |
745 |
} |
746 |
trans.Commit(); |
747 |
return count; |
748 |
} |
749 |
catch |
750 |
{ |
751 |
trans.Rollback(); |
752 |
throw; |
753 |
} |
754 |
} |
755 |
} |
756 |
} |
757 |
/// <summary> |
758 |
/// 执行多条SQL语句，实现数据库事务。 |
759 |
/// </summary> |
760 |
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> |
761 |
public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList) |
762 |
{ |
763 |
using (SqlConnection conn = new SqlConnection(connectionString)) |
764 |
{ |
765 |
conn.Open(); |
766 |
using (SqlTransaction trans = conn.BeginTransaction()) |
767 |
{ |
768 |
SqlCommand cmd = new SqlCommand(); |
769 |
try |
770 |
{ |
771 |
int indentity = 0; |
772 |
//循环 |
773 |
foreach (CommandInfo myDE in SQLStringList) |
774 |
{ |
775 |
string cmdText = myDE.CommandText; |
776 |
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; |
777 |
foreach (SqlParameter q in cmdParms) |
778 |
{ |
779 |
if (q.Direction == ParameterDirection.InputOutput) |
780 |
{ |
781 |
q.Value = indentity; |
782 |
} |
783 |
} |
784 |
PrepareCommand(cmd, conn, trans, cmdText, cmdParms); |
785 |
int val = cmd.ExecuteNonQuery(); |
786 |
foreach (SqlParameter q in cmdParms) |
787 |
{ |
788 |
if (q.Direction == ParameterDirection.Output) |
789 |
{ |
790 |
indentity = Convert.ToInt32(q.Value); |
791 |
} |
792 |
} |
793 |
cmd.Parameters.Clear(); |
794 |
} |
795 |
trans.Commit(); |
796 |
} |
797 |
catch |
798 |
{ |
799 |
trans.Rollback(); |
800 |
throw; |
801 |
} |
802 |
} |
803 |
} |
804 |
} |
805 |
/// <summary> |
806 |
/// 执行多条SQL语句，实现数据库事务。 |
807 |
/// </summary> |
808 |
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> |
809 |
public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) |
810 |
{ |
811 |
using (SqlConnection conn = new SqlConnection(connectionString)) |
812 |
{ |
813 |
conn.Open(); |
814 |
using (SqlTransaction trans = conn.BeginTransaction()) |
815 |
{ |
816 |
SqlCommand cmd = new SqlCommand(); |
817 |
try |
818 |
{ |
819 |
int indentity = 0; |
820 |
//循环 |
821 |
foreach (DictionaryEntry myDE in SQLStringList) |
822 |
{ |
823 |
string cmdText = myDE.Key.ToString(); |
824 |
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; |
825 |
foreach (SqlParameter q in cmdParms) |
826 |
{ |
827 |
if (q.Direction == ParameterDirection.InputOutput) |
828 |
{ |
829 |
q.Value = indentity; |
830 |
} |
831 |
} |
832 |
PrepareCommand(cmd, conn, trans, cmdText, cmdParms); |
833 |
int val = cmd.ExecuteNonQuery(); |
834 |
foreach (SqlParameter q in cmdParms) |