How to unit test T-SQL code using Resharper test runner?

Question

Basically, the title says it all: I am using the Resharper test runner (my tests are written using NUnit), and now I need to test some T-SQL code.

How do I do that?

Any help (links to tutorials & co.) would be appreciated.

What I do not know is:

Where do I put the tests? C#? T-SQL as well? ...?
How do I setup the test runner to run these tests?
Is this even possible with Resharper?

When you say "T-SQL", do you mean select statements, stored procedures or UDFs? Or all of them? — Daniel Hilgarth
All of them, mainly classical CRUD statements, but also stored procedures. — Golo Roden
I am not really sure what you are asking here. What prevents you from writing your tests in C# using NUnit and ADO.NET that makes use of those statements? On the other hand, why not use tSQLt? — Daniel Hilgarth
If I do it using C# and NUnit, it's not a unit test any more, but an integration test (I test C# code to access the DB and the stored procedure). tSQLt looks nice, but is an extra tool. I hoped that you could get it included in what is already there. — Golo Roden
I disagree that it is an integration test. You don't test the production code that access the database. Instead you are using ADO.NET directly inside your NUnit test just as a technical means to be able to test the statements. You don't test the integration of your app with the database. Having said that, I suggest you don't go this route. Use a database unit testing framework like tSQLt for unit testing database code. — Daniel Hilgarth

Mikael Engver Mikael Engver · Accepted Answer · 2013-03-10T19:39:37

I have written some unit tests for T-SQL that tests for syntactical errors in the Sql code. There tests are executed from C# in NUnit or Microsofts Unit testing Framework and run with the preceding statement:

SET FMTONLY ON;

Microsoft reference of this statement.

After you only get back metadata so nothing is really executed after this, so you can even test queries that would usually modify data.

For example, see this NUnit-test below that will be succesful if the Procedure exists and has no syntactical error in it self or in any of the stored procedures dependencies.

[Test]
public void GivenStoredProcedure_WhenParsedBySqlServer_ThenItsResultShouldBeZero()
{
    const string sqlText = "SET FMTONLY ON; EXEC dbo.MyStoreProc;";

    var sqlConnection = new SqlConnection(connectionString);
    var sqlCommand = new SqlCommand(sqlText, sqlConnection);
    sqlCommand.CommandType = CommandType.Text;

    try
    {
        sqlConnection.Open();
        var result = sqlCommand.ExecuteNonQuery();              
        Assert.That(result, Is.EqualTo(0));
    }
    finally
    {
        if (sqlConnection.State == ConnectionState.Open)
        {
            sqlConnection.Close();
        }
        sqlConnection.Dispose();
    }      
}

How to unit test T-SQL code using Resharper test runner?

1 Answers