How to write integration test for update function in repository services with dapper and nunit?

Question

I am working on a blazor server side project.

When I try to write integration tests for my repository pattern services I get the error: System.PlatformNotSupportedException : This platform does not support distributed transactions.. Trying to use a second connection and query whether the desired data was really saved triggers the error.

My test code:

namespace CVUnitTests.IntegrationTests.Services
{
    public class SkilServiceTest
    {
        [TestFixture]
        public class YourFixture
        {
            private TransactionScope _scope;
            private ISkillService _skillService;
            private IConfigurationRoot _config;

                [SetUp]
            public void SetUp()
            {
               _config = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();
               _skillService = new SkillService(_config);
               _scope = new TransactionScope();
            }

            [TearDown]
            public void TearDown()
            {
                _scope.Dispose();
            }


            [Test]
            public async Task UpdateSkill_NewSkillIsSaved()
            {
                var skillToUpdate = new Skill()
                {
                    Name = "TestSkill",
                    Type = SkillType.Hardskill,
                    Category = "TestCategory"
                };
                await _skillService.UpdateSkill(skillToUpdate);
                using IDbConnection connection = new SqlConnection(DatabaseUtils.GetConnectionString(_config));
                var sql = "SELECT * FROM skill WHERE Name = TestSkill";
                var queriedSkill = connection.QuerySingle<Skill>(sql);
                Assert.AreEqual(queriedSkill, skillToUpdate);
            }
        }
    }
}

I found that Query could be used to achieve this though I can't wrap both my queries not into one using because the service is opening up its own connection:

public Task<bool> UpdateSkill(Skill skill)
        {
            if (GetSkill(skill.Id) != null)
            {
                using IDbConnection dbConnection = new SqlConnection(_connectionString);
                var parameters = new
                {
                    Id = skill.Id,
                    skill.Name,
                    Skilltype = skill.Type == SkillType.Hardskill ? "TRUE" : "FALSE"
                };
                var sql = "update skill SET Name = @Name, Skilltype = @Skilltype where id = @Id";
                dbConnection.Open();
                var result = dbConnection.ExecuteAsync(sql, parameters).Result;
                dbConnection.Close();
                return Task.FromResult(true);
            }
            else
            {
                using IDbConnection dbConnection = new SqlConnection(_connectionString);
                var parameters = new {skill.Name, Skilltype = skill.Type == SkillType.Hardskill ? 1 : 0};
                var sql = "insert into skill (Name, Skilltype) values (@Name, @Skilltype)";
                dbConnection.Open();
                var result = dbConnection.ExecuteAsync(sql, parameters).Result;
                dbConnection.Close();
                return Task.FromResult(false);
            }

I get the error This platform does not support distributed transactions. Even when I run just one query within transaction scope.

I also tried reading out the dbConnection from my service instead of creating a new one, but didn't work out as well.

I actually could use multiple in my service function and return the skill afterwards though it seems kind of putting test code into my methods.

Is there a way I can still achieve my desired test behavior or would I have to change my test logic completely?

Answer 1

Finally got it running using the following code:

{
    public class SkilServiceTest
    {
        [TestFixture]
        public class YourFixture
        {
            private TransactionScope _scope;
            private ISkillService _skillService;
            private IConfigurationRoot _config;
            private DatabaseUtils _databaseUtils;
                
            [SetUp]
            public void SetUp()
            {
               _config = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();
               _skillService = new SkillService(_config);
               
               _databaseUtils = new DatabaseUtils(_config);
               //wipe data and create empty table to execute tests on empty tables 
               _databaseUtils.DropTable("Skill");
               _databaseUtils.CreateSkillTable();
            }

            [TearDown]
            public void TearDown()
            {
                //wipe data so database manipulation within tests has no effect afterwards and does not interfere later tests
                _databaseUtils.DropTable("Skill");
            }

            
            

            [Test]
            public async Task UpdateSkill_NewSkillIsSaved()
            {
                Skill skillToUpdate = new Skill()
                {
                    Name = "TestSkill",
                    Type = SkillType.Softskill,
                    Category = "SomeCategory",
                    Id = 1
                };

                await _skillService.UpdateSkill(skillToUpdate);
                string sql_query = "SELECT * FROM skill WHERE name = \'TestSkill\'";
                Skill queriedSkill = null;
                using (IDbConnection connection = new SqlConnection(DatabaseUtils.GetConnectionString(_config)))
                {
                    connection.Open();
                    queriedSkill = connection.QuerySingle<Skill>(sql_query); 
                }
                
                Assert.IsTrue(queriedSkill.Equals(skillToUpdate));
            }
        }
    }

had to change Update as well:

 public async Task<bool> UpdateSkillAsync(Skill skill)
        {
                        
            var parameters = new {Id = skill.Id};
            var sql_query = "SELECT * FROM skill WHERE id = @Id";
            var queriedSkill = DbConnection_second.QuerySingleOrDefault<Skill>(sql_query, parameters);

            if (queriedSkill != null)
            {
                var sql = "update skill SET Name = @Name, Type = @Type, Category = @Category where id = @Id";
                var result = await DbConnection.ExecuteAsync(sql, skill);
                return true;
            }
            else
            {
                var sql = "insert into skill (Name, Type, Category) values (@Name, @Type, @Category)";
                var result = await DbConnection.ExecuteAsync(sql, skill);
                return false;
            } 
        }