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

keerthi raja

5.00/5 (5 votes)

11 Jan 2023CPOL2 min read

5.5K

Using Insight.Database to Run and Complete SELECT SQL Commands (.NET Micro ORM)

In this post, we'll look at Select operations with Insight.Database using the conventional Micro-ORM method.

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

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

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

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.

SQL

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.

C++

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

SQL

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.

C++

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)