Insight.Database - .NET Micro ORM，第一部分 - 使用自动接口实现编写更少的DataAccess层代码

你为什么想要Insight.Database？

Insight.Database 拥有其他.NET Micro-ORMs的所有常见功能。但本文将重点介绍Insight.Database相较于其他ORM所独有且有趣的功能。

• Github源代码：https://github.com/jonwagner/Insight.Database
• Github Wiki：https://github.com/jonwagner/Insight.Database/wiki
• 演示项目URL：https://github.com/keerthirajap/Insight.Database.Demo

请查看我之前的文章

• 第一部分：Insight.Database - .NET Micro ORM，第一部分 - 使用自动接口实现编写更少的DataAccess层代码
• 第二部分：Insight.Database - .NET Micro ORM，第二部分 - 执行SELECT SQL命令
• 第三部分：即将推出

主题

1. 自动接口实现
2. 带异步的SQL属性
3. 在一行中传递SQL表值参数
4. 获取多个结果结构
5. 与.NET Core的依赖注入
6. 使用MOQ对WeatherForecastController进行单元测试
7. MOQ多个结果集

1. 自动接口实现

我认为自动接口实现是一项独有的功能，我们可以通过在接口中编写1到2行代码来执行完整的数据库操作，即使是很复杂的也是如此。

快速浏览

1. 创建一个存储过程来搜索Beer表，如下所示

   CREATE PROC GetBeerByType @type [varchar] AS 
         SELECT * FROM Beer WHERE Type = @type 
   GO

2. 为Beer表创建一个模型或POCO

   class Beer
   {
       public string Name;
       public string Flavor;        
   }

3. 为存储库创建一个接口（你不需要一个实现了该接口的具体类。）

   注意：正如你所见，SQL存储过程名称 "GetBeerByType" 和接口方法名称 "GetBeerByType" 是相同的。但不用担心，你也可以使用SQL属性来获得更好的命名约定。

   public interface IBeerRepository
   {        
       IList<Beer> GetBeerByType(string type);
   }

   **或者**

   public interface IBeerRepository
   {
       [Sql("[dbo].[GetBeerByType]")]    // For better understanding    
       IList<Beer> GetBeerByType(string type);
   }

4. 从Service层或Controller的构造函数访问存储库接口

       public WeatherForecastController()
       {
           // Create an instance
           DbConnection c = new SqlConnection(connectionString);
           IBeerRepository i = c.As<IBeerRepository>();
   
           // Call the Stored Procedure
           var results = i.GetBeerByType("ipa");
       }

5. 就是这样！你无需创建实现存储库接口的具体存储库类。因为在底层，Insight在运行时会创建一个匿名类，如下所示

   class Anonymous : DbConnectionWrapper, IBeerRepository
   {
       public IList<Beer> GetBeerByType(string type)
       {
           return InnerConnection.ExecuteSql("GetBeerByType", new { type = type });
       }
   }

让我们继续这个教程

必备组件

1. 安装.NET Core 3.1.0或更高版本的SDK。
2. 安装Visual Studio 2019和SQL Server Management Studio。
3. SQL Server 2008 R2或更高版本。

创建一个新数据库并执行下面的SQL脚本。

创建数据库和示例表

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE DATABASE [Insight.Database.Demo]
GO

USE [Insight.Database.Demo]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE _
object_id = OBJECT_ID(N'[dbo].[WeatherForecast]') AND type in (N'U'))
DROP TABLE [dbo].[WeatherForecast]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WeatherForecast](
    [WeatherForecastId] [int] IDENTITY(100,1) NOT NULL,
    [Date] [datetime] NULL,
    [TemperatureC] [int] NULL,
    [Summary] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.WeatherForecastId] PRIMARY KEY CLUSTERED 
(
    [WeatherForecastId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[WeatherForecast] ON 
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (106, CAST(N'2021-09-09T00:00:00.000' AS DateTime), 45, N'Scorching')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (105, CAST(N'2021-09-10T00:00:00.000' AS DateTime), 35, N'Sweltering')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (104, CAST(N'2021-09-11T00:00:00.000' AS DateTime), 25, N'Hot')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (103, CAST(N'2021-09-12T00:00:00.000' AS DateTime), 0, N'Chilly')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (102, CAST(N'2021-09-13T00:00:00.000' AS DateTime), 10, N'Warm')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (101, CAST(N'2021-09-14T00:00:00.000' AS DateTime), 5, N'Mild')
GO
INSERT [dbo].[WeatherForecast] ([WeatherForecastId], [Date], [TemperatureC], _
[Summary]) VALUES (100, CAST(N'2021-09-15T00:00:00.000' AS DateTime), -5, N'Freezing')
GO
SET IDENTITY_INSERT [dbo].[WeatherForecast] OFF
GO

简单的Get方法以获取所有WeatherForecast详情

1. 创建一个存储过程来Get

   CREATE PROC GetAllWeatherForecast
   AS
       SELECT * FROM [dbo].[WeatherForecast]
   GO

2. 为WeatherForecast创建一个接口，命名为IWeatherForecastRepository.cs

   注意：正如你所见，SQL存储过程名称和接口方法名称是相同的。

   namespace Insight.Database.Demo.Part1.Repository
   {
       using System;
       using System.Collections.Generic;
       using System.Linq;   
   
       public interface IWeatherForecastRepository
       {
           List<WeatherForecast> GetAllWeatherForecast();
       }
   }

3. 在Controller的构造函数中为IWeatherForecastRepository.cs创建一个实例。

       private readonly DbConnection _sqlConnection;
   
       private readonly IWeatherForecastRepository _weatherForecastRepository;
   
       public WeatherForecastController(ILogger<WeatherForecastController> logger)
       {
           this._sqlConnection = new SqlConnection("Data Source=.;
           Initial Catalog=Insight.Database.Demo;Persist Security Info=true;
                           Integrated Security=true;");
   
           this._weatherForecastRepository = 
                 this._sqlConnection.As<IWeatherForecastRepository>();
   
           _logger = logger;
       }

4. 从存储库获取WeatherForecast详情

   [HttpGet]
   public List<WeatherForecast> Get()
   {
       List<WeatherForecast> weatherForecasts = new List<WeatherForecast>();
       weatherForecasts = this._weatherForecastRepository.GetAllWeatherForecast();
   
       return weatherForecasts;
   }

5. Swagger中的结果

   

供你参考的表数据

2. 使用带异步的SQL属性进行简单的WeatherForecast Get

1. 以命名规范 "P_GetAllWeatherForecast" 创建新的存储过程以Get

   CREATE PROC P_GetAllWeatherForecast
   AS
       SELECT * FROM [dbo].[WeatherForecast]
   GO

2. 使用Async方法和SQL属性更新IWeatherForecastRepository.cs

   namespace Insight.Database.Demo.Part1.Repository
   {
       using System;
       using System.Collections.Generic;
       using System.Linq;  
       using System.Threading.Tasks; 
   
       public interface IWeatherForecastRepository
       {
           [Sql("[dbo].[P_GetAllWeatherForecast]")]
           Task<List<WeatherForecast>> GetAllWeatherForecastAsync();
       }
   }

3. 从存储库获取WeatherForecast详情

       [HttpGet]
       [Route("GetWeatherForecast-SQL-Attribute-Async")]
       public async Task<List<WeatherForecast>> GetAllWeatherForecastAsync()
       {
           List<WeatherForecast> weatherForecasts = new List<WeatherForecast>();
           weatherForecasts = 
                  await this._weatherForecastRepository.GetAllWeatherForecastAsync();
   
           return weatherForecasts;
       }

4. Swagger中的结果

   

   

3. 在一行中传递SQL表值参数

我认为这是Insight.Database的一个很棒的功能，与其他ORM相比，它大大减轻了向存储过程传递TVP的痛苦。

1. 针对WeatherForecast表创建新的表值参数

   CREATE TYPE T_AddWeatherForecast AS TABLE (
       [WeatherForecastId] [int],
       [Date] [datetime] NULL,
       [TemperatureC] [int] NULL,
       [Summary] [nvarchar](max) NULL
   )

2. 创建带表值参数的新存储过程

   ALTER PROC P_AddWeatherForecasts 
   (@WeatherForecasts  [T_AddWeatherForecast] READONLY)
    AS
   
       INSERT INTO [dbo].[WeatherForecast]
                  ([Date]
                  ,[TemperatureC]
                  ,[Summary])
       SELECT [Date]
             ,[TemperatureC]
             ,[Summary]
         FROM @WeatherForecasts
   
   GO

3. 使用一个新方法更新IWeatherForecastRepository.cs，通过传递List<WeatherForecast>来Add多个WeatherForecast详情。

   namespace Insight.Database.Demo.Part1.Repository
   {
       using System;
       using System.Collections.Generic;
       using System.Linq;  
       using System.Threading.Tasks; 
   
       public interface IWeatherForecastRepository
       {
           [Sql("[dbo].[P_AddWeatherForecasts]")]
           Task AddWeatherForecastsAsync(List<WeatherForecast> WeatherForecasts);
       }
   }

   注意：存储过程TVP的参数和AddWeatherForecastsAsync方法的参数是相同的，都是 "WeatherForecasts"。ORM就是这样工作的 ;-)

4. 更新WeatherForecastController.cs

   [HttpPost]
   [Route("AddWeatherForecasts")]
   public async Task<IActionResult> AddWeatherForecastsAsync
                    ([FromBody] List<WeatherForecast> weatherForecasts)
       {
           await this._weatherForecastRepository.AddWeatherForecastsAsync
                 (weatherForecasts);
   
           return Ok();
       }

5. 将WeatherForecast数组Post到Controller

   

   

数据库中的新记录

4. 获取多个结果结构

这是Insight.Database的一个很棒的功能，我们可以用一行代码从存储过程或查询中获取两个或更多（select）结果。当然，在其他ORM或ADO.NET中也可以做到，但这里的代码会更简洁。

我曾遇到过一个场景，需要在UI中显示两个表，而这个包在一个数据库调用中解决了问题，而不是使用两个数据库调用。

1. 创建另一个表SummaryDim

   CREATE TABLE [dbo].[SummaryDim](
       [SummaryId] [int] IDENTITY(1000,1) NOT NULL,    
       [Summary] [nvarchar](max) NULL,
    CONSTRAINT [PK_dbo.SummaryId] PRIMARY KEY CLUSTERED 
   (
       [SummaryId] DESC
   )
   )

   

2. 创建一个新的存储过程，从[WeatherForecast]和[dbo].[SummaryDim]表中选择多个结果。如果需要，可以传递TVP（只是为了展示它的简单性）。

   CREATE PROC P_GetAddWeatherForecastsAndSummary
   (@WeatherForecasts  [T_AddWeatherForecast] READONLY)
    AS
   
       INSERT INTO [dbo].[WeatherForecast]
                  ([Date]
                  ,[TemperatureC]
                  ,[Summary])
       SELECT [Date]
             ,[TemperatureC]
             ,[Summary]
         FROM @WeatherForecasts
   
        SELECT  * FROM [dbo].[WeatherForecast]
        SELECT  * FROM [dbo].[SummaryDim]
   
   GO

3. 使用一个新方法更新IWeatherForecastRepository.cs以获取多个结果，通过传递List<WeatherForecast>来添加多个WeatherForecast详情。

   namespace Insight.Database.Demo.Part1.Repository
   {
       using System;
       using System.Collections.Generic;
       using System.Linq;
       using System.Threading.Tasks;
   
       public interface IWeatherForecastRepository
       {  
           [Sql("[dbo].[P_GetAddWeatherForecastsAndSummary]")]
           Task<Results<WeatherForecast, SummaryDim>> 
           GetAddWeatherForecastsAndSummaryAsync
              (List<WeatherForecast> WeatherForecasts_New);
       }
   }

4. 更新WeatherForecastController.cs

   [HttpPost]
   [Route("GetAddWeatherForecastsAndSummary")]
   public async Task<IActionResult> 
   GetAddWeatherForecastsAndSummaryAsync([FromBody] 
                List<WeatherForecast> weatherForecasts_new)
   {
       List<WeatherForecast> weatherForecasts = new List<WeatherForecast>();
       List<SummaryDim> summaries = new List<SummaryDim>();
   
       var result = await this._weatherForecastRepository.
                    GetAddWeatherForecastsAndSummaryAsync(weatherForecasts_new);
   
       weatherForecasts = result.Set1.ToList();
       summaries = result.Set2.ToList();
   
       dynamic returnVal = new System.Dynamic.ExpandoObject();
       returnVal.weatherForecasts = weatherForecasts;
       returnVal.summaries = summaries;
   
       return Ok(returnVal);
   }

5. Swagger中的结果

   

   {
     "weatherForecasts": [
       {
         "weatherForecastId": 112,
         "date": "2021-11-06T10:08:23.66",
         "temperatureC": 101,
         "temperatureF": 213,
         "summary": "string_101"
       },
       {
         "weatherForecastId": 111,
         "date": "2021-11-05T10:08:23.66",
         "temperatureC": 100,
         "temperatureF": 211,
         "summary": "string_100"
       },
       {
         "weatherForecastId": 110,
         "date": "2021-11-06T10:08:23.66",
         "temperatureC": 101,
         "temperatureF": 213,
         "summary": "string_101"
       }
     ],
     "summaries": [
       {
         "summaryId": 1007,
         "summary": "Hot"
       },
       {
         "summaryId": 1006,
         "summary": "Balmy"
       },
       {
         "summaryId": 1005,
         "summary": "Warm"
       }
     ]
   }

5. .NET Core 中 IWeatherForecastRepository 的依赖注入

startup.cs 中的代码简化

services.AddScoped(b => this._sqlConnection.AsParallel<IWeatherForecastRepository>());

全部代码

Startup.cs

    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
            this._sqlConnection = new SqlConnection("Data Source=.;
            Initial Catalog=Insight.Database.Demo;Persist Security Info=true;
            Integrated Security=true;");
        }

        public IConfiguration Configuration { get; }

        private readonly DbConnection _sqlConnection;

        // This method gets called by the runtime. 
        // Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            SqlInsightDbProvider.RegisterProvider();

            services.AddControllers();          

            services.AddScoped
            (b => this._sqlConnection.AsParallel<IWeatherForecastRepository>());
        }

将 IWeatherForecastRepository 注入到 WeatherForecastController.cs 中

    public class WeatherForecastController : ControllerBase
    {
        private readonly ILogger<WeatherForecastController> _logger;

        private readonly DbConnection _sqlConnection;

        private readonly IWeatherForecastRepository _weatherForecastRepository;

        public WeatherForecastController(ILogger<WeatherForecastController> logger, 
        IWeatherForecastRepository weatherForecastRepository)
        {
            this._weatherForecastRepository = weatherForecastRepository;

            _logger = logger;
        }

6. 使用MOQ对WeatherForecastController进行单元测试

    public class Tests
    {
        private WeatherForecastController _weatherForecastController { get; set; }

        private Mock<ILogger<WeatherForecastController>> _logger { get; set; }

        private Mock<IWeatherForecastRepository> _weatherForecastRepository { get; set; }

        [SetUp]
        public void Setup()
        {
            this._weatherForecastRepository = new Mock<IWeatherForecastRepository>();
            this._logger = new Mock<ILogger<WeatherForecastController>>();
        }

        [Test]
        public void WeatherForecastController_Get()
        {
            //Arrange
            List<WeatherForecast> weatherForecasts = 
            Builder<WeatherForecast>.CreateListOfSize(5).Build().ToList();

            this._weatherForecastRepository
             .Setup(m => m.GetAllWeatherForecast())
             .Returns(weatherForecasts);

            this._weatherForecastController = 
            new WeatherForecastController(this._logger.Object, 
                                          this._weatherForecastRepository.Object);

            //Act
            var result = this._weatherForecastController.Get();

            //Assert

            Assert.AreEqual(result, weatherForecasts);
        }

7. MOQ多个结果集

    [Test]
    public async Task GetAddWeatherForecastsAndSummaryAsync()
    {
        //Arrange
        List<WeatherForecast> weatherForecasts = 
        Builder<WeatherForecast>.CreateListOfSize(5).Build().ToList();

        List<SummaryDim> summaries = 
        Builder<SummaryDim>.CreateListOfSize(5).Build().ToList();

        var resultSet = new Results<WeatherForecast, SummaryDim>
                        (weatherForecasts, summaries);

        this._weatherForecastRepository
         .Setup(m => m.GetAddWeatherForecastsAndSummaryAsync(weatherForecasts))
         .ReturnsAsync(resultSet);

        this._weatherForecastController = 
        new WeatherForecastController(this._logger.Object, 
                                      this._weatherForecastRepository.Object);

        //Act
        var result = 
        await this._weatherForecastController.GetAddWeatherForecastsAndSummaryAsync
                (weatherForecasts);

        //Assert
        Assert.AreEqual(result.GetType(), typeof(OkObjectResult));
    }

项目结构

将继续...

历史

• 2021年11月6日：初始版本