听闻少年二字,当与平庸相斥。这篇文章主要讲述轻量ORM-SqlRepoEx (十四)最佳实践之Dapper相关的知识,希望能为你提供帮助。
简介:SqlRepoEx是 .Net平台下兼容.NET Standard 2.0人一个轻型的ORM。解决了Lambda转Sql语句这一难题,SqlRepoEx使用的是Lambda表达式,所以,对c#程序员来说,是非常简单的,其语法特点与Linq to Sql极为相似。不仅实现了完整的Select、Insert、Update、Delete等语句解析器,同时,也实现了Select、where、order by等子句,这些语句与子句均支持导出SQL语句,使得拼接复杂场景SQL语句变得轻松,SqlRepoEx很快其原生数据访问与Dapper不相上下,SqlRepoEx本身支持Sql Server与mysql方言,同时通过SqlRepoEx.Normal支持非方言SQL。SqlRepoEx没侵入性,仅通过简单的几个特性,就能让类与数据库关联起来;
*本系列以静态工厂为例;
*数据来源于Northwind数据库;
*完整的代码见
SqlRepoEx2.2.1 GitHub示例
SqlRepoEx2.2.1 码云示例
一、IDbConnection可通过下列两种方式获取
1、工厂获取
private static IDbConnection dbConnection = MsSqlRepoFactory.DbConnection;
2、数据仓储
var repository = MsSqlRepoFactory.Create<
AzProducts>
();
IDbConnection dbConnection = repository.DbConnection;
二、 SQL语句中 @ 参数的生成和定义
1、SqlRepoEx的Insert、Updata 增加了ParamSql()方法获取
@ 参数 语句;
2、对于Where条件语句中,如要生成
@ 参数 语句 只需要在表达式中 .Where(p =>
p.ProductID == p.ProductID);
右侧表达式中使用类型属性表达式即可。
3、关于数据字段与属性联,将在下篇中介绍
三、 简单查询
public static void QueryOnly()
{
// 创建数据仓储
var repository = MsSqlRepoFactory.Create<
AzProducts>
();
//查询
var result = repository.Query().Top(10);
Console.WriteLine(result.Sql());
// 通过 Dapper 获取数据
IEnumerable<
AzProducts>
azProducts = dbConnection.Query<
AzProducts>
(result.Sql());
// 显示结果(只取两列,仅为显示目的)
foreach (var item in azProducts)
{
Console.WriteLine($"{item.ProductID} {item.ProductName2}");
}
}
此方法生成的 SQL
SELECT TOP (10) [dbo].[Products].[ProductID]
, [dbo].[Products].[ProductName] as [ProductName2]
【轻量ORM-SqlRepoEx (十四)最佳实践之Dapper】, [dbo].[Products].[SupplierID]
, [dbo].[Products].[CategoryID]
, [dbo].[Products].[QuantityPerUnit]
, [dbo].[Products].[UnitPrice]
, [dbo].[Products].[UnitsInStock]
, [dbo].[Products].[UnitsOnOrder]
, [dbo].[Products].[ReorderLevel]
, [dbo].[Products].[Discontinued]
FROM [dbo].[Products];
此方法生成的结果
1
Chai
2
Chang
3
Aniseed Syrup
4
Chef Anton‘s Cajun Seasoning
5
Chef Anton‘s Gumbo Mix
6
Grandma‘s Boysenberry Spread
7
Uncle Bob‘s Organic Dried Pears
8
Northwoods Cranberry Sauce
9
Mishi Kobe Niku
10
Ikura
四、InnerJoin 查询* LeftOuterJoin、RightOuterJoin与此例相似
public static void DoInnerJoin()
{
// 创建数据仓储
var repository = MsSqlRepoFactory.Create<
AzProducts>
();
// 构建查询语句,相较而言,语法更接近于SQL,与Linq是有很大区别的
var result = repository.Query()
.InnerJoin<
AzSuppliers>
()
.On<
AzSuppliers>
((l, r) =>
l.SupplierID == r.SupplierID, r =>
r.CompanyName)
.Top(10);
Console.WriteLine(result.Sql());
Console.WriteLine();
// 通过 Dapper 获取数据
IEnumerable<
AzProducts>
azProducts = dbConnection.Query<
AzProducts>
(result.Sql());
foreach (var item in azProducts)
{
Console.WriteLine($"{item.ProductID} {item.ProductName2} {item.Supplier}");
}
}
此方法生成的 SQL
SELECT TOP (10) [dbo].[Products].[ProductID]
, [dbo].[Products].[ProductName] as [ProductName2]
, [dbo].[Products].[SupplierID]
, [dbo].[Products].[CategoryID]
, [dbo].[Products].[QuantityPerUnit]
, [dbo].[Products].[UnitPrice]
, [dbo].[Products].[UnitsInStock]
, [dbo].[Products].[UnitsOnOrder]
, [dbo].[Products].[ReorderLevel]
, [dbo].[Products].[Discontinued]
, [dbo].[Suppliers].[CompanyName] as [Supplier]
FROM [dbo].[Products]
INNER JOIN [dbo].[Suppliers]
ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID];
此方法生成的结果
1
Chai
Exotic Liquids
2
Chang
Exotic Liquids
3
Aniseed Syrup
Exotic Liquids
4
Chef Anton‘s Cajun Seasoning
New Orleans Cajun Delights
5
Chef Anton‘s Gumbo Mix
New Orleans Cajun Delights
6
Grandma‘s Boysenberry Spread
Grandma Kelly‘s Homestead
7
Uncle Bob‘s Organic Dried Pears Grandma Kelly‘s Homestead
8
Northwoods Cranberry Sauce
Grandma Kelly‘s Homestead
9
Mishi Kobe Niku Tokyo Traders
10
Ikura
Tokyo Traders
五、条件查询
public static void QueryWhere()
{
// 创建数据仓储
var repository = MsSqlRepoFactory.Create<
AzProducts>
();
var result = repository.Query()
.Where(p =>
p.ProductName2.Contains("t") &
&
p.ProductID <
100)
.Top(10);
Console.WriteLine(result.Sql());
Console.WriteLine();
// 通过 Dapper 获取数据
IEnumerable<
AzProducts>
azProducts = dbConnection.Query<
AzProducts>
(result.Sql());
foreach (var item in azProducts)
{
Console.WriteLine($"{item.ProductID} {item.ProductName2}");
}
}
此方法生成的 SQL
SELECT TOP (10) [dbo].[Products].[ProductID]
, [dbo].[Products].[ProductName] as [ProductName2]
, [dbo].[Products].[SupplierID]
, [dbo].[Products].[CategoryID]
, [dbo].[Products].[QuantityPerUnit]
, [dbo].[Products].[UnitPrice]
, [dbo].[Products].[UnitsInStock]
, [dbo].[Products].[UnitsOnOrder]
, [dbo].[Products].[ReorderLevel]
, [dbo].[Products].[Discontinued]
FROM [dbo].[Products]
WHERE ((([dbo].[Products].[ProductName] LIKE ‘%t%‘) and ([dbo].[Products].[ProductID] <
100)));
此方法生成的结果
4
Chef Anton‘s Cajun Seasoning
5
Chef Anton‘s Gumbo Mix
8
Northwoods Cranberry Sauce
12
Queso Manchego La Pastora
14
Tofu
17
Alice Mutton
18
Carnarvon Tigers
19
Teatime Chocolate Biscuits
22
Gustaf‘s Kn?ckebr?d
23
Tunnbr?d
六、Union
public static void QueryUnion()
{
// 创建数据仓储
var repository = MsSqlRepoFactory.Create<
AzCustomers>
();
// 此语句不会参与数据查询,只是作为Union的包裹
// 如果此语句本身也是数据查询,请增加到new List<
UnionSql>
中
var result = repository.Query()
.Select(c =>
c.CustomerID, c =>
c.CompanyName);
var result01 = repository.Query()
.Select(c =>
c.CustomerID, c =>
c.CompanyName)
.Where(c =>
c.CustomerID == "ANATR");
var result02 = repository.Query()
.Select(c =>
c.CustomerID, c =>
c.CompanyName)
.Where(c =>
c.CustomerID == "FRANK");
var result03 = repository.Query()
.Select(c =>
c.CustomerID, c =>
c.CompanyName)
.Where(c =>
c.CustomerID == "TRADH");
var resultAllSql = result.UnionSql(new List<
UnionSql>
{
UnionSql.New(
result01,UnionType.Union ),
UnionSql.New(
result02,UnionType.Union ),
UnionSql.New(
result03,UnionType.Union ), });
Console.WriteLine(resultAllSql);
Console.WriteLine();
// 通过 Dapper 获取数据
IEnumerable<
AzCustomers>
azCustomers = dbConnection.Query<
AzCustomers>
(resultAllSql);
foreach (var item in azCustomers)
{
Console.WriteLine($"{item.CustomerID} {item.CompanyName}");
}
}
此方法生成的 SQL
SELECT [_this_is_union].[CustomerID]
, [_this_is_union].[CompanyName]
FROM ( SELECT [dbo].[Customers].[CustomerID]
, [dbo].[Customers].[CompanyName]
FROM [dbo].[Customers]
WHERE (([dbo].[Customers].[CustomerID] = ‘ANATR‘))
UNION
SELECT [dbo].[Customers].[CustomerID]
, [dbo].[Customers].[CompanyName]
FROM [dbo].[Customers]
WHERE (([dbo].[Customers].[CustomerID] = ‘FRANK‘))
UNION
SELECT [dbo].[Customers].[CustomerID]
, [dbo].[Customers].[CompanyName]
FROM [dbo].[Customers]
WHERE (([dbo].[Customers].[CustomerID] = ‘TRADH‘)) )
AS
_this_is_union
此方法生成的结果
ANATR
Ana Trujillo Emparedados y helados
FRANK
Frankenversand
TRADH
Tradi??o Hipermercados
七、增加(使用实例)public static void DoInsertEntityParam()
{
var repository = MsSqlRepoFactory.Create<
AzProducts>
();
AzProducts azProduct = new AzProducts { ProductName2 = "testvalue" };
var resultinsert = repository
.Insert();
// 使用ParamSql()方法获取 @ 参数SQL语句
Console.WriteLine(resultinsert.ParamSql());
Console.WriteLine();
// 需返回自增字段,所以用Query
IEnumerable<
AzProducts>
azProducts = dbConnection.Query<
AzProducts>
(resultinsert.ParamSql(), azProduct);
foreach (var item in azProducts)
{
Console.WriteLine($"{item.ProductID} {item.ProductName2}");
}
}
此方法生成的 SQL
INSERT [dbo].[Products]([ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued])
VALUES(@ProductName2,@SupplierID,@CategoryID,@QuantityPerUnit,@UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued);
SELECT [ProductID],[ProductName] as ProductName2,[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]
FROM [dbo].[Products]
WHERE [ProductID] = SCOPE_IDENTITY();
此方法生成的结果
96
testvalue
八、批增加(使用选择)public static void DoInsertEntityParamBatch()
{
// 创建数据仓储
var repository = MsSqlRepoFactory.Create<
AzProducts>
();
// 设置要批处理的数据
List<
AzProducts>
azProductList = new List<
AzProducts>
{
new AzProducts { ProductName2 = "testvalue1" ,CategoryID=1,UnitPrice=123},
new AzProducts { ProductName2 = "testvalue2" ,CategoryID=1,UnitPrice=123},
new AzProducts { ProductName2 = "testvalue3" ,CategoryID=1,UnitPrice=123},
new AzProducts { ProductName2 = "testvalue4" ,CategoryID=1,UnitPrice=123 },
new AzProducts { ProductName2 = "testvalue5" ,CategoryID=1,UnitPrice=123},
new AzProducts { ProductName2 = "testvalue6" ,CategoryID=1,UnitPrice=123},
};
// 使用选择增加
var resultinsert = repository
.Insert().ParamWith(c =>
c.ProductName2, c =>
c.UnitPrice, c =>
c.CategoryID);
Console.WriteLine(resultinsert.ParamSql());
Console.WriteLine();
// 通过 Dapper 批处理
dbConnection.Execute(resultinsert.ParamSql(), azProductList);
}
此方法生成的 SQL
INSERT [dbo].[Products]([ProductName],[UnitPrice],[CategoryID])
VALUES(@ProductName2,@UnitPrice,@CategoryID);
SELECT [ProductName] as ProductName2,[UnitPrice],[CategoryID],[ProductID]
FROM [dbo].[Products]
WHERE [ProductID] = SCOPE_IDENTITY();
九、更新
public static void DoUpdateEntityParam()
{
// 创建数据仓储
var repository = MsSqlRepoFactory.Create<
AzProducts>
();
// 构建更新语句
var resultUpdate = repository
.Update()
.ParamSet(p =>
p.ProductName2, p =>
p.CategoryID)
// Where 中使用下列格式语句,可生成带 @ 的参数
.Where(p =>
p.ProductID == p.ProductID);
Console.WriteLine(resultUpdate.ParamSql());
Console.WriteLine();
// 需更新的数据
AzProducts products = new AzProducts() { ProductID = 84, ProductName2 = "testvalue100", CategoryID = 7 };
// 通过 Dapper 更新数据
int result = dbConnection.Execute(resultUpdate.ParamSql(), products);
Console.WriteLine($"{result}");
}
此方法生成的 SQL
UPDATE [dbo].[Products]
SET ProductName
= @ProductName2, CategoryID
= @CategoryID
WHERE (([dbo].[Products].[ProductID] = @ProductID));
十、删除
public static void DoDeleteEntity(bool go = false)
{
// 创建数据仓储
var repository = MsSqlRepoFactory.Create<
AzProducts>
();
// 要删除的数据
AzProducts azProducts = new AzProducts { ProductName2 = "testvalue", ProductID = 81 };
// 构建删除,使用实例构建时,如果不设置 Where 语句
// SqlRepoEx 会以关键词来构建
Where 语句
var resultUpdate = repository.Delete().For(azProducts);
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
// 通过 Dapper 删除数据
int result = dbConnection.Execute(resultUpdate.Sql());
Console.WriteLine($"{result}");
}
此方法生成的 SQL
DELETE [dbo].[Products]
WHERE (([dbo].[Products].[ProductID] = @ProductID));
十一、使用事务public static void DoDeleteTransaction()
{
// 创建数据仓储
var repository = MsSqlRepoFactory.Create<
AzProducts>
();
// 构建删除,如果不是实例构建,用户必需自行指定删除条件
// Where 中使用下列格式语句,可生成带 @ 的参数
var resultUpdate = repository.Delete().Where(p =>
p.ProductID == p.ProductID);
// 要删除的数据集
List<
AzProducts>
azProductList = new List<
AzProducts>
{
new AzProducts{ProductID=92},
new AzProducts{ProductID=93},
new AzProducts{ProductID=94},
new AzProducts{ProductID=91},
};
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
// 使用事务控制
using (var transaction = dbConnection.BeginTransaction())
{
//
通过 Dapper 删除,同时指定了事务
dbConnection.Execute(resultUpdate.Sql(), azProductList, transaction: transaction);
// 仅为了演示,此处回滚事务,取消删除
// 如果相要提交事务,请将此处改为 transaction.Commit() 可看到删除效果
transaction.Rollback();
}
}
此方法生成的 SQL
DELETE [dbo].[Products]
WHERE (([dbo].[Products].[ProductID] = @ProductID));
推荐阅读
- 使用mybatis逆向工程生成对应的beandao以及mapper
- Mybatis MetaObject和ObjectWrapper设计概念
- FunctorApplicative 和 Monad x
- c#开发移动APP-Xamarin入门
- Android studio 不能创建Activity等文件
- adb.Android手机可以通过adb执行按键操作
- 第七篇-列表式App(ListActivity及ListView)
- 第三篇-以LinearLayout进行Android界面设计
- Android------------UI的控件学习内容