• C# 数据库通用操作类DBHelper


    企业信息系统开发主要是围绕数据库进行CRUD ,收藏一个通用的DBHELP操作类

    SQLHelper、DBHelper是ADO.NET的改造版

      1 using System;
      2 using System.Collections;
      3 using System.Collections.Specialized;
      4 using System.Data;
      5 using System.Data.SqlClient;
      6 using System.Configuration;
      7 using System.Data.Common;
      8 using System.Collections.Generic;
      9 namespace lrdbhelpSQL
     10 {
     11     /// <summary>
     12     /// 数据访问抽象基础类
     13     /// Copyright (C) Maticsoft 
     14     /// </summary>
     15     public abstract class DbHelperSQL
     16     {
     17         ////数据库连接字符串(web.config来配置),多数据库可使用DbHelperSQLP来实现.
     18         //public static string connectionString = GetConfigInfo();
     19 
     20         //public static string GetConfigInfo()
     21         //{
     22         //    string configInfo = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
     23         //    return configInfo;
     24         //}
     25 
     26         public static string connectionString = AppConst.DBConnstring;
     27 
     28         public DbHelperSQL()
     29         {
     30         }
     31 
     32 
     33         #region 公用方法
     34         /// <summary>
     35         /// 判断是否存在某表的某个字段
     36         /// </summary>
     37         /// <param name="tableName">表名称</param>
     38         /// <param name="columnName">列名称</param>
     39         /// <returns>是否存在</returns>
     40         public static bool ColumnExists(string tableName, string columnName)
     41         {
     42             string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
     43             object res = GetSingle(sql);
     44             if (res == null)
     45             {
     46                 return false;
     47             }
     48             return Convert.ToInt32(res) > 0;
     49         }
     50         public static int GetMaxID(string FieldName, string TableName)
     51         {
     52             string strsql = "select max(" + FieldName + ")+1 from " + TableName;
     53             object obj = GetSingle(strsql);
     54             if (obj == null)
     55             {
     56                 return 1;
     57             }
     58             else
     59             {
     60                 return int.Parse(obj.ToString());
     61             }
     62         }
     63         public static bool Exists(string strSql)
     64         {
     65             object obj = GetSingle(strSql);
     66             int cmdresult;
     67             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
     68             {
     69                 cmdresult = 0;
     70             }
     71             else
     72             {
     73                 cmdresult = int.Parse(obj.ToString()); //也可能=0
     74             }
     75             if (cmdresult == 0)
     76             {
     77                 return false;
     78             }
     79             else
     80             {
     81                 return true;
     82             }
     83         }
     84         /// <summary>
     85         /// 表是否存在
     86         /// </summary>
     87         /// <param name="TableName"></param>
     88         /// <returns></returns>
     89         public static bool TabExists(string TableName)
     90         {
     91             string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
     92             //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
     93             object obj = GetSingle(strsql);
     94             int cmdresult;
     95             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
     96             {
     97                 cmdresult = 0;
     98             }
     99             else
    100             {
    101                 cmdresult = int.Parse(obj.ToString());
    102             }
    103             if (cmdresult == 0)
    104             {
    105                 return false;
    106             }
    107             else
    108             {
    109                 return true;
    110             }
    111         }
    112         public static bool Exists(string strSql, params SqlParameter[] cmdParms)
    113         {
    114             object obj = GetSingle(strSql, cmdParms);
    115             int cmdresult;
    116             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
    117             {
    118                 cmdresult = 0;
    119             }
    120             else
    121             {
    122                 cmdresult = int.Parse(obj.ToString());
    123             }
    124             if (cmdresult == 0)
    125             {
    126                 return false;
    127             }
    128             else
    129             {
    130                 return true;
    131             }
    132         }
    133         #endregion
    134 
    135 
    136         #region  执行简单SQL语句
    137 
    138 
    139         /// <summary>
    140         /// 执行SQL语句,返回影响的记录数
    141         /// </summary>
    142         /// <param name="SQLString">SQL语句</param>
    143         /// <returns>影响的记录数</returns>
    144         public static int ExecuteSql(string SQLString)
    145         {
    146             using (SqlConnection connection = new SqlConnection(connectionString))
    147             {
    148                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
    149                 {
    150                     try
    151                     {
    152                         connection.Open();
    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                     return 0;
    162                 }
    163             }
    164         }
    165 
    166 
    167 
    168         public static int ExecuteSqlByTime(string SQLString, int Times)
    169         {
    170             using (SqlConnection connection = new SqlConnection(connectionString))
    171             {
    172                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
    173                 {
    174                     try
    175                     {
    176                         connection.Open();
    177                         cmd.CommandTimeout = Times;
    178                         int rows = cmd.ExecuteNonQuery();
    179                         return rows;
    180                     }
    181                     catch (System.Data.SqlClient.SqlException e)
    182                     {
    183                         connection.Close();
    184                         throw e;
    185                     }
    186                 }
    187             }
    188         }
    189 
    190 
    191         /// <summary>
    192         /// 执行多条SQL语句,实现数据库事务。
    193         /// </summary>
    194         /// <param name="SQLStringList">多条SQL语句</param>
    195         public static int ExecuteSqlTran(List<String> SQLStringList)
    196         {
    197             using (SqlConnection conn = new SqlConnection(connectionString))
    198             {
    199                 conn.Open();
    200                 SqlCommand cmd = new SqlCommand();
    201                 cmd.Connection = conn;
    202                 SqlTransaction tx = conn.BeginTransaction();
    203                 cmd.Transaction = tx;
    204                 try
    205                 {
    206                     int count = 0;
    207                     for (int n = 0; n < SQLStringList.Count; n++)
    208                     {
    209                         string strsql = SQLStringList[n];
    210                         if (strsql.Trim().Length > 1)
    211                         {
    212                             cmd.CommandText = strsql;
    213                             count += cmd.ExecuteNonQuery();
    214                         }
    215                     }
    216                     tx.Commit();
    217                     return count;
    218                 }
    219                 catch
    220                 {
    221                     tx.Rollback();
    222                     return 0;
    223                 }
    224             }
    225         }
    226         /// <summary>
    227         /// 执行带一个存储过程参数的的SQL语句。
    228         /// </summary>
    229         /// <param name="SQLString">SQL语句</param>
    230         /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
    231         /// <returns>影响的记录数</returns>
    232         public static int ExecuteSql(string SQLString, string content)
    233         {
    234             using (SqlConnection connection = new SqlConnection(content))
    235             {
    236                 SqlCommand cmd = new SqlCommand(SQLString, connection);
    237                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
    238                 myParameter.Value = content;
    239                 cmd.Parameters.Add(myParameter);
    240                 try
    241                 {
    242                     connection.Open();
    243                     int rows = cmd.ExecuteNonQuery();
    244                     return rows;
    245                 }
    246                 catch (System.Data.SqlClient.SqlException e)
    247                 {
    248                     //throw e;
    249                 }
    250                 finally
    251                 {
    252                     cmd.Dispose();
    253                     connection.Close();
    254                 }
    255                 return 0;
    256             }
    257         }
    258         /// <summary>
    259         /// 执行带一个存储过程参数的的SQL语句。
    260         /// </summary>
    261         /// <param name="SQLString">SQL语句</param>
    262         /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
    263         /// <returns>影响的记录数</returns>
    264         public static object ExecuteSqlGet(string SQLString, string content)
    265         {
    266             using (SqlConnection connection = new SqlConnection(connectionString))
    267             {
    268                 SqlCommand cmd = new SqlCommand(SQLString, connection);
    269                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
    270                 myParameter.Value = content;
    271                 cmd.Parameters.Add(myParameter);
    272                 try
    273                 {
    274                     connection.Open();
    275                     object obj = cmd.ExecuteScalar();
    276                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
    277                     {
    278                         return null;
    279                     }
    280                     else
    281                     {
    282                         return obj;
    283                     }
    284                 }
    285                 catch (System.Data.SqlClient.SqlException e)
    286                 {
    287                     throw e;
    288                 }
    289                 finally
    290                 {
    291                     cmd.Dispose();
    292                     connection.Close();
    293                 }
    294             }
    295         }
    296         /// <summary>
    297         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
    298         /// </summary>
    299         /// <param name="strSQL">SQL语句</param>
    300         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
    301         /// <returns>影响的记录数</returns>
    302         public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
    303         {
    304             using (SqlConnection connection = new SqlConnection(connectionString))
    305             {
    306                 SqlCommand cmd = new SqlCommand(strSQL, connection);
    307                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
    308                 myParameter.Value = fs;
    309                 cmd.Parameters.Add(myParameter);
    310                 try
    311                 {
    312                     connection.Open();
    313                     int rows = cmd.ExecuteNonQuery();
    314                     return rows;
    315                 }
    316                 catch (System.Data.SqlClient.SqlException e)
    317                 {
    318                     throw e;
    319                 }
    320                 finally
    321                 {
    322                     cmd.Dispose();
    323                     connection.Close();
    324                 }
    325             }
    326         }
    327 
    328 
    329         /// <summary>
    330         /// 执行一条计算查询结果语句,返回查询结果(object)。
    331         /// </summary>
    332         /// <param name="SQLString">计算查询结果语句</param>
    333         /// <returns>查询结果(object)</returns>
    334         public static object GetSingle(string SQLString)
    335         {
    336             using (SqlConnection connection = new SqlConnection(connectionString))
    337             {
    338                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
    339                 {
    340                     try
    341                     {
    342                         connection.Open();
    343                         object obj = cmd.ExecuteScalar();
    344                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
    345                         {
    346                             return null;
    347                         }
    348                         else
    349                         {
    350                             return obj;
    351                         }
    352                     }
    353                     catch (System.Data.SqlClient.SqlException e)
    354                     {
    355                         connection.Close();
    356                         throw e;
    357                     }
    358                 }
    359             }
    360         }
    361         public static object GetSingle(string SQLString, int Times)
    362         {
    363             using (SqlConnection connection = new SqlConnection(connectionString))
    364             {
    365                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
    366                 {
    367                     try
    368                     {
    369                         connection.Open();
    370                         cmd.CommandTimeout = Times;
    371                         object obj = cmd.ExecuteScalar();
    372                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
    373                         {
    374                             return null;
    375                         }
    376                         else
    377                         {
    378                             return obj;
    379                         }
    380                     }
    381                     catch (System.Data.SqlClient.SqlException e)
    382                     {
    383                         connection.Close();
    384                         throw e;
    385                     }
    386                 }
    387             }
    388         }
    389         /// <summary>
    390         /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
    391         /// </summary>
    392         /// <param name="strSQL">查询语句</param>
    393         /// <returns>SqlDataReader</returns>
    394         public static SqlDataReader ExecuteReader(string strSQL)
    395         {
    396             SqlConnection connection = new SqlConnection(connectionString);
    397             SqlCommand cmd = new SqlCommand(strSQL, connection);
    398             try
    399             {
    400                 connection.Open();
    401                 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    402                 return myReader;
    403             }
    404             catch (System.Data.SqlClient.SqlException e)
    405             {
    406                 throw e;
    407             }
    408 
    409 
    410         }
    411         /// <summary>
    412         /// 执行查询语句,返回DataSet
    413         /// </summary>
    414         /// <param name="SQLString">查询语句</param>
    415         /// <returns>DataSet</returns>
    416         public static DataSet Query(string SQLString)
    417         {
    418             using (SqlConnection connection = new SqlConnection(connectionString))
    419             {
    420                 DataSet ds = new DataSet();
    421                 try
    422                 {
    423                     connection.Open();
    424                     SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
    425                     command.Fill(ds, "ds");
    426                 }
    427                 catch (System.Data.SqlClient.SqlException ex)
    428                 {
    429                     //throw new Exception(ex.Message);
    430                 }
    431                 return ds;
    432             }
    433         }
    434         public static DataSet Query(string SQLString, int Times)
    435         {
    436             using (SqlConnection connection = new SqlConnection(connectionString))
    437             {
    438                 DataSet ds = new DataSet();
    439                 try
    440                 {
    441                     connection.Open();
    442                     SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
    443                     command.SelectCommand.CommandTimeout = Times;
    444                     command.Fill(ds, "ds");
    445                 }
    446                 catch (System.Data.SqlClient.SqlException ex)
    447                 {
    448                     throw new Exception(ex.Message);
    449                 }
    450                 return ds;
    451             }
    452         }
    453 
    454 
    455 
    456 
    457 
    458 
    459         #endregion
    460 
    461 
    462         #region 执行带参数的SQL语句
    463 
    464 
    465         /// <summary>
    466         /// 执行SQL语句,返回影响的记录数
    467         /// </summary>
    468         /// <param name="SQLString">SQL语句</param>
    469         /// <returns>影响的记录数</returns>
    470         public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
    471         {
    472             using (SqlConnection connection = new SqlConnection(connectionString))
    473             {
    474                 using (SqlCommand cmd = new SqlCommand())
    475                 {
    476                     try
    477                     {
    478                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
    479                         int rows = cmd.ExecuteNonQuery();
    480                         cmd.Parameters.Clear();
    481                         return rows;
    482                     }
    483                     catch (System.Data.SqlClient.SqlException e)
    484                     {
    485                         throw e;
    486                     }
    487                 }
    488             }
    489         }
    490 
    491 
    492 
    493 
    494         /// <summary>
    495         /// 执行多条SQL语句,实现数据库事务。
    496         /// </summary>
    497         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
    498         public static void ExecuteSqlTran(Hashtable SQLStringList)
    499         {
    500             using (SqlConnection conn = new SqlConnection(connectionString))
    501             {
    502                 conn.Open();
    503                 using (SqlTransaction trans = conn.BeginTransaction())
    504                 {
    505                     SqlCommand cmd = new SqlCommand();
    506                     try
    507                     {
    508                         //循环
    509                         foreach (DictionaryEntry myDE in SQLStringList)
    510                         {
    511                             string cmdText = myDE.Key.ToString();
    512                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
    513                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
    514                             int val = cmd.ExecuteNonQuery();
    515                             cmd.Parameters.Clear();
    516                         }
    517                         trans.Commit();
    518                     }
    519                     catch
    520                     {
    521                         trans.Rollback();
    522                         throw;
    523                     }
    524                 }
    525             }
    526         }
    527 
    528 
    529         /// <summary>
    530         /// 执行多条SQL语句,实现数据库事务。
    531         /// </summary>
    532         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
    533         public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
    534         {
    535             using (SqlConnection conn = new SqlConnection(connectionString))
    536             {
    537                 conn.Open();
    538                 using (SqlTransaction trans = conn.BeginTransaction())
    539                 {
    540                     SqlCommand cmd = new SqlCommand();
    541                     try
    542                     {
    543                         int indentity = 0;
    544                         //循环
    545                         foreach (DictionaryEntry myDE in SQLStringList)
    546                         {
    547                             string cmdText = myDE.Key.ToString();
    548                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
    549                             foreach (SqlParameter q in cmdParms)
    550                             {
    551                                 if (q.Direction == ParameterDirection.InputOutput)
    552                                 {
    553                                     q.Value = indentity;
    554                                 }
    555                             }
    556                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
    557                             int val = cmd.ExecuteNonQuery();
    558                             foreach (SqlParameter q in cmdParms)
    559                             {
    560                                 if (q.Direction == ParameterDirection.Output)
    561                                 {
    562                                     indentity = Convert.ToInt32(q.Value);
    563                                 }
    564                             }
    565                             cmd.Parameters.Clear();
    566                         }
    567                         trans.Commit();
    568                     }
    569                     catch
    570                     {
    571                         trans.Rollback();
    572                         throw;
    573                     }
    574                 }
    575             }
    576         }
    577         /// <summary>
    578         /// 执行一条计算查询结果语句,返回查询结果(object)。
    579         /// </summary>
    580         /// <param name="SQLString">计算查询结果语句</param>
    581         /// <returns>查询结果(object)</returns>
    582         public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
    583         {
    584             using (SqlConnection connection = new SqlConnection(connectionString))
    585             {
    586                 using (SqlCommand cmd = new SqlCommand())
    587                 {
    588                     try
    589                     {
    590                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
    591                         object obj = cmd.ExecuteScalar();
    592                         cmd.Parameters.Clear();
    593                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
    594                         {
    595                             return null;
    596                         }
    597                         else
    598                         {
    599                             return obj;
    600                         }
    601                     }
    602                     catch (System.Data.SqlClient.SqlException e)
    603                     {
    604                         throw e;
    605                     }
    606                 }
    607             }
    608         }
    609 
    610 
    611         /// <summary>
    612         /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
    613         /// </summary>
    614         /// <param name="strSQL">查询语句</param>
    615         /// <returns>SqlDataReader</returns>
    616         public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
    617         {
    618             SqlConnection connection = new SqlConnection(connectionString);
    619             SqlCommand cmd = new SqlCommand();
    620             try
    621             {
    622                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
    623                 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    624                 cmd.Parameters.Clear();
    625                 return myReader;
    626             }
    627             catch (System.Data.SqlClient.SqlException e)
    628             {
    629                 throw e;
    630             }
    631             //  finally
    632             //  {
    633             //  cmd.Dispose();
    634             //  connection.Close();
    635             //  }
    636 
    637 
    638         }
    639 
    640 
    641         /// <summary>
    642         /// 执行查询语句,返回DataSet
    643         /// </summary>
    644         /// <param name="SQLString">查询语句</param>
    645         /// <returns>DataSet</returns>
    646         public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
    647         {
    648             using (SqlConnection connection = new SqlConnection(connectionString))
    649             {
    650                 SqlCommand cmd = new SqlCommand();
    651                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
    652                 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
    653                 {
    654                     DataSet ds = new DataSet();
    655                     try
    656                     {
    657                         da.Fill(ds, "ds");
    658                         cmd.Parameters.Clear();
    659                     }
    660                     catch (System.Data.SqlClient.SqlException ex)
    661                     {
    662                         throw new Exception(ex.Message);
    663                     }
    664                     return ds;
    665                 }
    666             }
    667         }
    668 
    669 
    670 
    671 
    672         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
    673         {
    674             if (conn.State != ConnectionState.Open)
    675                 conn.Open();
    676             cmd.Connection = conn;
    677             cmd.CommandText = cmdText;
    678             if (trans != null)
    679                 cmd.Transaction = trans;
    680             cmd.CommandType = CommandType.Text;//cmdType;
    681             if (cmdParms != null)
    682             {
    683                 foreach (SqlParameter parameter in cmdParms)
    684                 {
    685                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
    686                         (parameter.Value == null))
    687                     {
    688                         parameter.Value = DBNull.Value;
    689                     }
    690                     cmd.Parameters.Add(parameter);
    691                 }
    692             }
    693         }
    694 
    695 
    696         #endregion
    697 
    698 
    699         #region 存储过程操作
    700 
    701 
    702         /// <summary>
    703         /// 执行存储过程,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
    704         /// </summary>
    705         /// <param name="storedProcName">存储过程名</param>
    706         /// <param name="parameters">存储过程参数</param>
    707         /// <returns>SqlDataReader</returns>
    708         public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
    709         {
    710             SqlConnection connection = new SqlConnection(connectionString);
    711             SqlDataReader returnReader;
    712             connection.Open();
    713             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
    714             command.CommandType = CommandType.StoredProcedure;
    715             returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
    716             return returnReader;
    717 
    718         }
    719 
    720 
    721 
    722 
    723         /// <summary>
    724         /// 执行存储过程
    725         /// </summary>
    726         /// <param name="storedProcName">存储过程名</param>
    727         /// <param name="parameters">存储过程参数</param>
    728         /// <param name="tableName">DataSet结果中的表名</param>
    729         /// <returns>DataSet</returns>
    730         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
    731         {
    732             using (SqlConnection connection = new SqlConnection(connectionString))
    733             {
    734                 DataSet dataSet = new DataSet();
    735                 connection.Open();
    736                 SqlDataAdapter sqlDA = new SqlDataAdapter();
    737                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
    738                 sqlDA.Fill(dataSet, tableName);
    739                 connection.Close();
    740                 return dataSet;
    741             }
    742         }
    743         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
    744         {
    745             using (SqlConnection connection = new SqlConnection(connectionString))
    746             {
    747                 DataSet dataSet = new DataSet();
    748                 connection.Open();
    749                 SqlDataAdapter sqlDA = new SqlDataAdapter();
    750                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
    751                 sqlDA.SelectCommand.CommandTimeout = Times;
    752                 sqlDA.Fill(dataSet, tableName);
    753                 connection.Close();
    754                 return dataSet;
    755             }
    756         }
    757 
    758 
    759 
    760 
    761         /// <summary>
    762         /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
    763         /// </summary>
    764         /// <param name="connection">数据库连接</param>
    765         /// <param name="storedProcName">存储过程名</param>
    766         /// <param name="parameters">存储过程参数</param>
    767         /// <returns>SqlCommand</returns>
    768         private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
    769         {
    770             SqlCommand command = new SqlCommand(storedProcName, connection);
    771             command.CommandType = CommandType.StoredProcedure;
    772             foreach (SqlParameter parameter in parameters)
    773             {
    774                 if (parameter != null)
    775                 {
    776                     // 检查未分配值的输出参数,将其分配以DBNull.Value.
    777                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
    778                         (parameter.Value == null))
    779                     {
    780                         parameter.Value = DBNull.Value;
    781                     }
    782                     command.Parameters.Add(parameter);
    783                 }
    784             }
    785 
    786 
    787             return command;
    788         }
    789 
    790 
    791         /// <summary>
    792         /// 执行存储过程,返回影响的行数 
    793         /// </summary>
    794         /// <param name="storedProcName">存储过程名</param>
    795         /// <param name="parameters">存储过程参数</param>
    796         /// <param name="rowsAffected">影响的行数</param>
    797         /// <returns></returns>
    798         public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
    799         {
    800             using (SqlConnection connection = new SqlConnection(connectionString))
    801             {
    802                 int result;
    803                 connection.Open();
    804                 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
    805                 rowsAffected = command.ExecuteNonQuery();
    806                 result = (int)command.Parameters["ReturnValue"].Value;
    807                 //Connection.Close();
    808                 return result;
    809             }
    810         }
    811 
    812 
    813         /// <summary>
    814         /// 创建 SqlCommand 对象实例(用来返回一个整数值) 
    815         /// </summary>
    816         /// <param name="storedProcName">存储过程名</param>
    817         /// <param name="parameters">存储过程参数</param>
    818         /// <returns>SqlCommand 对象实例</returns>
    819         private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
    820         {
    821             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
    822             command.Parameters.Add(new SqlParameter("ReturnValue",
    823                 SqlDbType.Int, 4, ParameterDirection.ReturnValue,
    824                 false, 0, 0, string.Empty, DataRowVersion.Default, null));
    825             return command;
    826         }
    827         #endregion
    828 
    829 
    830     }
    831 
    832 
    833 }
  • 相关阅读:
    [Err] 1168
    SpringAOP的运用方式——注解方式和XML配置方式
    展开运算符的理解
    find indexof findindex includes 的区别
    关于MVC MVVM的理解
    sync修饰符
    vue导航守卫
    输入网址到页面显示 经历了什么(转载)
    vue中侦听器的使用
    函数声明的三种方式
  • 原文地址:https://www.cnblogs.com/lrzy/p/15821131.html
Copyright © 2020-2023  润新知