How to write a Test for c# Delegate to a StoredProc?

How to write tests for delegate methods? or Beware of 2 open connections both with 'hooks' onto the same SQL table .... .

This was not straight forward to diagnose, test and prove is not a problem with my current solution.

How could I have TDD'd or written unit/integration tests to have trapped this? Redesign suggestions ...

1. Create a connection to the table 'TransferWebTransmit' to process all rows.


2. Execute a Reader to loop through 'old' records, (ID=1)


3. Call a delegate method to process the 'old' record. (NB keep current connection open until all rows are processed i.e. have called the delegate).

Delegate method:

1. Opens a new connection, executes a Stored Proc 'TransferWebTransmitUpdate'


2. which -> Updates the table 'TransferWebTransmit' row (ID=1), then does a SELECT on (ID=1) row ----> cursor lock! ----> .Net throws "System.Data.SqlClient.SqlException (0x801 31904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding". ----> Connections are locked. ----> Have to Kill processes to recover

Here's the delegate method:

    public int Update(int transferID)
    {
        var obj = new TransferWebMessage();

        using (SqlConnection conn = base.GetNewConnection())
        {
            using (SqlCommand sp_cmd = new SqlCommand())
            {
                sp_cmd.CommandText = "TransferWebTransmitUpdate";

                sp_cmd.CommandType = CommandType.StoredProcedure;
                sp_cmd.Parameters.AddWithValue("TransferID", transferID);

                sp_cmd.Connection = conn;
                conn.Open();
                SqlDataReader rdr = sp_cmd.ExecuteReader();
                int roweffected;
                while (rdr.Read())
                {
                    roweffected = rdr.GetInt32(0),
                }
            }
        }
        return roweffected;
    }

Here's the call to get the rows to process and call the delegate:

public void WatchForDataTransferRequests(_delegateMethod callback)
{
    using (SqlConnection conn = new SqlConnection(_insol_SubscriberConnectionString))
    {
        // Construct the command to get any new ProductReview rows from the database along with the corresponding product name
        // from the Product table.
        SqlCommand cmd = new SqlCommand(
            "SELECT [TransferID]" +
                " FROM [dbo].[TransferWebTransmit]" +
                " ORDER BY [TransferID] ASC", conn);

        cmd.Parameters.Add("@CurrentTransferID", SqlDbType.Int);
        conn.Open();

        SqlDataReader rdr = cmd.ExecuteReader();

        // Process the rows
        while (rdr.Read())
        {
            Int32 transferID = (Int32)rdr.GetInt32(0);
            callback(transferID);
        }
    }
}