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

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.

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

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:

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

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.