Dapper笔记
介绍
Dapper是一个用于.NET的简单的对象映射，支持异步,并且在速度上有着轻ORM之王的称号。
Dapper扩展IDbConnection，提供有用的扩展方法来查询数据库。
个人觉得他非常好用，现在已经取代了原来的SqlHelper。优点：
- 使用Dapper可以自动进行对象映射。Dapper原理通过Emit反射IDataReader的序列队列，来快速的得到和产生对象。
- 轻量级，单文件。
- 支持多数据库。
原生Dapper主要有以下扩展方法：
- Execute
- ExecuteScalar
- ExecuteReader
- Query
- QueryFirst
- QueryFirstOrDefault
...
安装Nuget：Install-Package Dapper
增删改
单次插入
using (var connection = GetConnection())
{
string sql = "INSERT INTO Order_item(ID,OrderID) VALUES(@ID,@OrderID)";
Order_item orderItem = new Order_item { ID = 10000, OrderID = 1000 };
connection.Execute(sql, orderItem);
}
批量插入
using (var connection = GetConnection())
{
string sql = "INSERT INTO Order_item(ID,OrderID) VALUES(@ID,@OrderID)";
Order_item[] orderItems =new Order_item[] {
new Order_item { ID = 10001, OrderID = 1000 },
new Order_item { ID = 10002, OrderID = 1000 },
};
connection.Execute(sql, orderItems);
}
修改、删除也使用Execute方法执行，把sql改成update、delete即可
查询
查询第一行第一列
conn.ExecuteScalar<T>(sql, param, transaction);
查询第一行
conn.QueryFirst<TEntity>(sql, param);
conn.QueryFirstOrDefault<TEntity>(sql, param);
QuerySingleAsync?
QuerySingleOrDefaultAsync?
查询集合
using (var connection = GetConnection())
{
string sql = "SELECT * FROM Order_item ORDER BY ID DESC LIMIT 10";
var orderItemList = connection.Query<Order_item>(sql);
}
IN查询
using (SqlConnection conn = new SqlConnection(connStr))
{
string sqlStr = @"select A.Id,A.Title,S.SeoKeywords from Article A where A.Id in @ids";
conn.Open();
var articleList = conn.Query(sqlStr, new { ids = new int[] { 41, 42, 43, 44, 45, 46, 47, 48 } });
foreach (var item in articleList)
{
Console.WriteLine(item.Id + " | " + item.SeoKeywords + " | ：" + item.Title);
}
}
Reader
using (var reader = conn.ExecuteReader(sql, param))
{
if (reader.Read())
{
int id = Convert.ToInt32(reader["id"]);
}
}
查询多表映射
实体：
public class ThemeInfo
{
public long ID { get; set; }
public string ThemeShowID { get; set; }
public string Name { get; set; }
public ThemeVersion ThemeVersion { get; set; }
}
public class ThemeVersion
{
public long ID { get; set; }
public long ThemeID { get; set; }
public string Version { get; set; }
}
public class ThemeInfoDto : ThemeInfo
{
public long VersionID { get; set; }
public string Version { get; set; }
}
One to One：一对一的关系映射结果到强类型集合
第一种方式：
var connection = GetConnection();
string sql = "select t.ID,t.ThemeShowID,t.Name,t.Description,v.ThemeID,v.Version from theme_info t inner join theme_version v on t.id = v.ThemeID where t.id=41 order by t.id desc limit 10";
var themeInfoDtoList = connection.Query<ThemeInfo, ThemeVersion, ThemeInfoDto>(sql, (themeInfo, themeVersion) =>
{
var themeInfoDto = new ThemeInfoDto();
themeInfoDto.ID = themeInfo.ID;
themeInfoDto.ThemeShowID = themeInfo.ThemeShowID;
themeInfoDto.Name = themeInfo.Name;
themeInfoDto.Description = themeInfo.Description;
themeInfoDto.Version = themeVersion.Version;
return themeInfoDto;
//themeInfo.ThemeVersion = themeVersion;
//return themeInfo;
}, splitOn: "ThemeID");//两表分割字段，也就是第二个表的第一个字段
第二种方式：
var connection = GetConnection();
string sql = "select t.ID,t.ThemeShowID,t.Name,v.ID,v.ThemeID,v.Version from theme_info t inner join theme_version v on t.id = v.ThemeID where t.id=41 order by t.id desc limit 10";
using (var reader = connection.ExecuteReader(sql))
{
var themeInfoParser = reader.GetRowParser<ThemeInfo>(startIndex: 0, length: 3);
var themeVersionParser = reader.GetRowParser<ThemeVersion>(startIndex: 3, length: 3);
while (reader.Read())
{
var themeInfo = themeInfoParser(reader);
var themeVersion = themeVersionParser(reader);
var themeInfoDto = new ThemeInfoDto();
themeInfoDto.ID = themeInfo.ID;
themeInfoDto.ThemeShowID = themeInfo.ThemeShowID;
themeInfoDto.Name = themeInfo.Name;
themeInfoDto.VersionID = themeVersion.ID;
themeInfoDto.Version = themeVersion.Version;
}
}
One to Many：一对多的关系映射结果到强类型集合
可以用以上两种方式实现，存储数据结构如下：
IDictionary<ThemeInfo,List<ThemeVersion>>
多结果集
using (SqlConnection conn = new SqlConnection(connStr))
{
string sqlStr = @"select Id,Title,Author from Article where Id = @id
select * from QQModel where Name = @name
select * from SeoTKD where Status = @status";
using (var multi = conn.QueryMultiple(sqlStr, new { id = 11, name = "打代码", status = 99 }))
{
//multi.IsConsumed reader的状态 ，true 是已经释放
if (!multi.IsConsumed)
{
//注意一个东西，Read获取的时候必须是按照上面返回表的顺序 （article，qqmodel，seotkd）
//强类型
var articleList = multi.Read<Temp>();//类不见得一定得和表名相同
var QQModelList = multi.Read<QQModel>();
var SeoTKDList = multi.Read<SeoTKD>();
//动态类型
//var articleList = multi.Read();
//var QQModelList = multi.Read();
//var SeoTKDList = multi.Read();
}
}
}
事务
var sql = "Invoice_Insert";
using (var connection = My.ConnectionFactory())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var affectedRows = connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure,
transaction: transaction);
transaction.Commit();
}
}
范围事务
using (var transaction = new TransactionScope())
{
var sql = "Invoice_Insert";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure);
}
transaction.Complete();
}
执行存储过程
执行一次存储过程
string sql = "Invoice_Insert";
using (var connection = My.ConnectionFactory())
{
var affectedRows = connection.Execute(sql,
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
commandType: CommandType.StoredProcedure);
}
执行多次存储过程
string sql = "Invoice_Insert";
using (var connection = My.ConnectionFactory())
{
var affectedRows = connection.Execute(sql,
new[]
{
new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_1"},
new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_2"},
new {Kind = InvoiceKind.StoreInvoice, Code = "Many_Insert_3"}
},
commandType: CommandType.StoredProcedure
);
}
