CLR: System.InvalidOperationException: The context connection is already in use.

Question

I keep getting this error when trying to perform a delete in the middle of my DataReader:

System.InvalidOperationException: The context connection is already in use.

I get all the rows that need to be deleted and then iterate through the data reader as there are a lot of rows returned.

SqlConnection conn = new SqlConnection("context connection=true");

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "spClean_Select_Product_For_Delete";
        cmd.CommandTimeout = 41600;

        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader();

        try
        {
            SqlContext.Pipe.Send(DateTime.Now.ToString() + " - Started working with Product");

            while (reader.Read())
            {
                SqlContext.Pipe.ExecuteAndSend(new SqlCommand("DELETE FROM ProductInfo WHERE ProductId = " 
                    + reader["ProductId"].ToString()));
            }

            SqlContext.Pipe.Send(DateTime.Now.ToString() + " - Completed working with Product");
        }
        finally
        {
            conn.Close();
            // Always call Close when done reading.
            reader.Close();
        }

Answer 1

The most likely cause for this exception is that you are sending delete commands to the server at the same time you are selecting records to delete. I would suggest that you first collect all the product ids you want to delete and only then delete them:

ICollection<int> productIds = new HashSet<int>();
using (var cmd = new SqlCommand("spClean_Select_Product_For_Delete"))
{
    using (var reader = cmd.ExecuteReader())
    {
        var productId = (int) reader["ProductId"];
        productIds.Add(productId);
    }
}
string deleteCmdString =
    "DELETE FROM Product WHERE ProductId IN (" + productIdsString + ")";
SqlContext.Pipe.ExecuteAndSend(new SqlCommand(deleteCmdString))

The code above is not complete or correct but you should get the idea.