无法将Excel数据批量插入数据库，执行超时错误求助

我在上传Excel数据至数据库时遇到问题，调试后确定问题出在代码行dbocontext.BulkInsert(list)处。单条数据通过表单添加一切正常，但批量导入就报错。

错误信息 #

Microsoft.Data.SqlClient.SqlException: 'Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operation canceled by user.
The instruction has ended.'

控制器代码 #

public IActionResult EnviarDados([FromForm] IFormFile ArquivoExcel)
{
    Stream stream = ArquivoExcel.OpenReadStream();

    IWorkbook MiExcel = null;

    if (Path.GetExtension(ArquivoExcel.FileName) == ".xlsx")
    {
        MiExcel = new XSSFWorkbook(stream);
    }
    else
    {
        MiExcel = new HSSFWorkbook(stream);
    }

    ISheet HojaExcel = MiExcel.GetSheetAt(0);

    int cantidadFilas = HojaExcel.LastRowNum;
    int cantidadFilas2 = HojaExcel.PhysicalNumberOfRows;

    List<ProdutoDB> lista = new List<ProdutoDB>();
   
     for (int i = 1; i <= cantidadFilas; i++)
    {

        IRow fila = HojaExcel.GetRow(i);

        lista.Add(new ProdutoDB
        {
            Name = fila.GetCell(0).ToString(),
            Sku = fila.GetCell(1).ToString(),
            Active = fila.GetCell(2).ToString(),
            Description = fila.GetCell(3).ToString(),
            Price = fila.GetCell(4).ToString(),
            Qty = fila.GetCell(5).ToString(),
            Ean = fila.GetCell(6).ToString(),
            SkuManufacturer = fila.GetCell(7).ToString(),
            NetWeight = fila.GetCell(8).ToString(),
            GrossWeight = fila.GetCell(9).ToString(),
            Width = fila.GetCell(10).ToString(),
            Height = fila.GetCell(11).ToString(),
            Depth = fila.GetCell(12).ToString(),
            Guarantee = fila.GetCell(13).ToString(),
            Origin = fila.GetCell(14).ToString(),
            Unity = fila.GetCell(15).ToString(),
            Ncm = fila.GetCell(16).ToString(),
            Manufacturer = fila.GetCell(17).ToString(),
            ExtraOperatingTime = fila.GetCell(18).ToString(),
            Category = fila.GetCell(19).ToString(),
            Images = fila.GetCell(20).ToString(),
            Status = fila.GetCell(21).ToString()
        });
    }
    _dbocontext.BulkInsert(lista);
    //_dbocontext.Add(lista);


   return StatusCode(StatusCodes.Status200OK, new { mensaje = "Os dados foram carregados com sucesso!" });
}

解决思路 #

• 调整批量插入超时参数
  多数BulkInsert扩展库支持自定义超时时间，显式设置更长的超时值：

  _dbocontext.BulkInsert(lista, options => {
      options.Timeout = 60; // 单位秒，根据数据量调整
  });
  

• 拆分数据批次插入
  若Excel数据量过大，一次性插入会触发超时，将列表拆分为多个小批次处理：

  int batchSize = 500; // 每批次500条，可根据实际情况调整
  for (int i = 0; i < lista.Count; i += batchSize)
  {
      var batch = lista.Skip(i).Take(batchSize).ToList();
      _dbocontext.BulkInsert(batch);
  }
  

• 优化数据库索引
  目标表的过多索引会拖慢批量写入速度，可先禁用索引，插入完成后重建：

  -- 禁用所有索引
  ALTER INDEX ALL ON ProdutoDB DISABLE;
  -- 数据插入完成后重建索引
  ALTER INDEX ALL ON ProdutoDB REBUILD;
  

• 修复数据转换逻辑
  代码中直接对所有单元格调用ToString()，未处理空单元格情况，可能导致数据库验证开销增加。建议添加空值判断：

  Name = fila.GetCell(0)?.ToString() ?? string.Empty,
  Price = fila.GetCell(4)?.ToString() ?? "0"
  

• 调整数据库连接超时
  检查连接字符串中的Connect Timeout设置，确保值足够大：

  Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Connect Timeout=60;
  

内容的提问来源于stack exchange，提问作者vitu