轻量ORM-SqlRepoEx （十四）最佳实践之Dapper（1）

简介：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}\t{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];

此方法生成的结果

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}\t{item.productname2}\t{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}\t{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}\t{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}\t{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));