常用功能的简单封装
public class Dapper
{
public string ConnectionString { get; set; }
public Dapper()
{
var Configuration = new ConfigurationBuilder()
.Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true })
.Build();
ConnectionString = Configuration["ConnectionStrings:MySql"];
}
public async Task<bool> InsertAsync<T>(T t) where T : class
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
return await connection.InsertAsync(t) > 0;
}
}
public async Task<bool> InsertAsync<T>(List<T> list) where T : class
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
return await connection.InsertAsync(list) > 0;
}
}
public async Task<bool> DeleteAsync<T>(T t) where T : class
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
return await connection.DeleteAsync(t);
}
}
public async Task<bool> UpdateAsync<T>(T t) where T : class
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
return await connection.UpdateAsync(t);
}
}
public async Task<IEnumerable<T>> GetAllAsync<T>() where T : class
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
return await connection.GetAllAsync<T>();
}
}
public async Task<T> GetByIDAsync<T>(int id) where T : class
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
return await connection.GetAsync<T>(id);
}
}
public async Task<int> ExecuteAsync(string path)
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
using (StreamReader streamReader = new StreamReader(path, System.Text.Encoding.UTF8))
{
var script = await streamReader.ReadToEndAsync();
return await connection.ExecuteAsync(script);
}
}
}
public async Task<int> ExecuteAsync(string sql, object param = null)
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
return await connection.ExecuteAsync(sql, param);
}
}
public async Task<bool> ExecuteAsyncTransaction(List<string> list)
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();
try
{
foreach (var sql in list)
{
await connection.ExecuteAsync(sql, null, transaction);
}
transaction.Commit();
return true;
}
catch (Exception e)
{
transaction.Rollback();
return false;
}
}
}
public async Task<bool> ExecuteAsyncTransaction(List<KeyValuePair<string, object>> list)
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();
try
{
foreach (var item in list)
{
await connection.ExecuteAsync(item.Key, item.Value, transaction);
}
transaction.Commit();
return true;
}
catch (Exception e)
{
transaction.Rollback();
return false;
}
}
}
public async Task<IEnumerable<dynamic>> QueryAsync(string sql, object param = null)
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
return await connection.QueryAsync(sql, param);
}
}
public async Task<dynamic> QueryFirstOrDefaultAsync(string sql, object param = null)
{
using (IDbConnection connection = new MySqlConnection(ConnectionString))
{
return await connection.QueryFirstOrDefaultAsync(sql, param);
}
}
}
调用
Dapper dapper = new Dapper();
Student student = new Student()
{
sid = 1,
sname = "张三",
sage = 20,
ssex = "男"
};
List<Student> students = new List<Student>()
{
new Student(){sid = 1, sname = "张三", sage = 20,ssex = "男"},
new Student(){sid = 2, sname = "李思思", sage = 23,ssex = "女"},
new Student(){sid = 3, sname = "王五", sage = 27,ssex = "男"}
};
//增加
await dapper.InsertAsync<Student>(student);
await dapper.InsertAsync<Student>(students);
//修改 / 删除 1、以[ExplicitKey] /[key]为条件
await dapper.UpdateAsync(new Student() { sid = 1, sname = "修改", sage = 20, ssex = "男" });
await dapper.DeleteAsync(new Student() { sid = 1 });
//修改 / 删除 2、直接写sql，灵活、简单
await dapper.ExecuteAsync(@"update student
set sid=0
where sname=@sname or sage=@sage;",
new { sname = "李思思", sage = 27 });
await dapper.ExecuteAsync(@"delete from student
where sname=@sname or sage=@sage;",
new { sname = "李思思", sage = 27 });
//简单事务: 无参数
List<string> list1 = new List<string>()
{
"insert into student(sid,sname) values('1','1')",
"insert into teacher(tid,tname) values('1','1')"
};
await dapper.ExecuteAsyncTransaction(list1);
//简单事务: 有参数
List<KeyValuePair<string, object>> list2 = new List<KeyValuePair<string, object>>();
list2.Add(new KeyValuePair<string, object>(
"insert into student(sid,sname) values(@sid,@sname)",
new { sid = 22, sname = "test1" }));
list2.Add(new KeyValuePair<string, object>(
"insert into teacher(tid,tname) values(@tid,@tname)",
new { tid = 33, tname = "test2" }));
await dapper.ExecuteAsyncTransaction(list2);
//查询
var result1 = dapper.GetAllAsync<Student>();
var result2 = dapper.GetByIDAsync<Student>(1);
var result3 = dapper.QueryAsync(@"SELECT s.sid AS '学号', s.sname AS '姓名',
COUNT(sc.cid) AS '课程数目',
SUM(sc.score) AS '总分数'
FROM student s, sc sc
WHERE s.sid = sc.sid
GROUP BY s.sid, s.sname");
