C#三层架构第四课之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;
        }
    }
}