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的设计:
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层了,欢迎拍砖.
你的持续关注,就是我不断前进的最好动力.
