从C#将多个记录插入Excel电子表格时，OLEDB的性能降低 [英] OLEDB slow performance when inserting multiple records from C# into an Excel spreadsheet

问题描述

我正在使用OLEDB连接(Microsoft.ACE.OLEDB.12)从C#数据表中读取记录，并将记录放入Excel电子表格中.

I am using an OLEDB connection (Microsoft.ACE.OLEDB.12) to read records from a C# data table and put the records into an excel spreadsheet.

我要遍历数据表中的每条记录，并使用插入表名值("等)建立OLEDBCommand.CommandText.我必须适当格式化值，以便插入字符串/字符/整数/小数一切正常，并且创建了我的电子表格，但是，当要插入大量记录(例如，超过500,000条)时，性能确实很慢，而且永远需要这样做.一次从我的C#数据表中读取一条记录，确保SQL语句具有该数据类型的正确语法，然后一次插入一个?

I am looping round each record in my data table and building up OLEDBCommand.CommandText using an "Insert into table name values (" etc. I am having to format the values appropriately so that string/char/integer/decimal are inserted correctly. This all works fine and my spreadsheet is created, however when there are loads of records to insert (e.g. 500,000 plus), then the performance is really slow and it takes for ever. Is there a quicker way of doing this, rather than reading one record from my C# datatable at a time, making sure that the SQL statement has the correct syntax for the datatype and inserting it one at a time?

任何帮助表示赞赏

谢谢

推荐答案

请确保对插入 查看全文