来源：https://blog.csdn.net/Tomato2313/article/details/78880969

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 = ,

                FK_ClassID =

            };

            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 = ,

                FK_ClassID =

            };

            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 = ; i < ; i++)

            {

                StudentInfo student = new StudentInfo

                {

                    Name = "强森" + i.ToString(),

                    Age = ,

                    FK_ClassID =

                };

                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 = ,

                FK_ClassID =

            };

            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 = ,

                Name = "老夫子",

                Age = ,

                FK_ClassID =

            };

            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 = ,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 = ,

                FK_ClassID =

            };

            StudentInfo student2 = new StudentInfo

            {

                Name = "恩格斯2",

                Age = ,

                FK_ClassID =

            };

            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

    }

}