三层架构之数据层（完美版）

用ASP.net的程序员，都知道三层架构，但往往把，业务层和数据层分开来，并不简单
 下面的一个原码是我项目的数据层，没有整理，看官可以根据需要自行更改，切不可套用
         原码：
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>
/// ADO.NET data accewss using the sql server Managed Provider
/// </summary>
public class listDataBind:IDisposable
{
    private SqlConnection conn;
    private SqlDataAdapter cmd;

  
   
    /// <summary>
    /// run stored procedure
    /// </summary>
    /// <param name="procName"></param>
    /// <param name="prams"></param>
    /// <returns></returns>
   /////////////执行ＳＱＬ语句
   public object RunSql(string strsql)
   {
       open();
       SqlCommand cmd=new SqlCommand(strsql,conn);
       try
       {
           return cmd.ExecuteScalar();

       }
       catch (Exception ex)
       {
           Error.Log(ex.ToString());
           return null;
       }
       finally
       {
           close();
       }

   }
   
 
    public DataSet RunProc(string procName)
    {
        DataSet ds = new DataSet();
       cmd = CreateCommand(procName, null);
       cmd.Fill(ds, "table");
       this.close();
    
       ds.Dispose();
        return ds;
    }
    public DataSet RunSQLstring(string sqlstr)
    {
        DataSet ds = new DataSet();
        open();
       cmd = new SqlDataAdapter(sqlstr, conn);
        cmd.Fill(ds, "table");
        this.close();
         ds.Dispose();
        return ds;
      

      
    }
    /// <summary>
    /// run stored procedure
    /// </summary>
    /// <param name="procName"></param>
    /// <param name="prams"></param>
    /// <param name="dataReader"></param>
    public DataSet RunProc(string procName, SqlParameter[] prams)
    {
        DataSet ds = new DataSet();
         cmd = CreateCommand(procName, prams);
        cmd.Fill(ds, "table");
        this.close();
     
        ds.Dispose();
        return ds;
    }
    public DataSet RunProc(string procName, SqlParameter[] prams, int startIndex, int pageSize)
    {
        DataSet ds = new DataSet();
        cmd = CreateCommand(procName, prams);
        cmd.Fill(ds, startIndex, pageSize, "table");
        cmd.Fill(ds, "AllDataTable");
        this.close();
     
        ds.Dispose();
        return ds;
 
    }
    private SqlDataAdapter CreateCommand(string procName, SqlParameter[] prams)
    {
           //maike sure connection is opn
        open();
        cmd = new SqlDataAdapter(procName, conn);
        cmd.SelectCommand.CommandType = CommandType.StoredProcedure;
        //add proc parameters
        if (prams != null)
        {
           
            foreach (SqlParameter parameter in prams)
                cmd.SelectCommand.Parameters.Add(parameter);   
        }
        //return param
        cmd.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
        return cmd;

 

    }
    private void open()
    {
        //open connenction
        if (conn == null)
        {
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString);
            conn.Open();
        }
    }
    private void close()
    {
        if (conn != null)
        {
            conn.Close();
        }
    }
    public void Dispose()
    {
        //maike sure connection is closed
        if (conn == null)
        {
            Dispose(true);
            //阻止GC调用Finalize析构函数
            GC.SuppressFinalize(true);
        }
    }
    protected virtual void Dispose(bool disposing)
    {
        if (!disposing)
       
            return;
            //逐层释放SqlDataAdapter占用的资源
            if (cmd != null)
            {
                if (cmd.SelectCommand != null)
                {
                    if (cmd.SelectCommand.Connection != null)
                      {
                        cmd.SelectCommand.Connection.Dispose();

                      }
                    }
                cmd.SelectCommand.Dispose();
                cmd = null;
            }
      
    }
   
    /// <summary>
    /// maike input param
    /// </summary>
    ///<param name="paramName">Name of param</param>
    ///<param name="Dbtyple">Param type</param>
    ///<param name="size">param value</param>
    ///<returns>new parameter</returns>
    public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Input,Value);
 
    }
    /// <summary>
    /// make input param
    /// </summary>
    /// <param name="paramName">name of param</param>
    /// <param name="DbType">param type</param>
    /// <param name="size">paramter size</param>
    /// <returns>new Parameter</returns>
    public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
 
    }
    /// <summary>
    /// make stored procedure param
    /// </summary>
    /// <param name="ParamName">Name of param</param>
    /// <param name="Dbtyple">param type</param>
    /// <param name="Direction">param dirction</param>
    /// <param name="Value">param value</param>
    /// <returns>new Parameter</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;
    }

 public listDataBind()
 {
  //
  // TODO: 在此处添加构造函数逻辑
  //
 }

}
    里面的Error是一个单独处理异常错误的类，主要是用EventLog添加日志 ，是.net提供的，有兴趣的可以查一下