Unit testing with manual transactions and layered transactions

Due to a few restrictions I can’t use entity Framework and thus Need to do SQL Connections, commands and Transactions manually.

While doing unit tests for the methods calling These data layer operations I stumbled upon a few Problems.

For the unit tests I NEED to do them in a Transaction as most of the operations are changing data by their nature and thus doing them outside a Transaction is problematic as that would Change the whole base data. Thus I Need to put a Transaction around These (with no commit fired at the end).

Now I have 2 different variants of how These BL methods work.
A few have Transactions themselves inside of them while others have no Transactions at all. Both of These variants cause Problems.

  • Layered Transaction: Here I get Errors that the DTC cancelled the distributed Transaction due to timeouts (although the timeouts being set to 15 minutes and it running only for 2 minutes).

  • Only 1 Transaction: Here I get an error about the state of the Transaction when I come to the “new SQLCommand” line in the called method.

My question here is what can I do to correct this and get unit testing with Manual normal and layered Transactions working?

Unit testing method example:

        using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.ConnectionString))
        {
            connection.Open();
            using (SqlTransaction transaction = connection.BeginTransaction())
            {
                MyBLMethod();
            }
        }

Example for a Transaction using method (very simplified)

        using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.ConnectionString))
        {
            connection.Open();
            using (SqlTransaction transaction = connection.BeginTransaction())
            {
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandTimeout = 900;   // Wait 15 minutes before a timeout
                command.CommandText = "INSERT ......";
                command.ExecuteNonQuery();

                // Following commands
                ....

                Transaction.Commit();
            }
        }

Example for a non Transaction using method

        using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.ConnectionString))
        {
            connection.Open();

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandTimeout = 900;   // Wait 15 minutes before a timeout
            command.CommandText = "INSERT ......";
            command.ExecuteNonQuery();
        }


Source: sql

Leave a Reply