C# Dapper 的简单使用 学习笔记

===============查询===============

1 查询所有数据

2 查询指定ID单条数据(带参数)

3 IN查询

4 两表联合查询

===============新增===============

1 插入单条数据(带参数)

2 插入单条数据(直接插入整个实体)

3 插入多条数据(实体)

4 插入数据后返回自增主键

===============更新===============

1 使用实体更新

2 参数更新

===============删除===============

1 删除

===============事务===============

1 事务

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
    }
}

 

转载于:https://my.oschina.net/u/3641517/blog/1543430