CODEWITHSUNDEEP
t-sqlunit-testingresharper
Golo Roden
Golo Roden

Reputation: 150634

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

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:

Upvotes: 2

Views: 465

Answers (1)

Mikael Engver
Mikael Engver

Reputation: 4768

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

Upvotes: -1

Related Questions