Insight.Database - .NET 微型 ORM - 第 2 部分 - 执行和执行 SELECT SQL 命令

• 下载演示 - 21.2 KB

引言

在本文中，我们将使用传统微型 ORM 技术，检查 Insight 在 SQL Server 数据库中执行 select 操作。

请查看我之前的文章

• 第 1 部分：Insight.Database - .NET 微型 ORM，第 1 部分 - 使用自动接口实现减少数据访问层代码
• 第 2 部分：Insight.Database - .NET 微型 ORM，第 2 部分 - 执行和执行 SELECT SQL 命令
• 第 3 部分：即将发布

现在我们继续本教程。

• GitHub 项目 URL

必备组件

1. 安装 .NET Core 5.0 或更高版本的 SDK。
2. 安装 SQL Server Management Studio 与 Visual Studio 2019。
3. Database Server 2008 R2 或更高版本
4. 创建新数据库后，运行以下 SQL 脚本。

/****** Object:  StoredProcedure [dbo].[P_GetUserEmailAddress]    
 Script Date: 1/8/2023 12:09:15 PM ******/
DROP PROCEDURE IF EXISTS [dbo].[P_GetUserEmailAddress]
GO
/****** Object:  StoredProcedure [dbo].[P_GetAllUsers]    
 Script Date: 1/8/2023 12:09:15 PM ******/
DROP PROCEDURE IF EXISTS [dbo].[P_GetAllUsers]
GO
/****** Object:  Table [dbo].[User]    Script Date: 1/8/2023 12:09:15 PM ******/
DROP TABLE IF EXISTS [dbo].[User]
GO
/****** Object:  Table [dbo].[User]    Script Date: 1/8/2023 12:09:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
    [UserId] [bigint] IDENTITY(100,1) NOT NULL,
    [FirstName] [nvarchar](300) NULL,
    [LastName] [nvarchar](300) NULL,
    [EmailAddress] [nvarchar](350) NULL,
    [Country] [nvarchar](350) NULL,
 CONSTRAINT [PK_dbo.User] PRIMARY KEY CLUSTERED
(
    [UserId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, _
       OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[User] ON
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], _
       [EmailAddress], [Country]) VALUES (109, N'Colleen', _
        N'Kessler', N'samara_corwin@nolanschuster.name', N'Marshall Islands')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], _
       [EmailAddress], [Country]) VALUES (108, N'Graciela', N'Keeling', _
        N'nakia_buckridge@goldner.biz', N'Cameroon')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], _
       [Country]) VALUES (107, N'Rosie', N'Mertz', _
        N'quinn_altenwerth@effertz.us', N'United States of America')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], _
       [Country]) VALUES (106, N'Amelia', N'Weimann', _
        N'braxton@sauerlittel.name', N'Saint Helena')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], _
       [Country]) VALUES (105, N'Rosalyn', N'Hammes', _
        N'magdalena.jones@hirthe.biz', N'India')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], _
       [Country]) VALUES (104, N'Reagan', N'Schneider', _
        N'earl@jones.us', N'Saint Helena')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], _
       [Country]) VALUES (103, N'Anderson', N'Balistreri', _
        N'ismael@considine.name', N'Svalbard & Jan Mayen Islands')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], _
       [Country]) VALUES (102, N'Maegan', N'Marks', _
        N'maurine.boehm@halvorson.ca', N'Moldova')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], _
       [Country]) VALUES (101, N'Alverta', N'Dibbert', _
        N'bud@streich.com', N'Saint Pierre and Miquelon')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], _
       [Country]) VALUES (100, N'Khalil', N'Fay', _
        N'boris_koch@bailey.info', N'Lithuania')
GO
SET IDENTITY_INSERT [dbo].[User] OFF
GO
/****** Object:  StoredProcedure [dbo].[P_GetAllUsers]    
 Script Date: 1/8/2023 12:09:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[P_GetAllUsers]
as
BEGIN
    SELECT *  FROM [dbo].[User]
END
GO
/****** Object:  StoredProcedure [dbo].[P_GetUserEmailAddress]    
 Script Date: 1/8/2023 12:09:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[P_GetUserEmailAddress]
                    @UserId                        BIGINT
as
BEGIN
    SELECT [EmailAddress]  FROM [dbo].[User]
    WHERE UserId = @UserId
END
GO

添加了带有接口和具体实现的 UserRepository 以及依赖注入

IUserRepository

带有 SQL 连接的 UserRepository

在 StartUp 中注册 Repository 依赖项

解析 Repository 依赖项

选择记录

1. 执行标量

• Wiki 文档链接

ExecuteScalar 和 ExecuteScalarSql 受 Insight 支持。这些技术产生集合第一行的第一列。

Execute Scalar 用于调用返回单个记录的存储过程。创建一个返回单个记录的 SP，如下所示。

CREATE PROC [dbo].[P_GetUserEmailAddress]
@UserId    BIGINT
as
BEGIN
    SELECT  * FROM [dbo].[User]
    WHERE UserId = @UserId
END

要获取电子邮件地址，请将 GetUserEmailAddress 方法添加到 Repository 接口、具体实现和 Controller。

//IUserRepository
public interface IUserRepository {
    Task < string > GetUserEmailAddressAsync(long userId);
}

//UserRepository
public async Task < string > GetUserEmailAddressAsync(long userId) {
    string emailAddress = await _sqlConnection.ExecuteScalarAsync < string > 
                          ("[dbo].[P_GetUserEmailAddress]", new {
        UserId = userId
    });
    return emailAddress;
}

//UserController
[HttpGet]
[Route("GetUserEmailAddress")]
public async Task < IActionResult > GetUserEmailAddressAsync(long userId) {
    string emailAddress = await this._userRepository.GetUserEmailAddressAsync(userId);
    return Ok(emailAddress);
}

Swagger 输出以按用户 ID 获取电子邮件地址

Execute Scalar SQL 用于返回单个记录的 Select 语句。

将 GetUserEmailAddress_V1 方法添加到 Repository 接口、具体实现和 Controller 以获取用户的电子邮件地址。

//IUserRepository
Task <string> GetUserEmailAddressAsync_V1(long userId);

//UserRepository
public async Task < string > GetUserEmailAddressAsync_V1(long userId) {
string emailAddress = await _sqlConnection.ExecuteScalarSqlAsync < string > 
       (@ "SELECT [EmailAddress]  FROM [dbo].[User]
    WHERE UserId = @UserId ",  new {UserId = userId});
    return emailAddress;
}

//UserController
HttpGet]
Route("GetUserEmailAddress_V1")]
public async Task < IActionResult > GetUserEmailAddressAsync_V1(long userId) {
    string emailAddress = 
    await this._userRepository.GetUserEmailAddressAsync_V1(userId);
    return Ok(emailAddress);
}

这是从用户 ID 获取电子邮件地址的 Swagger 输出

执行查询

Wiki 文档链接

使用 Query 和 QuerySql 方法返回表的一行或多行。

1. 查询

Query 用于调用返回一行或多行的存储过程。创建一个如下所示的 SP。

CREATE PROC [dbo].[P_GetAllUsers]
as
BEGIN
    SELECT *  FROM [dbo].[User]
END

要获取所有用户数据，请将 GetAllUsers 方法添加到 Repository 接口、具体实现和 Controller。

//IUserRepository
Task<List<User>> GetAllUsersAsync();

//UserRepository
public async Task<List<User>> GetAllUsersAsync() {
    var users = await _sqlConnection.QueryAsync < User > ("[dbo].[P_GetAllUsers]");
    return users.ToList();
}

//UserController
[HttpGet]
[Route("GetAllUsers")]
public async Task < IActionResult > GetAllUsersAsync() {
    var users = await this._userRepository.GetAllUsersAsync();
    return Ok(users);
}

Swagger 输出以获取所有用户信息

2. QuerySQL

QuerySQL 用于直接 SQL Select 语句以获取一行或多行。

将 GetAllUsers_V1 方法添加到 Repository 接口、具体实现和 Controller 以获取所有用户详细信息。

//IUserRepository
Task<List<User>> GetAllUsersAsync_V1();

//UserRepository
public async Task<List<User>> GetAllUsersAsync_V1() {
    var users = await _sqlConnection.QuerySqlAsync<User> 
                ("SELECT * FROM [dbo].[User]");
    return users.ToList();
}

//UserController
[HttpGet]
[Route("GetAllUsers_V1")]
public async Task < IActionResult > GetAllUsersAsync_V1() {
    var users = await this._userRepository.GetAllUsersAsync_V1();
    return Ok(users);
}

Swagger 输出：获取所有用户数据

添加了 UserController 的单元测试

   internal class UserControllerTests
    {
        private UserController _userController { get; set; }

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

        private Mock<IUserRepository> _iUserRepository { get; set; }

        private List<User> Users { get; set; } = new List<User>();

        [SetUp]
        public void Setup()
        {
            Users = Builder<User>
                    .CreateListOfSize(5)
                    .All()
                    .With(c => c.FirstName = Faker.Name.First())
                    .With(c => c.LastName = Faker.Name.Last())
                    .With(c => c.EmailAddress = Faker.Internet.Email())
                    .With(c => c.Country = Faker.Address.Country())
                    .Build()
                    .ToList();

            this._iUserRepository = new Mock<IUserRepository>(MockBehavior.Strict);
            this._logger = new Mock<ILogger<UserController>>();
        }

        [Test]
        public async Task GetUserEmailAddressAsync_Positive()
        {
            //Arrange     
            this._iUserRepository
             .Setup(m => m.GetUserEmailAddressAsync(It.IsAny<long>()))
             .ReturnsAsync(Users.FirstOrDefault().EmailAddress);

            this._userController = new UserController(this._logger.Object, 
                                   this._iUserRepository.Object);

            //Act
            var result = await this._userController.GetUserEmailAddressAsync(100);

            //Assert
            Assert.AreEqual(result.GetType(), typeof(OkObjectResult));
            var jsonResult = ((OkObjectResult)result).Value.ToString();
            Assert.AreEqual(Users.FirstOrDefault().EmailAddress, jsonResult);
        }
    }

测试结果

本文的项目结构

在下一篇文章中，我们将讨论实现 Insert、Update、Delete 和其他功能。

历史

• 2023 年 1 月 10 日：初始版本