步步为营 .NET三层架构解析 五、DAL与IDAL的设计
IDAL:数据访问层接口,接口是一种系列‘功能’的声明或名单，接口没有实现细节.
IDAL的作用是把访问数据的实现与客户端分开，符合“Program to an interface, not an implementation”的设计原理，这样
1。客户端不依赖于DAL的具体实现的类
2。可以通过工厂类/配置设置改换具体实现的类(譬如从Oracle到SQLServer)
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层了,欢迎拍砖.
你的持续关注,就是我不断前进的最好动力.
