-1

I am trying to make an exact copy from one sql table to another. Both tables reside in the same database. I am trying to use SqlBulkCopy but receive a "There is already an open DataReader associated with this Command which must be closed first." error.

I have found many similar problems and tried the solutions contained therein with no success. I am very new to this so any help with this (or any of the code below) is appreciated.

SqlCommand readLocal = new SqlCommand("Select CompanyNumber,CompanyName,AccessNumber,TruckName,TransferStartTime,TransferEndTime,TransferVolume FROM TransfersNew", conLocal);

try
{
    conLocal.Open();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conLocal))
    {
        bulkCopy.DestinationTableName = "dbo.TransfersHistory";

        try
        {
            using (SqlDataReader readerLocal = readLocal.ExecuteReader())
            {
                while (readerLocal.Read())
                {
                    bulkCopy.WriteToServer(readerLocal);
                }
            }
        }
        catch (Exception ex)
        {
            Globals.infoString = ex.Message;
            Globals.infoStringColor = true;
            var newForm = new pagePopupInfo();
            newForm.ShowDialog();
        }
        finally
        {
            conLocal.Close();
        }
    }
}
catch (Exception ex)
{
    Globals.infoString = ex.Message;
    Globals.infoStringColor = true;
    var newForm = new pagePopupInfo();
    newForm.ShowDialog();
}
finally
{
    conLocal.Close();
}

I'm wide open to suggestions. Thanks

Matheus' suggestion worked. My additional issue regarding not copying the data turned out to be that it was just not copying the first record. I changed my code to this to correct that issue:

try
        {
            conLocal.Open();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conLocal))
            {
                bulkCopy.DestinationTableName = "dbo.TransfersHistory";
                SqlDataReader readerLocal = readLocal.ExecuteReader();
                bulkCopy.WriteToServer(readerLocal);
            }
        }
newStack
  • 1
  • 3
  • 1
    Possible duplicate of [Error "There is already an open DataReader associated with this Command which must be closed first" when using 2 distinct commands](https://stackoverflow.com/questions/18475195/error-there-is-already-an-open-datareader-associated-with-this-command-which-mu) – Ňɏssa Pøngjǣrdenlarp Jul 25 '19 at 21:11
  • Add MultipleActiveResultSets=true to your connection string. [link](https://learn.microsoft.com/en-us/previous-versions/dotnet/netframework-4.0/h32h3abf(v=vs.100)) – Matheus Dasuke Jul 25 '19 at 21:13
  • I followed Matheus' suggestion and I no longer get the error, so thanks for that. However it also did not copy the data to the TransfersHistory table. So I'll dig into that and report back. – newStack Jul 25 '19 at 21:53

1 Answers1

0

The reason that you have two Using, one inside the another, so it is considered as two connections.

You can try something like this : 

                   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conLocal))
                   using (SqlDataReader readerLocal = readLocal.ExecuteReader())
                    {
                        while (readerLocal.Read())
                        {
                            bulkCopy.WriteToServer(readerLocal);
                        }
                    }
J.K
  • 1,178
  • 10
  • 13