架构B/S四 DBUtility 数据层基类 放公共类(二) SqlHelper 类

放公共类

如:SQLHelper.cs数据访问抽象基础类    ControlBindHelper.cs 控件帮定抽象基础类 DataBase.cs 操作控件类 MessageBox.cs 显示消息提示对话框类。 PageValidate.cs 页面数据校验类  

SQLHelper.cs数据访问抽象基础类 代码

/// <summary>
   /// 执行多条SQL语句，实现数据库事务。
   /// </summary>
   /// <param name="SQLStringList">多条SQL语句</param>  
   public static void ExecuteSqlTran(ArrayList SQLStringList)
   {
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
     conn.Open();
     SqlCommand cmd = new SqlCommand();
     cmd.Connection=conn;    
     SqlTransaction tx=conn.BeginTransaction();   
     cmd.Transaction=tx;    
     try
     {     
      for(int n=0;n<SQLStringList.Count;n++)
      {
       string strsql=SQLStringList[n].ToString();
       if (strsql.Trim().Length>1)
       {
        cmd.CommandText=strsql;
        cmd.ExecuteNonQuery();
       }
      }          
      tx.Commit();
     }
     catch(System.Data.SqlClient.SqlException E)
     {  
      tx.Rollback();
      throw new Exception(E.Message);
     }
    }
   }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>  
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count+=cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
        /// <summary>
        /// WangHaojie
        /// 执行两条以上SQL语句，LOG使用，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>  
        public static int ExecuteSqlTranWHJ(List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();                   
                    return count;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
        /// <summary>
        /// WangHaojie 写
        /// 实现数据库事务，用于添加煤票。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>  
        public static int SqlTranWHJ(List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    //int LogCount = 0;
                    int CollKicket = 0;
                    int CoalAccountManage = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
   /// <summary>
   /// 执行带一个存储过程参数的的SQL语句。
   /// </summary>
   /// <param name="SQLString">SQL语句</param>
   /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
   /// <returns>影响的记录数</returns>
   public static int ExecuteSql(string SQLString,string content)
   {    
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
     SqlCommand cmd = new SqlCommand(SQLString,connection);  
     System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText);
     myParameter.Value = content ;
     cmd.Parameters.Add(myParameter);
     try
     {
      connection.Open();
      int rows=cmd.ExecuteNonQuery();
      return rows;
     }
     catch(System.Data.SqlClient.SqlException E)
     {    
      throw new Exception(E.Message);
     }
     finally
     {
      cmd.Dispose();
      connection.Close();
     }
    }
   }  
   /// <summary>
   /// 执行带一个存储过程参数的的SQL语句。
   /// </summary>
   /// <param name="SQLString">SQL语句</param>
   /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
   /// <returns>影响的记录数</returns>
   public static object ExecuteSqlGet(string SQLString,string content)
   {    
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
     SqlCommand cmd = new SqlCommand(SQLString,connection);  
     System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText);
     myParameter.Value = content ;
     cmd.Parameters.Add(myParameter);
     try
     {
      connection.Open();
      object obj = cmd.ExecuteScalar();
      if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
      {     
       return null;
      }
      else
      {
       return obj;
      }  
     }
     catch(System.Data.SqlClient.SqlException E)
     {    
      throw new Exception(E.Message);
     }
     finally
     {
      cmd.Dispose();
      connection.Close();
     }
    }
   }  
   /// <summary>
   /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
   /// </summary>
   /// <param name="strSQL">SQL语句</param>
   /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
   /// <returns>影响的记录数</returns>
   public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
   {  
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
     SqlCommand cmd = new SqlCommand(strSQL,connection);
     System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image);
     myParameter.Value = fs ;
     cmd.Parameters.Add(myParameter);
     try
     {
      connection.Open();
      int rows=cmd.ExecuteNonQuery();
      return rows;
     }
     catch(System.Data.SqlClient.SqlException E)
     {    
      throw new Exception(E.Message);
     }
     finally
     {
      cmd.Dispose();
      connection.Close();
     }    
    }
   }
   /// <summary>
   /// 执行一条计算查询结果语句，返回查询结果（object）。
   /// </summary>
   /// <param name="SQLString">计算查询结果语句</param>
   /// <returns>查询结果（object）</returns>
   public static object GetSingle(string SQLString)
   {
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
     using(SqlCommand cmd = new SqlCommand(SQLString,connection))
     {
      try
      {
       connection.Open();
       object obj = cmd.ExecuteScalar();
       if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
       {     
        return null;
       }
       else
       {
        return obj;
       }    
      }
      catch(System.Data.SqlClient.SqlException e)
      {      
       connection.Close();
       throw new Exception(e.Message);
      }
     }
    }
   }
   public static object GetSingle(string SQLString,int Times)
   {
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
     using(SqlCommand cmd = new SqlCommand(SQLString,connection))
     {
      try
      {
       connection.Open();
       cmd.CommandTimeout=Times;
       object obj = cmd.ExecuteScalar();
       if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
       {     
        return null;
       }
       else
       {
        return obj;
       }    
      }
      catch(System.Data.SqlClient.SqlException e)
      {      
       connection.Close();
       throw new Exception(e.Message);
      }
     }
    }
   }
   /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：使用后一定要对SqlDataReader进行Close )
   /// </summary>
   /// <param name="strSQL">查询语句</param>
   /// <returns>SqlDataReader</returns>
   public static SqlDataReader ExecuteReader(string strSQL)
   {
    SqlConnection connection = new SqlConnection(connectionString);   
    SqlCommand cmd = new SqlCommand(strSQL,connection);    
    try
    {
     connection.Open();
     SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
     return myReader;
    }
    catch(System.Data.SqlClient.SqlException e)
    {        
     throw new Exception(e.Message);
    }  
//    finally
//    {
//     cmd.Dispose();
//     connection.Close();
//    }
   }  
   /// <summary>
   /// 执行查询语句，返回DataSet
   /// </summary>
   /// <param name="SQLString">查询语句</param>
   /// <returns>DataSet</returns>
   public static DataSet Query(string SQLString)
   {
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
     DataSet ds = new DataSet();
     try
     {
      connection.Open();     
      SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);
      command.Fill(ds,"ds");
     }
     catch(System.Data.SqlClient.SqlException ex)
     {    
      throw new Exception(ex.Message);
     }   
     return ds;
    }   
   }
   public static DataSet Query(string SQLString,int Times)
   {
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
     DataSet ds = new DataSet();
     try
     {
      connection.Open();     
      SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);  
      command.SelectCommand.CommandTimeout=Times;
      command.Fill(ds,"ds");
     }
     catch(System.Data.SqlClient.SqlException ex)
     {    
      throw new Exception(ex.Message);
     }   
     return ds;
    }   
   }

   #endregion

        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataTable</returns>
        public static DataTable ReturnTableQuery(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataTable dtbl = new DataTable();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(dtbl);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return dtbl;
            }
        }
        public static DataTable ReturnTableQuery(string SQLString, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataTable dtbl = new DataTable();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.SelectCommand.CommandTimeout = Times;
                    command.Fill(dtbl);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return dtbl;
            }
        }