轻量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")] 。

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

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

7、控制器的演示了标准方法

 public class AzCustomersController : Controller
    {
        IRepositoryFactory repositoryFactory;
        IRepository<AzCustomers> repository;

        public AzCustomersController(IRepositoryFactory repositoryFactory)
        {
            this.repositoryFactory = repositoryFactory;
            this.repository = repositoryFactory.Create<AzCustomers>();
        }
        /// <summary>
        /// 返回 客户 列表
        /// 异步调用数据,其异步部分明细View没有Controller只有View
        /// </summary>
        public IActionResult Index(int pageindex = 1)
        {
            var queryresult = repository.Query()
             .Select(s => s.CustomerID
                        , s => s.CompanyName
                        , s => s.ContactName
                        , s => s.ContactTitle
                        , s => s.Address
                        , s => s.City
                        , s => s.Region
                        , s => s.PostalCode
                        , s => s.Country
                        , s => s.Phone
                        , s => s.Fax
                ).OrderBy(o => o.CustomerID).Page(20, pageindex).PageGo();

            var model = AzCustomersList.GetModelList(queryresult, 20, pageindex);
            string xrh = Request.Headers["X-Requested-With"];
            if (!string.IsNullOrEmpty(xrh) && xrh.Equals("XMLHttpRequest", System.StringComparison.OrdinalIgnoreCase))
            {
                return PartialView("DetailsPage", model);
            }
            return View(model);
        }

        /// <summary>
        /// 增加客户
        /// </summary>
        public ActionResult Create()
        {

            var model = new AzCustomers();
            return View(model);
        }

        /// <summary>
        /// 增加保存客户
        /// </summary>
        [HttpPost, ValidateAntiForgeryToken]
        [ActionName("Create")]
        public IActionResult CreatePost(AzCustomers model)
        {
            if (ModelState.IsValid)
            {
                repository.Insert().With(s => s.CustomerID, model.CustomerID)
                         .With(s => s.CompanyName, model.CompanyName)
                         .With(s => s.ContactName, model.ContactName)
                         .With(s => s.ContactTitle, model.ContactTitle)
                         .With(s => s.Address, model.Address)
                         .With(s => s.City, model.City)
                         .With(s => s.Region, model.Region)
                         .With(s => s.PostalCode, model.PostalCode)
                         .With(s => s.Country, model.Country)
                         .With(s => s.Phone, model.Phone)
                         .With(s => s.Fax, model.Fax)
                 .Go();//按增加保存 
                return RedirectToAction("Index");
            }

            return View(model);
        }

        /// <summary>
        /// 编辑客户
        /// </summary>
        public IActionResult Edit(string Id)
        {
            var model = repository.Query()
                    .Select(s => s.CustomerID
                            , s => s.CompanyName
                            , s => s.ContactName
                            , s => s.ContactTitle
                            , s => s.Address
                            , s => s.City
                            , s => s.Region
                            , s => s.PostalCode
                            , s => s.Country
                            , s => s.Phone
                            , s => s.Fax
                     ).Where(s => s.CustomerID == Id).Go().FirstOrDefault();

            return View(model);
        }

        /// <summary>
        ///  保存编辑的客户
        /// </summary>
        [HttpPost, ValidateAntiForgeryToken]
        [ActionName("Edit")]
        public IActionResult EditPost(AzCustomers model)
        {
            if (ModelState.IsValid)
            {
                repository.Update().Set(s => s.CustomerID, model.CustomerID)
                        .Set(s => s.CompanyName, model.CompanyName)
                        .Set(s => s.ContactName, model.ContactName)
                        .Set(s => s.ContactTitle, model.ContactTitle)
                        .Set(s => s.Address, model.Address)
                        .Set(s => s.City, model.City)
                        .Set(s => s.Region, model.Region)
                        .Set(s => s.PostalCode, model.PostalCode)
                        .Set(s => s.Country, model.Country)
                        .Set(s => s.Phone, model.Phone)
                        .Set(s => s.Fax, model.Fax)
            .Go();//按增加保存 
                return RedirectToAction("Index");
            }
            return View(model);
        }

        /// <summary>
        /// 显示客户单个记录
        /// </summary>
        public IActionResult Details(string Id)
        {
            var model = repository.Query()
                    .Select(s => s.CustomerID
                            , s => s.CompanyName
                            , s => s.ContactName
                            , s => s.ContactTitle
                            , s => s.Address
                            , s => s.City
                            , s => s.Region
                            , s => s.PostalCode
                            , s => s.Country
                            , s => s.Phone
                            , s => s.Fax
                     ).Where(s => s.CustomerID == Id).Go().FirstOrDefault();
            return View(model);
        }


        /// <summary>
        /// 独立页面删除客户
        /// </summary>
        public ActionResult Delete(string Id)
        {
            var model = repository.Query()
                  .Select(s => s.CustomerID
                          , s => s.CompanyName
                          , s => s.ContactName
                          , s => s.ContactTitle
                          , s => s.Address
                          , s => s.City
                          , s => s.Region
                          , s => s.PostalCode
                          , s => s.Country
                          , s => s.Phone
                          , s => s.Fax
                   ).Where(s => s.CustomerID == Id).Go().FirstOrDefault();
            return View(model);
        }

        /// <summary>
        /// 独立页面删除客户
        /// </summary>
        [HttpPost, ActionName("Delete")]
        public IActionResult DeleteConfirmed(AzCustomers model)
        {
            repository.Delete().Where(c => c.CustomerID == model.CustomerID).Go();
            return RedirectToAction("Index");
        }
    }

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

 

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

（1）、引用SqlRepoEx.MsSql.ServiceCollection

（2）、 services.AddSimpleSqlRepo(ConnectionString);

（3）、 AzCustomersController(IRepositoryFactory repositoryFactory)

（4）、this.repository = repositoryFactory.Create<AzCustomers>();

 

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