DBHelper，dbhelper.cs

DBHelper，dbhelper.cs

　Content: 把李天平老師的代碼拿過來學習

　　原文地址:http://www.cnblogs.com/aivdesign/articles/1263247.html

　　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     /// 
  12     /// 數據訪問抽象基礎類
  13     /// Copyright (C) 2004-2008 By LiTianPing 
  14     /// 
  15     public abstract class DbHelperSQL
  16     {
  17         //數據庫連接字符串(web.config來配置)，可以動態更改connectionString支持多數據庫.  
  18         public static string connectionString = PubConstant.ConnectionString;       
  19         public DbHelperSQL()
  20         {            
  21         }
  22 
  23         #region 公用方法
  24         /// 
  25         /// 判斷是否存在某表的某個字段
  26         /// 
  27         /// 表名稱
  28         /// 列名稱
  29         /// 是否存在
  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 = DbHelperSQL.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 = DbHelperSQL.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());
  64             }
  65             if (cmdresult == 0)
  66             {
  67                 return false;
  68             }
  69             else
  70             {
  71                 return true;
  72             }
  73         }
  74         /// 
  75         /// 表是否存在
  76         /// 
  77         /// 
  78         /// 
  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 = DbHelperSQL.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 = DbHelperSQL.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         /// 
 128         /// 執行SQL語句，返回影響的記錄數
 129         /// 
 130         /// SQL語句
 131         /// 影響的記錄數
 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         /// 
 176         /// 執行Sql和Oracle滴混合事務
 177         /// 
 178         /// SQL命令行列表
 179         /// Oracle命令行列表
 180         /// 執行結果 0-由於SQL造成事務失敗 -1 由於Oracle造成事務失敗 1-整體事務執行成功
 181         public static int ExecuteSqlTran(List list, List 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         /// 
 283         /// 執行多條SQL語句，實現數據庫事務。
 284         /// 
 285         /// 多條SQL語句  
 286         public static int ExecuteSqlTran(List 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         /// 
 318         /// 執行帶一個存儲過程參數的的SQL語句。
 319         /// 
 320         /// SQL語句
 321         /// 參數內容,比如一個字段是格式復雜的文章，有特殊符號，可以通過這個方式添加
 322         /// 影響的記錄數
 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         /// 
 349         /// 執行帶一個存儲過程參數的的SQL語句。
 350         /// 
 351         /// SQL語句
 352         /// 參數內容,比如一個字段是格式復雜的文章，有特殊符號，可以通過這個方式添加
 353         /// 影響的記錄數
 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         /// 
 387         /// 向數據庫裡插入圖像格式的字段(和上面情況類似的另一種實例)
 388         /// 
 389         /// SQL語句
 390         /// 圖像字節,數據庫的字段類型為image的情況
 391         /// 影響的記錄數
 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         /// 
 419         /// 執行一條計算查詢結果語句，返回查詢結果（object）。
 420         /// 
 421         /// 計算查詢結果語句
 422         /// 查詢結果（object）
 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         /// 
 479         /// 執行查詢語句，返回SqlDataReader ( 注意：調用該方法後，一定要對SqlDataReader進行Close )
 480         /// 
 481         /// 查詢語句
 482         /// SqlDataReader
 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         /// 
 500         /// 執行查詢語句，返回DataSet
 501         /// 
 502         /// 查詢語句
 503         /// DataSet
 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         /// 
 549         /// 執行SQL語句，返回影響的記錄數
 550         /// 
 551         /// SQL語句
 552         /// 影響的記錄數
 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         /// 
 576         /// 執行多條SQL語句，實現數據庫事務。
 577         /// 
 578         /// SQL語句的哈希表（key為sql語句，value是該語句的SqlParameter[]）
 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         /// 
 609         /// 執行多條SQL語句，實現數據庫事務。
 610         /// 
 611         /// SQL語句的哈希表（key為sql語句，value是該語句的SqlParameter[]）
 612         public static int ExecuteSqlTran(System.Collections.Generic.List 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         /// 
 678         /// 執行多條SQL語句，實現數據庫事務。
 679         /// 
 680         /// SQL語句的哈希表（key為sql語句，value是該語句的SqlParameter[]）
 681         public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List 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         /// 
 726         /// 執行多條SQL語句，實現數據庫事務。
 727         /// 
 728         /// SQL語句的哈希表（key為sql語句，value是該語句的SqlParameter[]）
 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         /// 
 774         /// 執行一條計算查詢結果語句，返回查詢結果（object）。
 775         /// 
 776         /// 計算查詢結果語句
 777         /// 查詢結果（object）
 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         /// 
 807         /// 執行查詢語句，返回SqlDataReader ( 注意：調用該方法後，一定要對SqlDataReader進行Close )
 808         /// 
 809         /// 查詢語句
 810         /// SqlDataReader
 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         /// 
 835         /// 執行查詢語句，返回DataSet
 836         /// 
 837         /// 查詢語句
 838         /// DataSet
 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         /// 
 893         /// 執行存儲過程，返回SqlDataReader ( 注意：調用該方法後，一定要對SqlDataReader進行Close )
 894         /// 
 895         /// 存儲過程名
 896         /// 存儲過程參數
 897         /// SqlDataReader
 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         /// 
 912         /// 執行存儲過程
 913         /// 
 914         /// 存儲過程名
 915         /// 存儲過程參數
 916         /// DataSet結果中的表名
 917         /// DataSet
 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         /// 
 948         /// 構建 SqlCommand 對象(用來返回一個結果集，而不是一個整數值)
 949         /// 
 950         /// 數據庫連接
 951         /// 存儲過程名
 952         /// 存儲過程參數
 953         /// SqlCommand
 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         /// 
 976         /// 執行存儲過程，返回影響的行數  
 977         /// 
 978         /// 存儲過程名
 979         /// 存儲過程參數
 980         /// 影響的行數
 981         /// 
 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         /// 
 997         /// 創建 SqlCommand 對象實例(用來返回一個整數值) 
 998         /// 
 999         /// 存儲過程名
1000         /// 存儲過程參數
1001         /// SqlCommand 對象實例
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 }