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

In this article, we examine Select operations using the traditional Micro-ORM approach with Insight.Database.

Please check my previous articles

• Part 1: Insight.Database, .NET Micro ORM - Write Less Code In Data Access Layer Using Auto Interface Implementation
• Part 2: Insight.Database, .NET Micro ORM - Executing and carrying out SELECT SQL commands (Part 2)
• Part 3: Coming soon.

Let's continue with this tutorial now.

• Demo 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'[email protected]', N'Marshall Islands')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], [Country]) VALUES (108, N'Graciela', N'Keeling', N'[email protected]', N'Cameroon')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], [Country]) VALUES (107, N'Rosie', N'Mertz', N'[email protected]', N'United States of America')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], [Country]) VALUES (106, N'Amelia', N'Weimann', N'[email protected]', N'Saint Helena')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], [Country]) VALUES (105, N'Rosalyn', N'Hammes', N'[email protected]', N'India')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], [Country]) VALUES (104, N'Reagan', N'Schneider', N'[email protected]', N'Saint Helena')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], [Country]) VALUES (103, N'Anderson', N'Balistreri', N'[email protected]', N'Svalbard & Jan Mayen Islands')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], [Country]) VALUES (102, N'Maegan', N'Marks', N'[email protected]', N'Moldova')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], [Country]) VALUES (101, N'Alverta', N'Dibbert', N'[email protected]', N'Saint Pierre and Miquelon')
GO
INSERT [dbo].[User] ([UserId], [FirstName], [LastName], [EmailAddress], [Country]) VALUES (100, N'Khalil', N'Fay', N'[email protected]', 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.

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 on implementing Insert, Update, Delete, and other features.