Firebird dotnet 批量插入

发布于 2025-01-01 00:53:43 字数 1075 浏览 1 评论 0原文

有人可以帮我加快速度吗? 我有一个名为 dsresult 的数据集(来自 csv 文件),我想将其放入 firebird 表中。 目前我一次执行 1 行,但我更愿意分批执行 500 行。 我正在使用 firebird.net 提供程序,

string connectionString =    "ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=MYDB.FDB";
string sql = "INSERT INTO POSTIN (NUMID, CHANGE, PLACENAME, BOXCODE, STRCODE, TOWN)  VALUES (@NUMID, @CHANGE, @PLACENAME, @BOXCODE, @STRCODE, @TOWN)";
FbConnection conn = new FbConnection(connectionString)
FbCommand command = new FbCommand(sql, conn);                   
foreach (DataRow r in dsResult.Tables[0].Rows)
 {
  command.Parameters.AddWithValue("@NUMID", r["NUMID"]);
  command.Parameters.AddWithValue("@CHANGE", r["CHANGE"]);
  command.Parameters.AddWithValue("@PLACENAME", r["PLACENAME"]);
  command.Parameters.AddWithValue("@BOXCODE", r["BOXCODE"]);
  command.Parameters.AddWithValue("@STRCODE", r["STRCODE"]);
  command.Parameters.AddWithValue("@TOWN", r["TOWN"]);         
  command.ExecuteNonQuery();
 }

它需要 aaaaaaaaaaages 才能运行。 在delphi中我只会使用cachedupdates。 一次发布 500 条记录 并在第 500 次

感谢时做出承诺

can someone help me speed this up.
I have a dataset (from a csv file) called dsresult and I want to pump it into a firebird table.
Currently I am doing it 1 row at a time, but I would prefer to do this in batches of 500 rows.
I am using the firebird.net provider

string connectionString =    "ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=MYDB.FDB";
string sql = "INSERT INTO POSTIN (NUMID, CHANGE, PLACENAME, BOXCODE, STRCODE, TOWN)  VALUES (@NUMID, @CHANGE, @PLACENAME, @BOXCODE, @STRCODE, @TOWN)";
FbConnection conn = new FbConnection(connectionString)
FbCommand command = new FbCommand(sql, conn);                   
foreach (DataRow r in dsResult.Tables[0].Rows)
 {
  command.Parameters.AddWithValue("@NUMID", r["NUMID"]);
  command.Parameters.AddWithValue("@CHANGE", r["CHANGE"]);
  command.Parameters.AddWithValue("@PLACENAME", r["PLACENAME"]);
  command.Parameters.AddWithValue("@BOXCODE", r["BOXCODE"]);
  command.Parameters.AddWithValue("@STRCODE", r["STRCODE"]);
  command.Parameters.AddWithValue("@TOWN", r["TOWN"]);         
  command.ExecuteNonQuery();
 }

it takes aaaaaaaaaaages to run.
in delphi i would have just jused cachedupdates.
post 500 records at a time
and commit on the 500th

Thanks

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

眸中客 2025-01-08 00:53:43

尝试这样的事情:

using(FbConnection c = new FbConnection(csb.ToString()))
{
    FbBatchExecution fbe = new FbBatchExecution(c);
    //loop through your commands here
    {
        fbe.SqlStatements.Add(cmd);
    }
    fbe.Execute();
}

try something like this:

using(FbConnection c = new FbConnection(csb.ToString()))
{
    FbBatchExecution fbe = new FbBatchExecution(c);
    //loop through your commands here
    {
        fbe.SqlStatements.Add(cmd);
    }
    fbe.Execute();
}
尤怨 2025-01-08 00:53:43

Firebird 的有线协议不支持在一批(以及一次往返)中发送更多命令。可能最好的主意是使用 EXECUTE BLOCK (又名匿名存储过程)并在那里发送插入内容。

例如:

execute block
as
begin
  insert into ...;
  insert into ...;
  ...
end

并执行这个。

顺便说一句,FbBatchExecution 也将一次发送一个命令。

Firebird's wire protocol doesn't support sending more commands in one batch (and in one roundtrip). Probably best idea is to use EXECUTE BLOCK (aka anonymous stored procedure) and send inserts there.

For example:

execute block
as
begin
  insert into ...;
  insert into ...;
  ...
end

and execute this.

BTW the FbBatchExecution will send one command a time as well.

锦上情书 2025-01-08 00:53:43

您应该只添加参数一次,并且只在循环中更改它们的值,例如:

创建参数一次:

command.Parameters.Add("@NUMID", FbDbType.Int)
....

在循环中执行:

foreach (DataRow r in dsResult.Tables[0].Rows)
{
  command.Parameters["@NUMID"].Value = r["NUMID"];
  ....
}

这确实会加快速度。

You should only add the parameters once, and only change the values off them in the loop so something like:

create the parameters once:

command.Parameters.Add("@NUMID", FbDbType.Int)
....

in the loop do:

foreach (DataRow r in dsResult.Tables[0].Rows)
{
  command.Parameters["@NUMID"].Value = r["NUMID"];
  ....
}

this should really speed things up.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文