SQLHelper 数据库访问助手类

SQLHelper类执行对数据库的访问，并返回值：连接DB的方法以及SqlCommand的各种方法的重写（传入参数，参数类型，返回的值）等。

App_Code文件夹存放网站中通用的类，App_Data文件夹存放一些数据文件XML文件等，WebConfig文件中存放全局的参数。

缓存可以提高对DB的访问效率，.CS文件更改后需要编译重新生成。

try,catch中可以把错误日志写到Log中。

 

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;//访问数据库



/// <summary>
/// 
/// </summary>
public class SQLHelper
{
    SqlConnection sqlCon;
    SqlCommand sqlCom;
    SqlDataReader sdr;
    public SQLHelper()
    {
     
    }
    //获取一个打开的连接
    private SqlConnection getCon()//调用时会得到一个已经 打开的sqlConnection
    {
        string strCon = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;//获得web.config中的连接字符串
        if (sqlCon==null)//还没有建立连接
        {
            sqlCon = new SqlConnection(strCon);
            sqlCon.Open();
        }
        if (sqlCon.State==ConnectionState.Closed)
        {
            sqlCon.Open();//如果处于关闭状态，不需要建立连接，只需要将其打开

        }
        return sqlCon;
    }

    //执行无参数的ExecuteNonQuery

    public int ExecuteNonQuery(string sql,CommandType ct)
    {
        int res = 0;
        try
        {
            sqlCom = new SqlCommand(sql, getCon());
            sqlCom.CommandType = ct;//sql语句类型
            res = sqlCom.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;//异常处理机制，可以把出错信息写入log中
        }
        finally
        {
            //关闭连接
            if (sqlCon.State==ConnectionState.Open)
            {
                sqlCon.Close();
            }
        }
        return res;
 
    }

    //执行的参数的NonQuery

    public int ExecuteNonQuery(string sql,SqlParameter[] sqlPara, CommandType ct)
    {
        int res = 0;
        try
        {
            sqlCom = new SqlCommand(sql, getCon());
            sqlCom.CommandType = ct;//sql语句类型
            sqlCom.Parameters.AddRange(sqlPara);//添加参数数组
            res = sqlCom.ExecuteNonQuery();
            sqlCom.Parameters.Clear();//清理参数，清空
        }
        catch (Exception ex)
        {
            throw ex;//异常处理机制，可以把出错信息写入log中
        }
        finally
        {
            //关闭连接
            if (sqlCon.State == ConnectionState.Open)
            {
                sqlCon.Close();
            }
        }
        return res;

    }

    //执行没有参数的ExcuteScalar

    public object ExecuteScalar(string sql, CommandType ct)
    {
        object res = null;
        try
        {
            sqlCom = new SqlCommand(sql, getCon());
            sqlCom.CommandType = ct;
            res = sqlCom.ExecuteScalar();

        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            if (sqlCon.State == ConnectionState.Open)
            {
                sqlCon.Close();
            }

        }
        return res;
    }

    // ExcuteScalar方法，返回object类型

    public object ExecuteScalar(string sql, SqlParameter[] sqlPara, CommandType ct)
    {
        object res=null;
        try
        {
            sqlCom = new SqlCommand(sql,getCon());
            sqlCom.CommandType = ct;
            sqlCom.Parameters.AddRange(sqlPara);
            res = sqlCom.ExecuteScalar();
            sqlCom.Parameters.Clear();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (sqlCon.State == ConnectionState.Open)
            {
                sqlCon.Close();
            }
        }
        return res;
    }

 

    //执行无参数的ExecuteReader

    public SqlDataReader ExecuteReader(string sql,CommandType ct)
    {
        SqlDataReader sdr = null;
        try
        {
            sqlCom = new SqlCommand(sql,getCon());
            sqlCom.CommandType = ct;  
            using (sdr = sqlCom.ExecuteReader(CommandBehavior.CloseConnection))//sqlDataReader注销释放空间时，同时关闭数据库的连接
            {
                
            }
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            if (sqlCon.State == ConnectionState.Open)
            {
                sqlCon.Close();
            }
        }
        return sdr;
 
    }
    // 执行有参数的ExecuteReader

    public SqlDataReader ExecuteReader(string sql,SqlParameter[] sqlPara,CommandType ct)
    {
        SqlDataReader sdr = null;
        sqlCom = new SqlCommand(sql,getCon());
        sqlCom.Parameters.AddRange(sqlPara);
        using (sdr=sqlCom.ExecuteReader(CommandBehavior.CloseConnection))
        {
            
        }
        return sdr;
    }

    // 执行无参数的ExecuteQuery
    public DataTable ExecuteQuery(string sql,CommandType ct)
    {
        DataTable dt = null;
        SqlDataReader sdr = null;
        try
        {
            sqlCom = new SqlCommand(sql, getCon());
            sqlCom.CommandType = ct;
            using (sdr = sqlCom.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }

        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            if (sqlCon.State==ConnectionState.Open)
            {
                sqlCon.Close();
            }
        }
        return dt;
    }

    // 执行有参数的ExecuteQuery
    public DataTable ExecuteQuery(string sql, SqlParameter[] sqlPara, CommandType ct)
    {
        DataTable dt = null;
        SqlDataReader sdr = null;
        sqlCom = new SqlCommand(sql,getCon());
        sqlCom.CommandType = ct;
        sqlCom.Parameters.AddRange(sqlPara);
        using (sdr=sqlCom.ExecuteReader(CommandBehavior.CloseConnection))
        {
            dt.Load(sdr);
        }
        return dt;
    }
}

 

<connectionStrings>
<add name="conStr" connectionString="server=.;database=schoolnews;uid=sa;pwd=;"/>
</connectionStrings>