轻量ORM-SqlRepoEx （七）AspNetCore应用

orm-sqlrepoex 是 .net平台下兼容.net standard 2.0，一个实现以lambda表达式转转换标准sql语句，使用强类型操作数据的轻量级orm工具，在减少魔法字串同时，通过灵活的lambda表达式组合，实现业务数据查询的多样性。

orm-sqlrepoex 也是一个极易使用的工具，通过在aspnetcore中的应用可以展示。

 本案例源码在：

https://github.com/azthinker/sqlrepoex2.0demoforaspcore

或

https://gitee.com/azthinker/sqlrepoex2.0demoforaspcore

源码部分代码是使用代码工具生成

https://github.com/azthinker/codetoolsolution

 

1、新建一个aspnetcore项目

2、通过nuget下载sqlrepoex库、由于本例中是aspnetcore.mvc项目，案例中使用的是sql server的northwind数据库，所以选择下载

 sqlrepoex.mssql.servicecollection

 

3、在startup.cs文件的public void configureservices(iservicecollection services)中添加

             string connectionstring = "data source=(local);initial catalog=northwind;user id=test;password=test";

            services.addsimplesqlrepo(connectionstring);

 

 

4、增加一个简单类azcustomers，其属性来源于 customers 表。为使sqlrepoex 精准访问，增加特性标识  [tablename("customers")] 。

 

 1 using system;
 2 using sqlrepoex.core.customattribute;
 3 
 4 // 客户 业务类
 5 namespace demotools.bll.demonorthwind
 6 {
 7     [tablename("customers")]
 8     /// <summary>
 9     /// 客户 业务类
10     /// </summary>
11     public sealed class azcustomers
12     {
13         public string customerid { get; set; }
14 
15         public string companyname { get; set; }
16 
17         public string contactname { get; set; }
18 
19         public string contacttitle { get; set; }
20 
21         public string address { get; set; }
22 
23         public string city { get; set; }
24 
25         public string region { get; set; }
26 
27         public string postalcode { get; set; }
28 
29         public string country { get; set; }
30 
31         public string phone { get; set; }
32 
33         public string fax { get; set; }
34 
35     }
36 }

view code

 

5、增加一人简单的列表类 azcustomerslist，其中实现了ipagedlist接口，此接口是webdiyer.webcontrols.aspnetcore分页控件中定义，由于webdiyer.webcontrols.aspnetcore的源码不支持core2.1，所以重新编译，并将源码加工程中。

 

using system;
using system.collections.generic;
using webdiyer.webcontrols.aspnetcore;

//客户列表类
namespace demotools.bll.demonorthwind
{
    /// <summary>
    /// 客户  列表类
    /// </summary>
    public class azcustomerslist : list<azcustomers>, ipagedlist
    {
        public string displaydescription = "客户";
        public int pagesize { get; set; }
        public int totalitemcount { get; set; }
        public int currentpageindex { get; set; }

        public static azcustomerslist getmodellist((ienumerable<azcustomers> queryresult, int pagecount) queryresult, int pagesize, int currentpageindex)
        {
            azcustomerslist models = new azcustomerslist();
            models.addrange(queryresult.queryresult);
            models.totalitemcount = queryresult.pagecount;
            models.pagesize = pagesize;
            models.currentpageindex = currentpageindex;
            return models;
        }
    }
}

view code

 

6、增加一个控制器并在控制器的构造方法 azcustomerscontroller(irepositoryfactory repositoryfactory)，irepositoryfactory是sqlrepoex 工厂类的接口，由于前面（第2条中）已经注册了sqlrepoex 所需的依赖，此处仅需在构造中加入此接口即可。

 

  1 using system.linq;
  2 using demotools.bll.demonorthwind;
  3 using microsoft.aspnetcore.authorization;
  4 using microsoft.aspnetcore.mvc;
  5 using sqlrepoex.abstractions;
  6 
  7 // 客户 控制器
  8 namespace demotools.webui.demonorthwind.controllers
  9 {
 10     /// <summary>
 11     /// 客户
 12     /// </summary>
 13     public class azcustomerscontroller : controller
 14     {
 15         irepositoryfactory repositoryfactory;
 16         irepository<azcustomers> repository;
 17         public azcustomerscontroller(irepositoryfactory repositoryfactory)
 18         {
 19             this.repositoryfactory = repositoryfactory;
 20             this.repository = repositoryfactory.create<azcustomers>();
 21         }
 22 
 23         /// <summary>
 24         /// 返回 客户 列表
 25         /// 异步调用数据,其异步部分明细view没有controller只有view
 26         /// </summary>
 27         public iactionresult index(int pageindex = 1)
 28         {
 29             var queryresult = repository.query()
 30              .select(s => s.customerid
 31                         , s => s.companyname
 32                         , s => s.contactname
 33                         , s => s.contacttitle
 34                         , s => s.address
 35                         , s => s.city
 36                         , s => s.region
 37                         , s => s.postalcode
 38                         , s => s.country
 39                         , s => s.phone
 40                         , s => s.fax
 41                 ).orderby(o => o.customerid).page(20, pageindex).pagego();
 42             var model = azcustomerslist.getmodellist(queryresult, 20, pageindex);
 43             string xrh = request.headers["x-requested-with"];
 44             if (!string.isnullorempty(xrh) && xrh.equals("xmlhttprequest", system.stringcomparison.ordinalignorecase))
 45             {
 46                 return partialview("detailspage", model);
 47             }
 48             return view(model);
 49         }
 50 
 51         /// <summary>
 52         /// 增加客户
 53         /// </summary>
 54         public actionresult create()
 55         {
 56             var model = new azcustomers();
 57             return view(model);
 58         }
 59 
 60         /// <summary>
 61         /// 增加保存客户
 62         /// </summary>
 63         [httppost, validateantiforgerytoken]
 64         [actionname("create")]
 65         public iactionresult createpost(azcustomers model)
 66         {
 67             if (modelstate.isvalid)
 68             {
 69                 repository.insert().with(s => s.customerid, model.customerid)
 70                          .with(s => s.companyname, model.companyname)
 71                          .with(s => s.contactname, model.contactname)
 72                          .with(s => s.contacttitle, model.contacttitle)
 73                          .with(s => s.address, model.address)
 74                          .with(s => s.city, model.city)
 75                          .with(s => s.region, model.region)
 76                          .with(s => s.postalcode, model.postalcode)
 77                          .with(s => s.country, model.country)
 78                          .with(s => s.phone, model.phone)
 79                          .with(s => s.fax, model.fax)
 80                  .go();//按增加保存 
 81                 return redirecttoaction("index");
 82             }
 83             return view(model);
 84         }
 85 
 86         /// <summary>
 87         /// 编辑客户
 88         /// </summary>
 89         public iactionresult edit(string id)
 90         {
 91             var model = repository.query()
 92                     .select(s => s.customerid
 93                             , s => s.companyname
 94                             , s => s.contactname
 95                             , s => s.contacttitle
 96                             , s => s.address
 97                             , s => s.city
 98                             , s => s.region
 99                             , s => s.postalcode
100                             , s => s.country
101                             , s => s.phone
102                             , s => s.fax
103                      ).where(s => s.customerid == id).go().firstordefault();
104             return view(model);
105         }
106 
107         /// <summary>
108         ///  保存编辑的客户
109         /// </summary>
110         [httppost, validateantiforgerytoken]
111         [actionname("edit")]
112         public iactionresult editpost(azcustomers model)
113         {
114             if (modelstate.isvalid)
115             {
116                 repository.update().set(s => s.customerid, model.customerid)
117                         .set(s => s.companyname, model.companyname)
118                         .set(s => s.contactname, model.contactname)
119                         .set(s => s.contacttitle, model.contacttitle)
120                         .set(s => s.address, model.address)
121                         .set(s => s.city, model.city)
122                         .set(s => s.region, model.region)
123                         .set(s => s.postalcode, model.postalcode)
124                         .set(s => s.country, model.country)
125                         .set(s => s.phone, model.phone)
126                         .set(s => s.fax, model.fax)
127             .go();//按增加保存 
128                 return redirecttoaction("index");
129             }
130             return view(model);
131         }
132 
133         /// <summary>
134         /// 显示客户单个记录
135         /// </summary>
136         public iactionresult details(string id)
137         {
138             var model = repository.query()
139                     .select(s => s.customerid
140                             , s => s.companyname
141                             , s => s.contactname
142                             , s => s.contacttitle
143                             , s => s.address
144                             , s => s.city
145                             , s => s.region
146                             , s => s.postalcode
147                             , s => s.country
148                             , s => s.phone
149                             , s => s.fax
150                      ).where(s => s.customerid == id).go().firstordefault();
151             return view(model);
152         }
153 
154         /// <summary>
155         /// 独立页面删除客户
156         /// </summary>
157         public actionresult delete(string id)
158         {
159             var model = repository.query()
160                   .select(s => s.customerid
161                           , s => s.companyname
162                           , s => s.contactname
163                           , s => s.contacttitle
164                           , s => s.address
165                           , s => s.city
166                           , s => s.region
167                           , s => s.postalcode
168                           , s => s.country
169                           , s => s.phone
170                           , s => s.fax
171                    ).where(s => s.customerid == id).go().firstordefault();
172             return view(model);
173         }
174 
175         /// <summary>
176         /// 独立页面删除客户
177         /// </summary>
178         [httppost, actionname("delete")]
179         public iactionresult deleteconfirmed(azcustomers model)
180         {
181             repository.delete().where(c => c.customerid == model.customerid).go();
182             return redirecttoaction("index");
183         }
184 
185     }
186 }

view code

 

7、view的实现和其他代码参见上面给出的地址中的源码。

 

总结：从上面看出，对特sqlrepoex 所需要特定的操作，仅在第2、第3、第6中是必需的

（1）、引用sqlrepoex.mssql.servicecollection

（2）、 services.addsimplesqlrepo(connectionstring);

（3）、 azcustomerscontroller(irepositoryfactory repositoryfactory)

（4）、this.repository = repositoryfactory.create<azcustomers>();

 

然后就可以轻松的通过sqlrepoex 访问数据库了。