Insight.Database - .NET Micro ORM - Part 2 - Executing And Carrying Out SELECT SQL Commands

• Download demo - 21.2 KB

Introduction

We'll examine select operations in SQL Server Database with Insight in this article utilizing a database using the traditional Micro-ORM technique.

Please check my previous articles:

• Part 1: Insight.Database - .NET Micro ORM, Part 1 - Write Less Code in Data Access layer using Auto Interface Implementation
• Part 2: Insight.Database - .NET Micro ORM, Part 2 - Executing And Carrying Out SELECT SQL Commands
• Part 3: Coming soon

Let's continue with this tutorial now.

• GitHub Project URL

Prerequisites

1. Install the SDK for.NET Core 5.0 or later.
2. Install SQL Server Management Studio with Visual Studio 2019.
3. Database Server 2008 R2 or Later
4. After creating a new database, run the SQL scripts provided below.

/****** 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

Added User Repository with Interface and Concrete with DI

IUserRepository

UserRepository with SQL Connection

Registering Repository Dependencies in StartUp

Resolve Repository Dependencies

Selecting Records

1. Execute Scalar

• Wiki Document Link

ExecuteScalar and ExecuteScalarSql are supported by Insight. These techniques yield the first column of the set's first row.

Execute Scalar is used for calling Stored Procedures which returns single records. Create an SP which returns a single record as given below.

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

To obtain an email address, add GetUserEmailAddress methods to the Repository Interface, Concrete, and 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 output to get Email Address by User Id:

Execute Scalar SQL is used for Select statement which returns single records.

Add GetUserEmailAddress_V1 methods to the Repository Interface, Concrete, and Controller to acquire a user's email address.

//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);
}

Here is the Swagger output for getting an email address from a User Id:

Execute Query

Wiki Document Link

A row or more rows of a table are returned using Query and QuerySql methods.

1. Query

Query is used for calling Stored Procedures which is a row or multiple rows. Create an SP as given below.

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

To get all users data, add GetAllUsers methods to the Repository Interface, Concrete, and 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 output to get All User Information:

2. QuerySQL

QuerySQL is used for direct SQL Select statements to get a row or multiple rows.

Add GetAllUsers_V1 methods to the Repository Interface, Concrete, and Controller to acquire all user details.

//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 Output: Get All User's Data

Added Unit Test for 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);
     }
 }

Test Results

Project structure of this article:

In the next article, we will discuss about implementing Insert, Update, Delete and other features.

History

• 10^th January, 2023: Initial version