NET-数据访问基础类(基于SQLServer)

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