C＃中的批量插入（？） [英] bulk insert in C# (?)

问题描述

如何使用ADO.NET以非常快的速度插入大量记录？


谢谢！

解决方案

不确定您的确切问题是什么，但这是一般解决方案：


您可以使用BULK INSERT sql语句从文本中插入大量记录文件。要从ADO .NET调用BULK INSERT语句，可以使用SqlCommand对象执行DDL语句。


示例：

----- -----

private void Test（）

{

SqlConnection conn;

SqlCommand命令; < br $> b $ b尝试

{

conn = new SqlConnection（" Data Source = MYSERVER; Initial Catalog = Northwind; Integrated_Security = SSPI" ）;


命令=新的SqlCommand（）;


conn.Open（）;


command.Connection = conn;

command.CommandText =" BULK INSERT Northwind.dbo。[Order Details]" +

@" FROM''f：\ orders\lineitem.tbl''" +

" WITH" +

"（" +

" FIELDTERMINATOR =''|''，" +

" ROWTERMINATOR ='' ：\ n''，" +

" FIRE_TRIGGERS" +

"）" ;;


命令.ExecuteNonQuery（）;

}

catch（例外e）

{

MessageBox.Show（e。消息）;

}


}


Daniel P.写道：

如何使用ADO.NET以非常快的速度插入大量记录？

谢谢！

您还可以使用dataAdapter对象的.Update方法将数据表加载到db表。

http://msdn.microsoft.com/ library / de ... pdatetopic.asp


Daniel P.写道：

如何使用ADO.NET以非常快的速度插入大量记录？

谢谢！

Lenn，


这种方法对于插入大量记录来说实在不是太快了。

以下内容来自您引用的文章：


"应该注意的是，这些声明不是作为批处理执行的。

流程;每一行都是单独更新的。


批量更新的另一种可能方法是自己批量处理。 IOW，

你可以创建解析文件来创建插入语句。您可以通过

在每个语句之间放置分号然后执行

连接这些语句中的许多（100'或可能1000'） />
连接语句。这是一种在

批次中插入记录的快速方法，因为它可以大大减少到DB的数量。如果出现错误

，则几乎不可能隔离哪个记录导致

问题。


祝你好运。


Scott

您还可以使用dataAdapter对象的.Update方法将数据表加载到
a db table。

http://msdn.microsoft.com/library/de...pdatetopic.asp

How can I use ADO.NET to insert lots of records in a very fast way?

Thanks!

解决方案

Not sure what your exact problem is, however here is a general solution:

You can use the BULK INSERT sql statement to insert large volumes of records from a text file. To call the BULK INSERT statement from ADO .NET, you can use a SqlCommand object to execute a DDL statment.

Example:
----------
private void Test()
{
SqlConnection conn;
SqlCommand command;

try
{
conn = new SqlConnection("Data Source=MYSERVER;Initial Catalog=Northwind;Integrated_Security=SSPI");

command = new SqlCommand();

conn.Open();

command.Connection = conn;
command.CommandText = "BULK INSERT Northwind.dbo.[Order Details]" +
@"FROM ''f:\orders\lineitem.tbl''" +
"WITH" +
"(" +
"FIELDTERMINATOR = ''|''," +
"ROWTERMINATOR = '':\n''," +
"FIRE_TRIGGERS" +
")";

command.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show (e.Message);
}

}

"Daniel P." wrote:

How can I use ADO.NET to insert lots of records in a very fast way?

Thanks!

Also you can use .Update method of dataAdapter object to load datatable to a db table.

http://msdn.microsoft.com/library/de...pdatetopic.asp

"Daniel P." wrote:

How can I use ADO.NET to insert lots of records in a very fast way?

Thanks!

Lenn,

This method really isn''t too fast for inserting a large number of records.
The following comes from the article you cite:

"It should be noted that these statements are not performed as a batch
process; each row is updated individually."

Another possible way of doing batch updates is to batch them yourself. IOW,
you can create parse the file to create insert statements. You can
concatenate many (100''s or possibly 1000''s) of these statements together by
placing a semicolon in between each statement and then execute the
concatenated statement. This is a really fast way of inserting records in a
batch since it can greatly reduce the number of trips to the DB. If an error
occurs though, it''s almost impossible to isolate which record caused the
problem.

Good luck.

Scott

Also you can use .Update method of dataAdapter object to load datatable to a db table.

http://msdn.microsoft.com/library/de...pdatetopic.asp

这篇关于C＃中的批量插入（？）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！