How to Unit Test Sql Queries without affecting or manipulating real Database with c#

Question

I'm using moq framework for unit test my C# code. its working good with ef core and its objects, but I also have some sql queries in my code that I have to include in Unit Testing process. below is my method which contains some sql scripts, I want to run this script in unit testing but without affecting on real database.

public void DeleteContentData(string connectionString)
{
   using IDbConnection db = new SqlConnection(connectionString);
   DynamicParameters dynParameters1 = new DynamicParameters();
   dynParameters1.Add("@contentId", _content.content_id);
   db.Execute("DELETE FROM [content] WHERE content_id=@contentId", dynParameters1);
   db.Execute("INSERT INTO [content] (content_id) VALUES (@contentId);", dynParameters1);
}

How can I execute this method and run sql scripts without delete records and insert new records in unit testing.

Depends a little on what exactly you’re testing, but you would usually mock the `IDbConnection`, and for this you would typically have a `DbConnectionFactory` that actually creates the connection, rather than create it explicitly in your code as you have at the moment, and then mock (or fake) that to return your mock. The connection factory would be injected into the object containing your method. That would allow you to replace the script behaviour with e.g. null behaviour. If you actually want the scripts to have an effect, then you’d be outside the scope of "unit" testing. — sellotape, Jul 20 '23 at 06:09

SomeBody · Answer 1 · 2023-07-20T06:01:02.373

0

Instead of passing a connection string, you have to pass the dbconnection:

public void DeleteContentData(IDbConnection db)
{
   DynamicParameters dynParameters1 = new DynamicParameters();
   dynParameters1.Add("@contentId", _content.content_id);
   db.Execute("DELETE FROM [content] WHERE content_id=@contentId", dynParameters1);
   db.Execute("INSERT INTO [content] (content_id) VALUES (@contentId);", dynParameters1);
}

You can keep an overloaded version of the method where you have the connection string as a parameter. That way, you don't have to change all the calls of your method:

public void DeleteContentData(string connectionString)
{
   using IDbConnection db = new SqlConnection(connectionString);
   DeleteContentData(db);
}

For your unit test, you create a mock of your IDbConenction. You call your method with this mocked instance and in the end of the test, you check whether the Execute methods where called correctly. I don't know your class under test and the DynamicParameters class, hence I can only guess how the test might look. But maybe this way:

[TestMethod]
public void TestDeleteContentData()
{
  // arrange
  var db = new Mock<IDbConenction>();
  var classUnderTest = new ClassUnderTest();
  classUnderTest._content.content_id = 5;

  // act
  classUnderTest.DeleteContentData(db.Object);

  // assert
  db.Verify(x => x.Execute("DELETE FROM [content] WHERE content_id=@contentId", It.Is<DynamicParameters>(y => y.Contains("@contentId",5)));
}

edited Jul 20 '23 at 06:01

answered Jul 20 '23 at 05:46

SomeBody

7,515
2
17
33

Ohk, that's not a big deal for parameters, but my main concern is about mocking the DbConnection, so my queries won't affect on DB. Even when I'm creating a new instance with 'using IDbConnection db = new SqlConnection(connectionString)' it is creating a new connection and manipulating data in DB. – Aman Tiwari Jul 20 '23 at 05:50
... i **a** DB. Not neccessarily **the** DB ;) – Fildor Jul 20 '23 at 05:57
@AmanTiwari I added an example how the test might look, I hope it becomes clearer. This test won't manipulate the database. – SomeBody Jul 20 '23 at 06:01
@SomeBody Thanks for your consideration, its really appreciated. Looks like your code can do something, but it encountered a small issue, can you look into it? 'DynamicParameters' does not contain a definition for 'Contains' and the best extension method overload "Memory Extensions.Contains (ReadOnlySpan>, content_id=@contentId", It.Is(y => y.Co ReadOnlySpan, String Comparison)' requires a receiver of type 'System.ReadOnlySpan' – Aman Tiwari Jul 20 '23 at 06:27
@AmanTiwari As I wrote, I don't know how the `DynamicParameters` class works. You have to adapt the code that you can test that the query is acutally called with the correct value for content id. If you can't to that, you are free to also use `It.IsAny()`. – SomeBody Jul 20 '23 at 06:29
@SomeBody I updated code as 'db.Verify(x => x.Execute("DELETE FROM [content] WHERE content_id=5", It.IsAny(), null, null, null), Times.Once);' no compile time error. but its not working as expected. I tried by modifying with different approaches but giving this error on run time 'Unsupported expression: conn => conn.Execute(It.IsAny(), It.IsAny – Aman Tiwari Jul 20 '23 at 06:44
@AmanTiwari To solve this problem, you might check: https://stackoverflow.com/questions/2295960/mocking-extension-methods-with-moq – SomeBody Jul 20 '23 at 06:46

Guru Stron · Answer 2 · 2023-07-20T12:43:48.340

I would say it up for debate if there is a point of testing such code without actually hitting database and if they can be called unit tests if they actually hit one.

Still if you want to follow the approach without hitting the database then you will need to refactor the code so IDbConnection can be injected (either via ctor or method parameter) and pass a mocked instance with corresponding methods mocked.

If you want to verify that the code actually works there are two main options:

Switch from unit tests to integration ones and hit actual database and validate that what is needed is deleted
Use test containers - spin up a throwaway db instance in container and do with it whatever you want.

score 0 · Accepted Answer · answered Jul 31 '23 at 10:37

0

I found the Best suitable solution for me to Unit Test SQL Queries in this article: https://www.codeproject.com/Articles/5332010/Unit-Test-Your-Database-Classes.

This article also contains demo project for reference and also has a video on YouTube: https://www.youtube.com/watch?v=ta4ZVLPra5E

You can follow this article if trying to figure out a good way to Unit Test your Sql Queries. Let me know if someone facing issues, I can help.

answered Jul 31 '23 at 10:37

Aman Tiwari

41
4

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 03 '23 at 13:07

How to Unit Test Sql Queries without affecting or manipulating real Database with c#

3 Answers3