C# Dapper 基本使用 增删改查事务等

直接上代码，有空会上传Demo

using DapperTest.Models;using System.Collections.Generic;using System.Web.Http;using Dapper;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Configuration;namespace DapperTest.Controllers{    public class HomeController : ApiController    {        #region 查询        /// <summary>        /// 查询所有数据        /// </summary>        /// <returns></returns>        [HttpGet]        public IHttpActionResult GetStudentList()        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"SELECT * FROM STUDENT";            using (IDbConnection conn = new SqlConnection(conStr))            {                var result = conn.Query<StudentInfo>(sql).ToList();                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));            }        }        /// <summary>        /// 查询指定ID单条数据(带参数)        /// </summary>        /// <returns></returns>        [HttpGet]        public IHttpActionResult GetStudentInfo(string ID)        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"SELECT * FROM STUDENT WHERE STUID in @STUID";            using (IDbConnection conn = new SqlConnection(conStr))            {                var result = conn.Query<StudentInfo>(sql, new { STUID = ID });                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));            }        }        /// <summary>        /// IN查询        /// </summary>        /// <returns></returns>        [HttpGet]        public IHttpActionResult GetStudentInfos(string IDStr)        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"SELECT * FROM STUDENT WHERE STUID in @STUIDStr";            var IDArr = IDStr.Split(',');            using (IDbConnection conn = new SqlConnection(conStr))            {                var result = conn.Query<StudentInfo>(sql, new { STUIDStr = IDArr });                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));            }        }        /// <summary>        /// 两表联合查询        /// </summary>        /// <returns></returns>        [HttpGet]        public IHttpActionResult GetStudentAndClass()        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"SELECT * FROM STUDENT A JOIN CLASS B ON A.FK_CLASSID = B.ID";            using (IDbConnection conn = new SqlConnection(conStr))            {                var result = conn.Query(sql);                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));            }        }        #endregion        #region 新增        /// <summary>        /// 插入单条数据(带参数)        /// </summary>        /// <returns></returns>        [HttpPost]        public IHttpActionResult AddStudent()        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)";            StudentInfo student = new StudentInfo            {                Name = "恩格斯",                Age = 55,                FK_ClassID = 1            };            using (IDbConnection conn = new SqlConnection(conStr))            {                var result = conn.Execute(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID });                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));            }        }        /// <summary>        /// 插入单条数据(直接插入整个实体)        /// </summary>        /// <returns></returns>        [HttpPost]        public IHttpActionResult AddStudentInfo()        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@FK_CLASSID)";            StudentInfo student = new StudentInfo            {                Name = "马克思",                Age = 55,                FK_ClassID = 1            };            using (IDbConnection conn = new SqlConnection(conStr))            {                var result = conn.Execute(sql, student);                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));            }        }        /// <summary>        /// 插入多条数据(实体)        /// </summary>        /// <returns></returns>        [HttpPost]        public IHttpActionResult AddStudentList()        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@FK_CLASSID)";            List<StudentInfo> list = new List<StudentInfo>();            for (int i = 0; i < 3; i++)            {                StudentInfo student = new StudentInfo                {                    Name = "强森" + i.ToString(),                    Age = 55,                    FK_ClassID = 1                };                list.Add(student);            }            using (IDbConnection conn = new SqlConnection(conStr))            {                var result = conn.Execute(sql, list);                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));            }        }        /// <summary>        /// 插入数据后返回自增主键        /// </summary>        /// <returns></returns>        [HttpPost]        public IHttpActionResult AddReturnID()        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)";            StudentInfo student = new StudentInfo            {                Name = "恩格斯",                Age = 55,                FK_ClassID = 1            };            using (IDbConnection conn = new SqlConnection(conStr))            {                sql += "SELECT SCOPE_IDENTITY()";                var result = conn.Execute(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID });                var id = conn.QueryFirstOrDefault<int>(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID });                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, id));            }        }        #endregion        #region 更新        /// <summary>        /// 使用实体更新        /// </summary>        /// <returns></returns>        [HttpPost]        public IHttpActionResult UpdateStudetInfo()        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"UPDATE STUDENT SET NAME=@NAME,AGE=@AGE,FK_CLASSID=@FK_CLASSID WHERE STUID = @StuID";            StudentInfo student = new StudentInfo            {                StuID = 1,                Name = "老夫子",                Age = 59,                FK_ClassID = 2            };            using (IDbConnection conn = new SqlConnection(conStr))            {                var result = conn.Execute(sql, student);                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));            }        }        /// <summary>        /// 参数更新        /// </summary>        /// <returns></returns>        [HttpPost]        public IHttpActionResult UpdateStudet(int ID)        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"UPDATE STUDENT SET NAME=@NAME,AGE=@AGE,FK_CLASSID=@FK_CLASSID WHERE STUID = @StuID";            using (IDbConnection conn = new SqlConnection(conStr))            {                var result = conn.Execute(sql, new {NAME = "尼古拉斯赵四",AGE = 1,StuID = ID});                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));            }        }        #endregion        #region 删除        public IHttpActionResult Delete(int ID)        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"DELETE STUDENT  WHERE STUID = @StuID";            using (IDbConnection conn = new SqlConnection(conStr))            {                var result = conn.Execute(sql, new { StuID = ID });                return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));            }        }        #endregion        #region 事务        [HttpPost]        public IHttpActionResult AddStudentT()        {            string conStr = ConfigurationManager.AppSettings["SqlConnStr"];            string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)";            StudentInfo student = new StudentInfo            {                Name = "恩格斯",                Age = 55,                FK_ClassID = 1            };            StudentInfo student2 = new StudentInfo            {                Name = "恩格斯2",                Age = 55,                FK_ClassID = 1            };                       try            {                using (IDbConnection conn = new SqlConnection(conStr))                {                    IDbTransaction transaction = conn.BeginTransaction();                    var result = conn.Execute(sql, student);                    var result1 = conn.Execute(sql, student2);                    transaction.Commit();                    return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result));                }            }            catch (System.Exception)            {                throw;            }                   }        #endregion    }}