--创建一个员工表create table Employee(    Em_Id int identity(1,1) primary key,    Em_Name varchar(50) not null,    Em_Age int default(18) not null)--部门表Create Table Department(    Depart_Id int identity(1,1) primary key,    Depart_Name varchar(20) not null,    )--员工所属部门关系表Create table EmployeePartment(    EP_Id int identity(1,1) primary key,    Em_Id int not null,    Depart_Id int not null)

/// <summary>    /// 生成Employee实体，注意类属性与表字段要一一对应    /// </summary>    public class Employee    {        public int Em_Id { get; set; }        public string Em_Name { get; set; }        public int Em_Age { get; set; }    }    /// <summary>    /// 生成部门Department实体    /// </summary>    public class Department    {        public int Depart_Id { get; set; }        public string Depart_Name { get; set; }    }    /// <summary>    /// 生成部门员工所属部门对应关系    /// </summary>    public class EmployeePartment    {        public int EP_Id { get; set; }        public int Em_Id { get; set; }        public int Depart_Id { get; set; }    }

/// <summary>        /// 声明object类型可用单个对象时插入单对象，也可批量BulkInsert插入(只要实现IEnumable接口)        /// </summary>        /// <param name="obj"></param>        private static void InsertEmployee(Object obj)        {            using (var conn = GetConnection())            {                string sql = "insert into employee values (@Em_Name,@Em_Age)";                int result = conn.Execute(sql, obj);            }        }        /// <summary>        /// 插入部门操作        /// </summary>        /// <param name="depart"></param>        private static void InsertDepartment(object depart)        {            CommandDefinition command = new CommandDefinition("insert into department values (@Depart_Name)", depart);            using (var conn=GetConnection())            {                conn.Execute(command);            }        }        /// <summary>        /// 生成sqlConnection对象。返回IDbConnection.        /// </summary>        /// <returns></returns>        private static IDbConnection GetConnection()        {            var conn = new SqlConnection(connstr);            return conn;        }

//此处为方法调用 //Ado.net方法删除 DeleteEmployeeById(1);UseCommandDeleteEmployeeUseById(2);//存储过程删除ProcDeleteEmployeeById(3);//此处为方法实现 /// <summary>        /// 根据ID删除对应Employee        /// </summary>        /// <param name="id">待删除的EmployeeId</param>        private static void DeleteEmployeeById(int id)        {            using (var conn=GetConnection())            {               int result= conn.Execute("delete from Employee where EM_Id=@id", new { @id = id });            }        }        /// <summary>        /// 使用Command形式删除操作        /// </summary>        /// <param name="id">待删除的Employee的ID</param>        private static void UseCommandDeleteEmployeeUseById(int id)        {            var command = new CommandDefinition("delete from Employee where Em_Id=@Eid", new { @Eid = id }, null, null, CommandType.Text,CommandFlags.None);            using (var conn=GetConnection())            {                int result = conn.Execute(command);            }        }        /// <summary>        /// 使用存储过程形式删除Employee        /// </summary>        /// <param name="id">待删除的Employee</param>        private static void ProcDeleteEmployeeById(int id)        {            using (var conn = GetConnection())            {               int result= conn.Execute("pr_delete_employee", new { @id = id },null,null,CommandType.StoredProcedure);            }        }

//方法调用  UpdateEmployeeName(4, "新名称");  UseCommandUpdateEmployee(4, 18);  ProcUpdateEmployeeName(5, "旧名称");//方法实现            /// <summary>        /// 更新指定ID名称为新值        /// </summary>        /// <param name="eid">Employee的Id</param>        /// <param name="name">新的employee名称</param>        private static void UpdateEmployeeName(int eid, string name)        {            using (var conn=GetConnection())            {               int result= conn.Execute("update Employee set Em_Name=@name where Em_Id=@id", new { @name = name, @id = eid });            }        }        /// <summary>        /// 使用Command形式更新Employee信息        /// </summary>        /// <param name="eid">待更新的EmployeeId</param>        /// <param name="Age">Age新值</param>        private static void UseCommandUpdateEmployee(int eid, int Age)        {            var command=new CommandDefinition("update Employee set Em_Age=@age where em_Id=@eid",new{@age=Age,@eid=eid},null,null,CommandType.Text,CommandFlags.None);            using (var conn=GetConnection())            {                int result = conn.Execute(command);            }        }        /// <summary>        /// 更新指定ID名称为新值        /// </summary>        /// <param name="eid">Employee的Id</param>        /// <param name="name">新的employee名称</param>        private static void ProcUpdateEmployeeName(int eid, string name)        {            using (var conn = GetConnection())            {                var p = new DynamicParameters();                p.Add("@id", eid);                p.Add("@name", name);                int result = conn.Execute("pr_update_employee", new {  id = eid,name = name },null,null,CommandType.StoredProcedure));            }        }

 //简单查询            Employee employee = GetEmployeeById(5);            if (employee != null)            {                Console.WriteLine("ID为5的员工已找到名称:" + employee.Em_Name);            }  //子查询            List<Employee> list_employees = GetEmployeesByPartId(1);            Console.WriteLine("共找到{0}记录:", list_employees.Count);            for (int i = 0; i < list_employees.Count; i++)            {                Console.Write(list_employees[i].Em_Name + ",");            }/// <summary>        /// 根据ID查找EmpolyeeID 支持ADO语句和存储过程        /// </summary>        /// <param name="id"></param>        /// <returns></returns>        private static Employee GetEmployeeById(int id)        {            Employee employee = new Employee();            using (var conn = GetConnection())            {                employee = conn.Query<Employee>("select * from employee where Em_Id=@id", new { id = id }).FirstOrDefault();            }            //CommandDefinition command = new CommandDefinition("select * from employee wehre em_id=@id");            //using (var conn = GetConnection())            //{            //    employee = conn.Query<Employee>(command).FirstOrDefault();            //}            return employee;        }        /// <summary>        /// 子查询        /// </summary>        /// <param name="partid"></param>        /// <returns></returns>        private static List<Employee> GetEmployeesByPartId(int partid)        {            List<Employee> employees = new List<Employee>();            CommandDefinition command = new CommandDefinition("select * from employee where em_id in (select em_id from  EmployeePARTMENT where Depart_Id=@id) ", new { id = partid });            using (var conn = GetConnection())            {                employees = conn.Query<Employee>(command).ToList();            }            return employees;        }