c# - 插入数百万条记录的最佳方法

问题描述

我正在使用 C# asp.net core、linQ 和 T-SQL 中的托管服务。

我需要在我的数据库中逐一插入记录。当然这不是一个快速的操作，但我在这个领域没有那么有经验，所以也许我做错了。这是我在经理中的代码：

    public void StrategyMassive(string foldpathsave)
    {
        using (IServiceScope scope = _services.CreateScope())
        {
            List<string> filesreading = new List<string>();

            VUContext _context = scope.ServiceProvider.GetRequiredService<VUContext>();

            List<string> filesnumber = File.ReadAllLines(foldpathsave).ToList();

            filesreading = filesnumber.ToList();

            filesreading.RemoveRange(0, 2);

            foreach (string singlefile in filesreading)
            {

                //INTERNAL DATA NORMALIZATION

                _repository.ImportAdd(_context, newVUL, newC2, newC3, newDATE);

                _repository.Save(_context);

            }
        }
    }

这是我的存储库界面：

    public void ImportAdd(VUContext _context, AVuTable newVUL, ACs2Table newC2, ACs3Table newC3, ADateTable newDATe)
    {
        _context.AVuTable.Add(newVU);

        _context.ADateTable.Add(newDATE);

        if (newC2 != null)
        {
            _context.ACs2Table.Add(newC2);
        }

        if (newC3 != null)
        {
            _context.ACs3Table.Add(newC3);
        }

        public void Save(VUContext _context)

        {
            _context.SaveChanges();
        }
    }

我知道这一切都很简单，那么我怎样才能加快这个插入的速度呢？

标签： c#sql-serverlinqtsqlasp.net-core

以我的经验，SqlBulkCopy 是最快的方法。filesnumber 听起来用词不当，我怀疑您正在阅读经过一些规范化过程后要加载到 SQL Server 的分隔文件列表。如果您在最初将数据加载到临时文件后在服务器端进行规范化，那可能会更快。以下是分隔文件中的示例 SqlBulkCopy：

void Main()
{
  Stopwatch sw = new Stopwatch();
  sw.Start();
  string sqlConnectionString = @"server=.\SQLExpress2012;Trusted_Connection=yes;Database=SampleDb";

  string path = @"d:\temp\SampleTextFiles";
  string fileName = @"combDoubledX.csv";

  using (OleDbConnection cn = new OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path+
    ";Extended Properties=\"text;HDR=No;FMT=Delimited\";"))

  using (SqlConnection scn = new SqlConnection( sqlConnectionString ))
  {
  OleDbCommand cmd = new OleDbCommand("select * from "+fileName, cn);

  SqlBulkCopy sbc = new SqlBulkCopy(scn, SqlBulkCopyOptions.TableLock,null);

  sbc.ColumnMappings.Add(0,"[Category]");
  sbc.ColumnMappings.Add(1,"[Activity]");
  sbc.ColumnMappings.Add(5,"[PersonId]");
  sbc.ColumnMappings.Add(6,"[FirstName]");
  sbc.ColumnMappings.Add(7,"[MidName]");
  sbc.ColumnMappings.Add(8,"[LastName]");
  sbc.ColumnMappings.Add(12,"[Email]");

  cn.Open();
  scn.Open();

  SqlCommand createTemp = new SqlCommand();
  createTemp.CommandText = @"if exists
   (SELECT * FROM tempdb.sys.objects 
   WHERE object_id = OBJECT_ID(N'[tempdb]..[##PersonData]','U'))
   BEGIN
        drop table [##PersonData];
   END

  create table ##PersonData 
  (
    [Id] int identity primary key,
    [Category] varchar(50), 
    [Activity] varchar(50) default 'NullOlmasin', 
    [PersonId] varchar(50), 
    [FirstName] varchar(50), 
    [MidName] varchar(50), 
    [LastName] varchar(50), 
    [Email] varchar(50)
  )
"; 
  createTemp.Connection = scn;
  createTemp.ExecuteNonQuery();

  OleDbDataReader rdr = cmd.ExecuteReader();

  sbc.NotifyAfter = 200000;
  //sbc.BatchSize = 1000;
  sbc.BulkCopyTimeout = 10000;
  sbc.DestinationTableName = "##PersonData";
  //sbc.EnableStreaming = true;

  sbc.SqlRowsCopied += (sender,e) =>
    {
    Console.WriteLine("-- Copied {0} rows to {1}.[{2} milliseconds]", 
      e.RowsCopied, 
      ((SqlBulkCopy)sender).DestinationTableName,
      sw.ElapsedMilliseconds);
    };

  sbc.WriteToServer(rdr);

  if (!rdr.IsClosed) { rdr.Close(); }

  cn.Close();
  scn.Close();
  }
  sw.Stop();
  sw.Dump();
}

该文件中的几个示例行：

"Computer Labs","","LRC 302 Open Lab","","","10057380","Test","","Cetin","","5550123456","","cb@nowhere.com"
"Computer Labs","","LRC 302 Open Lab","","","123456789","John","","Doe","","5551234567","","jdoe@somewhere.com"
"Computer Labs","","LRC 302 Open Lab","","","012345678","Mary","","Doe","","5556666444","","mdoe@here.com"

您可以创建并运行 Tasks<> 的列表，从源中执行 SqlBulkCopy 读取（SqlBulkCopy 支持一系列读取器）。

c# - 插入数百万条记录的最佳方法

问题描述

解决方案

推荐阅读