步步为营 .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;
       }
      

    }

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

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