Dapper 增删改查

 

 string  conStr = ConfigurationManager.ConnectionStrings["SqlConnStr"].ConnectionString;
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns></returns>
        public  string GetStudentList()
        {
            string sql = @"SELECT * FROM student";
            using (IDbConnection conn = new SqlConnection(conStr))
            {
                var result = conn.Query<Models.student>(sql).ToList();
                string json = JsonConvert.SerializeObject(result);
                return json;
            }
        }
        /// <summary>
        /// 查询所有返回table
        /// </summary>
        /// <returns></returns>
        public DataTable GetStudentData()
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT * FROM student";
            using (IDbConnection conn = new SqlConnection(conStr))
            {
                var result = conn.ExecuteReader(sql);
                dt.Load(result);
                return dt;
            }
        }
        /// <summary>
        /// 通过条件查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetStudentInfo(string id)
        {
            string sql = @"SELECT * FROM student where s_Id=@id";
            using (IDbConnection conn = new SqlConnection(conStr))
            {
                var result = conn.Query<Models.student>(sql,new { id =id}).ToList();
                string json = JsonConvert.SerializeObject(result);
                return json;
            }
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="s_Name"></param>
        /// <param name="s_Age"></param>
        /// <param name="s_Mobile"></param>
        /// <param name="s_classId"></param>
        /// <returns></returns>
        public int  CreateStu(string s_Name,int s_Age,string s_Mobile,string s_classId)
        {
            string sql = @"insert student(s_Name, s_Age,s_Mobile,s_classId) values (@s_Name, @s_Age,@s_Mobile,@s_classId)";
            using (IDbConnection conn = new SqlConnection(conStr))
            {
                var result = conn.Execute(sql, new { s_Name = s_Name, s_Age = s_Age, s_Mobile = s_Mobile, s_classId = s_classId });
                return result;
            }
        }
        /// <summary>
        /// 批量添加
        /// </summary>
        /// <returns></returns>
        public int CreateListStu()
        {
            string json =GetStudentList();
            Models.student[] stul = JsonConvert.DeserializeObject<Models.student[]>(json);
            List<Models.student> updateList = new List<Models.student>(stul);
            string sql = @"insert student(s_Name, s_Age,s_Mobile,s_classId) values (@s_Name, @s_Age,@s_Mobile,@s_classId)";
            using (IDbConnection conn = new SqlConnection(conStr))
            {
                var result = conn.Execute(sql, updateList);
                return result;
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="s_Name"></param>
        /// <param name="s_Age"></param>
        /// <param name="s_Mobile"></param>
        /// <param name="s_classId"></param>
        /// <param name="s_Id"></param>
        /// <returns></returns>
        public int Update(string s_Name, int s_Age, string s_Mobile, string s_classId,string s_Id)
        {
            string sql = @"update student set s_Name=@s_Name,s_Age=@s_Age,s_Mobile=@s_Mobile,s_classId=@s_classId where s_Id=@s_Id";
            using (IDbConnection conn = new SqlConnection(conStr))
            {
                var result = conn.Execute(sql, new { s_Name = s_Name, s_Age = s_Age, s_Mobile = s_Mobile, s_classId = s_classId , s_Id = s_Id });
                return result;
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="s_Id"></param>
        /// <returns></returns>
        public int Delete(string s_Id)
        {
            string sql = @"delete from student where s_Id=@s_Id";
            using (IDbConnection conn = new SqlConnection(conStr))
            {
                var result = conn.Execute(sql,new { s_Id= s_Id });
                return result;
            }
        }

如有错误或不恰当之处，望大佬们给予批评指正