C#/.NET/.NET Core Dapper 批量删除、新增、修改说明
Dapper是什么？Dapper是一款.Net平台简单（Simple）的对象映射库，并且Dapper拥有着“微型ORM之王”的称号。
就速度而言与手写ADO.NET SqlDateReader相同。
ORM是Object Relational Mapper的缩写，代表着数据库与编程语言之间的映射。
简单来说就是使用Entity Framework、NHibernate 来处理大数据访问及关系映射，未免有点杀鸡用牛刀，那么Dapper会是不错的选择。
1、Dapper基本用法
通过NuGet安装：Dapper包，如果要进行mysql连接还需要安装Mysql.Data包
2、数据库连接配置
<connectionStrings>
<add name="MysqlServer" connectionString="Database=用数据库名称;Data Source=IP;Port=端口; User Id=用户名;Password=密码;Charset=utf8mb4;TreatTinyAsBoolean=false;" /> //Mysql
<add name="LinqConnection" connectionString="Data Source=IP;Initial Catalog=数据库名称; User ID=用户名;Password=密码" providerName="System.Data.SqlClient"/> //sql
</connectionStrings>
3、dapper 数据库连接方法
Mysql连接方法：
public class DapperService
{
public static MySqlConnection MySqlConnection()
{
string mysqlConnectionStr = ConfigurationManager.ConnectionStrings["MysqlServer"].ToString();
var connection = new MySqlConnection(mysqlConnectionStr); connection.Open();
return connection;
}
}
mssql连接方法：
public class DapperService {
public static SqlConnection MySqlConnection()
{
string mysqlConnectionStr =ConfigurationManager.ConnectionStrings["LinqConnection"].ToString();
var connection = new SqlConnection(mysqlConnectionStr);
connection.Open();
return connection;
}
}
4、新增方法（单体、批量）
单体：
public ActionResult GetDapper(CarModel carModel)
{
try
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
int result = conn.Execute("insert into UserInfo(UserTel,UserName,UserPwd) values (@UserTel,@UserName,@UserPwd)", carModel);
}
return Json("success");
}
catch(Exception e)
{
return Json("failed");
}
}
批量：
public ActionResult GetDapper(List<CarModel> carModel)
{
try
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
int result = conn.Execute("insert into UserInfo(UserTel,UserName,UserPwd) values(@UserTel,@UserName,@UserPwd)", carModel);
}
return Json("success");
}
catch(Exception e)
{
return Json("failed");
}
}
5、删除方法（单体、批量）
单体：
public static int Delete(CarModel carModel)
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
return conn.Execute("delete from UserInfo where id=@ID", carModel);
}
}
批量：
public static int Delete(List<CarModel> carModel)
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
return conn.Execute("delete from UserInfo where id=@ID", carModel);
}
}
6、更新方法（单体、批量）
单体：
public static int Update(CarModel carModel)
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
return conn.Execute("update UserInfo set name=@name where id=@ID", carModel);
}
}
批量：
public static int Update(List<CarModel> carModel)
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
return conn.Execute("update UserInfo set name=@name where id=@ID", carModel);
}
}
7、查询
无参查询
public static List<CarModel> Query()
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
return conn.Query<CarModel>("select * from UserInfo ").ToList();
}
}
有参查询
public static Person Query(CarModel carModel)
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
return conn.Query<CarModel>("select * from UserInfo where
id=@ID",carModel).SingleOrDefault();
}
}
8、Dapper的复杂操作
In操作
public static List<CarModel> QueryIn()
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
var sql = "select * from UserInfo where id in @ids";
return conn.Query<CarModel>(sql, new { ids = new int { 1, 2 }, }).ToList();
}
}
public static List<CarModel> QueryIn(int[] ids)
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
var sql = "select * from UserInfo where id in @ids";
return conn.Query<CarModel>(sql, new { ids }).ToList();
}
}
9、多语句操作
public ActionResult QueryMultiple()
{
try
{
using (IDbConnection conn = DapperService.MySqlConnection())
{
var sql= "select * from Person; select * from UserInfo";
var multiReader = conn.QueryMultiple(sql);
var personList = multiReader.Read<Person>();
var bookList = multiReader.Read<CarModel>(); multiReader.Dispose();
}
return Json("success");
}
catch(Exception e)
{
return Json("failed");
}
}
Dapper是什么？
Dapper是一款.Net平台简单（Simple）的对象映射库，并且Dapper拥有着“微型ORM之王”的称号。
就速度而言与手写ADO.NET SqlDateReader相同。
ORM是Object Relational Mapper的缩写，代表着数据库与编程语言之间的映射。
简单来说就是使用Entity Framework、NHibernate 来处理大数据访问及关系映射，未免有点杀鸡用牛刀，那么Dapper会是不错的选择。
1、Dapper基本用法
通过NuGet安装：Dapper包，如果要进行mysql连接还需要安装Mysql.Data包
2、数据库连接配置
[*]
[*] //Mysql
[*]
[*] //sql
[*]
3、dapper 数据库连接方法
Mysql连接方法：
[*] public class DapperService
[*] {
[*]
[*] public static MySqlConnection MySqlConnection()
[*] {
[*] string mysqlConnectionStr = ConfigurationManager.ConnectionStrings["MysqlServer"].ToString();
[*] var connection = new MySqlConnection(mysqlConnectionStr); connection.Open();
[*] return connection;
[*] }
[*] }
mssql连接方法：
[*] public class DapperService {
[*]
[*] public static SqlConnection MySqlConnection()
[*] {
[*]
[*] string mysqlConnectionStr =ConfigurationManager.ConnectionStrings["LinqConnection"].ToString();
[*] var connection = new SqlConnection(mysqlConnectionStr);
[*] connection.Open();
[*] return connection;
[*] }
[*] }
4、新增方法（单体、批量）
单体：
[*] public ActionResult GetDapper(CarModel carModel)
[*] {
[*] try
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] int result = conn.Execute("insert into UserInfo(UserTel,UserName,UserPwd) values (@UserTel,@UserName,@UserPwd)", carModel);
[*] }
[*] return Json("success");
[*] }
[*] catch(Exception e)
[*] {
[*] return Json("failed");
[*] }
[*] }
批量：
[*] public ActionResult GetDapper(List carModel)
[*] {
[*] try
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] int result = conn.Execute("insert into UserInfo(UserTel,UserName,UserPwd) values(@UserTel,@UserName,@UserPwd)", carModel);
[*] }
[*] return Json("success");
[*] }
[*] catch(Exception e)
[*] {
[*] return Json("failed");
[*] }
[*] }
5、删除方法（单体、批量）
单体：
[*] public static int Delete(CarModel carModel)
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] return conn.Execute("delete from UserInfo where id=@ID", carModel);
[*] }
[*] }
批量：
[*] public static int Delete(List carModel)
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] return conn.Execute("delete from UserInfo where id=@ID", carModel);
[*] }
[*] }
6、更新方法（单体、批量）
单体：
[*] public static int Update(CarModel carModel)
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] return conn.Execute("update UserInfo set name=@name where id=@ID", carModel);
[*] }
[*] }
批量：
[*] public static int Update(List carModel)
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] return conn.Execute("update UserInfo set name=@name where id=@ID", carModel);
[*] }
[*] }
7、查询
无参查询
[*] public static List Query()
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] return conn.Query("select * from UserInfo ").ToList();
[*] }
[*] }
有参查询
[*] public static Person Query(CarModel carModel)
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] return conn.Query("select * from UserInfo where
[*] id=@ID",carModel).SingleOrDefault();
[*] }
[*] }
8、Dapper的复杂操作
In操作
[*] public static List QueryIn()
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] var sql = "select * from UserInfo where id in @ids";
[*] return conn.Query(sql, new { ids = new int { 1, 2 }, }).ToList();
[*] }
[*] }
[*]
[*] public static List QueryIn(int[] ids)
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] var sql = "select * from UserInfo where id in @ids";
[*] return conn.Query(sql, new { ids }).ToList();
[*] }
[*] }
9、多语句操作
[*] public ActionResult QueryMultiple()
[*] {
[*] try
[*] {
[*] using (IDbConnection conn = DapperService.MySqlConnection())
[*] {
[*] var sql= "select * from Person; select * from UserInfo";
[*] var multiReader = conn.QueryMultiple(sql);
[*] var personList = multiReader.Read();
[*] var bookList = multiReader.Read(); multiReader.Dispose();
[*] }
[*] return Json("success");
[*] }
[*] catch(Exception e)
[*] {
[*] return Json("failed");
[*] }
[*] }
来源:https://www.cnblogs.com/51net/archive/2023/02/26/17157012.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]