• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

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

原作者: [db:作者] 来自: [db:来源] 收藏 邀请
来源: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 = 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 } }

 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
MixingunmanagedC++andCSharp发布时间:2022-07-13
下一篇:
C#数组发布时间:2022-07-13
热门推荐
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap