向前

using System; using System.Data; using System.Configuration; using System.Web; using System.Data.SqlClient;

/// <summary> ///SQLHelper 的摘要说明 /// </summary> public class SQLHelper {     public SQLHelper()     {         //         //TODO: 在此处添加构造函数逻辑         //     }

    //获取数据库连接字符串，其属于静态变量且只读，项目中所有文档可以直接使用，但不能修改     public static readonly string StrCon = ConfigurationManager.AppSettings["strCon"];     public static readonly string ConStr = ConfigurationManager.AppSettings["con"];

    private SqlConnection getConn()     {         SqlConnection conn = new SqlConnection(StrCon);

        conn.Open();

        return conn;     }     /// <summary>     /// 为执行命令准备参数     /// </summary>     /// <param name="cmd">SqlCommand 命令</param>     /// <param name="conn">已经存在的数据库连接</param>     /// <param name="trans">数据库事物处理</param>     /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>     /// <param name="cmdText">Command text，T-SQL语句 例如 Select * from Products</param>     /// <param name="cmdParms">返回带参数的命令</param>     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);         }     }     /// <summary>     /// 执行一条不返回结果的SqlCommand，通过一个已经存在的数据库事物处理     /// 使用参数数组提供参数     /// </summary>     /// <remarks>     /// 使用示例：     ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));     /// </remarks>     /// <param name="trans">一个存在的 sql 事物处理</param>     /// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>     /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>     /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>     /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>     public 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;     }     ///// <summary>     ///// 执行一条不返回结果的SqlCommand，通过一个已经存在的数据库事物处理     ///// 使用参数数组提供参数     ///// </summary>     ///// <remarks>     ///// 使用示例：     /////  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));     ///// </remarks>     ///// <param name="trans">一个存在的 sql 事物处理</param>     ///// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>     ///// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>     ///// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>     ///// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>     //public 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;     //}

    //查询     public SqlDataReader execQuery(string sql, SqlParameter[] parames)     {         SqlConnection conn = getConn();

        SqlCommand comm = new SqlCommand(sql, conn);

        if (parames != null)         {             foreach (SqlParameter para in parames)             {                 comm.Parameters.Add(para);             }         }

        return comm.ExecuteReader(CommandBehavior.CloseConnection);

    }

    #region 重载查询     public SqlDataReader execQuery(string sql, SqlParameter[] parames,CommandType ct)     {         SqlConnection conn = getConn();

        SqlCommand comm = new SqlCommand(sql, conn);         comm.CommandType = ct;         if (parames != null)         {             foreach (SqlParameter para in parames)             {                 comm.Parameters.Add(para);             }         }

        return comm.ExecuteReader(CommandBehavior.CloseConnection);

    }     #endregion

    //增删改     public int execNonQuery(string sql, SqlParameter[] parames)     {         SqlConnection conn = getConn();

        SqlCommand comm = new SqlCommand(sql, conn);

        if (parames != null)         {             foreach (SqlParameter para in parames)             {                 comm.Parameters.Add(para);             }         }

        try         {             return comm.ExecuteNonQuery();         }         catch (Exception e)         {             throw e;         }         finally         {             if (conn.State == ConnectionState.Open)             {                 conn.Close();                 conn.Dispose();             }         }     }

    /// <summary>     /// 返回DataSet     /// </summary>     /// <param name="sql">要执行的SQL语句</param>     /// <param name="parames">需要的参数</param>     /// <returns></returns>     public DataSet execForDataSet(string sql, SqlParameter[] parames)     {         SqlConnection conn = getConn();

        //SqlDataAdapter sda = new SqlDataAdapter(sql, conn);  无参数时使用

        //有参数时使用         SqlCommand comm = new SqlCommand(sql, conn);

        if (parames != null)         {             foreach (SqlParameter para in parames)             {                 comm.Parameters.Add(para);             }         }

        SqlDataAdapter sda = new SqlDataAdapter(comm);

        DataSet ds = new DataSet();

        sda.Fill(ds);

        return ds;     }

    /// <summary>     /// 执行存储过程，返回DataSet     /// </summary>     /// <param name="sql"></param>     /// <param name="parames"></param>     /// <returns></returns>     public DataSet execProc(string sql, SqlParameter[] parames, CommandType type)     {         SqlConnection conn = getConn();

        SqlCommand comm = new SqlCommand(sql, conn);         comm.CommandType = type;

        if (parames != null)         {             foreach (SqlParameter para in parames)             {                 comm.Parameters.Add(para);             }         }

        SqlDataAdapter sda = new SqlDataAdapter(comm);

        DataSet ds = new DataSet();

        sda.Fill(ds);

        return ds;

    }

    /// <summary>     /// 返回统计结果     /// </summary>     /// <param name="sql"></param>     /// <param name="parames"></param>     /// <param name="type"></param>     /// <returns></returns>     public int execScalar(string sql, SqlParameter[] parames)     {         SqlConnection conn = getConn();

        SqlCommand comm = new SqlCommand(sql, conn);         comm.CommandType = CommandType.StoredProcedure;

        if (parames != null)         {             foreach (SqlParameter para in parames)             {                 comm.Parameters.Add(para);             }         }         try         {             return Convert.ToInt32(comm.ExecuteScalar());         }         catch (Exception e)         {             throw e;         }         finally         {             if (conn.State == ConnectionState.Open)             {                 conn.Close();                 conn.Dispose();             }         }

    }

    #region 重载查询     public int execScalar(string sql)     {         SqlConnection conn = getConn();

        SqlCommand comm = new SqlCommand(sql, conn);                      try         {             return Convert.ToInt32(comm.ExecuteScalar());         }         catch (Exception e)         {             throw e;         }         finally         {             if (conn.State == ConnectionState.Open)             {                 conn.Close();                 conn.Dispose();             }         }

    }     #endregion }