SqlHelper帮助类

本文主要是介绍SqlHelper帮助类，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

SqlHelper帮助类

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

这篇关于SqlHelper帮助类的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！