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

我正在使用c#asp.net核心,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();


 }


 }



时间: 作者:

以我的经验,SqlBulkCopy是最快的方法。最初将数据加载到临时文件后,如果您在服务器端进行规范化,这甚至可能会更快。这是使用分隔文件的SqlBulkCopy示例:


void Main()


{


 Stopwatch sw = new Stopwatch();


 sw.Start();


 string sqlConnectionString = @"server=.SQLExpress2012;Trusted_Connection=yes;Database=SampleDb";



 string path = @"d:tempSampleTextFiles";


 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"



作者:
...