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

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

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);

       /// <summary>

       /// 把SqlDataReader转化成Custom

       /// </summary>

       /// <param name="dr"></param>

       /// <returns></returns>

       custom ConvertDrToCustom(SqlDataReader dr);

       /// <summary>

       /// 把SqlDataReader转化成List Custom

       /// </summary>

       /// <param name="dr"></param>

       /// <returns></returns>

       List<custom> ConvertDrToCustomList(SqlDataReader dr);

   }

与之想对应的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);

       }

       public  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;

       }

       public  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>

    /// 把SqlDataReader转化为List Department

    /// </summary>

    /// <param name="dr"></param>

    /// <returns></returns>

    List<department> ConvertDrToListDepartment(SqlDataReader dr);

    /// <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);

    /// <summary>

    /// 把SqlDataReader转化为department

    /// </summary>

    /// <param name="dr"></param>

    /// <returns></returns>

    department ConvertDrToDepartment(SqlDataReader dr);

}

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

view sourceprint?

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);

    }

    public 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);

        }

    }

    public 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;

    }

 }

DAL层我们就设计完了,接下来我们就开始设计BLL层了,欢迎拍砖.

你的持续关注,就是我不断前进的最好动力.