I Know there's other Posts that have similar issues but i couldn't find a concrete answer for mine so here i am :D. I'm creating unit tests for an Dapper API ive been building. The API is built in c# and connects to a Postgres DB in production. The API uses DI and the IDbConnectionFactory to provide the connection string to the controller. To test the API ive created an MStest project to test the controllers. I have been trying to attempt to create an sqlite in memory DB to simulate the actual Db as to not rely on an external dependency ie my Postgres Db.
I've been successfully able to create the Db and populate it with data and ive validated that it has sample data in there. but when trying to pass this sqlite connection string to my controller it says table cant be found..... please help :D
//my controller
using CrudApi.Models;
using CrudApi.DbConnectionFactories;
using Dapper;
using Microsoft.AspNetCore.Mvc;
using Npgsql;
using System.Data.Common;
namespace CrudApi.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class HolidayController : ControllerBase
{
// Api Boiler Plate
private readonly IConfiguration config;
private readonly DbConnection _connection;
public HolidayController(IConfiguration config, IDbConnectionFactory connectionFactory)
{
this.config = config;
_connection = connectionFactory.CreateConnection();
}
// Gets all the Holiday in the DB
[HttpGet]
public async Task<ActionResult<List<Holiday>>> GetAllHoliday()
{
IEnumerable<Holiday> holiday = await SelectAllHoliday(_connection);
return Ok(holiday);
}
private static async Task<IEnumerable<Holiday>> SelectAllHoliday(DbConnection connection)
{
return await connection.QueryAsync<Holiday>("select * from tbl03_holiday;");
}
}
}
//test for controller
using CrudApi.Controllers;
using CrudApi.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Configuration;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using System.Data.Common;
using System;
namespace CrudApi.Tests
{
[TestClass]
public class HolidayControllerTests
{
private static IConfiguration _config;
private static DbConnection _connection; // Change the type to DbConnection
private static HolidayController _controller;
[ClassInitialize]
public static void ClassInitialize(TestContext context)
{
var configBuilder = new ConfigurationBuilder();
_config = configBuilder.Build();
// Create an in-memory SQLite database connection factory
var connectionFactory = new SqliteConnectionFactory("DataSource=:memory:;Cache=Shared");
_connection = connectionFactory.CreateConnection();
_connection.Open(); // Keep the connection open for the duration of the tests
// Create the controller
_controller = new HolidayController(_config, connectionFactory);
// Initialize the database schema and populate with test data
InitializeDatabase(_connection);
}
[ClassCleanup]
public static void ClassCleanup()
{
// Cleanup resources
if (_connection != null)
{
_connection.Close();
}
}
private static void InitializeDatabase(DbConnection connection)
{
if (connection.State == ConnectionState.Open)
{
Console.WriteLine("connection is open");
}
else
{
Console.WriteLine("connection is closed");
}
connection.Open(); // Open the connection
using (var command = connection.CreateCommand())
{
command.CommandText = @"
CREATE TABLE tbl03_holiday (
holiday_id INTEGER PRIMARY KEY AUTOINCREMENT,
uid TEXT NOT NULL,
from_date TEXT NOT NULL,
to_date TEXT NOT NULL,
holiday_type TEXT NOT NULL
);
INSERT INTO tbl03_holiday (holiday_id, uid, from_date, to_date, holiday_type) VALUES (1, 'test1', '0001-01-01', '00001-01-02', 'Holiday 1');
INSERT INTO tbl03_holiday (holiday_id, uid, from_date, to_date, holiday_type) VALUES (2, 'test2', '0001-01-01', '00001-01-02', 'Holiday 2');
";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
Console.WriteLine("Table created");
}
}
private static void DisplayTableData(string tableName, DbConnection connection)
{
using (var command = connection.CreateCommand())
{
command.CommandText = $"SELECT * FROM {tableName};";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write($"{reader.GetName(i)}: {reader.GetValue(i)}\t");
}
Console.WriteLine();
}
}
}
}
[TestMethod]
public void TestDisplayTableData()
{
DisplayTableData("tbl03_holiday", _connection);
}
[TestMethod]
public async Task GetAllHoliday_Returns_All_Holidays()
{
// Arrange
if (_connection.State == ConnectionState.Open)
{
Console.WriteLine("Connection is open");
}
else
{
Console.WriteLine("Connection is closed");
}
//Print the connection string
var connectionString = _connection.ConnectionString;
Console.WriteLine($"Test method called with connection string: {connectionString}");
// Act
var result = await _controller.GetAllHoliday();
// Assert
Assert.IsNotNull(result);
Assert.IsInstanceOfType(result.Result, typeof(OkObjectResult));
var okResult = result.Result as OkObjectResult;
Assert.IsInstanceOfType(okResult.Value, typeof(List<Holiday>));
var holidays = okResult.Value as List<Holiday>;
Assert.AreEqual(2, holidays.Count);
}
}
}
My current output:
Passed TestDisplayTableData [3 ms]
Standard Output Messages:
connection is open
Table created
holiday_id: 1 uid: test1 from_date: 0001-01-01 to_date: 00001-01-02 holiday_type: Holiday 1
holiday_id: 2 uid: test2 from_date: 0001-01-01 to_date: 00001-01-02 holiday_type: Holiday 2
Failed GetAllHoliday_Returns_All_Holidays [122 ms]
Error Message:
Test method CrudApi.Tests.HolidayControllerTests.GetAllHoliday_Returns_All_Holidays threw exception:
Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'no such table: tbl03_holiday'.
Stack Trace:
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
at Microsoft.Data.Sqlite.SqliteCommand.GetStatements()+MoveNext()
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 434
at CrudApi.Controllers.HolidayController.SelectAllHoliday(DbConnection connection) in
Standard Output Messages:
Connection is open
Test method called with connection string: DataSource=:memory:;Cache=Shared
If i havent provided enough info please feel free to say
As per Microsoft:
The linked page suggests a different connection string format for persistent in-memory databases: