当前位置:网站首页>C dapper basically uses addition, deletion, modification and query transactions, etc
C dapper basically uses addition, deletion, modification and query transactions, etc
2022-04-23 17:10:00 【Tomato Warrior】
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 Inquire about
/// <summary>
/// Query all the data
/// </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. The request is successful , result));
}
}
/// <summary>
/// The query ID A single data ( With parameters )
/// </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. The request is successful , result));
}
}
/// <summary>
/// IN Inquire about
/// </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. The request is successful , result));
}
}
/// <summary>
/// Joint query of two tables
/// </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. The request is successful , result));
}
}
#endregion
#region newly added
/// <summary>
/// Insert a single piece of data ( With parameters )
/// </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 = " Engels ",
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. The request is successful , result));
}
}
/// <summary>
/// Insert a single piece of data ( Insert the whole entity directly )
/// </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 = " Marx ",
Age = 55,
FK_ClassID = 1
};
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Execute(sql, student);
return Ok(ReturnJsonResult.GetJsonResult(RequestResult. The request is successful , result));
}
}
/// <summary>
/// Insert multiple data ( Entity )
/// </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 = " Johnson " + 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. The request is successful , result));
}
}
/// <summary>
/// Insert data and return auto increment primary key
/// </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 = " Engels ",
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. The request is successful , id));
}
}
#endregion
#region to update
/// <summary>
/// Use entity update
/// </summary>
/// <returns></returns>
[HttpPost]
public IHttpActionResult UpdateStudetInfo()
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"UPDATE STUDENT SET [email protected],[email protected],[email protected]_CLASSID WHERE STUID = @StuID";
StudentInfo student = new StudentInfo
{
StuID = 1,
Name = " The professor ",
Age = 59,
FK_ClassID = 2
};
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Execute(sql, student);
return Ok(ReturnJsonResult.GetJsonResult(RequestResult. The request is successful , result));
}
}
/// <summary>
/// Parameters are updated
/// </summary>
/// <returns></returns>
[HttpPost]
public IHttpActionResult UpdateStudet(int ID)
{
string conStr = ConfigurationManager.AppSettings["SqlConnStr"];
string sql = @"UPDATE STUDENT SET [email protected],[email protected],[email protected]_CLASSID WHERE STUID = @StuID";
using (IDbConnection conn = new SqlConnection(conStr))
{
var result = conn.Execute(sql, new {NAME = " Nicholas Zhao si ",AGE = 1,StuID = ID});
return Ok(ReturnJsonResult.GetJsonResult(RequestResult. The request is successful , result));
}
}
#endregion
#region Delete
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. The request is successful , result));
}
}
#endregion
#region Business
[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 = " Engels ",
Age = 55,
FK_ClassID = 1
};
StudentInfo student2 = new StudentInfo
{
Name = " Engels 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. The request is successful , result));
}
}
catch (System.Exception)
{
throw;
}
}
#endregion
}
}
版权声明
本文为[Tomato Warrior]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204230553458123.html
边栏推荐
- ClickHouse-数据类型
- PHP efficiently reads large files and processes data
- ACL 2022 | dialogved: a pre trained implicit variable encoding decoding model for dialogue reply generation
- _ Mold_ Board_
- PostgreSQL column storage and row storage
- Change the password after installing MySQL in Linux
- [PROJECT] small hat takeout (8)
- On lambda powertools typescript
- org. apache. parquet. schema. InvalidSchemaException: A group type can not be empty. Parquet does not su
- Idea of batch manufacturing test data, with source code
猜你喜欢
. net type transfer
[registration] tf54: engineer growth map and excellent R & D organization building
Node access to Alipay open platform sandbox to achieve payment function
Shell脚本——Shell编程规范及变量
Smart doc + Torna generate interface document
【生活中的逻辑谬误】稻草人谬误和无力反驳不算证明
TypeError: set_ figure_ params() got an unexpected keyword argument ‘figsize‘
1-1 NodeJS
CentOS MySQL multi instance deployment
Lock lock
随机推荐
Baidu Map Case - Zoom component, map scale component
SPC introduction
Error in v-on handler: "typeerror: cannot read property 'resetfields' of undefined"
Input file upload
org. apache. parquet. schema. InvalidSchemaException: A group type can not be empty. Parquet does not su
Detailed explanation of C webpai route
Shell-sort命令的使用
Milvus 2.0 détails du système d'assurance de la qualité
Wiper component encapsulation
Preliminary understanding of promse
【解决报错】Error in v-on handler: “TypeError: Cannot read property ‘resetFields’ of undefined”
El cascade and El select click elsewhere to make the drop-down box disappear
[C] thoroughly understand the deep copy
Milvus 2.0 質量保障系統詳解
Detailed explanation of Milvus 2.0 quality assurance system
New keyword learning and summary
1-1 NodeJS
On lambda powertools typescript
Freecodecamp ---- budget & category exercise
How to implement distributed locks with redis?