Since we are treating each database operation as a transaction and explicitly calling the Commit() function in user command or query in the Application project, there can be two ways to test the UserRepository:

1. Create the tests that use the actual database to perform database operation but DO NOT call Commit() function, in this case, your query will be verified against database schema, you will receive an error or success message based on your input data but data won’t be modified or added in the database. That’s a pretty safe and easy way to test the database without changing the data state in the database.
2. Create a clone of the original database and actually perform database operations to insert, update, or delete the data in the database. In this case, we will be doing end to end testing that would exactly match with actual way of using the application. Of course, we will implement the data purge functionality after running each cycle of integration testing but the only issue is to keep sync the integration tests database with the original one.

I am going to go with the second approach to create a clone of the original database and actually perform the database operations. Feel free to try the first approach.

Open the Microsoft SQL Server Management Studio, New Query and run the first query:

CREATE DATABASE TestFullstackHub

Once the database is created, run the second query:

USE [TestFullstackHub]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
	[UserID] [bigint] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](250) NOT NULL,
	[LastName] [varchar](250) NOT NULL,
	[DOB] [datetime] NOT NULL,
	[Gender] [char](1) NOT NULL,
	[EmailAddress] [varchar](300) NOT NULL,
	[PhoneNumber] [varchar](50) NULL,
	[City] [varchar](100) NOT NULL,
	[State] [varchar](100) NOT NULL,
	[Zip] [numeric](10, 0) NOT NULL,
	[Country] [varchar](250) NOT NULL,
	[DateAdded] [datetime] NOT NULL,
	[DateUpdated] [datetime] NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_DateUpdated] DEFAULT (NULL) FOR [DateUpdated]
GO
USE [master]
GO
ALTER DATABASE [FullstackHub] SET READ_WRITE
GO

In the tests folder, create a new project UserManagement.Persistence.IntegrationTests by selecting MSTest Test Project (.NET Core) template. Delete the default UnitTest1.cs file and install the following dependencies:

• Shouldly 3.0.2
• xunit 2.4.1
• xunit.runner.visualstudio 2.4.3
• System.Data.Common 4.3.0
• Add UserManagement.Domain and UserManagement.Persistence projects references.

We already have understood the concept and use of fixture class in the previous part .i.e. to provide to the fixed testing environment. The UserFixture for Persistence project would be quite simple, we will inject our testing database connection string to the UserRepository class. Create a new class UserFixture in the root of UserManagement.Persistence.IntegrationTests project and replace its content with the following:

namespace UserManagement.Persistance.IntegrationTests
{
    using System.Data;
    using System.Data.SqlClient;
    using UserManagement.Domain.Repositories;
    using UserManagement.Persistence.Repositories;
    using Xunit;
    public class UserFixture
    {
        public IUserRepository UserRepository { get; }

        public UserFixture()
        {
            IDbConnection dbConnection = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=TestFullstackHub");
            UserRepository = new UserRepository(dbConnection, null);
        }

        [CollectionDefinition("UserCollection")]
        public class QueryCollection : ICollectionFixture<UserFixture>
        {
        }
    }
}

Make sure to check your connection string before moving further, above one is specific to my local machine settings.

Create a new folder User and add a class UserRepositoryTest in it, replace its content with the following:

namespace UserManagement.Persistance.IntegrationTests.User
{
    using Shouldly;
    using System;
    using System.Linq;
    using System.Threading.Tasks;
    using UserManagement.Domain.Repositories;
    using Xunit;

    [Collection("UserCollection")]
    public class UserRepositoryTest : IDisposable
    {
        private readonly IUserRepository userRepository;
        public UserRepositoryTest(UserFixture fixture)
        {
            userRepository = fixture.UserRepository;
        }

        [Fact]
        public async Task TestAddUser_GivenCorrectParam_ReturnUserID()
        {
            var res = await AddNewUser();
            res.ShouldBeGreaterThan(0);
        }

        [Fact]
        public async Task TestGetAllUsers_GivenCorrectParam_ReturnUserList()
        {
            await AddNewUser();
            var res = await userRepository.GetAllUsers();
            res.Count().ShouldBeGreaterThan(0);
        }

        [Fact]
        public async Task TestGetUserByID_GivenCorrectParam_ReturnUserList()
        {
            var userId = await AddNewUser();
            var res = await userRepository.GetUser(userId);
            res.ShouldBeOfType<Domain.Entities.User>();
        }

        [Fact]
        public async Task TestUpdateUser_GivenCorrectParam_ReturnTrue()
        {
            var userId = AddNewUser().Result;
            var user = new Domain.Entities.User
            {
                FirstName = "John",
                LastName = "Doe",
                City = "Falls Chruch",
                Country = "USA",
                State = "VA",
                Zip = "22044",
                DateAdded = new DateTime(2019, 01, 01),
                DOB = new DateTime(1980, 01, 01),
                EmailAddress = "jdoe@fullstackhub.io",
                Gender = "F",
                PhoneNumber = "000-000-000",
                UserID = userId,
            };

            var res = await userRepository.UpdateUser(user);
            res.ShouldBeTrue();
        }

        [Fact]
        public async Task TestDeleteUser_GivenCorrectParam_ReturnTrue()
        {
            var userId = AddNewUser().Result;
            var res = await userRepository.DeleteUser(userId);
            res.ShouldBeTrue();
        }

        private async Task<int> AddNewUser()
        {
            var user = new Domain.Entities.User
            {
                FirstName = "John",
                LastName = "Doe",
                City = "Falls Chruch",
                Country = "USA",
                State = "VA",
                Zip = "22044",
                DateAdded = new DateTime(2019, 01, 01),
                DOB = new DateTime(1980, 01, 01),
                EmailAddress = "jdoe@fullstackhub.io",
                Gender = "M",
                PhoneNumber = "444-443-4444"
            };

            return await userRepository.AddUser(user);
        }

        public void Dispose()
        {
            userRepository.DeleteAllUser();
        }
    }
}

The tests are quite self-explanatory, we created a positive test case against each UserRepository function. In each test case, we are first creating a new user in a database and then performing the corresponding test. In the Dispose() method, we are deleting the records inserted into a database during tests to keep it clean each time test cases are run. For practice, please create a few negative test cases.

In next part, let’s create the Integration tests for UserManagement.API project.