CODEWITHSUNDEEP
c#sql-server
mngeek206
mngeek206

Reputation: 5157

Testing SQL methods that don't use transactions?

Suppose I have a database method that looks like this:

public void insertRow(SqlConnection c)
{
    using (var cmd = new SqlCommand("insert into myTable values(@dt)",c))
    {
        cmd.Parameters.Add(new SqlParameter("@dt",DbType.DateTime)).Value = DateTime.Now;
        cmd.ExecuteNonQuery();
    }
}

Now suppose I want to test this method. So, I write a test case that attempts to wrap this method inside a transaction, so that I can rollback the change after testing the result of the insertion:

public void testInsertRow()
{
    SqlConnection c = new SqlConnection("connection.string.here");
    SqlTransaction trans = c.BeginTransaction();
    insertRow();
    // do something here to evaluate what happened, e.g. query the DB
    trans.Rollback();
}

This however fails to work, because:

ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

Is there a way to accomplish this, without having to rewrite every single database method to accept a transaction, and then rewrite every single call to pass null into the method for the transaction?

For example, this would work:

public void insertRow(SqlConnection c, SqlTransaction t)
{
    using (var cmd = new SqlCommand("insert into myTable values(@dt)",c))
    {
        if (t != null) cmd.Transaction = t;
        cmd.Parameters.Add(new SqlParameter("@dt",DbType.DateTime)).Value = DateTime.Now;
        cmd.ExecuteNonQuery();
    }
    c.Close();
}

But then, I have to either rewrite each and every call to the database method to include that null parameter, or write override signatures for each and every database method that automatically pass in a null, e.g. 

public void insertRow(SqlConnection c) { insertRow(c, null); }

What's the best way to allow transaction-based testing of database calls?

Upvotes: 0

Views: 220

Answers (1)

Scott Chamberlain
Scott Chamberlain

Reputation: 127543

You can use a TransactionScope to add the connections automatically in to a transaction

public void testInsertRow()
{
    using(TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        SqlConnection c = new SqlConnection("connection.string.here");
        insertRow(c);
        // do something here to evaluate what happened, e.g. query the DB

        //do not call scope.Complete() so we get a rollback.
    }   
}

Now this will cause tests to block each other if you have multiple parallel tests running. If you database is set up to support it you could do Snapshot isolation so updates from concurrent tests won't lock each other out.

public void testInsertRow()
{
    using(TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, 
                                                        new TransactionOptions(IsolationLevel = IsolationLevel.Snapshot))
    {
        SqlConnection c = new SqlConnection("connection.string.here");
        insertRow(c);
        // do something here to evaluate what happened, e.g. query the DB

        //do not call scope.Complete() so we get a rollback.
    }   
}

Upvotes: 2

Related Questions