C#访问SQLServer数据库访问帮助类

SQLServer数据库访问帮助类

这里有一个Mysql帮助类的使用例子可以参考（大同小异）

写了一个Mysql的例子，SQLServer的差不多，一样的

C#简单三层结构设计UI、BLL、DAL

1.web.config配置数据库连接字符串

 1 <?xml version="1.0"?>
 2 <configuration>
 3   <appSettings>
 4     <!-- 连接字符串是否加密 -->
 5     <add key="ConStringEncrypt" value="false"/>
 6     <!-- 数据库连接字符串,（如果采用加密方式，上面一项要设置为true；加密工具，可在官方下载，
 7              如果使用明文这样server=127.0.0.1;database=.....,上面则设置为false。 -->
 8     <add key="ConnectionString" value="server=127.0.0.1;database=BenXHCMS;uid=sa;pwd=jyf"/>
 9   </appSettings>
10 </configuration>

2.数据库字符串读取类（多一个加密算法，可以自己添加）

 1     public class PubConstant
 2     {        
 3         /// <summary>
 4         /// 获取连接字符串
 5         /// </summary>
 6         public static string ConnectionString
 7         {           
 8             get 
 9             {
10                 string _connectionString = ConfigurationManager.AppSettings["ConnectionString"];       
11                 string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
12                 if (ConStringEncrypt == "true")
13                 {
14                     _connectionString = DESEncrypt.Decrypt(_connectionString);
15                 }
16                 return _connectionString; 
17             }
18         }
19 
20         /// <summary>
21         /// 得到web.config里配置项的数据库连接字符串。
22         /// </summary>
23         /// <param name="configName"></param>
24         /// <returns></returns>
25         public static string GetConnectionString(string configName)
26         {
27             string connectionString = ConfigurationManager.AppSettings[configName];
28             string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
29             if (ConStringEncrypt == "true")
30             {
31                 connectionString = DESEncrypt.Decrypt(connectionString);
32             }
33             return connectionString;
34         }
35 
36 
37     }

3.SQLServer数据库访问类

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

 

posted @ 2017-03-18 11:29  JiYF  阅读(8537)  评论(0)  编辑  收藏  举报