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?

Instead of `TransactionScope` use `using(connection.BeginTransaction())` don't forget all DB objects should be in `using` blocks, not cached in fields — Charlieface, Jun 07 '21 at 20:27
I tried to add `connection.BeginTransaction()` in updating the answer though I still get the same error. Do I have to use it in method as well somehow? — OuttaSpaceTime, Jun 08 '21 at 07:22
You can't just change a method to fix this. `UpdateSkill` uses its own connection. The only way to get it under the control of an *external* transaction is to use a distributed transaction. You have to rethink the entire `Repository` class. Including why you added it in the first place. One of the *greatest* advances in scaleability are the disconnected operations supported by ADO.NET and ORMs like Entity Framework. All changes are cached and nothing is persisted until the code explicitly asks for it. If you want to roll back, just don't save the changes. — Panagiotis Kanavos, Jun 08 '21 at 08:02
If you want your repository to use transactions over multiple methods, you need to use a *single* connection and a single transaction. The class should implement `IDisposable` and close the connections and transactions when disposed. It should also be short-lived, to avoid blocking other classes. — Panagiotis Kanavos, Jun 08 '21 at 08:04
Okay I mainly wanted to use this for testing purpose in my application I haven't seen any purpose for using this so far since I created the services for this in the first place. Would you suggest to refactor the service for cleaner testing or might there be a different easier way to test the same logic? — OuttaSpaceTime, Jun 08 '21 at 08:07
To get any performance benefit from using Dapper though, methods like `GetSkill` should be avoided. The whole point of using Dapper is to *avoid* reading an object just to update it, and yet that's what `GetSkill` does. Worse, you'll have to use a transaction to ensure the results are consistent. You *can* write a single "UPSERT" SQL batch though, either by using the `MERGE` command or `UPDATE` and `INSERT` with `WHERE` clauses that only allow one of them to complete. In both cases you'll have to use a transaction in the query (MERGE isn't atomic) BUT that transaction would be short-lived. — Panagiotis Kanavos, Jun 08 '21 at 08:07
@FelixOuttaSpace `repository` is used to abstract storage, not make testing easier. That's a benefit though. ORMs like EF Core offer the same benefits and more. So does plain-old ADO.NET. A DataTable knows nothing about the database it will be saved to. Once you start using transactions though, you *have* to take connection and transaction lifetimes into account. — Panagiotis Kanavos, Jun 08 '21 at 08:09

OuttaSpaceTime · Accepted Answer · 2021-06-12T09:14:14.227

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;
            } 
        }

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

1 Answers1