P>/// <summary>通用数据库接口</P>
0002 <P>/// </summary>
0003 using System;
0004 using System.Collections;
0005 using System.Collections.Specialized;
0006 using System.Data;
0007 using System.Data.SqlClient;
0008 using System.Configuration;
0009 using System.Data.Common;
0010 using System.Collections.Generic;
0011 namespace Legalsoft.Wizard.DBUtility
0012 {
0013 public enum EffentNextType
0014 {
0015 /// <summary>
0016 /// 对其他语句无任何影响
0017 /// </summary>
0018 None,
0019 /// <summary>
0020 /// 当前语句必须为"SELECT COUNT(1) FROM .."格式，如果存在则继续执行，不存在回滚事务
0021 /// </summary>
0022 WhenHaveContine,
0023 /// <summary>
0024 /// 当前语句必须为"SELECT COUNT(1) FROM .."格式，如果不存在则继续执行，存在回滚事务
0025 /// </summary>
0026 WhenNoHaveContine,
0027 /// <summary>
0028 /// 当前语句影响到的行数必须大于0，否则回滚事务
0029 /// </summary>
0030 ExcuteEffectRows,
0031 /// <summary>
0032 /// 引发事件-当前语句必须为"SELECT COUNT(1) FROM .."格式，如果不存在则继续执行，存在回滚事务
0033 /// </summary>
0034 SolicitationEvent
0035 }
0036 public class CommandInfo
0037 {
0038 public object ShareObject = null;
0039 public object OriginalData = null;
0040 event EventHandler _solicitationEvent;
0041 public event EventHandler SolicitationEvent
0042 {
0043 add
0044 {
0045 _solicitationEvent += value;
0046 }
0047 remove
0048 {
0049 _solicitationEvent -= value;
0050 }
0051 }
0052 public void OnSolicitationEvent()
0053 {
0054 if (_solicitationEvent != null)
0055 {
0056 _solicitationEvent(this,new EventArgs());
0057 }
0058 }
0059 public string CommandText;
0060 public System.Data.Common.DbParameter[] Parameters;
0061 public EffentNextType EffentNextType = EffentNextType.None;
0062 public CommandInfo()
0063 {
0064 }
0065 public CommandInfo(string sqlText, SqlParameter[] para)
0066 {
0067 this.CommandText = sqlText;
0068 this.Parameters = para;
0069 }
0070 public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type)
0071 {
0072 this.CommandText = sqlText;
0073 this.Parameters = para;
0074 this.EffentNextType = type;
0075 }
0076 }
0077 /// <summary>
0078 /// 数据访问抽象基础类
0079 /// </summary>
0080 public abstract class SQLHelper
0081 {
0082 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
0083 public static string connectionString = "";
0084 public SQLHelper()
0085 {
0086 connectionString = @"DATA SOURCE=(local);UID=sa;PWD=111111;DATABASE=LegalSoft";
0087 }
0088 #region 公用方法
0089 /// <summary>
0090 /// 读取指定图片的二进制信息
0091 /// </summary>
0092 /// <param name="id"></param>
0093 /// <returns></returns>
0094 public object LoadImage(int id)
0095 {
0096 SqlConnection myConnection = new SqlConnection(connectionString);
0097 SqlCommand myCommand = new SqlCommand("SELECT [bImage] FROM [tblCAIPIAO_NEWS_IMG] WHERE <A href="mailto:id=@id" target=_blank>id=@id</A>",
myConnection);
0098 myCommand.CommandType = CommandType.Text;
0099 myCommand.Parameters.Add(new SqlParameter("@id", id));
0100 myConnection.Open();
0101 object result = myCommand.ExecuteScalar();
0102 try
0103 {
0104 return new System.IO.MemoryStream((byte[])result);
0105 }
0106 catch (ArgumentNullException e)
0107 {
0108 return null;
0109 }
0110 finally
0111 {
0112 myConnection.Close();
0113 }
0114 }
0115 /// <summary>
0116 /// 判断是否存在某表的某个字段
0117 /// </summary>
0118 /// <param name="tableName">表名称</param>
0119 /// <param name="columnName">列名称</param>
0120 /// <returns>是否存在</returns>
0121 public static bool ColumnExists(string tableName, string columnName)
0122 {
0123 string sql = "select count(1) from syscolumns where [id]=object_id("" + tableName + "") and [name]="" + columnName + """;
0124 object res = GetSingle(sql);
0125 if (res == null)
0126 {
0127 return false;
0128 }
0129 return Convert.ToInt32(res) > 0;
0130 }
0131 public static int GetMaxID(string FieldName, string TableName)
0132 {
0133 string strsql = "select max(" + FieldName + ")+1 from " + TableName;
0134 object obj = SQLHelper.GetSingle(strsql);
0135 if (obj == null)
0136 {
0137 return 1;
0138 }
0139 else
0140 {
0141 return int.Parse(obj.ToString());
0142 }
0143 }
0144 public static bool Exists(string strSql)
0145 {
0146 object obj = SQLHelper.GetSingle(strSql);
0147 int cmdresult;
0148 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
0149 {
0150 cmdresult = 0;
0151 }
0152 else
0153 {
0154 cmdresult = int.Parse(obj.ToString());
0155 }
0156 if (cmdresult == 0)
0157 {
0158 return false;
0159 }
0160 else
0161 {
0162 return true;
0163 }
0164 }
0165 /// <summary>
0166 /// 表是否存在
0167 /// </summary>
0168 /// <param name="TableName"></param>
0169 /// <returns></returns>
0170 public static bool TabExists(string TableName)
0171 {
0172 string strsql = "select count(*) from sysobjects where id = object_id(N"[" + TableName + "]") and OBJECTPROPERTY(id, N"IsUserTable") = 1";
0173 //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N"[dbo].[" + TableName + "]") AND type in (N"U")";
0174 object obj = SQLHelper.GetSingle(strsql);
0175 int cmdresult;
0176 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
0177 {
0178 cmdresult = 0;
0179 }
0180 else
0181 {
0182 cmdresult = int.Parse(obj.ToString());
0183 }
0184 if (cmdresult == 0)
0185 {
0186 return false;
0187 }
0188 else
0189 {
0190 return true;
0191 }
0192 }
0193 public static bool Exists(string strSql, params SqlParameter[] cmdParms)
0194 {
0195 object obj = SQLHelper.GetSingle(strSql, cmdParms);
0196 int cmdresult;
0197 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
0198 {
0199 cmdresult = 0;
0200 }
0201 else
0202 {
0203 cmdresult = int.Parse(obj.ToString());
0204 }
0205 if (cmdresult == 0)
0206 {
0207 return false;
0208 }
0209 else
0210 {
0211 return true;
0212 }
0213 }
0214 #endregion
0215 #region 执行简单SQL语句
0216 /// <summary>
0217 /// 执行SQL语句，返回影响的记录数
0218 /// </summary>
0219 /// <param name="SQLString">SQL语句</param>
0220 /// <returns>影响的记录数</returns>
0221 public static int ExecuteSql(string SQLString)
0222 {
0223 using (SqlConnection connection = new SqlConnection(connectionString))
0224 {
0225 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
0226 {
0227 try
0228 {
0229 connection.Open();
0230 int rows = cmd.ExecuteNonQuery();
0231 return rows;
0232 }
0233 catch (System.Data.SqlClient.SqlException e)
0234 {
0235 connection.Close();
0236 throw e;
0237 }
0238 }
0239 }
0240 }
0241 public static int ExecuteSqlByTime(string SQLString, int Times)
0242 {
0243 using (SqlConnection connection = new SqlConnection(connectionString))
0244 {
0245 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
0246 {
0247 try
0248 {
0249 connection.Open();
0250 cmd.CommandTimeout = Times;
0251 int rows = cmd.ExecuteNonQuery();
0252 return rows;
0253 }
0254 catch (System.Data.SqlClient.SqlException e)
0255 {
0256 connection.Close();
0257 throw e;
0258 }
0259 }
0260 }
0261 }
0262 /// <summary>
0263 /// 执行Sql和Oracle滴混合事务
0264 /// </summary>
0265 /// <param name="list">SQL命令行列表</param>
0266 /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
0267 /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
0268 public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
0269 {
0270 using (SqlConnection conn = new SqlConnection(connectionString))
0271 {
0272 conn.Open();
0273 SqlCommand cmd = new SqlCommand();
0274 cmd.Connection = conn;
0275 SqlTransaction tx = conn.BeginTransaction();
0276 cmd.Transaction = tx;
0277 try
0278 {
0279 foreach (CommandInfo myDE in list)
0280 {
0281 string cmdText = myDE.CommandText;
0282 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
0283 PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
0284 if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
0285 {
0286 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
0287 {
0288 tx.Rollback();
0289 throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
0290 //return 0;
0291 }
0292 object obj = cmd.ExecuteScalar();
0293 bool isHave = false;
0294 if (obj == null && obj == DBNull.Value)
0295 {
0296 isHave = false;
0297 }
0298 isHave = Convert.ToInt32(obj) > 0;
0299 if (isHave)
0300 {
0301 //引发事件
0302 myDE.OnSolicitationEvent();
0303 }
0304 }
0305 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
0306 {
0307 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
0308 {
0309 tx.Rollback();
0310 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
0311 //return 0;
0312 }
0313 object obj = cmd.ExecuteScalar();
0314 bool isHave = false;
0315 if (obj == null && obj == DBNull.Value)
0316 {
0317 isHave = false;
0318 }
0319 isHave = Convert.ToInt32(obj) > 0;
0320 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
0321 {
0322 tx.Rollback();
0323 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
0324 //return 0;
0325 }
0326 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
0327 {
0328 tx.Rollback();
0329 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
0330 //return 0;
0331 }
0332 continue;
0333 }
0334 int val = cmd.ExecuteNonQuery();
0335 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
0336 {
0337 tx.Rollback();
0338 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
0339 //return 0;
0340 }
0341 cmd.Parameters.Clear();
0342 }
0343 //string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
0344 //bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
0345 //if (!res)
0346 //{
0347 // tx.Rollback();
0348 // throw new Exception("Oracle执行失败");
0349 // return -1;
0350 //}
0351 tx.Commit();
0352 return 1;
0353 }
0354 catch (System.Data.SqlClient.SqlException e)
0355 {
0356 tx.Rollback();
0357 throw e;
0358 }
0359 catch (Exception e)
0360 {
0361 tx.Rollback();
0362 throw e;
0363 }
0364 }
0365 }
0366 /// <summary>
0367 /// 执行多条SQL语句，实现数据库事务。
0368 /// </summary>
0369 /// <param name="SQLStringList">多条SQL语句</param>
0370 public static int ExecuteSqlTran(List<String> SQLStringList)
0371 {
0372 using (SqlConnection conn = new SqlConnection(connectionString))
0373 {
0374 conn.Open();
0375 SqlCommand cmd = new SqlCommand();
0376 cmd.Connection = conn;
0377 SqlTransaction tx = conn.BeginTransaction();
0378 cmd.Transaction = tx;
0379 try
0380 {
0381 int count = 0;
0382 for (int n = 0; n < SQLStringList.Count; n++)
0383 {
0384 string strsql = SQLStringList[n];
0385 if (strsql.Trim().Length > 1)
0386 {
0387 cmd.CommandText = strsql;
0388 count += cmd.ExecuteNonQuery();
0389 }
0390 }
0391 tx.Commit();
0392 return count;
0393 }
0394 catch
0395 {
0396 tx.Rollback();
0397 return 0;
0398 }
0399 }
0400 }
0401 /// <summary>
0402 /// 执行带一个存储过程参数的的SQL语句。
0403 /// </summary>
0404 /// <param name="SQLString">SQL语句</param>
0405 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
0406 /// <returns>影响的记录数</returns>
0407 public static int ExecuteSql(string SQLString, string content)
0408 {
0409 using (SqlConnection connection = new SqlConnection(connectionString))
0410 {
0411 SqlCommand cmd = new SqlCommand(SQLString, connection);
0412 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
0413 myParameter.Value = content;
0414 cmd.Parameters.Add(myParameter);
0415 try
0416 {
0417 connection.Open();
0418 int rows = cmd.ExecuteNonQuery();
0419 return rows;
0420 }
0421 catch (System.Data.SqlClient.SqlException e)
0422 {
0423 throw e;
0424 }
0425 finally
0426 {
0427 cmd.Dispose();
0428 connection.Close();
0429 }
0430 }
0431 }
0432 /// <summary>
0433 /// 执行带一个存储过程参数的的SQL语句。
0434 /// </summary>
0435 /// <param name="SQLString">SQL语句</param>
0436 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
0437 /// <returns>影响的记录数</returns>
0438 public static object ExecuteSqlGet(string SQLString, string content)
0439 {
0440 using (SqlConnection connection = new SqlConnection(connectionString))
0441 {
0442 SqlCommand cmd = new SqlCommand(SQLString, connection);
0443 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
0444 myParameter.Value = content;
0445 cmd.Parameters.Add(myParameter);
0446 try
0447 {
0448 connection.Open();
0449 object obj = cmd.ExecuteScalar();
0450 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
0451 {
0452 return null;
0453 }
0454 else
0455 {
0456 return obj;
0457 }
0458 }
0459 catch (System.Data.SqlClient.SqlException e)
0460 {
0461 throw e;
0462 }
0463 finally
0464 {
0465 cmd.Dispose();
0466 connection.Close();
0467 }
0468 }
0469 }
0470 /// <summary>
0471 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
0472 /// </summary>
0473 /// <param name="strSQL">SQL语句</param>
0474 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
0475 /// <returns>影响的记录数</returns>
0476 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
0477 {
0478 using (SqlConnection connection = new SqlConnection(connectionString))
0479 {
0480 SqlCommand cmd = new SqlCommand(strSQL, connection);
0481 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
0482 myParameter.Value = fs;
0483 cmd.Parameters.Add(myParameter);
0484 try
0485 {
0486 connection.Open();
0487 int rows = cmd.ExecuteNonQuery();
0488 return rows;
0489 }
0490 catch (System.Data.SqlClient.SqlException e)
0491 {
0492 throw e;
0493 }
0494 finally
0495 {
0496 cmd.Dispose();
0497 connection.Close();
0498 }
0499 }
0500 }
0501 /// <summary>
0502 /// 执行一条计算查询结果语句，返回查询结果（object）。
0503 /// </summary>
0504 /// <param name="SQLString">计算查询结果语句</param>
0505 /// <returns>查询结果（object）</returns>
0506 public static object GetSingle(string SQLString)
0507 {
0508 using (SqlConnection connection = new SqlConnection(connectionString))
0509 {
0510 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
0511 {
0512 try
0513 {
0514 connection.Open();
0515 object obj = cmd.ExecuteScalar();
0516 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
0517 {
0518 return null;
0519 }
0520 else
0521 {
0522 return obj;
0523 }
0524 }
0525 catch (System.Data.SqlClient.SqlException e)
0526 {
0527 connection.Close();
0528 throw e;
0529 }
0530 }
0531 }
0532 }
0533 public static object GetSingle(string SQLString, int Times)
0534 {
0535 using (SqlConnection connection = new SqlConnection(connectionString))
0536 {
0537 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
0538 {
0539 try
0540 {
0541 connection.Open();
0542 cmd.CommandTimeout = Times;
0543 object obj = cmd.ExecuteScalar();
0544 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
0545 {
0546 return null;
0547 }
0548 else
0549 {
0550 return obj;
0551 }
0552 }
0553 catch (System.Data.SqlClient.SqlException e)
0554 {
0555 connection.Close();
0556 throw e;
0557 }
0558 }
0559 }
0560 }
0561 /// <summary>
0562 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
0563 /// </summary>
0564 /// <param name="strSQL">查询语句</param>
0565 /// <returns>SqlDataReader</returns>
0566 public static SqlDataReader ExecuteReader(string strSQL)
0567 {
0568 SqlConnection connection = new SqlConnection(connectionString);
0569 SqlCommand cmd = new SqlCommand(strSQL, connection);
0570 try
0571 {
0572 connection.Open();
0573 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
0574 return myReader;
0575 }
0576 catch (System.Data.SqlClient.SqlException e)
0577 {
0578 throw e;
0579 }
0580 }
0581 /// <summary>
0582 /// 执行查询语句，返回DataSet
0583 /// </summary>
0584 /// <param name="SQLString">查询语句</param>
0585 /// <returns>DataSet</returns>
0586 public static DataSet Query(string SQLString)
0587 {
0588 using (SqlConnection connection = new SqlConnection(connectionString))
0589 {
0590 DataSet ds = new DataSet();
0591 try
0592 {
0593 connection.Open();
0594 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
0595 command.Fill(ds, "ds");
0596 }
0597 catch (System.Data.SqlClient.SqlException ex)
0598 {
0599 throw new Exception(ex.Message);
0600 }
0601 return ds;
0602 }
0603 }
0604 /// <summary>
0605 /// 查询并得到数据集DataSet
0606 /// </summary>
0607 /// <param name="SQLString"></param>
0608 /// <param name="Times"></param>
0609 /// <returns></returns>
0610 public static DataSet Query(string SQLString, int Times)
0611 {
0612 using (SqlConnection connection = new SqlConnection(connectionString))
0613 {
0614 DataSet ds = new DataSet();
0615 try
0616 {
0617 connection.Open();
0618 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
0619 command.SelectCommand.CommandTimeout = Times;
0620 command.Fill(ds, "ds");
0621 }
0622 catch (System.Data.SqlClient.SqlException ex)
0623 {
0624 throw new Exception(ex.Message);
0625 }
0626 return ds;
0627 }
0628 }
0629 #endregion
0630 #region 执行带参数的SQL语句
0631 /// <summary>
0632 /// 执行SQL语句，返回影响的记录数
0633 /// </summary>
0634 /// <param name="SQLString">SQL语句</param>
0635 /// <returns>影响的记录数</returns>
0636 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
0637 {
0638 using (SqlConnection connection = new SqlConnection(connectionString))
0639 {
0640 using (SqlCommand cmd = new SqlCommand())
0641 {
0642 try
0643 {
0644 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
0645 int rows = cmd.ExecuteNonQuery();
0646 cmd.Parameters.Clear();
0647 return rows;
0648 }
0649 catch (System.Data.SqlClient.SqlException e)
0650 {
0651 throw e;
0652 }
0653 }
0654 }
0655 }
0656 /// <summary>
0657 /// 执行多条SQL语句，实现数据库事务。
0658 /// </summary>
0659 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
0660 public static void ExecuteSqlTran(Hashtable SQLStringList)
0661 {
0662 using (SqlConnection conn = new SqlConnection(connectionString))
0663 {
0664 conn.Open();
0665 using (SqlTransaction trans = conn.BeginTransaction())
0666 {
0667 SqlCommand cmd = new SqlCommand();
0668 try
0669 {
0670 //循环
0671 foreach (DictionaryEntry myDE in SQLStringList)
0672 {
0673 string cmdText = myDE.Key.ToString();
0674 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
0675 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
0676 int val = cmd.ExecuteNonQuery();
0677 cmd.Parameters.Clear();
0678 }
0679 trans.Commit();
0680 }
0681 catch
0682 {
0683 trans.Rollback();
0684 throw;
0685 }
0686 }
0687 }
0688 }
0689 /// <summary>
0690 /// 执行多条SQL语句，实现数据库事务。
0691 /// </summary>
0692 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
0693 public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
0694 {
0695 using (SqlConnection conn = new SqlConnection(connectionString))
0696 {
0697 conn.Open();
0698 using (SqlTransaction trans = conn.BeginTransaction())
0699 {
0700 SqlCommand cmd = new SqlCommand();
0701 try
0702 {
0703 int count = 0;
0704 //循环
0705 foreach (CommandInfo myDE in cmdList)
0706 {
0707 string cmdText = myDE.CommandText;
0708 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
0709 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
0710 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
0711 {
0712 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
0713 {
0714 trans.Rollback();
0715 return 0;
0716 }
0717 object obj = cmd.ExecuteScalar();
0718 bool isHave = false;
0719 if (obj == null && obj == DBNull.Value)
0720 {
0721 isHave = false;
0722 }
0723 isHave = Convert.ToInt32(obj) > 0;
0724 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
0725 {
0726 trans.Rollback();
0727 return 0;
0728 }
0729 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
0730 {
0731 trans.Rollback();
0732 return 0;
0733 }
0734 continue;
0735 }
0736 int val = cmd.ExecuteNonQuery();
0737 count += val;
0738 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
0739 {
0740 trans.Rollback();
0741 return 0;
0742 }
0743 cmd.Parameters.Clear();
0744 }
0745 trans.Commit();
0746 return count;
0747 }
0748 catch
0749 {
0750 trans.Rollback();
0751 throw;
0752 }
0753 }
0754 }
0755 }
0756 /// <summary>
0757 /// 执行多条SQL语句，实现数据库事务。
0758 /// </summary>
0759 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
0760 public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
0761 {
0762 using (SqlConnection conn = new SqlConnection(connectionString))
0763 {
0764 conn.Open();
0765 using (SqlTransaction trans = conn.BeginTransaction())
0766 {
0767 SqlCommand cmd = new SqlCommand();
0768 try
0769 {
0770 int indentity = 0;
0771 //循环
0772 foreach (CommandInfo myDE in SQLStringList)
0773 {
0774 string cmdText = myDE.CommandText;
0775 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
0776 foreach (SqlParameter q in cmdParms)
0777 {
0778 if (q.Direction == ParameterDirection.InputOutput)
0779 {
0780 q.Value = indentity;
0781 }
0782 }
0783 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
0784 int val = cmd.ExecuteNonQuery();
0785 foreach (SqlParameter q in cmdParms)
0786 {
0787 if (q.Direction == ParameterDirection.Output)
0788 {
0789 indentity = Convert.ToInt32(q.Value);
0790 }
0791 }
0792 cmd.Parameters.Clear();
0793 }
0794 trans.Commit();
0795 }
0796 catch
0797 {
0798 trans.Rollback();
0799 throw;
0800 }
0801 }
0802 }
0803 }
0804 /// <summary>
0805 /// 执行多条SQL语句，实现数据库事务。
0806 /// </summary>
0807 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
0808 public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
0809 {
0810 using (SqlConnection conn = new SqlConnection(connectionString))
0811 {
0812 conn.Open();
0813 using (SqlTransaction trans = conn.BeginTransaction())
0814 {
0815 SqlCommand cmd = new SqlCommand();
0816 try
0817 {
0818 int indentity = 0;
0819 //循环
0820 foreach (DictionaryEntry myDE in SQLStringList)
0821 {
0822 string cmdText = myDE.Key.ToString();
0823 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
0824 foreach (SqlParameter q in cmdParms)
0825 {
0826 if (q.Direction == ParameterDirection.InputOutput)
0827 {
0828 q.Value = indentity;
0829 }
0830 }
0831 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
0832 int val = cmd.ExecuteNonQuery();
0833 foreach (SqlParameter q in cmdParms)
0834 {
0835 if (q.Direction == ParameterDirection.Output)
0836 {
0837 indentity = Convert.ToInt32(q.Value);
0838 }
0839 }
0840 cmd.Parameters.Clear();
0841 }
0842 trans.Commit();
0843 }
0844 catch
0845 {
0846 trans.Rollback();
0847 throw;
0848 }
0849 }
0850 }
0851 }
0852 /// <summary>
0853 /// 执行一条计算查询结果语句，返回查询结果（object）。
0854 /// </summary>
0855 /// <param name="SQLString">计算查询结果语句</param>
0856 /// <returns>查询结果（object）</returns>
0857 public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
0858 {
0859 using (SqlConnection connection = new SqlConnection(connectionString))
0860 {
0861 using (SqlCommand cmd = new SqlCommand())
0862 {
0863 try
0864 {
0865 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
0866 object obj = cmd.ExecuteScalar();
0867 cmd.Parameters.Clear();
0868 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
0869 {
0870 return null;
0871 }
0872 else
0873 {
0874 return obj;
0875 }
0876 }
0877 catch (System.Data.SqlClient.SqlException e)
0878 {
0879 throw e;
0880 }
0881 }
0882 }
0883 }
0884 /// <summary>
0885 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
0886 /// </summary>
0887 /// <param name="strSQL">查询语句</param>
0888 /// <returns>SqlDataReader</returns>
0889 public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
0890 {
0891 SqlConnection connection = new SqlConnection(connectionString);
0892 SqlCommand cmd = new SqlCommand();
0893 try
0894 {
0895 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
0896 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
0897 cmd.Parameters.Clear();
0898 return myReader;
0899 }
0900 catch (System.Data.SqlClient.SqlException e)
0901 {
0902 throw e;
0903 }
0904 //finally
0905 //{
0906 //cmd.Dispose();
0907 //connection.Close();
0908 //}
0909 }
0910 /// <summary>
0911 /// 执行查询语句，返回DataSet
0912 /// </summary>
0913 /// <param name="SQLString">查询语句</param>
0914 /// <returns>DataSet</returns>
0915 public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
0916 {
0917 using (SqlConnection connection = new SqlConnection(connectionString))
0918 {
0919 SqlCommand cmd = new SqlCommand();
0920 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
0921 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
0922 {
0923 DataSet ds = new DataSet();
0924 try
0925 {
0926 da.Fill(ds, "ds");
0927 cmd.Parameters.Clear();
0928 }
0929 catch (System.Data.SqlClient.SqlException ex)
0930 {
0931 throw new Exception(ex.Message);
0932 }
0933 return ds;
0934 }
0935 }
0936 }
0937 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
0938 {
0939 if (conn.State != ConnectionState.Open)
0940 conn.Open();
0941 cmd.Connection = conn;
0942 cmd.CommandText = cmdText;
0943 if (trans != null)
0944 cmd.Transaction = trans;
0945 cmd.CommandType = CommandType.Text;//cmdType;
0946 if (cmdParms != null)
0947 {
0948 foreach (SqlParameter parameter in cmdParms)
0949 {
0950 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
0951 (parameter.Value == null))
0952 {
0953 parameter.Value = DBNull.Value;
0954 }
0955 cmd.Parameters.Add(parameter);
0956 }
0957 }
0958 }
0959 #endregion
0960 #region 存储过程操作
0961 /// <summary>
0962 /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
0963 /// </summary>
0964 /// <param name="storedProcName">存储过程名</param>
0965 /// <param name="parameters">存储过程参数</param>
0966 /// <returns>SqlDataReader</returns>
0967 public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
0968 {
0969 SqlConnection connection = new SqlConnection(connectionString);
0970 SqlDataReader returnReader;
0971 connection.Open();
0972 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
0973 command.CommandType = CommandType.StoredProcedure;
0974 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
0975 return returnReader;
0976 }
0977 /// <summary>
0978 /// 执行存储过程
0979 /// </summary>
0980 /// <param name="storedProcName">存储过程名</param>
0981 /// <param name="parameters">存储过程参数</param>
0982 /// <param name="tableName">DataSet结果中的表名</param>
0983 /// <returns>DataSet</returns>
0984 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
0985 {
0986 using (SqlConnection connection = new SqlConnection(connectionString))
0987 {
0988 DataSet dataSet = new DataSet();
0989 connection.Open();
0990 SqlDataAdapter sqlDA = new SqlDataAdapter();
0991 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
0992 sqlDA.Fill(dataSet, tableName);
0993 connection.Close();
0994 return dataSet;
0995 }
0996 }
0997 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
0998 {
0999 using (SqlConnection connection = new SqlConnection(connectionString))
1000 {
1001 DataSet dataSet = new DataSet();
1002 connection.Open();
1003 SqlDataAdapter sqlDA = new SqlDataAdapter();
1004 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
1005 sqlDA.SelectCommand.CommandTimeout = Times;
1006 sqlDA.Fill(dataSet, tableName);
1007 connection.Close();
1008 return dataSet;
1009 }
1010 }
1011 /// <summary>
1012 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
1013 /// </summary>
1014 /// <param name="connection">数据库连接</param>
1015 /// <param name="storedProcName">存储过程名</param>
1016 /// <param name="parameters">存储过程参数</param>
1017 /// <returns>SqlCommand</returns>
1018 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
1019 {
1020 SqlCommand command = new SqlCommand(storedProcName, connection);
1021 command.CommandType = CommandType.StoredProcedure;
1022 foreach (SqlParameter parameter in parameters)
1023 {
1024 if (parameter != null)
1025 {
1026 // 检查未分配值的输出参数,将其分配以DBNull.Value.
1027 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
1028 (parameter.Value == null))
1029 {
1030 parameter.Value = DBNull.Value;
1031 }
1032 command.Parameters.Add(parameter);
1033 }
1034 }
1035 return command;
1036 }
1037 /// <summary>
1038 /// 执行存储过程，返回影响的行数
1039 /// </summary>
1040 /// <param name="storedProcName">存储过程名</param>
1041 /// <param name="parameters">存储过程参数</param>
1042 /// <param name="rowsAffected">影响的行数</param>
1043 /// <returns></returns>
1044 public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
1045 {
1046 using (SqlConnection connection = new SqlConnection(connectionString))
1047 {
1048 int result;
1049 connection.Open();
1050 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
1051 rowsAffected = command.ExecuteNonQuery();
1052 result = (int)command.Parameters["ReturnValue"].Value;
1053 //Connection.Close();
1054 return result;
1055 }
1056 }
1057 /// <summary>
1058 /// 创建 SqlCommand 对象实例(用来返回一个整数值)
1059 /// </summary>
1060 /// <param name="storedProcName">存储过程名</param>
1061 /// <param name="parameters">存储过程参数</param>
1062 /// <returns>SqlCommand 对象实例</returns>
1063 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
1064 {
1065 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
1066 command.Parameters.Add(new SqlParameter("ReturnValue",
1067 SqlDbType.Int, 4, ParameterDirection.ReturnValue,
1068 false, 0, 0, string.Empty, DataRowVersion.Default, null));
1069 return command;
1070 }
1071 #endregion
1072 }
1073 }</P>
