关于SqlServer的DBHelper类以及数据分页

前端：

1  <My:AspNetPager class="arPage" PageSize="20" UrlPaging="true" runat="server" ID="pgServer"
2                 CustomInfoHTML="共%PageCount%页[%RecordCount%条]记录<span>|</span>每页<em>%PageSize%</em>条<span>|</span>当前第<em>%CurrentPageIndex%</em>页"
3                 FirstPageText="首页" LastPageText="尾页" NextPageText="下一页" PrevPageText="上一页" ShowCustomInfoSection="Left"
4                 Height="18px" OnPageChanged="pgServer_PageChanged">
5             </My:AspNetPager>

后台方法：

 1 using Comm;
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Data;
 5 using System.Linq;
 6 using System.Web;
 7 using System.Web.UI;
 8 using System.Web.UI.WebControls;
 9 
10 public partial class Web_Admin_Sutestatus_statue_list : AdminPageBase
11 {
12     Maticsoft.BLL.tb_SiteStats tbSiteStatesBLL = new Maticsoft.BLL.tb_SiteStats();
13     protected void Page_Load(object sender, EventArgs e)
14     {
15         if (!IsPostBack)
16         {
17             pgServer.RecordCount = tbSiteStatesBLL.GetRecordCount("");
18             //loadData();
19         }
20     }
21     /// <summary>
22     /// 加载数据
23     /// </summary>
24     public void loadData(string str)
25     {
26         DataSet data = tbSiteStatesBLL.GetListByPage(str, " VisitTime desc ", (pgServer.CurrentPageIndex - 1) * pgServer.PageSize + 1, (pgServer.CurrentPageIndex - 1) * pgServer.PageSize + pgServer.PageSize);
27         rpList.DataSource = data;
28         rpList.DataBind();
29     }
30 
31     /// <summary>
32     /// 删除
33     /// </summary>
34     /// <param name="sender"></param>
35     /// <param name="e"></param>
36     protected void But_Delete_Click(object sender, EventArgs e)
37     {
38         string ids = Request.Form["checkboxs"];
39         if (string.IsNullOrEmpty(ids))
40         {
41             return;
42         }
43 
44         tbSiteStatesBLL.DeleteList(ids);
45         base.AddLog(3, "管理uc_id：" + ids);
46         loadData("");
47     }
48     protected void pgServer_PageChanged(object src, EventArgs e)
49     {
50         loadData("");
51     }
52 
53     /// <summary>
54     /// 导出
55     /// </summary>
56     /// <param name="sender"></param>
57     /// <param name="e"></param>
58     //protected void But_Excel_Click(object sender, EventArgs e)
59     //{
60     //    string[] fileName = { "State_title", "Last_url", "State_url", "IsNewUser", "State_time", "IpAddress", "ExtStr1", "VisitTime", "VisitWay", "KeyWords", "UserAction", "Remark" };
61     //    string[] title = { "", "", "", "", "", "", "", "", "", "", "", "" };
62     //    DataToExcel.Export(fileName, title, "tb_SiteStats", "列表_" + DateTime.Now.ToString("yyy-MM-dd"), getStrWhere(), getSort());
63     //}
64 
65     protected void btnSearch_Click(object sender, EventArgs e)
66     {
67         string strWhere = " 1=1 ";
68         if (!string.IsNullOrEmpty(txt_Title.Text.Trim()))
69         {
70             strWhere += " and State_title like '%" + txt_Title.Text.Trim() + "%'";
71         }
72         pgServer.RecordCount = tbSiteStatesBLL.GetRecordCount(strWhere);
73         loadData(strWhere);
74     }
75 }

DAL方法：

 1 /// <summary>
 2         /// 分页获取数据列表
 3         /// </summary>
 4         public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
 5         {
 6             StringBuilder strSql=new StringBuilder();
 7             strSql.Append("SELECT * FROM ( ");
 8             strSql.Append(" SELECT ROW_NUMBER() OVER (");
 9             if (!string.IsNullOrEmpty(orderby.Trim()))
10             {
11                 strSql.Append("order by T." + orderby );
12             }
13             else
14             {
15                 strSql.Append("order by T.ID desc");
16             }
17             strSql.Append(")AS Row, T.*  from tb_SiteStats T ");
18             if (!string.IsNullOrEmpty(strWhere.Trim()))
19             {
20                 strSql.Append(" WHERE " + strWhere);
21             }
22             strSql.Append(" ) TT");
23             strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
24             return DbHelperSQLServer.Query(strSql.ToString());
25         }

DBHelperSqlServer类：

   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 using Maticsoft.DBUtility;
  10 
  11 namespace DAL
  12 {
  13     public abstract class DbHelperSQLServer
  14     {
  15         public static string connectionString = "Server=10.72.25.82;User ID=gwbnshweb;Password=gwbnsh@123;database=GwbnShWeb;Connection Reset=FALSE";
  16         //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.        
  17         //public static string connectionString = ConfigurationManager.ConnectionStrings["SqlConncectionString"].ConnectionString;
  18         public DbHelperSQLServer() { }
  19 
  20         #region 公用方法
  21         /// <summary>
  22         /// 判断是否存在某表的某个字段
  23         /// </summary>
  24         /// <param name="tableName">表名称</param>
  25         /// <param name="columnName">列名称</param>
  26         /// <returns>是否存在</returns>
  27         public static bool ColumnExists(string tableName, string columnName)
  28         {
  29             string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
  30             object res = GetSingle(sql);
  31             if (res == null)
  32             {
  33                 return false;
  34             }
  35             return Convert.ToInt32(res) > 0;
  36         }
  37         public static int GetMinID(string FieldName, string TableName)
  38         {
  39             string strsql = "select min(" + FieldName + ") from " + TableName;
  40             object obj = DbHelperSQLServer.GetSingle(strsql);
  41             if (obj == null)
  42             {
  43                 return 0;
  44             }
  45             else
  46             {
  47                 return int.Parse(obj.ToString());
  48             }
  49         }
  50         public static int GetMaxID(string FieldName, string TableName)
  51         {
  52             string strsql = "select max(" + FieldName + ")+1 from " + TableName;
  53             object obj = DbHelperSQLServer.GetSingle(strsql);
  54             if (obj == null)
  55             {
  56                 return 1;
  57             }
  58             else
  59             {
  60                 return int.Parse(obj.ToString());
  61             }
  62         }
  63         public static bool Exists(string strSql)
  64         {
  65             object obj = DbHelperSQLServer.GetSingle(strSql);
  66             int cmdresult;
  67             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
  68             {
  69                 cmdresult = 0;
  70             }
  71             else
  72             {
  73                 cmdresult = int.Parse(obj.ToString());
  74             }
  75             if (cmdresult == 0)
  76             {
  77                 return false;
  78             }
  79             else
  80             {
  81                 return true;
  82             }
  83         }
  84         /// <summary>
  85         /// 表是否存在
  86         /// </summary>
  87         /// <param name="TableName"></param>
  88         /// <returns></returns>
  89         public static bool TabExists(string TableName)
  90         {
  91             string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
  92             //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
  93             object obj = DbHelperSQLServer.GetSingle(strsql);
  94             int cmdresult;
  95             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
  96             {
  97                 cmdresult = 0;
  98             }
  99             else
 100             {
 101                 cmdresult = int.Parse(obj.ToString());
 102             }
 103             if (cmdresult == 0)
 104             {
 105                 return false;
 106             }
 107             else
 108             {
 109                 return true;
 110             }
 111         }
 112         public static bool Exists(string strSql, params SqlParameter[] cmdParms)
 113         {
 114             object obj = DbHelperSQLServer.GetSingle(strSql, cmdParms);
 115             int cmdresult;
 116             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 117             {
 118                 cmdresult = 0;
 119             }
 120             else
 121             {
 122                 cmdresult = int.Parse(obj.ToString());
 123             }
 124             if (cmdresult == 0)
 125             {
 126                 return false;
 127             }
 128             else
 129             {
 130                 return true;
 131             }
 132         }
 133         #endregion
 134 
 135         #region  执行简单SQL语句
 136 
 137         /// <summary>
 138         /// 执行SQL语句，返回影响的记录数
 139         /// </summary>
 140         /// <param name="SQLString">SQL语句</param>
 141         /// <returns>影响的记录数</returns>
 142         public static int ExecuteSql(string SQLString)
 143         {
 144             using (SqlConnection connection = new SqlConnection(connectionString))
 145             {
 146                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
 147                 {
 148                     try
 149                     {
 150                         connection.Open();
 151                         int rows = cmd.ExecuteNonQuery();
 152                         return rows;
 153                     }
 154                     catch (System.Data.SqlClient.SqlException e)
 155                     {
 156                         connection.Close();
 157                         throw e;
 158                     }
 159                 }
 160             }
 161         }
 162 
 163         /// <param name="connection">SqlConnection对象</param>
 164         /// <param name="trans">SqlTransaction事件</param>
 165         /// <param name="SQLString">SQL语句</param>
 166         /// <returns>影响的记录数</returns>
 167         public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString)
 168         {
 169             using (SqlCommand cmd = new SqlCommand(SQLString, connection))
 170             {
 171                 try
 172                 {
 173                     cmd.Connection = connection;
 174                     cmd.Transaction = trans;
 175                     int rows = cmd.ExecuteNonQuery();
 176                     return rows;
 177                 }
 178                 catch (System.Data.SqlClient.SqlException e)
 179                 {
 180                     //trans.Rollback();
 181                     throw e;
 182                 }
 183             }
 184         }
 185 
 186         public static int ExecuteSqlByTime(string SQLString, int Times)
 187         {
 188             using (SqlConnection connection = new SqlConnection(connectionString))
 189             {
 190                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
 191                 {
 192                     try
 193                     {
 194                         connection.Open();
 195                         cmd.CommandTimeout = Times;
 196                         int rows = cmd.ExecuteNonQuery();
 197                         return rows;
 198                     }
 199                     catch (System.Data.SqlClient.SqlException e)
 200                     {
 201                         connection.Close();
 202                         throw e;
 203                     }
 204                 }
 205             }
 206         }
 207 
 208         /// <summary>
 209         /// 执行Sql和Oracle滴混合事务
 210         /// </summary>
 211         /// <param name="list">SQL命令行列表</param>
 212         /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
 213         /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
 214         public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
 215         {
 216             using (SqlConnection conn = new SqlConnection(connectionString))
 217             {
 218                 conn.Open();
 219                 SqlCommand cmd = new SqlCommand();
 220                 cmd.Connection = conn;
 221                 SqlTransaction tx = conn.BeginTransaction();
 222                 cmd.Transaction = tx;
 223                 try
 224                 {
 225                     foreach (CommandInfo myDE in list)
 226                     {
 227                         string cmdText = myDE.CommandText;
 228                         SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
 229                         PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
 230                         if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
 231                         {
 232                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
 233                             {
 234                                 tx.Rollback();
 235                                 throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
 236                                 //return 0;
 237                             }
 238 
 239                             object obj = cmd.ExecuteScalar();
 240                             bool isHave = false;
 241                             if (obj == null && obj == DBNull.Value)
 242                             {
 243                                 isHave = false;
 244                             }
 245                             isHave = Convert.ToInt32(obj) > 0;
 246                             if (isHave)
 247                             {
 248                                 //引发事件
 249                                 myDE.OnSolicitationEvent();
 250                             }
 251                         }
 252                         if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
 253                         {
 254                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
 255                             {
 256                                 tx.Rollback();
 257                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
 258                                 //return 0;
 259                             }
 260 
 261                             object obj = cmd.ExecuteScalar();
 262                             bool isHave = false;
 263                             if (obj == null && obj == DBNull.Value)
 264                             {
 265                                 isHave = false;
 266                             }
 267                             isHave = Convert.ToInt32(obj) > 0;
 268 
 269                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
 270                             {
 271                                 tx.Rollback();
 272                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
 273                                 //return 0;
 274                             }
 275                             if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
 276                             {
 277                                 tx.Rollback();
 278                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
 279                                 //return 0;
 280                             }
 281                             continue;
 282                         }
 283                         int val = cmd.ExecuteNonQuery();
 284                         if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
 285                         {
 286                             tx.Rollback();
 287                             throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
 288                             //return 0;
 289                         }
 290                         cmd.Parameters.Clear();
 291                     }
 292                     //string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
 293                     //bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
 294                     //if (!res)
 295                     //{
 296                     //    tx.Rollback();
 297                     //    throw new Exception("Oracle执行失败");
 298                     // return -1;
 299                     //}
 300                     tx.Commit();
 301                     return 1;
 302                 }
 303                 catch (System.Data.SqlClient.SqlException e)
 304                 {
 305                     tx.Rollback();
 306                     throw e;
 307                 }
 308                 catch (Exception e)
 309                 {
 310                     tx.Rollback();
 311                     throw e;
 312                 }
 313             }
 314         }
 315         /// <summary>
 316         /// 执行多条SQL语句，实现数据库事务。
 317         /// </summary>
 318         /// <param name="SQLStringList">多条SQL语句</param>        
 319         public static int ExecuteSqlTran(List<String> SQLStringList)
 320         {
 321             using (SqlConnection conn = new SqlConnection(connectionString))
 322             {
 323                 conn.Open();
 324                 SqlCommand cmd = new SqlCommand();
 325                 cmd.Connection = conn;
 326                 SqlTransaction tx = conn.BeginTransaction();
 327                 cmd.Transaction = tx;
 328                 try
 329                 {
 330                     int count = 0;
 331                     for (int n = 0; n < SQLStringList.Count; n++)
 332                     {
 333                         string strsql = SQLStringList[n];
 334                         if (strsql.Trim().Length > 1)
 335                         {
 336                             cmd.CommandText = strsql;
 337                             count += cmd.ExecuteNonQuery();
 338                         }
 339                     }
 340                     tx.Commit();
 341                     return count;
 342                 }
 343                 catch
 344                 {
 345                     tx.Rollback();
 346                     return 0;
 347                 }
 348             }
 349         }
 350         /// <summary>
 351         /// 执行带一个存储过程参数的的SQL语句。
 352         /// </summary>
 353         /// <param name="SQLString">SQL语句</param>
 354         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
 355         /// <returns>影响的记录数</returns>
 356         public static int ExecuteSql(string SQLString, string content)
 357         {
 358             using (SqlConnection connection = new SqlConnection(connectionString))
 359             {
 360                 SqlCommand cmd = new SqlCommand(SQLString, connection);
 361                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
 362                 myParameter.Value = content;
 363                 cmd.Parameters.Add(myParameter);
 364                 try
 365                 {
 366                     connection.Open();
 367                     int rows = cmd.ExecuteNonQuery();
 368                     return rows;
 369                 }
 370                 catch (System.Data.SqlClient.SqlException e)
 371                 {
 372                     throw e;
 373                 }
 374                 finally
 375                 {
 376                     cmd.Dispose();
 377                     connection.Close();
 378                 }
 379             }
 380         }
 381         /// <summary>
 382         /// 执行带一个存储过程参数的的SQL语句。
 383         /// </summary>
 384         /// <param name="SQLString">SQL语句</param>
 385         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
 386         /// <returns>影响的记录数</returns>
 387         public static object ExecuteSqlGet(string SQLString, string content)
 388         {
 389             using (SqlConnection connection = new SqlConnection(connectionString))
 390             {
 391                 SqlCommand cmd = new SqlCommand(SQLString, connection);
 392                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
 393                 myParameter.Value = content;
 394                 cmd.Parameters.Add(myParameter);
 395                 try
 396                 {
 397                     connection.Open();
 398                     object obj = cmd.ExecuteScalar();
 399                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 400                     {
 401                         return null;
 402                     }
 403                     else
 404                     {
 405                         return obj;
 406                     }
 407                 }
 408                 catch (System.Data.SqlClient.SqlException e)
 409                 {
 410                     throw e;
 411                 }
 412                 finally
 413                 {
 414                     cmd.Dispose();
 415                     connection.Close();
 416                 }
 417             }
 418         }
 419         /// <summary>
 420         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
 421         /// </summary>
 422         /// <param name="strSQL">SQL语句</param>
 423         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
 424         /// <returns>影响的记录数</returns>
 425         public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
 426         {
 427             using (SqlConnection connection = new SqlConnection(connectionString))
 428             {
 429                 SqlCommand cmd = new SqlCommand(strSQL, connection);
 430                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
 431                 myParameter.Value = fs;
 432                 cmd.Parameters.Add(myParameter);
 433                 try
 434                 {
 435                     connection.Open();
 436                     int rows = cmd.ExecuteNonQuery();
 437                     return rows;
 438                 }
 439                 catch (System.Data.SqlClient.SqlException e)
 440                 {
 441                     throw e;
 442                 }
 443                 finally
 444                 {
 445                     cmd.Dispose();
 446                     connection.Close();
 447                 }
 448             }
 449         }
 450 
 451         /// <summary>
 452         /// 执行一条计算查询结果语句，返回查询结果（object）。
 453         /// </summary>
 454         /// <param name="SQLString">计算查询结果语句</param>
 455         /// <returns>查询结果（object）</returns>
 456         public static object GetSingle(string SQLString)
 457         {
 458             using (SqlConnection connection = new SqlConnection(connectionString))
 459             {
 460                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
 461                 {
 462                     try
 463                     {
 464                         connection.Open();
 465                         object obj = cmd.ExecuteScalar();
 466                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 467                         {
 468                             return null;
 469                         }
 470                         else
 471                         {
 472                             return obj;
 473                         }
 474                     }
 475                     catch (System.Data.SqlClient.SqlException e)
 476                     {
 477                         connection.Close();
 478                         throw e;
 479                     }
 480                 }
 481             }
 482         }
 483         public static object GetSingle(string SQLString, int Times)
 484         {
 485             using (SqlConnection connection = new SqlConnection(connectionString))
 486             {
 487                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
 488                 {
 489                     try
 490                     {
 491                         connection.Open();
 492                         cmd.CommandTimeout = Times;
 493                         object obj = cmd.ExecuteScalar();
 494                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 495                         {
 496                             return null;
 497                         }
 498                         else
 499                         {
 500                             return obj;
 501                         }
 502                     }
 503                     catch (System.Data.SqlClient.SqlException e)
 504                     {
 505                         connection.Close();
 506                         throw e;
 507                     }
 508                 }
 509             }
 510         }
 511         /// <summary>
 512         /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
 513         /// </summary>
 514         /// <param name="strSQL">查询语句</param>
 515         /// <returns>SqlDataReader</returns>
 516         public static SqlDataReader ExecuteReader(string strSQL)
 517         {
 518             SqlConnection connection = new SqlConnection(connectionString);
 519             SqlCommand cmd = new SqlCommand(strSQL, connection);
 520             try
 521             {
 522                 connection.Open();
 523                 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 524                 return myReader;
 525             }
 526             catch (System.Data.SqlClient.SqlException e)
 527             {
 528                 throw e;
 529             }
 530 
 531         }
 532         /// <summary>
 533         /// 执行查询语句，返回DataSet
 534         /// </summary>
 535         /// <param name="SQLString">查询语句</param>
 536         /// <returns>DataSet</returns>
 537         public static DataSet Query(string SQLString)
 538         {
 539 
 540             using (SqlConnection connection = new SqlConnection(connectionString))
 541             {
 542                 DataSet ds = new DataSet();
 543                 try
 544                 {
 545                     connection.Open();
 546                     SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
 547                     command.Fill(ds, "ds");
 548                 }
 549                 catch (System.Data.SqlClient.SqlException ex)
 550                 {
 551                     throw new Exception(ex.Message);
 552                 }
 553                 return ds;
 554             }
 555 
 556         }
 557         public static DataSet Query(string SQLString, int Times)
 558         {
 559             using (SqlConnection connection = new SqlConnection(connectionString))
 560             {
 561                 DataSet ds = new DataSet();
 562                 try
 563                 {
 564                     connection.Open();
 565                     SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
 566                     command.SelectCommand.CommandTimeout = Times;
 567                     command.Fill(ds, "ds");
 568                 }
 569                 catch (System.Data.SqlClient.SqlException ex)
 570                 {
 571                     throw new Exception(ex.Message);
 572                 }
 573                 return ds;
 574             }
 575         }
 576 
 577         /// <summary>
 578         /// 2012-2-21新增重载，执行查询语句，返回DataSet
 579         /// </summary>
 580         /// <param name="connection">SqlConnection对象</param>
 581         /// <param name="trans">SqlTransaction事务</param>
 582         /// <param name="SQLString">SQL语句</param>
 583         /// <returns>DataSet</returns>
 584         public static DataSet Query(SqlConnection connection, SqlTransaction trans, string SQLString)
 585         {
 586             DataSet ds = new DataSet();
 587             try
 588             {
 589                 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
 590                 command.SelectCommand.Transaction = trans;
 591                 command.Fill(ds, "ds");
 592             }
 593             catch (System.Data.SqlClient.SqlException ex)
 594             {
 595                 throw new Exception(ex.Message);
 596             }
 597             return ds;
 598 
 599         }
 600 
 601 
 602         #endregion
 603 
 604         #region 执行带参数的SQL语句
 605 
 606         /// <summary>
 607         /// 执行SQL语句，返回影响的记录数
 608         /// </summary>
 609         /// <param name="SQLString">SQL语句</param>
 610         /// <returns>影响的记录数</returns>
 611         public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
 612         {
 613             using (SqlConnection connection = new SqlConnection(connectionString))
 614             {
 615                 using (SqlCommand cmd = new SqlCommand())
 616                 {
 617                     try
 618                     {
 619                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
 620                         int rows = cmd.ExecuteNonQuery();
 621                         cmd.Parameters.Clear();
 622                         return rows;
 623                     }
 624                     catch (System.Data.SqlClient.SqlException e)
 625                     {
 626                         throw e;
 627                     }
 628                 }
 629             }
 630         }
 631         /// <param name="connection">SqlConnection对象</param>
 632         /// <param name="trans">SqlTransaction对象</param>
 633         /// <param name="SQLString">SQL语句</param>
 634         /// <returns>影响的记录数</returns>
 635         public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
 636         {
 637             using (SqlCommand cmd = new SqlCommand())
 638             {
 639                 try
 640                 {
 641                     PrepareCommand(cmd, connection, trans, SQLString, cmdParms);
 642                     int rows = cmd.ExecuteNonQuery();
 643                     cmd.Parameters.Clear();
 644                     return rows;
 645                 }
 646                 catch (System.Data.SqlClient.SqlException e)
 647                 {
 648                     //trans.Rollback();
 649                     throw e;
 650                 }
 651             }
 652         }
 653 
 654         /// <summary>
 655         /// 执行多条SQL语句，实现数据库事务。
 656         /// </summary>
 657         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
 658         public static void ExecuteSqlTran(Hashtable SQLStringList)
 659         {
 660             using (SqlConnection conn = new SqlConnection(connectionString))
 661             {
 662                 conn.Open();
 663                 using (SqlTransaction trans = conn.BeginTransaction())
 664                 {
 665                     SqlCommand cmd = new SqlCommand();
 666                     try
 667                     {
 668                         //循环
 669                         foreach (DictionaryEntry myDE in SQLStringList)
 670                         {
 671                             string cmdText = myDE.Key.ToString();
 672                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
 673                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
 674                             int val = cmd.ExecuteNonQuery();
 675                             cmd.Parameters.Clear();
 676                         }
 677                         trans.Commit();
 678                     }
 679                     catch
 680                     {
 681                         trans.Rollback();
 682                         throw;
 683                     }
 684                 }
 685             }
 686         }
 687         /// <summary>
 688         /// 执行多条SQL语句，实现数据库事务。
 689         /// </summary>
 690         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
 691         public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
 692         {
 693             using (SqlConnection conn = new SqlConnection(connectionString))
 694             {
 695                 conn.Open();
 696                 using (SqlTransaction trans = conn.BeginTransaction())
 697                 {
 698                     SqlCommand cmd = new SqlCommand();
 699                     try
 700                     {
 701                         int count = 0;
 702                         //循环
 703                         foreach (CommandInfo myDE in cmdList)
 704                         {
 705                             string cmdText = myDE.CommandText;
 706                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
 707                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
 708 
 709                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
 710                             {
 711                                 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
 712                                 {
 713                                     trans.Rollback();
 714                                     return 0;
 715                                 }
 716 
 717                                 object obj = cmd.ExecuteScalar();
 718                                 bool isHave = false;
 719                                 if (obj == null && obj == DBNull.Value)
 720                                 {
 721                                     isHave = false;
 722                                 }
 723                                 isHave = Convert.ToInt32(obj) > 0;
 724 
 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)
 835                             {
 836                                 if (q.Direction == ParameterDirection.Output)
 837                                 {
 838                                     indentity = Convert.ToInt32(q.Value);
 839                                 }
 840                             }
 841                             cmd.Parameters.Clear();
 842                         }
 843                         trans.Commit();
 844                     }
 845                     catch
 846                     {
 847                         trans.Rollback();
 848                         throw;
 849                     }
 850                 }
 851             }
 852         }
 853         /// <summary>
 854         /// 执行一条计算查询结果语句，返回查询结果（object）。
 855         /// </summary>
 856         /// <param name="SQLString">计算查询结果语句</param>
 857         /// <returns>查询结果（object）</returns>
 858         public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
 859         {
 860             using (SqlConnection connection = new SqlConnection(connectionString))
 861             {
 862                 using (SqlCommand cmd = new SqlCommand())
 863                 {
 864                     try
 865                     {
 866                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
 867                         object obj = cmd.ExecuteScalar();
 868                         cmd.Parameters.Clear();
 869                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 870                         {
 871                             return null;
 872                         }
 873                         else
 874                         {
 875                             return obj;
 876                         }
 877                     }
 878                     catch (System.Data.SqlClient.SqlException e)
 879                     {
 880                         throw e;
 881                     }
 882                 }
 883             }
 884         }
 885         /// <param name="connection">SqlConnection对象</param>
 886         /// <param name="trans">SqlTransaction事务</param>
 887         /// <param name="SQLString">计算查询结果语句</param>
 888         /// <returns>查询结果（object）</returns>
 889         public static object GetSingle(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
 890         {
 891             using (SqlCommand cmd = new SqlCommand())
 892             {
 893                 try
 894                 {
 895                     PrepareCommand(cmd, connection, trans, SQLString, cmdParms);
 896                     object obj = cmd.ExecuteScalar();
 897                     cmd.Parameters.Clear();
 898                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 899                     {
 900                         return null;
 901                     }
 902                     else
 903                     {
 904                         return obj;
 905                     }
 906                 }
 907                 catch (System.Data.SqlClient.SqlException e)
 908                 {
 909                     trans.Rollback();
 910                     throw e;
 911                 }
 912             }
 913         }
 914 
 915         /// <summary>
 916         /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
 917         /// </summary>
 918         /// <param name="strSQL">查询语句</param>
 919         /// <returns>SqlDataReader</returns>
 920         public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
 921         {
 922             SqlConnection connection = new SqlConnection(connectionString);
 923             SqlCommand cmd = new SqlCommand();
 924             try
 925             {
 926                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
 927                 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 928                 cmd.Parameters.Clear();
 929                 return myReader;
 930             }
 931             catch (System.Data.SqlClient.SqlException e)
 932             {
 933                 throw e;
 934             }
 935             //            finally
 936             //            {
 937             //                cmd.Dispose();
 938             //                connection.Close();
 939             //            }    
 940 
 941         }
 942 
 943         /// <summary>
 944         /// 执行查询语句，返回DataSet
 945         /// </summary>
 946         /// <param name="SQLString">查询语句</param>
 947         /// <returns>DataSet</returns>
 948         public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
 949         {
 950             using (SqlConnection connection = new SqlConnection(connectionString))
 951             {
 952                 SqlCommand cmd = new SqlCommand();
 953                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
 954                 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 955                 {
 956                     DataSet ds = new DataSet();
 957                     try
 958                     {
 959                         da.Fill(ds, "ds");
 960                         cmd.Parameters.Clear();
 961                     }
 962                     catch (System.Data.SqlClient.SqlException ex)
 963                     {
 964                         throw new Exception(ex.Message);
 965                     }
 966                     return ds;
 967                 }
 968             }
 969         }
 970 
 971         /// <summary>
 972         /// 2012-2-21新增重载，执行查询语句，返回DataSet
 973         /// </summary>
 974         /// <param name="connection">SqlConnection对象</param>
 975         /// <param name="trans">SqlTransaction事务</param>
 976         /// <param name="SQLString">查询语句</param>
 977         /// <returns>DataSet</returns>
 978         public static DataSet Query(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
 979         {
 980             SqlCommand cmd = new SqlCommand();
 981             PrepareCommand(cmd, connection, trans, SQLString, cmdParms);
 982             using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 983             {
 984                 DataSet ds = new DataSet();
 985                 try
 986                 {
 987                     da.Fill(ds, "ds");
 988                     cmd.Parameters.Clear();
 989                 }
 990                 catch (System.Data.SqlClient.SqlException ex)
 991                 {
 992                     trans.Rollback();
 993                     throw new Exception(ex.Message);
 994                 }
 995                 return ds;
 996             }
 997         }
 998 
 999 
1000         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
1001         {
1002             if (conn.State != ConnectionState.Open)
1003                 conn.Open();
1004             cmd.Connection = conn;
1005             cmd.CommandText = cmdText;
1006             if (trans != null)
1007                 cmd.Transaction = trans;
1008             cmd.CommandType = CommandType.Text;//cmdType;
1009             if (cmdParms != null)
1010             {
1011 
1012 
1013                 foreach (SqlParameter parameter in cmdParms)
1014                 {
1015                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
1016                         (parameter.Value == null))
1017                     {
1018                         parameter.Value = DBNull.Value;
1019                     }
1020                     cmd.Parameters.Add(parameter);
1021                 }
1022             }
1023         }
1024 
1025         #endregion
1026 
1027         #region 存储过程操作
1028 
1029         /// <summary>
1030         /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
1031         /// </summary>
1032         /// <param name="storedProcName">存储过程名</param>
1033         /// <param name="parameters">存储过程参数</param>
1034         /// <returns>SqlDataReader</returns>
1035         public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
1036         {
1037             SqlConnection connection = new SqlConnection(connectionString);
1038             SqlDataReader returnReader;
1039             connection.Open();
1040             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
1041             command.CommandType = CommandType.StoredProcedure;
1042             returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
1043             return returnReader;
1044 
1045         }
1046 
1047 
1048         /// <summary>
1049         /// 执行存储过程
1050         /// </summary>
1051         /// <param name="storedProcName">存储过程名</param>
1052         /// <param name="parameters">存储过程参数</param>
1053         /// <param name="tableName">DataSet结果中的表名</param>
1054         /// <returns>DataSet</returns>
1055         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
1056         {
1057             using (SqlConnection connection = new SqlConnection(connectionString))
1058             {
1059                 DataSet dataSet = new DataSet();
1060                 connection.Open();
1061                 SqlDataAdapter sqlDA = new SqlDataAdapter();
1062                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
1063                 sqlDA.Fill(dataSet, tableName);
1064                 connection.Close();
1065                 return dataSet;
1066             }
1067         }
1068         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
1069         {
1070             using (SqlConnection connection = new SqlConnection(connectionString))
1071             {
1072                 DataSet dataSet = new DataSet();
1073                 connection.Open();
1074                 SqlDataAdapter sqlDA = new SqlDataAdapter();
1075                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
1076                 sqlDA.SelectCommand.CommandTimeout = Times;
1077                 sqlDA.Fill(dataSet, tableName);
1078                 connection.Close();
1079                 return dataSet;
1080             }
1081         }
1082 
1083 
1084         /// <summary>
1085         /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
1086         /// </summary>
1087         /// <param name="connection">数据库连接</param>
1088         /// <param name="storedProcName">存储过程名</param>
1089         /// <param name="parameters">存储过程参数</param>
1090         /// <returns>SqlCommand</returns>
1091         private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
1092         {
1093             SqlCommand command = new SqlCommand(storedProcName, connection);
1094             command.CommandType = CommandType.StoredProcedure;
1095             foreach (SqlParameter parameter in parameters)
1096             {
1097                 if (parameter != null)
1098                 {
1099                     // 检查未分配值的输出参数,将其分配以DBNull.Value.
1100                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
1101                         (parameter.Value == null))
1102                     {
1103                         parameter.Value = DBNull.Value;
1104                     }
1105                     command.Parameters.Add(parameter);
1106                 }
1107             }
1108 
1109             return command;
1110         }
1111 
1112         /// <summary>
1113         /// 执行存储过程，返回影响的行数        
1114         /// </summary>
1115         /// <param name="storedProcName">存储过程名</param>
1116         /// <param name="parameters">存储过程参数</param>
1117         /// <param name="rowsAffected">影响的行数</param>
1118         /// <returns></returns>
1119         public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
1120         {
1121             using (SqlConnection connection = new SqlConnection(connectionString))
1122             {
1123                 int result;
1124                 connection.Open();
1125                 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
1126                 rowsAffected = command.ExecuteNonQuery();
1127                 result = (int)command.Parameters["ReturnValue"].Value;
1128                 //Connection.Close();
1129                 return result;
1130             }
1131         }
1132 
1133         /// <summary>
1134         /// 创建 SqlCommand 对象实例(用来返回一个整数值)    
1135         /// </summary>
1136         /// <param name="storedProcName">存储过程名</param>
1137         /// <param name="parameters">存储过程参数</param>
1138         /// <returns>SqlCommand 对象实例</returns>
1139         private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
1140         {
1141             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
1142             command.Parameters.Add(new SqlParameter("ReturnValue",
1143                 SqlDbType.Int, 4, ParameterDirection.ReturnValue,
1144                 false, 0, 0, string.Empty, DataRowVersion.Default, null));
1145             return command;
1146         }
1147         #endregion
1148 
1149     }
1150 }

附件下载：DBHelper，AspNetPagerCN-7.5.1