Unit Test for a sql query c#

When writing unit tests, you should not try to test querying the a real database. Unit testing is about testing individual units, this mean you should mock out external dependencies from the unit you are testing.

What you probably are trying to do is integration testing.

It doesn't generally make much sense to unit test database calls. A unit test is supposed to test an isolated unit and not a remote database that you integrate with.

You can however connect to a database in a unit test just like you would connect to a database in an executable application or a data access layer. There are numerous examples of how to do this if you Google or Bing for it. You could for example use a SqlDataReader: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx.

This is a well known problem. You should use a transaction scope. I do not know if you want to use sql directly or just use entity framework but the idea is that you create a transaction scope in the beginning of your test, add some test data to the database, call the method with the select query and then don't commit or complete the transaction (scope) to ensure your test data is roll backed.

Example

[TestMethod()] 
public void GetUsersTest() 
{ 
    string connectionString = GetConnectionString();     

    using (TransactionScope ts = new TransactionScope()) 
    { 
        using (SqlConnection connection = 
            new SqlConnection(connectionString)) 
        { 
            connection.Open(); 
            DataLayer dataAccessLayer = new DataLayer();     

            DataSet dataSet = dataAccessLayer.GetUsers(); 
            AddNewUser("Fred", connection);     

            dataSet = dataAccessLayer.GetUsers(); 
            DataRow[] dr = dataSet.Tables[0].Select("[UserName] = 'Fred'"); 
            Assert.AreEqual(1, dr.Length); 
        } 
    } 
}

If you are using EntityFrameworkCore with C#, one way to unit test your methods that writes/reads to a db is using InMemory option. The InMemory provider is useful when you want to test components using something that approximates connecting to the real database, without the overhead of actual database operations.This option wont simulate real database connection but provides a simple way to persist data during unit testing. Heres a link : https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/in-memory

Btw, if you are trying to test whether your call will fail on a new environment, I think you should be doing an Integration Test.

TL;DR – check out DbSample on GitHub, a sample EF Core based project with fully automated tests against MS SQL Server and a GitHub Actions pipeline.

A detailed analysis of orchestrating tests is covered in "Pain & Gain of automated tests against SQL (MS SQL, PostgreSQL)" article. The key steps are:

1. Launch SQL engine in Docker (use official images for SQL Server or PostgreSQL) and create a test database, like

docker run --name sql-server -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Secret_Passw0rd" -e "MSSQL_PID=Express" -p 1433:1433 -d mcr.microsoft.com/mssql/server 
# Creating a new DB
docker exec -i sql-server /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Secret_Passw0rd -d master -Q "CREATE DATABASE TestDb"

2. Populate the schema, logic, lookup dictionaries, etc (by running an SQL script prepared earlier):

# Copy the SQL script
docker cp CreateOrMigrateDatabase.sql sql-server:/home/script.sql
# Executing the script
docker exec -i sql-server /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Secret_Passw0rd -d TestDb -i /home/script.sql

3. Connect xUnit/NUnit tests to the database and for each test (rinse and repeat):
   1. Seed test data
   2. Perform the testable activity and checks
   3. Revert the database to the pristine state (via Respawn):

Checkpoint checkPoint = new();
await checkPoint.Reset(_sqlConnection);

4. Tear down the SQL engine along with the database and other artefacts.

Don't cry "It's not a unit test!"

The OP's request is legit for asserting a certain behaviour (e.g. applying BDD), where SQL queries/commands is essential part, isolating which might hurt reliability of the project. And also using in-memory DB providers would mask potential issues and discouraged by Microsoft.

Why so complicated?

Testing against a real SQL engine has to work:

• locally on dev machines (that run Windows, MacOS or Linux);
• build pipelines in the cloud (e.g. Azure DevOps pipelines, GitHub Actions, etc.).

So Docker indeed