A transport-level error has occurred when receiving results from the server Error C#

Question

I tried to read information from excel file and save it in SQL .

When I run the code local it works perfect but the exception thrown only in the test environment.

The error is:

A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)

My code looks like this:

public bool ExtractExcelToDB(int activityId, string tableName, string fileName)
{
    try
    {
        var path = GetPath(activityId);
        path = Path.Combine(path, fileName);

        using (FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
        {
            using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
            {
                DataSet result = excelReader.AsDataSet();
                DataTable dt = result.Tables["sheet"];
                DataTable newDt = dt.Select().Skip(1).Take(dt.Rows.Count).CopyToDataTable();

                using (SqlBulkCopy sqlBulk = new SqlBulkCopy(GetConnectionString()))
                {
                    sqlBulk.DestinationTableName = tableName;
                    sqlBulk.WriteToServer(newDt);
                }
            }

            return true;
        }
    }
}

I write the exception to DB and the code failed in this row:

sqlBulk.WriteToServer(newDt);

My connection string in the server looks like this:

<connectionStrings>
    <add name="xxx" 
         connectionString="metadata=res://*/DataModel.csdl|res://*/DataModel.ssdl|res://*/DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=xxx;initial catalog=xxx;persist security info=True;user id=xxx;password=xxx;multipleactiveresultsets=True;application name=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" />
</connectionStrings>

I tried almost every solution I saw without success.

EDIT

I can save file with 50 rows but not with 2000.. (Problem only in test environment not in local)

Answer 1

After 2 days I founded this work for me (Also using @marc_s answer)

I use transaction and "save" each time only 10 rows

Here is my code:

public bool ExtractExcelToDB(int activityId, string tableName, string fileName) {
  using(SqlConnection connection = new SqlConnection(GetConnectionStringBulk())) {
    SqlTransaction tran = null;
    try {
      connection.Open();
      tran = connection.BeginTransaction();

      var path = GetPath(activityId);
      path = Path.Combine(path, fileName);
      using(FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read)) {
        using(IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream)) {
          DataSet result = excelReader.AsDataSet();
          DataTable dt = result.Tables["sheet"];
          DataTable newDt = dt.Select().Skip(1).Take(dt.Rows.Count).CopyToDataTable();
          using(SqlBulkCopy sqlBulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran)) {
            sqlBulk.DestinationTableName = tableName;

            var bacth = newDt.Rows.Count / 10;
            for (int i = 0; i <= bacth; i++) {
              var rows = newDt.Select().Skip(i * 10).Take(10).ToArray();
              sqlBulk.WriteToServer(rows);
            }
          }

          tran.Commit();

        }
        return true;
      }


    } catch (Exception ex) {


      db.ExceptionErrors.Add(new ExceptionError {
        Value = ex.Message,
          Date = DateTime.Now
      });
      db.SaveChanges();

      if (tran != null) {
        tran.Rollback();
      }

      return false;
    }
  }
}