employeedal.cs

c#三层架构项目开发的全过程

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Model;

namespace DAL.Employee
{
    public class EmployeeDal
    {
        #region 获取职位名称
        /// <summary>
        /// 获取职位名称
        /// </summary>
        /// <returns>返回职位名称数据集</returns>
        public List<Model.Employee.EmployeeModel> SelectRoleName()
        {
            string commandtext = "SelectRole_Name";
            DataSet ds = new DataSet();
            SqlHelper.FillDataSet(SqlHelper.conStr, CommandType.StoredProcedure, commandtext, ds, new string[] { }, null);
            List<Model.Employee.EmployeeModel> Srn = new List<Model.Employee.EmployeeModel>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Model.Employee.EmployeeModel sn = new Model.Employee.EmployeeModel();
                sn.role_Name = item["role_Name"].ToString();
                Srn.Add(sn);
            }
            return Srn;
        }
        #endregion

        #region 获取分店名称
        /// <summary>
        /// 获取分店名称
        /// </summary>
        /// <returns>返回职位分店数据集</returns>
        public List<Model.Employee.EmployeeModel> SelectShopName()
        {
            string commandtext = "SelectShop_Name";
            DataSet ds = new DataSet();
            SqlHelper.FillDataSet(SqlHelper.conStr, CommandType.StoredProcedure, commandtext, ds, new string[] { }, null);
            List<Model.Employee.EmployeeModel> Ssn = new List<Model.Employee.EmployeeModel>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Model.Employee.EmployeeModel esn = new Model.Employee.EmployeeModel();
                esn.Shop_name = item["Shop_name"].ToString();
                Ssn.Add(esn);
            }
            return Ssn;
        }
        #endregion;

        #region 获取职位ID
        /// <summary>
        /// 用职位名称找职位ID
        /// </summary>
        /// <returns></returns>
        public List<Model.Employee.EmployeeModel> SelectRoleID(string role_name)
        {
            string commandtext = "EmployeeSelect_Role_Id";
            SqlParameter rolename = new SqlParameter("@role_name", SqlDbType.VarChar, 20);
            rolename.Value = role_name;
            DataSet ds = new DataSet();
            SqlHelper.FillDataSet(SqlHelper.conStr, CommandType.StoredProcedure, commandtext, ds, new string[] { }, new SqlParameter[] { rolename });
            List<Model.Employee.EmployeeModel> sri = new List<Model.Employee.EmployeeModel>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Model.Employee.EmployeeModel er = new Model.Employee.EmployeeModel();
                er.role_ID = (int)item["role_ID"];
                sri.Add(er);
            }
            return sri;
        }
        #endregion

        #region 获取分店ID
        /// <summary>
        /// 用分店名称找分店ID
        /// </summary>
        /// <returns></returns>
        public List<Model.Employee.EmployeeModel> SelectShopID(string shop_name)
        {
            string commandtext = "EmployeeSelect_Shop_Id";
            SqlParameter shopname = new SqlParameter("@shop_name", SqlDbType.VarChar, 20);
            shopname.Value = shop_name;
            DataSet ds = new DataSet();
            SqlHelper.FillDataSet(SqlHelper.conStr, CommandType.StoredProcedure, commandtext, ds, new string[] { }, new SqlParameter[] { shopname });
            List<Model.Employee.EmployeeModel> ssi = new List<Model.Employee.EmployeeModel>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Model.Employee.EmployeeModel ss = new Model.Employee.EmployeeModel();
                ss.Shop_id = (int)item["Shop_id"];
                ssi.Add(ss);
            }
            return ssi;
        }
        #endregion

        #region 员工添加
        /// <summary>
        /// 员工添加
        /// </summary>
        /// <param name="role_id">职位id</param>
        /// <param name="shop_id">分店id</param>
        /// <param name="employee_name">员工姓名</param>
        /// <param name="employee_in_time">入职时间</param>
        /// <param name="employee_tel">联系电话</param>
        /// <returns></returns>
        public int EmployeeAdd(string empid, int role_id, int shop_id, string employee_name, DateTime employee_in_time, string employee_tel, string employee_state)
        {
            string commandtext = "Employee_Add";
            SqlParameter id = new SqlParameter("@Employee_ID",SqlDbType.VarChar,50);
            SqlParameter roleid = new SqlParameter("@role_id", SqlDbType.Int);
            SqlParameter shopid = new SqlParameter("@shop_id", SqlDbType.Int);
            SqlParameter name = new SqlParameter("@employee_name", SqlDbType.VarChar, 10);
            SqlParameter intime = new SqlParameter("@employee_in_time", SqlDbType.DateTime);
            SqlParameter tel = new SqlParameter("@employee_tel", SqlDbType.Char,20);
            SqlParameter state = new SqlParameter("@Employee_state", SqlDbType.VarChar, 10);

            id.Value = empid;
            roleid.Value = role_id;
            shopid.Value = shop_id;
            name.Value = employee_name;
            intime.Value = employee_in_time;
            tel.Value = employee_tel;
            state.Value = employee_state;
            return SqlHelper.ExecuteNonQuery(SqlHelper.conStr, CommandType.StoredProcedure, commandtext, new SqlParameter[] { id, roleid, shopid, name, intime, tel, state });
        }
        #endregion

        #region 员工信息显示
        /// <summary>
        /// 员工信息显示
        /// </summary>
        /// <returns></returns>
        public List<Model.Employee.EmployeeModel> EmployeeInfShow()
        {
            string commandtext = "Employee_Inf_Show";
            DataSet ds = new DataSet();
            SqlHelper.FillDataSet(SqlHelper.conStr, CommandType.StoredProcedure, commandtext, ds, new string[] { }, null);
            List<Model.Employee.EmployeeModel> eis = new List<Model.Employee.EmployeeModel>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Model.Employee.EmployeeModel ei = new Model.Employee.EmployeeModel();
                ei.Employee_ID = item["Employee_ID"].ToString();
                ei.Employee_Name = item["Employee_Name"].ToString();
                ei.Employee_In_time = (DateTime)item["Employee_In_time"];
                ei.Employee_state = item["Employee_state"].ToString();
                ei.Employee_Tel = item["Employee_Tel"].ToString();
                ei.Employee_pwd = item["Employee_pwd"].ToString();
                //ei.role_ID = (int)item["role_ID"];
                //ei.Shop_id = (int)item["Shop_id"];
                eis.Add(ei);
            }
            return eis;
        }
        #endregion

        #region 根据员工名称信息查询
        /// <summary>
        /// 根据员工名称信息查询
        /// </summary>
        /// <returns></returns>
        public List<Model.Employee.EmployeeModel> EmployeeNameSelect(string employee_name)
        {
            string commandtext = "Employee_Information_select";
            SqlParameter employeename = new SqlParameter("@Employee_name", SqlDbType.VarChar, 10);
            employeename.Value = employee_name;
            DataSet ds = new DataSet();
            SqlHelper.FillDataSet(SqlHelper.conStr, CommandType.StoredProcedure, commandtext, ds, new string[] { }, new SqlParameter[] { employeename });
            List<Model.Employee.EmployeeModel> eis = new List<Model.Employee.EmployeeModel>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Model.Employee.EmployeeModel esi = new Model.Employee.EmployeeModel();
                esi.Employee_ID = item["Employee_ID"].ToString().Trim();
                esi.Employee_Name = item["Employee_Name"].ToString();
                esi.Employee_In_time = (DateTime)item["Employee_In_time"];
                eis.Add(esi);
            }
            return eis;
        }
        #endregion

        #region 根据员工编号信息查询
        /// <summary>
        /// 根据员工编号信息查询
        /// </summary>
        /// <returns></returns>
        public List<Model.Employee.EmployeeModel> EmployeeIDSelects(string employee_id)
        {
            string commandtext = "Employee_ID_select";
            SqlParameter employeeid = new SqlParameter("@Employee_id", SqlDbType.Char, 20);
            employeeid.Value = employee_id;
            DataSet ds = new DataSet();
            SqlHelper.FillDataSet(SqlHelper.conStr, CommandType.StoredProcedure, commandtext, ds, new string[] { }, new SqlParameter[] { employeeid });
            List<Model.Employee.EmployeeModel> eis = new List<Model.Employee.EmployeeModel>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Model.Employee.EmployeeModel esi = new Model.Employee.EmployeeModel();
                esi.Employee_ID = item["Employee_ID"].ToString().Trim();
                esi.Employee_Name = item["Employee_Name"].ToString();
                esi.Employee_In_time = (DateTime)item["Employee_In_time"];
                eis.Add(esi);
            }
            return eis;
        }
        #endregion

        #region 员工信息在修改框显示
        /// <summary>
        /// 员工信息显示在修改页面的修改框里
        /// </summary>
        /// <param name="employee_id">员工编号</param>
        /// <returns></returns>
        public List<Model.Employee.EmployeeModel> EmployInfUpdateShow(string employee_id)
        {
            string commandtext = "Employee_InfUpdate_Show";
            SqlParameter employeeid = new SqlParameter("@employee_id", SqlDbType.Char,20);
            employeeid.Value = employee_id;
            DataSet ds = new DataSet();
            SqlHelper.FillDataSet(SqlHelper.conStr, CommandType.StoredProcedure, commandtext, ds, new string[] { "Users" }, new SqlParameter[] { employeeid });
            List<Model.Employee.EmployeeModel> eius = new List<Model.Employee.EmployeeModel>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Model.Employee.EmployeeModel eus = new Model.Employee.EmployeeModel();
                eus.Employee_ID = item["Employee_ID"].ToString();
                eus.Employee_Name = item["Employee_Name"].ToString();
                eus.Employee_Tel = item["Employee_Tel"].ToString();