【C# Dapper 基本使用 增删改查事务等】亦余心之所善兮，虽九死其犹未悔。这篇文章主要讲述C# Dapper 基本使用 增删改查事务等相关的知识，希望能为你提供帮助。
1 using DapperTest.Models; 2 using System.Collections.Generic; 3 using System.Web.Http; 4 using Dapper; 5 using System.Data; 6 using System.Data.SqlClient; 7 using System.Linq; 8 using System.Configuration; 9 10 namespace DapperTest.Controllers 11 { 12public class HomeController : ApiController 13{ 14#region 查询 15 16/// < summary> 17/// 查询所有数据 18/// < /summary> 19/// < returns> < /returns> 20[HttpGet] 21public IHttpActionResult GetStudentList() 22{ 23string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 24string sql = @"SELECT * FROM STUDENT"; 25using (IDbConnection conn = new SqlConnection(conStr)) 26{ 27var result = conn.Query< StudentInfo> (sql).ToList(); 28return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 29} 30} 31 32/// < summary> 33/// 查询指定ID单条数据(带参数) 34/// < /summary> 35/// < returns> < /returns> 36[HttpGet] 37public IHttpActionResult GetStudentInfo(string ID) 38{ 39string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 40string sql = @"SELECT * FROM STUDENT WHERE STUID in @STUID"; 41using (IDbConnection conn = new SqlConnection(conStr)) 42{ 43var result = conn.Query< StudentInfo> (sql, new { STUID = ID }); 44return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 45} 46} 47 48/// < summary> 49/// IN查询 50/// < /summary> 51/// < returns> < /returns> 52[HttpGet] 53public IHttpActionResult GetStudentInfos(string IDStr) 54{ 55string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 56string sql = @"SELECT * FROM STUDENT WHERE STUID in @STUIDStr"; 57var IDArr = IDStr.Split(‘,‘); 58using (IDbConnection conn = new SqlConnection(conStr)) 59{ 60var result = conn.Query< StudentInfo> (sql, new { STUIDStr = IDArr }); 61return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 62} 63} 64 65/// < summary> 66/// 两表联合查询 67/// < /summary> 68/// < returns> < /returns> 69[HttpGet] 70public IHttpActionResult GetStudentAndClass() 71{ 72string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 73string sql = @"SELECT * FROM STUDENT A JOIN CLASS B ON A.FK_CLASSID = B.ID"; 74using (IDbConnection conn = new SqlConnection(conStr)) 75{ 76var result = conn.Query(sql); 77return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 78} 79} 80 81#endregion 82 83#region 新增 84 85 86/// < summary> 87/// 插入单条数据(带参数) 88/// < /summary> 89/// < returns> < /returns> 90[HttpPost] 91public IHttpActionResult AddStudent() 92{ 93string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 94string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)"; 95 96StudentInfo student = new StudentInfo 97{ 98Name = "恩格斯", 99Age = 55, 100FK_ClassID = 1 101}; 102 103using (IDbConnection conn = new SqlConnection(conStr)) 104{ 105var result = conn.Execute(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID }); 106return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 107} 108} 109 110 111/// < summary> 112/// 插入单条数据(直接插入整个实体) 113/// < /summary> 114/// < returns> < /returns> 115[HttpPost] 116public IHttpActionResult AddStudentInfo() 117{ 118string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 119string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@FK_CLASSID)"; 120 121StudentInfo student = new StudentInfo 122{ 123Name = "马克思", 124Age = 55, 125FK_ClassID = 1 126}; 127 128using (IDbConnection conn = new SqlConnection(conStr)) 129{ 130var result = conn.Execute(sql, student); 131return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 132} 133} 134 135/// < summary> 136/// 插入多条数据(实体) 137/// < /summary> 138/// < returns> < /returns> 139[HttpPost] 140public IHttpActionResult AddStudentList() 141{ 142string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 143string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@FK_CLASSID)"; 144 145List< StudentInfo> list = new List< StudentInfo> (); 146for (int i = 0; i < 3; i++) 147{ 148StudentInfo student = new StudentInfo 149{ 150Name = "强森" + i.ToString(), 151Age = 55, 152FK_ClassID = 1 153}; 154list.Add(student); 155} 156 157using (IDbConnection conn = new SqlConnection(conStr)) 158{ 159var result = conn.Execute(sql, list); 160return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 161} 162} 163 164/// < summary> 165/// 插入数据后返回自增主键 166/// < /summary> 167/// < returns> < /returns> 168[HttpPost] 169public IHttpActionResult AddReturnID() 170{ 171string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 172string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)"; 173 174StudentInfo student = new StudentInfo 175{ 176Name = "恩格斯", 177Age = 55, 178FK_ClassID = 1 179}; 180 181using (IDbConnection conn = new SqlConnection(conStr)) 182{ 183sql += "SELECT SCOPE_IDENTITY()"; 184var result = conn.Execute(sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID }); 185var id = conn.QueryFirstOrDefault< int> (sql, new { NAME = student.Name, AGE = student.Age, CLASSID = student.FK_ClassID }); 186return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, id)); 187} 188} 189#endregion 190 191#region 更新 192/// < summary> 193/// 使用实体更新 194/// < /summary> 195/// < returns> < /returns> 196[HttpPost] 197public IHttpActionResult UpdateStudetInfo() 198{ 199string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 200string sql = @"UPDATE STUDENT SET [email protected],[email protected],[email protected]_CLASSID WHERE STUID = @StuID"; 201StudentInfo student = new StudentInfo 202{ 203StuID = 1, 204Name = "老夫子", 205Age = 59, 206FK_ClassID = 2 207}; 208using (IDbConnection conn = new SqlConnection(conStr)) 209{ 210var result = conn.Execute(sql, student); 211return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 212} 213} 214 215/// < summary> 216/// 参数更新 217/// < /summary> 218/// < returns> < /returns> 219[HttpPost] 220public IHttpActionResult UpdateStudet(int ID) 221{ 222string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 223string sql = @"UPDATE STUDENT SET [email protected],[email protected],[email protected]_CLASSID WHERE STUID = @StuID"; 224using (IDbConnection conn = new SqlConnection(conStr)) 225{ 226var result = conn.Execute(sql, new {NAME = "尼古拉斯赵四",AGE = 1,StuID = ID}); 227return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 228} 229} 230#endregion 231 232#region 删除 233public IHttpActionResult Delete(int ID) 234{ 235string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 236string sql = @"DELETE STUDENTWHERE STUID = @StuID"; 237using (IDbConnection conn = new SqlConnection(conStr)) 238{ 239var result = conn.Execute(sql, new { StuID = ID }); 240return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 241} 242} 243#endregion 244 245#region 事务 246[HttpPost] 247public IHttpActionResult AddStudentT() 248{ 249string conStr = ConfigurationManager.AppSettings["SqlConnStr"]; 250string sql = @"INSERT INTO STUDENT (NAME,AGE,FK_CLASSID) VALUES (@NAME,@AGE,@CLASSID)"; 251 252StudentInfo student = new StudentInfo 253{ 254Name = "恩格斯", 255Age = 55, 256FK_ClassID = 1 257}; 258 259StudentInfo student2 = new StudentInfo 260{ 261Name = "恩格斯2", 262Age = 55, 263FK_ClassID = 1 264}; 265 266try 267{ 268using (IDbConnection conn = new SqlConnection(conStr)) 269{ 270IDbTransaction transaction = conn.BeginTransaction(); 271var result = conn.Execute(sql, student); 272var result1 = conn.Execute(sql, student2); 273transaction.Commit(); 274return Ok(ReturnJsonResult.GetJsonResult(RequestResult.请求成功, result)); 275} 276} 277catch (System.Exception) 278{ 279throw; 280} 281 282} 283#endregion 284} 285 }
推荐阅读
- Android-系统绘图真相
- Vant Weapp的dialog组件在mpvue小程序中使用注意事项
- appium启动
- app点击底部菜单切换标题
- Android-图像原理/绘制原理
- Android-加载大图片
- 关于Mybatis中Mapper是使用XML还是注解的一些思考
- project online get approvals task data 获取审批待办任务接口
- flutter版本的玩Android客户端