DataAccessLayer.cs文件

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;

/// <summary>
/// CNetWorks数据访问层
///**************************************
/// DesignBy:兴百放
/// CreateDate:2006-12-09 4:41
/// *************************************
/// </summary>
public class DataAccessLayer
{
    private SqlCommand cmd;                   //建立Command对象
    private static string errorMeg;           //错误信息包括Sql语句及存储过程
    private static bool isShowErrorMeg;       //是否显示错误信息

    /// <summary>
    /// 返回值参数类型
    /// </summary>
    public enum ParameterKind
    {
        Int, NVarChar, Bit, VarChar                      //如果变化，可以在加
    }
    /// <summary>
    /// 获得错误信息
    /// </summary>
    public string Error
    {
        get
        {
            return errorMeg;
        }
    }

    /// <summary>
    /// 构造函数，初始化值
    /// </summary>
 
    public DataAccessLayer()
 {
        cmd = new SqlCommand();              //建立Command 连接
        //只要修该这里的参数就行，把你的Web.Config中的连接字符串名改成你的就行

        cmd.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NicNetWorkConnectionString"].ConnectionString);
        isShowErrorMeg = true;               //本地运行为True;服务器运行为False;
 }

    /// <summary>
    /// 初始化cm.CommandType和cm.CommandType
    /// </summary>
    /// <param name="commandText">执行存储过程或Sql语句</param>
    /// <param name="commandType">1--Sql语句；2--存储过程</param>
    public void SetCommand(string commandText,int commandType)
    {
        errorMeg = "0";                       //清空错误信息
        cmd.CommandText = commandText;
        if (commandType == 1)
        {
            cmd.CommandType = CommandType.Text; //执行Sql语句
        }
        else
        {
            cmd.CommandType = CommandType.StoredProcedure;   //执行存储过程
        }
    }

//---------------------------------------设置错误信息---------------------------------------------//
    /// <summary>
    /// 设置显示错误信息
    /// </summary>
    /// <param name="FunctionName">出现错误的函数名</param>
    /// <param name="cmdtext">错误Sql语句或存储过程</param>
    /// <param name="message">错误信息</param>
    private void SetErrorMeg(string FunctionName,string cmdtext,string message)
    {
        errorMeg = "<br>函数名为：" + FunctionName + "出现错误.<br>错误名为：" + message;
        if(isShowErrorMeg)              
        {
            errorMeg += "<br>查询语句或存储过程是：" + cmdtext;

        }
        addLogErr(cmdtext,errorMeg);
        cmd.Connection.Close();    //关闭连接
    }

记录日志文件
/// <summary>
/// 把错误日志记录到log文件下
/// </summary>
/// <param name="SPName">查询语句或者存储过程名</param>
/// <param name="ErrDescribe">错误描述</param>
    public void addLogErr(string SPName, string ErrDescribe)
    {
        //记录到错误日志
        string FilePath = System.Web.HttpContext.Current.Server.MapPath("~/Log/" + DateTime.Now.ToString("yyyyMMdd") + ".txt");
        System.Text.StringBuilder str = new System.Text.StringBuilder();
        str.Append(DateTime.Now.ToString());
        str.Append(" ");
        str.Append(SPName);
        str.Append(" ");
        str.Append(ErrDescribe.Replace("<br>", ""));
        str.Append(" ");

        System.IO.StreamWriter sw = null;
        try
        {
            sw = new System.IO.StreamWriter(FilePath, true, System.Text.Encoding.Unicode);
            sw.Write(str.ToString());
        }
        catch (Exception ex)
        {
            System.Web.HttpContext.Current.Response.Write("没有访问日志文件的权限！或日志文件只读!");
        }
        finally
        {
            if (sw != null)
                sw.Close();
        }
    }
    记录日志文件
    //---------------------------------------设置错误信息---------------------------------------------//

 

 

    //---------------------------------- —— 存储过程部分，包括存储过程的参数----------------------------//
    /// <summary>
    /// 清空参数值
    /// </summary>
    public void ClearParamers()
    {
        cmd.Parameters.Clear();     //清空参数
    }
  
 
    /// <summary>
    /// 设置返回方向
    /// </summary>
    /// <param name="ParameterName">参数名，如@Return</param>
    /// <param name="Kind">参数的类型，如nvchar</param>
    /// <param name="Des">参数的方向,1-输出参数；2-返回值;3-输入参数</param>
    /// <param name="valueSize">输入参数的值得大小</param>
    public void AddNewParameter(string ParameterName,ParameterKind Kind,string Des,string valueSize)
    {
        switch(Kind)
        {
            case ParameterKind.Bit :
                cmd.Parameters.Add(ParameterName,SqlDbType.Bit,int.Parse(valueSize));   //设置参数
                break;
            case ParameterKind.Int :
                cmd.Parameters.Add(ParameterName,SqlDbType.Int,int.Parse(valueSize));   //设置参数
                break; 
            case ParameterKind.NVarChar :
                cmd.Parameters.Add(ParameterName,SqlDbType.NVarChar,int.Parse(valueSize));   //设置参数
                break;
            case ParameterKind.VarChar :
                cmd.Parameters.Add(ParameterName,SqlDbType.VarChar,int.Parse(valueSize));
                break;
        }
        switch(Des)
        {
            case "1" :
                cmd.Parameters[ParameterName].Direction = ParameterDirection.Output;   //设置方向
                break;
            case "2" :
                cmd.Parameters[ParameterName].Direction = ParameterDirection.ReturnValue;   //设置方向
                break;
            case "3" :
                cmd.Parameters[ParameterName].Direction = ParameterDirection.Input;
                break;
        }
        //if (Des == "1")
        //{
           
        //}
        //else if (Des == "2")
        //{
           
        //}
        //else
        //{
           
        //}
    }
    /// <summary>
    /// 根据参数获得存储过程的返回值
    /// </summary>
    /// <param name="ParameterName">参数名，如@UserName</param>
    /// <returns></returns>
    public string GetParameter(string ParameterName)
    {
        //cmd.Parameters[ParameterName].
        return cmd.Parameters[ParameterName].Value.ToString();
    }
    public int GetParameter(int ParameterName)
    {
        return int.Parse(cmd.Parameters[ParameterName].Value.ToString());
    }
    /// 添加参数值
    /// </summary>
    /// <param name="ParameterName">参数名 如@UserName</param>
    /// <param name="ParameterValue">参数名的值</param>
    public void AddNewParameter(string ParameterName,string ParameterValue)
    {
        cmd.Parameters.Add(ParameterName,ParameterValue);
    }
    public void AddNewParameter(string ParameterName,int ParameterValue)
    {
        cmd.Parameters.Add(ParameterName,ParameterValue);
    }
    public void AddNewParameter(string ParameterName, bool ParameterValue)
    {
        cmd.Parameters.Add(ParameterName, ParameterValue);
    }

    //运行存储过程返回DataSet
    /// <summary>
    /// 运行存储过程返回DataSet,DataSet里面可能有好多表
    /// </summary>
    /// <param name="StroreName">存储过程名</param>
    /// <returns>返回DataSet如果出错侧返回null</returns>
    public DataSet RunStoreDataSet(string StroreName)
    {
        SetCommand(StroreName,2);  //执行存储过程
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        try
        {
            DataSet ds = new DataSet();
            sda.Fill(ds);
            return ds;
        }
        catch(System.Exception e1)
        {
            SetErrorMeg("RunStoreDataSet", StroreName, e1.ToString());   //处理错误
            return null;
        }
    }
    /// <summary>
    /// 执行存储过程返回DataTable，一张表最好用它；
    /// </summary>
    /// <param name="StoreName">存储过程名</param>
    /// <returns>返回DataTable</returns>
    public DataTable RunStoreDataTable(string StoreName)
    {
        SetCommand(StoreName, 2);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        try
        {
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        catch (System.Exception e1)
        {
            SetErrorMeg("RunStoreDataTable", StoreName, e1.ToString());   //处理错误
            return null;
        }
        finally
        {
            sda.Dispose();   //释放资源
        }
    }
    /// <summary>
    /// 运行存储过程返回数据阅读器,单行纪录
    /// </summary>
    /// <param name="StoreName">存储过程名</param>
    /// <returns>DataReader</returns>
    public SqlDataReader RunStoreNameDataReader(string StoreName)
    {
        SqlDataReader dr=null;

        SetCommand(StoreName,2);
        try
        {
            if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
             dr= cmd.ExecuteReader();
        }
        catch (System.Exception e1)
        {
            SetErrorMeg("RunStoreNameDataReader", StoreName, e1.ToString());    //处理错误信息
        }
        return dr;
    }

    /// <summary>
    /// 运行存储过程，用于添加数据的添加，删除，修改,无返回值
    /// </summary>
    /// <param name="StoreName">存储过程名</param>
    public void RunStore(string StoreName)
    {
        SetCommand(StoreName, 2);
        try
        {
            //如果cmd对象的连接关闭侧打开
            if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            //执行
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception e1)
        {
            SetErrorMeg("RunStore", StoreName, e1.ToString());    //处理错误信息
        }
        //finally
        //{
        //    Dispone();
        //}
    }
    /// <summary>
    /// 运行存储过程，返回第一行第一列
    /// </summary>
    /// <param name="StoreName"></param>
    /// <returns></returns>
    public string RunStoreFirst(string StoreName)
    {
        SetCommand(StoreName, 2);
        string flag = null;
        try
        {
            if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            //执行
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                flag = dr.GetValue(0).ToString();
            }
        }
        catch (System.Exception e1)
        {
            SetErrorMeg("RunStore", StoreName, e1.ToString());
            return null;
        }
        Dispone();
        return flag;
    }
    //---------------------------------- —— 存储过程部分，包括存储过程的参数----------------------------//

    //---------------------------------------sql部分---------------------------------------------
    /// <summary>
    /// 运行SQl语句返回第一条记录的第一列的值。
    /// </summary>
    /// <param name="sqlName"></param>
    public string RunSql(string sqlName)
    {
        SetCommand(sqlName,1);
        string flag = null;
        try
        {
            if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            //执行
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                flag = dr.GetValue(0).ToString();
            }
        }
        catch (System.Exception e1)
        {
            SetErrorMeg("RunSql", sqlName, e1.ToString());
            return null;
        }
        Dispone();
        return flag;
    }
    /// <summary>
    /// 执行sql语句，返回DataSet
    /// </summary>
    /// <param name="SqlName">sql语句</param>
    /// <returns>返回DataSet</returns>
    public DataSet RunSqlDataSet(string SqlName)
    {
        SetCommand(SqlName, 1);  //执行sql
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        try
        {
            DataSet ds = new DataSet();
            sda.Fill(ds);
            return ds;
        }
        catch (System.Exception e1)
        {
            SetErrorMeg("RunSqlDataSet", SqlName, e1.ToString());   //处理错误
            return null;
        }
        //finally
        //{
        //    sda.Dispose();  //释放sda的资源
        //}
    }
    /// <summary>
    /// 执行sql语句返回DataTable
    /// </summary>
    /// <param name="SqlName">sql语句</param>
    /// <returns>返回DataSet</returns>
    public DataTable RunSqlDataTable(string SqlName)
    {
        SetCommand(SqlName, 1);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        try
        {
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        catch (System.Exception e1)
        {
            SetErrorMeg("RunSqlDataTable", SqlName, e1.ToString());   //处理错误
            return null;
        }
        finally
        {
            sda.Dispose();   //释放资源
        }
    }
    /// <summary>
    /// 执行sql语句，返回DataReader
    /// </summary>
    /// <param name="SqlName"></param>
    /// <returns></returns>
    public SqlDataReader RunSqlNameDataReader(string SqlName)
    {
        SqlDataReader dr = null;

        SetCommand(SqlName, 1);
        try
        {
            if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            dr = cmd.ExecuteReader();
        }
        catch (System.Exception e1)
        {
            SetErrorMeg("RunSqlNameDataReader", SqlName, e1.ToString());    //处理错误信息
        }
        return dr;
    }
    /// <summary>
    /// 执行sql语句，无返回值，用于数据的添加，删除，修改
    /// </summary>
    /// <param name="SqlName">sql语句</param>
    public void RunSqlName(string SqlName)
    {
        SetCommand(SqlName, 1);
        try
        {
            //如果cmd对象的连接关闭侧打开
            if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            //执行
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception e1)
        {
            SetErrorMeg("RunSql", SqlName, e1.ToString());    //处理错误信息
        }
    }

    //---------------------------------------sql部分---------------------------------------------

    释放资源
    public void Dispone()
    {
        errorMeg=null;
        cmd.Parameters.Clear();
        cmd.Connection.Close();
        cmd.Dispose();
    }
}