DbHelperSQL

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