SqlBulkCopy WriteToServer requires an open and available Connection. The connection's current state is closed

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Problem

You execute the method WriteToServer, and the following error is thrown:

WriteToServer requires an open and available Connection. The connection's current state is closed.

using(var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
{
    // Oops! The connection has never been opened
    //connection.Open();

    using (var sqlBulk = new SqlBulkCopy(connection))
    {		
     	sqlBulk.DestinationTableName = "Customers";
     	sqlBulk.WriteToServer(dt);
    }
}

Try it

Solution

Cause

  • The connection is closed before calling WriteToServer or it was never open.

Fix

  • ENSURE to open the connection
using(var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
{
    // Make sure that connection is open
    connection.Open();

    using (var sqlBulk = new SqlBulkCopy(connection))
    {		
     	sqlBulk.DestinationTableName = "Customers";
     	sqlBulk.WriteToServer(dt);
    }
}

Try it



Got any SqlBulkCopy Question?