C#三层架构第四课之DAL层

上山打老虎

DAL层的方法基本与BLL层一致，前期可以这么做，简单易于理解，后期BLL一个方法可能包含若干个DAL的方法。
参考代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data.SqlClient;
using System.Data;
//DAL层调用公共的类库
using DbUtil;
using IDAL;
namespace DAL
{
  public class DeptDao:IDept
  {
    //1.复制 service层的几个方法;
    //CRUD:增加  U：修改  D：删除；R:检索;  
    public bool addDept(Dept dept)
    {  
      String sql = "insert dept values(@deptId,@deptName)";
      SqlParameter(参数名,值),这里通过封装好的dept对象来取值
      //cmd.Parameters.Add(new SqlParameter("@deptId",dept.DeptId));
      //cmd.Parameters.Add(new SqlParameter("@deptName", dept.DeptName));
      List<SqlParameter> parameters = new List<SqlParameter>();
      //问题转移为如何向集合parameters,增加参数和值;
      parameters.Add(new SqlParameter("@deptId", dept.DeptId));
      parameters.Add(new SqlParameter("@deptName", dept.DeptName));
      return DBHelper.update(sql,parameters);
    }
    public bool updateDept(Dept dept)
    {
      String sql = "update dept set deptName=@deptName where deptId=@deptId";
      List<SqlParameter> parameters = new List<SqlParameter>();
      parameters.Add(new SqlParameter("@deptName",dept.DeptName));
      parameters.Add(new SqlParameter("@deptId",dept.DeptId));
      return DBHelper.update(sql,parameters);
    }
    public bool delDept(Dept dept)       
    {
      String sql = "delete from dept where deptid=@deptId";
      List<SqlParameter> parameters = new List<SqlParameter>();      
      parameters.Add(new SqlParameter("@deptId", dept.DeptId));
      return DBHelper.update(sql, parameters); ;
    }
    public DataTable refreshData() {
      String sql = "select deptId 部门编号,deptName 部门名称 from dept";
      return DBHelper.query(sql,null);
    }
    public DataTable findDeptByName(String deptName)
    {
      String url = "server=.;database=MyDb;uid=sa;pwd=123456";
      //1.链接对象;
      SqlConnection conn = new SqlConnection(url);
      conn.Open();
      //2.SqlCommand
      SqlCommand cmd = conn.CreateCommand();
      //String sql = "select * from dept where deptName like '{0}' ",deptName;
      String sql = "select deptId 部门编号,deptName 部门名称 from dept where deptName like @deptName";
      cmd.CommandText = sql;
      String str = "%" + deptName + "%";
      cmd.Parameters.Add(new SqlParameter("@deptName",str));
      //需要用到适配器对象;适配器没有这种参数方式
      SqlDataAdapter da = new SqlDataAdapter();
      da.SelectCommand = cmd;
      DataTable dt=new DataTable();
      da.Fill(dt);
      return dt;
    }
  }
}