我的SQLHelper

Global:

<%@ Application Language="C#" %>
<%@ Import Namespace="System.Web.Configuration" %>
<script runat="server">

    void Application_Start(object sender, EventArgs e)
    {
        // 在应用程序启动时运行的代码
        System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString.ToString());
    }
   
    void Application_End(object sender, EventArgs e)
    {
        //  在应用程序关闭时运行的代码
        System.Data.SqlClient.SqlDependency.Stop(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString.ToString());
    }
       

 

 

SQLHelper:

 

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Caching;

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

    #region 通用方法
    // 数据连接池  
    private SqlConnection con;
    /// <summary>  
    /// 返回数据库连接字符串  
    /// </summary>  
    /// <returns></returns>  
    public String GetSqlConnection()
    {
        String conn = ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString.ToString();
        return conn;
    }
    #endregion

    #region 执行sql字符串
    /// <summary>  
    /// 执行不带参数的SQL语句  
    /// </summary>  
    /// <param name="Sqlstr"></param>  
    /// <returns></returns>  
    public  int ExecuteSql(String Sqlstr)
    {
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            conn.Open();
           int ret=cmd.ExecuteNonQuery();
            conn.Close();
            return ret;
        }
    }
    /// <summary>  
    /// 执行带参数的SQL语句  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <param name="param">参数对象数组</param>  
    /// <returns></returns>  
    public  int ExecuteSql(String Sqlstr, SqlParameter[] param)
    {
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            cmd.Parameters.AddRange(param);
            conn.Open();
            int ret=cmd.ExecuteNonQuery();
            conn.Close();
            return ret;
        }
    }
    /// <summary>  
    /// 返回DataReader  
    /// </summary>  
    /// <param name="Sqlstr"></param>  
    /// <returns></returns>  
    public  SqlDataReader ExecuteReader(String Sqlstr)
    {
        String ConnStr = GetSqlConnection();
        SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的  
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            conn.Open();
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
        }
        catch //(Exception ex)  
        {
            return null;
        }
    }
    /// <summary>
    /// 返回DataReader  
    /// </summary>
    /// <param name="Sqlstr"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public SqlDataReader ExecuteReader(String Sqlstr,SqlParameter[] param)
    {
        String ConnStr = GetSqlConnection();
        SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的  
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            if (param != null)
                cmd.Parameters.AddRange(param);
            conn.Open();
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
        }
        catch //(Exception ex)  
        {
            return null;
        }
    }
    /// <summary>  
    /// 执行SQL语句并返回数据表  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <returns></returns>  
    public DataTable ExecuteDt(String Sqlstr)
    {
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
            DataTable dt = new DataTable();
            conn.Open();
            da.Fill(dt);
            conn.Close();
            return dt;
        }
    }
    /// <summary>
    /// 执行SQL语句并 可以加入缓存
    /// </summary>
    /// <param name="Sqlstr">SQL语句</param>
    /// <param name="isCache">是否加入缓存</param>
    /// <param name="CacheName">缓存名称</param>
    /// <returns></returns>
    public DataTable ExecuteDt(String Sqlstr,bool isCache,string CacheName)
    {
        DataTable dt = new DataTable();
        if (HttpContext.Current.Cache[CacheName] != null)
        {
            dt = (DataTable)HttpContext.Current.Cache[CacheName];
            //HttpContext.Current.Response.Write("1");
            return dt;
        }
        else
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand com = new SqlCommand(Sqlstr);
                com.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(com);
                if (isCache)
                {
                    SqlCacheDependency sqlDep = new SqlCacheDependency(com);
                    conn.Open();
                    da.Fill(dt);
                    HttpContext.Current.Cache.Insert(CacheName, dt, sqlDep, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.High, null);
                    com.Clone();
                    conn.Close();
                    return dt;
                }
                else
                {
                    conn.Open();
                    da.Fill(dt);
                    com.Clone();
                    conn.Close();
                    return dt;
                }
            }
        }
    }
    /// <summary>
    /// 执行SQL语句并返回数据表  
    /// </summary>
    /// <param name="Sqlstr">SQL语句</param>
    /// <param name="param">参数</param>
    /// <returns></returns>
    public DataTable ExecuteDt(String Sqlstr, SqlParameter[] param)
    {
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
            if (param != null)
            {
                da.SelectCommand.Parameters.AddRange(param);
            }
            DataTable dt = new DataTable();
            conn.Open();
            da.Fill(dt);
            da.SelectCommand.Parameters.Clear();
            conn.Close();
            return dt;
        }
    }
    /// <summary>
    /// 执行带参数的SQL 并且可以加入指定缓存
    /// </summary>
    /// <param name="Sqlstr">SQL语句</param>
    /// <param name="param">相应参数</param>
    /// <param name="isCache">是否加入缓存</param>
    /// <param name="CacheName">加入缓存key</param>
    /// <returns></returns>
    public DataTable ExecuteDt(String Sqlstr, SqlParameter[] param,bool isCache,string CacheName)
    {
        DataTable dt = new DataTable();
        if (HttpContext.Current.Cache[CacheName] != null)
        {
            dt = (DataTable)HttpContext.Current.Cache[CacheName];
           // HttpContext.Current.Response.Write("1");
            return dt;
        }
        else
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                if (param != null)
                {
                    da.SelectCommand.Parameters.AddRange(param);
                }
                if (isCache)
                {
                    SqlCacheDependency sqlDep = new SqlCacheDependency(da.SelectCommand);
                    conn.Open();
                    da.Fill(dt);
                    HttpContext.Current.Cache.Insert(CacheName, dt, sqlDep, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.High, null);
                    da.SelectCommand.Parameters.Clear();
                    da.Dispose();
                    conn.Close();
                    return dt;
                }
                else
                {
                    conn.Open();
                    da.Fill(dt);
                    da.SelectCommand.Parameters.Clear();
                    conn.Close();
                    return dt;
                }
            }
        }
    }
    public DataTable ExecuteDt(SqlCommand cmd)
    {
         String ConnStr = GetSqlConnection();
         using (SqlConnection conn = new SqlConnection(ConnStr))
         {
             SqlDataAdapter da = new SqlDataAdapter(cmd);
            
             DataTable dt = new DataTable();
             conn.Open();
             da.Fill(dt);
             da.SelectCommand.Parameters.Clear();
             conn.Close();
             return dt;
         }
    }
    /// <summary>  
    /// 执行SQL语句并返回DataSet  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <returns></returns>  
    public DataSet ExecuteDs(String Sqlstr)
    {
        String ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
            DataSet ds = new DataSet();
            conn.Open();
            da.Fill(ds);
            conn.Close();
            return ds;
        }
    }
    /// <summary>
    /// 执行SQL语句返回DataSet
    /// </summary>
    /// <param name="Sqlstr">SQL语句</param>
    /// <param name="isCache">是否进行缓存</param>
    /// <param name="CacheName">缓存名称</param>
    /// <returns></returns>
    public DataSet ExecuteDs(String Sqlstr,bool isCache,String CacheName)
    {
        DataSet ds = new DataSet();
        if (HttpContext.Current.Cache[CacheName] != null)
        { ds = (DataSet)HttpContext.Current.Cache[CacheName]; return ds; }
        else
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand com = new SqlCommand(Sqlstr);
                com.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(com);
               
                if (isCache)
                {
                    SqlCacheDependency sqlDp = new SqlCacheDependency(com);
                    conn.Open();
                    da.Fill(ds);
                    HttpContext.Current.Cache.Insert(CacheName, ds, sqlDp, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.High, null);
                    conn.Close();
                    return ds;
                }
                else
                {
                    conn.Open();
                    da.Fill(ds);
                    conn.Close();
                    return ds;
                }
            }
        }
    }

    /// <summary>
    /// 执行SQL语句并返回DataSet  
    /// </summary>
    /// <param name="Sqlstr"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public DataSet ExecuteDs(String Sqlstr,SqlParameter[] param,bool isCache,string CacheName)
    {
         DataSet ds = new DataSet();
         if (HttpContext.Current.Cache[CacheName] != null)
         {
             ds = (DataSet)HttpContext.Current.Cache[CacheName];
             //HttpContext.Current.Response.Write("1");
             return ds;
         }
         else
         {
             String ConnStr = GetSqlConnection();
             using (SqlConnection conn = new SqlConnection(ConnStr))
             {
                 SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                 if (param != null)
                 {
                     da.SelectCommand.Parameters.AddRange(param);
                 }
                 //加入缓存

                 if (isCache)
                 {
                     SqlCacheDependency sqlDp = new SqlCacheDependency(da.SelectCommand);
                     conn.Open();
                     da.Fill(ds);
                     HttpContext.Current.Cache.Insert(CacheName, ds, sqlDp, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.High, null);
                     da.SelectCommand.Parameters.Clear();
                     conn.Close();
                     return ds;
                 }
                 else
                 {
                     conn.Open();
                     da.Fill(ds);
                     da.SelectCommand.Parameters.Clear();
                     con.Close();
                     return ds;
                 }
             }
         }
    }
    /// <summary>
    ///  执行SQL返回第一单元格数据
    /// </summary>
    /// <param name="Sqlstr"></param>
    /// <returns></returns>
    public  object ExecuteSa(String Sqlstr)
    {
        string ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            conn.Open();
            object ret = cmd.ExecuteScalar();
            conn.Close();
            return ret;
        }
    }
    /// <summary>
    /// 执行SQL返回第一单元格数据
    /// </summary>
    /// <param name="Sqlstr"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public object ExecuteSa(String Sqlstr, SqlParameter[] param)
    {
        string ConnStr = GetSqlConnection();
        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Sqlstr;
            if (param != null)
                cmd.Parameters.AddRange(param);
            conn.Open();
            object ret = cmd.ExecuteScalar();
            conn.Close();
            return ret;
        }
    }
    /// <summary>
    /// 执行SQL语句返回 SqlCommand
    /// </summary>
    /// <param name="Sqlstr"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    //public SqlCommand CreateCmd(String Sqlstr, SqlParameter[] param)
    //{
    //    Open();
    //    SqlCommand cmd = new SqlCommand(Sqlstr, con);
    //    if (param != null)
    //    {
    //        foreach (SqlParameter parameter in param)
    //            cmd.Parameters.Add(parameter);
    //    }
    //    return cmd;
    //}
    //public SqlCommand CreateCmd(string conString,string cmdString)
    //{
    //    string conStr = "server=.;database=HousingData;User ID=cx637;Password=ppp123;";
    //    using (SqlConnection con = new SqlConnection(conString))
    //    {
    //        SqlCommand cmd = new SqlCommand();
    //        cmd.Connection = con;
    //        cmd.CommandText = cmdString;
    //        con.Open();
    //        return cmd;
    //    }
    //}
    #endregion

    #region 操作存储过程
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <returns>存储过程的返回值</returns>  
    public int RunProc(string procName)
    {
        SqlCommand cmd = CreateCommand(procName, null);
        int ret= cmd.ExecuteNonQuery();
        this.Close();
        return ret;
    }
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="prams">存储过程的输入参数列表</param>  
    /// <returns>存储过程的返回值</returns>  
    public int RunProc(string procName, SqlParameter[] prams)
    {
        SqlCommand cmd = CreateCommand(procName, prams);
        int ret=cmd.ExecuteNonQuery();
        this.Close();
        return ret;
    }
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="dataReader">结果集</param>  
    public void RunProc(string procName, out SqlDataReader dataReader)
    {
        SqlCommand cmd = CreateCommand(procName, null);
        dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="prams">存储过程的输入参数列表</param>  
    /// <param name="dataReader">结果集</param>  
    public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
    {
        SqlCommand cmd = CreateCommand(procName, prams);
        dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    /// <summary>  
    /// 创建Command对象用于访问存储过程  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="prams">存储过程的输入参数列表</param>  
    /// <returns>Command对象</returns>  
    private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
    {
        // 确定连接是打开的  
        Open();
        //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );  
        SqlCommand cmd = new SqlCommand(procName, con);
        cmd.CommandType = CommandType.StoredProcedure;
        // 添加存储过程的输入参数列表  
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
                cmd.Parameters.Add(parameter);
        }
        // 返回Command对象  
        return cmd;
    }
    /// <summary>  
    /// 创建输入参数  
    /// </summary>  
    /// <param name="ParamName">参数名</param>  
    /// <param name="DbType">参数类型</param>  
    /// <param name="Size">参数大小</param>  
    /// <param name="Value">参数值</param>  
    /// <returns>新参数对象</returns>  
    public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }
    /// <summary>  
    /// 创建输出参数  
    /// </summary>  
    /// <param name="ParamName">参数名</param>  
    /// <param name="DbType">参数类型</param>  
    /// <param name="Size">参数大小</param>  
    /// <returns>新参数对象</returns>  
    public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
    }
    /// <summary>  
    /// 创建存储过程参数  
    /// </summary>  
    /// <param name="ParamName">参数名</param>  
    /// <param name="DbType">参数类型</param>  
    /// <param name="Size">参数大小</param>  
    /// <param name="Direction">参数的方向(输入/输出)</param>  
    /// <param name="Value">参数值</param>  
    /// <returns>新参数对象</returns>  
    public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
    {
        SqlParameter param;
        if (Size > 0)
        {
            param = new SqlParameter(ParamName, DbType, Size);
        }
        else
        {
            param = new SqlParameter(ParamName, DbType);
        }
        param.Direction = Direction;
        if (!(Direction == ParameterDirection.Output && Value == null))
        {
            param.Value = Value;
        }
        return param;
    }
    #endregion

    #region 数据库连接和关闭
   
    /// <summary>  
    /// 打开连接池  
    /// </summary>  
    private void Open()
    {
        // 打开连接池  
        if (con == null)
        {
            //这里不仅需要using System.Configuration;还要在引用目录里添加  
            con = new SqlConnection(GetSqlConnection());
            con.Open();
        }
    }
    /// <summary>  
    /// 关闭连接池  
    /// </summary>  
    public void Close()
    {
        if (con != null)
            con.Close();
    }
    /// <summary>  
    /// 释放连接池  
    /// </summary>  
    public void Dispose()
    {
        // 确定连接已关闭  
        if (con != null)
        {
            con.Dispose();
            con = null;
        }
    }
   
    #endregion

}