步步为营 .NET三层架构解析五、DAL与IDAL的设计

IDAL:数据访问层接口,接口是一种系列‘功能’的声明或名单，接口没有实现细节.

IDAL的作用是把访问数据的实现与客户端分开，符合“Program to an interface, not an implementation”的设计原理，这样
1。客户端不依赖于DAL的具体实现的类
2。可以通过工厂类/配置设置改换具体实现的类(譬如从Oracle到SQLServer)

DAL:数据访问层,主要用来做数据逻辑处理,具体为业务逻辑层或表示层提供数据服务。

先来看下IDAL的设计:

ICustom.cs

public interface ICustom
   {
       /// <summary>
       /// 添加一条记录
       /// </summary>
       /// <param name="Custom"></param>
       /// <returns></returns>
       int Addcustom(custom Custom);
       /// <summary>
       /// 概据帐户名获取用户的信息
       /// </summary>
       /// <param name="nename"></param>
       /// <returns></returns>
       custom Getsinglecname(string nename);
       /// <summary>
       /// 更样用户的密码
       /// </summary>
       /// <param name="Custom"></param>
       void Updatepassword(custom Custom);
       /// <summary>
       /// 获取用户列表
       /// </summary>
       /// <returns></returns>
       List<custom> Getcustom();
       /// <summary>
       /// 根据ID删除用户记录
       /// </summary>
       /// <param name="nid"></param>
       void Deletecustom(int nid);
       /// <summary>
       /// 根据ID获取用户信息
       /// </summary>
       /// <param name="nid"></param>
       /// <returns></returns>
       custom Getcustomer(int nid);
       /// <summary>
       /// 更新用户信息
       /// </summary>
       /// <param name="Custom"></param>
       void updatecustom(custom Custom);
       /// <summary>
       /// 根据部门ID获取部门员工列表
       /// </summary>
       /// <param name="nid"></param>
       /// <returns></returns>
       List<custom> Getdepartcustom(int nid);    }

与之想对应的customSQL.cs设计:

public class customSQL:ICustom
   {
 
       public int Addcustom(custom Custom)
       {
           SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
           SqlParameter[] ParamList = {
              sqlHelper.CreateInParam("@cname",SqlDbType.NVarChar,50,Custom.cname),
              sqlHelper .CreateInParam("@departID",SqlDbType.Int ,4,Custom.departID),
              sqlHelper .CreateInParam("@age",SqlDbType.Int,4,Custom.age),
              sqlHelper.CreateInParam("@ename",SqlDbType.NVarChar,50,Custom.ename),
              sqlHelper.CreateInParam("@password",SqlDbType.NVarChar,50,Custom.password)
          };
           try
           {
               return (sqlHelper.RunProc("spInsertCustom", ParamList));
           }
           catch (Exception ex)
           {
               SystemError.CreateErrorLog(ex.Message);
               throw new Exception(ex.Message, ex);
           }
 
       }
       public custom Getsinglecname(string nename)
       {
           SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
           SqlParameter[] Paramlist = {
          sqlHelper.CreateInParam("ename",SqlDbType.NVarChar,50,nename)
           
          };
           SqlDataReader dr = null;
           try
           {
               sqlHelper.RunProc("spGetsingleename", Paramlist, out dr);
           }
           catch (Exception ex)
           {
               SystemError.CreateErrorLog(ex.Message);
               throw new Exception(ex.Message, ex);
           }
           custom Custom = new custom();
           while (dr.Read())
           {
               Custom.id = Int32.Parse(dr["id"].ToString());
               Custom.cname = dr["cname"].ToString();
               Custom.ename = dr["ename"].ToString();
               Custom.departID = int.Parse(dr["departID"].ToString());
               Custom.password = dr["password"].ToString();
               Custom.age = int.Parse(dr["age"].ToString());
           }
           dr.Dispose();
           return Custom;
 
       }
       public void Updatepassword(custom Custom)
       {
           SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
           SqlParameter[] ParamList = {
              sqlHelper.CreateInParam("@id",SqlDbType.Int,4,Custom.id),
              sqlHelper.CreateInParam("@cname",SqlDbType.NVarChar,50,Custom.cname),
              sqlHelper .CreateInParam("@departID",SqlDbType.Int ,4,Custom.departID),
              sqlHelper .CreateInParam("@age",SqlDbType.Int,4,Custom.age),
              sqlHelper.CreateInParam("@ename",SqlDbType.NVarChar,50,Custom.ename),
              sqlHelper.CreateInParam("@password",SqlDbType.NVarChar,50,Custom.password)
          };
           try
           {
               sqlHelper.RunProc("spUpdatepassword", ParamList);
           }
           catch (Exception ex)
           {
               SystemError.CreateErrorLog(ex.Message);
               throw new Exception(ex.Message, ex);
           }
 
       }
 
       public List<custom> Getcustom()
       {
           SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
           SqlDataReader dr = null;
           try
           {
               sqlHelper.RunProc("spGetcustom", out dr);
           }
           catch (Exception ex)
           {
               SystemError.CreateErrorLog(ex.Message);
               throw new Exception(ex.Message, ex);
           }
           return ConvertDrToCustomList(dr);
       }
       public void Deletecustom(int nid)
       {
           SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
           SqlParameter[] Paramlist = {
             sqlHelper.CreateInParam("id",SqlDbType.Int,4,nid)
             };
           try
           {
               sqlHelper.RunProc("spDeletecustom", Paramlist);
           }
           catch (Exception ex)
           {
               SystemError.CreateErrorLog(ex.Message);
               throw new Exception(ex.Message, ex);
           }
 
       }
       public custom Getcustomer(int nid)
       {
           SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
           SqlParameter[] Paramlist = {
          sqlHelper.CreateInParam("id",SqlDbType.Int,4,nid)
           
          };
           SqlDataReader dr = null;
           try
           {
               sqlHelper.RunProc("spGetcustomer", Paramlist, out dr);
           }
           catch (Exception ex)
           {
               SystemError.CreateErrorLog(ex.Message);
               throw new Exception(ex.Message, ex);
           }
           dr.Dispose();
           return ConvertDrToCustom(dr);
       }
       public void updatecustom(custom Custom)
       {
 
           SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
           SqlParameter[] ParamList = {
              sqlHelper.CreateInParam("@id",SqlDbType.Int,4,Custom.id),
              sqlHelper.CreateInParam("@cname",SqlDbType.NVarChar,50,Custom.cname),
              sqlHelper .CreateInParam("@departID",SqlDbType.Int ,4,Custom.departID),
              sqlHelper .CreateInParam("@age",SqlDbType.Int,4,Custom.age),
              sqlHelper.CreateInParam("@ename",SqlDbType.NVarChar,50,Custom.ename)
          };
           try
           {
               sqlHelper.RunProc("spupdatecustom", ParamList);
           }
           catch (Exception ex)
           {
               SystemError.CreateErrorLog(ex.Message);
               throw new Exception(ex.Message, ex);
           }
       }
       public List<custom> Getdepartcustom(int nid)
       {
           SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
           SqlDataReader dr = null;
           SqlParameter[] Paramlist = {
          sqlHelper.CreateInParam("departID",SqlDbType.Int,4,nid)
           
          };
           try
           {
               sqlHelper.RunProc("spGetdepartcustom", Paramlist, out dr);
           }
           catch (Exception ex)
           {
               SystemError.CreateErrorLog(ex.Message);
               throw new Exception(ex.Message, ex);
           }
           return ConvertDrToCustomList(dr);
 
       }
       private custom ConvertDrToCustom(SqlDataReader dr)
       {
           custom Custom = new custom();
           while (dr.Read())
           {
               departmentSQL DepartmentSQL = new departmentSQL();
               department Department = new department();
               Department = DepartmentSQL.Getsingledepartment(Int32.Parse(dr["departID"].ToString()));
               Custom.id = Int32.Parse(dr["id"].ToString());
               Custom.ename = dr["ename"].ToString();
               Custom.cname = dr["cname"].ToString();
               Custom.age = Int32.Parse(dr["age"].ToString());
               Custom.departID = Int32.Parse(dr["departID"].ToString());
               Custom.departname = Department.departname;
               Custom.password = dr["password"].ToString();
    
           }
           dr.Dispose();
           return Custom;
       }
       private List<custom> ConvertDrToCustomList(SqlDataReader dr)
       {
           List<custom> Customlist = new List<custom>();
           while (dr.Read())
           {
               departmentSQL DepartmentSQL = new departmentSQL();
               department Department = new department();
               Department = DepartmentSQL.Getsingledepartment(Int32.Parse(dr["departID"].ToString()));
               custom Custom = new custom();
               Custom.id = Int32.Parse(dr["id"].ToString());
               Custom.ename = dr["ename"].ToString();
               Custom.cname = dr["cname"].ToString();
               Custom.age = Int32.Parse(dr["age"].ToString());
               Custom.departID = Int32.Parse(dr["departID"].ToString());
               Custom.departname = Department.departname;
               Custom.password = dr["password"].ToString();
               Customlist.Add(Custom);
               Custom = null;
           }
           dr.Dispose();
           return Customlist;
 
       }
 
   }

接下来再看IDepartment.cs的设计:

public interface IDepartment
{
    /// <summary>
    /// 增加一条部门数据
    /// </summary>
    /// <param name="Department"></param>
    /// <returns></returns>
    int Adddepartment(department Department);
    /// <summary>
    /// 获取部门列表
    /// </summary>
    /// <returns></returns>
    List<department> Getdepartment();
    /// <summary>
    /// 根据部门ID获取部门信息
    /// </summary>
    /// <param name="nid"></param>
    /// <returns></returns>
    department Getsingledepartment(int nid);
    /// <summary>
    /// 根据部门名称获取部门信息
    /// </summary>
    /// <param name="ndepartname"></param>
    /// <returns></returns>
    department Getdepartmenter(string ndepartname);
    /// <summary>
    /// 更新部门信息
    /// </summary>
    /// <param name="Department"></param>
    void Updatepartment(department Department);
    /// <summary>
    /// 根据ID删除部门信息
    /// </summary>
    /// <param name="nid"></param>
    void Deletedepart(int nid);    }

与之相对应的departmentSQL.cs的设计:

public class departmentSQL:IDepartment
 {
    public int Adddepartment(department Department)
    {
        SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
        SqlParameter[] ParamList = {
        sqlHelper.CreateInParam("@departname",SqlDbType.NVarChar,50,Department.departname),
        sqlHelper .CreateInParam("@description",SqlDbType.NVarChar,50,Department.description)
        };
        try
        {
            return (sqlHelper.RunProc("spInsertDepartment", ParamList));
        }
        catch (Exception ex)
        {
            SystemError.CreateErrorLog(ex.Message);
            throw new Exception(ex.Message, ex);
        }
 
    }
 
    public List<department> Getdepartment()
    {
        SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
        SqlDataReader dr = null;
        try
        { sqlHelper.RunProc("spGetAlldepartment", out dr); }
        catch (Exception ex)
        {
            SystemError.CreateErrorLog(ex.Message);
            throw new Exception(ex.Message, ex);
        }
 
        return ConvertDrToListDepartment(dr);
    }
    private List<department> ConvertDrToListDepartment(SqlDataReader dr)
    {
        List<department> Departmentlist = new List<department>();
        while (dr.Read())
        {
            department Department = new department();
            Department.id = Int32.Parse(dr["id"].ToString());
            Department.departname = dr["departname"].ToString();
            Department.description = dr["description"].ToString();
            Departmentlist.Add(Department);
            Department = null;
        }
        dr.Dispose();
        return Departmentlist;
    }
    public department Getsingledepartment(int nid)
    {
        SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
        SqlParameter[] ParamList = {
        sqlHelper.CreateInParam("@id",SqlDbType.Int,4,nid)
        };
        SqlDataReader dr = null;
        try
        { sqlHelper.RunProc("spGetdepartment",ParamList,out dr); }
        catch (Exception ex)
        {
            SystemError.CreateErrorLog(ex.Message);
            throw new Exception(ex.Message, ex);
        }
        return ConvertDrToDepartment(dr);
     
    }
    public department Getdepartmenter(string ndepartname)
    {
        SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
        SqlParameter[] ParamList = {
        sqlHelper.CreateInParam("@departname",SqlDbType.NVarChar,50,ndepartname)
        };
        SqlDataReader dr = null;
        try
        { sqlHelper.RunProc("spGetdepartmenter", ParamList, out dr); }
        catch (Exception ex)
        {
            SystemError.CreateErrorLog(ex.Message);
            throw new Exception(ex.Message, ex);
        }
        return ConvertDrToDepartment(dr);
    }
    public void Updatepartment(department Department)
    {
        SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
        SqlParameter[] ParamList = {
        sqlHelper.CreateInParam("@id",SqlDbType.Int,4,Department.id),
        sqlHelper.CreateInParam("@departname",SqlDbType.NVarChar,50,Department.departname),
        sqlHelper .CreateInParam("@description",SqlDbType.NVarChar,50,Department.description)
        };
        try
        {
            sqlHelper.RunProc("spupdatedepart", ParamList);
        }
        catch (Exception ex)
        {
            SystemError.CreateErrorLog(ex.Message);
            throw new Exception(ex.Message, ex);
        }
    }
    public void Deletedepart(int nid)
    {
        SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
        SqlParameter[] ParamList = {
        sqlHelper.CreateInParam("@id",SqlDbType.Int,4,nid)
        };
    
        try
        { sqlHelper.RunProc("spdeletedepart", ParamList); }
        catch (Exception ex)
        {
            SystemError.CreateErrorLog(ex.Message);
            throw new Exception(ex.Message, ex);
        }
    }
    private department ConvertDrToDepartment(SqlDataReader dr)
    {
        department Department = new department();
        while (dr.Read()) 
        {
 
            Department.id = Int32.Parse(dr["id"].ToString());
            Department.departname = dr["departname"].ToString();
            Department.description = dr["description"].ToString();
        }
        dr.Dispose();
        return Department;
    }
    
 
 }