整合測試
有賴於之前實作EF Core的備案，這一次也是在業界比較常見採取的Dapper做法。
很多老系統都是仰賴ADO.net 存取SQL方式來進行做系統開發，有些情況舊系統改寫的時候，可以搭配Dapper來進行做改寫。
這邊是記錄用Dapper方式來做整合測試的部分，以做一個解決的備案。
先設定
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<TargetFramework>net8.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
<InvariantGlobalization>false</InvariantGlobalization>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Dapper" Version="2.1.35" />
<PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.2" />
<PackageReference Include="Swashbuckle.AspNetCore" Version="6.4.0" />
<InternalsVisibleTo Include="CategoryAPIDemo" />
</ItemGroup>
</Project>
畢竟很多系統必須依靠ADO.net資料存取體系，也是延續上述的做法只是用Dapper來做資料存取。
安裝套件
- dapper
- Microsoft.Data.SqlClient
先建立ASP.net Web Core API，並在專案中建立Infrastructure資料夾、在Infrastructure底下建立Repository與Repository底下建立Interface資料夾，以及在Infrastructure底下建立Model資料夾，如下
建立Models底下建立Category.cs
namespace CategoryAPIDemo.Infrastructure.Models
{
public class Category
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
}建立ICategoryRepository.cs和實作CategoryRepository.cs
namespace CategoryAPIDemo.Infrastructure.Repository.Interface
{
public interface ICategoryRepository
{
Task<IEnumerable<Category>> GetCategoriesAsync();
Task<Category> GetCategoryByIdAsync(int id);
Task<int> AddCategoryAsync(Category category);
Task<int> UpdateCategoryAsync(Category category);
Task<int> DeleteCategoryAsync(int id);
}
}using System.Data;
using Dapper;
using CategoryAPIDemo.Infrastructure.Models;
using CategoryAPIDemo.Infrastructure.Repository.Interface;
namespace CategoryAPIDemo.Infrastructure.Repository
{
public class CategoryRepository : ICategoryRepository
{
private readonly IDbConnection _dbConnection;
public CategoryRepository(IDbConnection dbConnection)
{
_dbConnection = dbConnection;
}
public async Task<IEnumerable<Category>> GetCategoriesAsync()
{
return await _dbConnection.QueryAsync<Category>("SELECT * FROM Categories");
}
public async Task<Category> GetCategoryByIdAsync(int id)
{
return await _dbConnection.QuerySingleOrDefaultAsync<Category>("SELECT * FROM Categories WHERE Id = @Id", new { Id = id });
}
public async Task<int> AddCategoryAsync(Category category)
{
var sql = "INSERT INTO Categories (Name, Description) VALUES (@Name, @Description)";
return await _dbConnection.ExecuteAsync(sql, category);
}
public async Task<int> UpdateCategoryAsync(Category category)
{
var sql = "UPDATE Categories SET Name = @Name, Description = @Description WHERE Id = @Id";
return await _dbConnection.ExecuteAsync(sql, category);
}
public async Task<int> DeleteCategoryAsync(int id)
{
var sql = "DELETE FROM Categories WHERE Id = @Id";
return await _dbConnection.ExecuteAsync(sql, new { Id = id });
}
}
}
在appsettings.json
"ConnectionStrings": {
"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=categoryDB;Trusted_Connection=True;"
}實作API
using CategoryAPIDemo.Infrastructure.Models;
using CategoryAPIDemo.Infrastructure.Repository.Interface;
using Microsoft.AspNetCore.Mvc;
namespace CategoryAPIDemo.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class CategoryController : ControllerBase
{
private readonly ICategoryRepository _categoryRepository;
public CategoryController(ICategoryRepository categoryRepository)
{
_categoryRepository = categoryRepository;
}
[HttpGet]
public async Task<IEnumerable<Category>> GetCategories()
{
return await _categoryRepository.GetCategoriesAsync();
}
[HttpGet("{id}")]
public async Task<IActionResult> GetCategory(int id)
{
var category = await _categoryRepository.GetCategoryByIdAsync(id);
if (category == null)
return NotFound();
return Ok(category);
}
[HttpPost]
public async Task<IActionResult> AddCategory(Category category)
{
await _categoryRepository.AddCategoryAsync(category);
return CreatedAtAction(nameof(GetCategory), new { id = category.Id }, category);
}
[HttpPut("{id}")]
public async Task<IActionResult> UpdateCategory(int id, Category category)
{
if (id != category.Id)
return BadRequest();
await _categoryRepository.UpdateCategoryAsync(category);
return NoContent();
}
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteCategory(int id)
{
await _categoryRepository.DeleteCategoryAsync(id);
return NoContent();
}
}
}
並在Program.cs中加入
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
// Inject IDbConnection for Dapper
builder.Services.AddScoped<IDbConnection>(x =>
new SqlConnection(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddScoped<ICategoryRepository, CategoryRepository>();
var app = builder.Build();//Program.cs
public partial class Program { } // 為了 WebApplicationFactory 以便在測試專案當中能夠使用Program並建立XUnit專案，安裝所需套件
- Testcontainers.MsSql
- Microsoft.AspNetCore.Mvc.Testing
- FluentAssertions
開始定義SQL SERVER容器，並撰寫CategoryApiApplicationFactory.cs
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc.Testing;
using Microsoft.VisualStudio.TestPlatform.TestHost;
using System.Data;
using DotNet.Testcontainers.Builders;
using DotNet.Testcontainers.Containers;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Data.SqlClient;
using Dapper;
namespace CategoryAPIDemoTests
{
public class CategoryApiApplicationFactory : WebApplicationFactory<Program>, IAsyncLifetime
{
private const string Database = "master";
private const string Username = "sa";
private const string Password = "yourStrong(!)Password";
private const ushort MsSqlPort = 1433;
private readonly IContainer _mssqlContainer;
private string _connectionString;
public CategoryApiApplicationFactory()
{
_mssqlContainer = new ContainerBuilder()
.WithImage("mcr.microsoft.com/mssql/server:2019-latest")
.WithPortBinding(MsSqlPort, true)
.WithEnvironment("ACCEPT_EULA", "Y")
.WithEnvironment("MSSQL_SA_PASSWORD", Password)
.WithWaitStrategy(Wait.ForUnixContainer().UntilPortIsAvailable(MsSqlPort))
.Build();
}
protected override void ConfigureWebHost(IWebHostBuilder builder)
{
var host = _mssqlContainer.Hostname;
var port = _mssqlContainer.GetMappedPublicPort(MsSqlPort);
_connectionString = $"Server={host},{port};Database={Database};User Id={Username};Password={Password};TrustServerCertificate=True";
builder.ConfigureServices(services =>
{
services.AddScoped<IDbConnection>(sp => new SqlConnection(_connectionString));
});
EnsureDatabaseCreatedAsync();
}
public async Task InitializeAsync()
{
await _mssqlContainer.StartAsync();
}
public async Task DisposeAsync()
{
await _mssqlContainer.DisposeAsync();
}
private async Task EnsureDatabaseCreatedAsync()
{
using (var connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
// Read and execute SQL scripts
var sqlFilesDirectory = Path.Combine(Directory.GetCurrentDirectory(), "SqlScripts");
var sqlFiles = Directory.GetFiles(sqlFilesDirectory, "*.sql");
foreach (var file in sqlFiles)
{
var sqlContent = await File.ReadAllTextAsync(file);
await connection.ExecuteAsync(sqlContent);
}
}
}
}
}在測試專案建立SqlScripts/Category_Create.sql，主要未來在這邊會放置多個Create_Table來做為建構測試資料表用途，並將複製到目錄永遠複製。
CREATE TABLE[dbo].[Categories] (
[Id][int] IDENTITY(1, 1) NOT NULL,
[Name] [nvarchar] (max)NOT NULL,
[Description] [nvarchar] (max)NOT NULL,
CONSTRAINT[PK_Categories] PRIMARY KEY CLUSTERED
(
[Id] ASC
)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] TEXTIMAGE_ON[PRIMARY]
using System.Net.Http.Json;
using FluentAssertions;
namespace CategoryAPIDemoTests
{
public class CategoryApiTests : IClassFixture<CategoryApiApplicationFactory>
{
private HttpClient _client;
public CategoryApiTests(CategoryApiApplicationFactory factory)
{
_client = factory.CreateClient();
}
[Fact]
public async Task GetCategories_ShouldReturnSuccess()
{
var response = await _client.GetAsync("/api/Category");
response.EnsureSuccessStatusCode();
var content = await response.Content.ReadAsStringAsync();
content.Should().NotBeNullOrEmpty();
}
[Fact]
public async Task CreateCategory_ShouldAddNewCategory()
{
var newCategory = new
{
Name = "New Category",
Description = "This is a test category"
};
var response = await _client.PostAsJsonAsync("/api/Category", newCategory);
response.EnsureSuccessStatusCode();
var content = await response.Content.ReadAsStringAsync();
content.Should().Contain("New Category");
}
}
}
接者進行跑整合測試。
元哥的筆記