In this article, we examine Select operations using the traditional Micro-ORM approach with Insight.Database.
Please check my previous articles
Let's continue with this tutorial now.
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.
/****** 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
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.
Join the conversation! Your thoughts help the community grow.