using System;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Collections;namespace MyTest.common{    public abstract class SqlHelper    {        public static readonly string ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;                private static Hashtable m_ParmCache = Hashtable.Synchronized(new Hashtable());                /// 
        /// depiction：SQL执行无返回结果的操作        /// 
        /// 
     <数据库链接串>
              /// 
      
      <执行sql或存储过程 commandtype.text|commandtype.storedprocedure>
               /// 
       
       <执行sql或存储过程名称>
                /// 
        
        <参数集>
                 /// 
         
                  ///
          <对方法返回值的说明，该说明必须明确说明返回的值代表什么含义>
            /// 
          
                 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)        {            SqlCommand cmd = new SqlCommand();            using (SqlConnection conn = new SqlConnection(connectionString))            {                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);                int val = cmd.ExecuteNonQuery();                cmd.Parameters.Clear();                return val;            }        }        /// 
         
        /// depiction：SQL执行无返回结果的操作        /// 
        /// 
         
         <数据库连接>
                  /// 
          
          <执行sql或存储过程 commandtype.text|commandtype.storedprocedure>
            /// 
           
           <执行sql或存储过程名称>
             /// 
            
            <参数集>
              /// 
             
               ///
              <对方法返回值的说明，该说明必须明确说明返回的值代表什么含义>
                /// 
              
              public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// 
             
 /// depiction：SQL执行无返回结果的操作（存在事务操作） /// 
 /// 
             
             <事务>
               /// 
              
              <执行sql或存储过程 commandtype.text|commandtype.storedprocedure>
                /// 
               
               <执行sql或存储过程名称>
                 /// 
                
                <参数集>
                  /// 
                 
                   ///
                  <对方法返回值的说明，该说明必须明确说明返回的值代表什么含义>
                    /// 
                  
                  public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// 
                 
 /// depiction：SQL执行返回返回数据集SqlDataReader /// 
 /// 
                 
                 <数据库连接串>
                   /// 
                  
                  <执行sql或存储过程 commandtype.text|commandtype.storedprocedure>
                    /// 
                   
                   <执行sql或存储过程名称>
                     /// 
                    
                    <参数集>
                      /// 
                     
                       /// 
                      <返回数据集sqldatareader>
                        /// 
                      
                      public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connectionString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// 
                     
 /// depiction：SQL执行返回值 /// 
 /// 
                     
                     <数据库连接串>
                       /// 
                      
                      <执行sql或存储过程 commandtype.text|commandtype.storedprocedure>
                        /// 
                       
                       <执行sql或存储过程名称>
                         /// 
                        
                        <参数集>
                          /// 
                         
                           /// 
                          <返回数据集sqldatareader>
                            /// 
                          
                          public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = new SqlConnection(connectionString)) { PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// 
                         
 /// depiction：SQL执行返回值 /// 
 /// 
                         
                         <数据库连接>
                           /// 
                          
                          <执行sql或存储过程 commandtype.text|commandtype.storedprocedure>
                            /// 
                           
                           <执行sql或存储过程名称>
                             /// 
                            
                            <参数集>
                              /// 
                             
                               /// 
                              <返回sql执行结果>
                                /// 
                              
                              public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) { m_ParmCache[cacheKey] = commandParameters; } /// 
                             
 /// depiction：SQL执行返回DataTable /// 
 /// 
                             
                             <数据库连接串>
                               /// 
                              
                              <执行sql或存储过程 commandtype.text|commandtype.storedprocedure>
                                /// 
                               
                               <执行sql或存储过程名称>
                                 /// 
                                
                                <参数集>
                                  /// 
                                 
                                   /// 
                                  <返回数据集datatable>
                                    /// 
                                  
                                  public static DataTable GetTable(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { DataTable dt = new DataTable(); SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(dt); } return dt; } /// 
                                 
 /// depiction：SQL执行返回DataSet /// 
 /// 
                                 
                                 <数据库连接串>
                                   /// 
                                  
                                  <执行sql或存储过程 commandtype.text|commandtype.storedprocedure>
                                    /// 
                                   
                                   <执行sql或存储过程名称>
                                     /// 
                                    
                                    <参数集>
                                      /// 
                                     
                                       /// 
                                      <返回数据集dataset>
                                        /// 
                                      
                                      public static DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); } return ds; } public static SqlParameter[] GetCachedParameters(string cacheKey) { SqlParameter[] cachedParms = (SqlParameter[])m_ParmCache[cacheKey]; if (cachedParms == null) return null; SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++) clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms; } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } public static string ConnStr1 { get; set; } }}