dbf文件插入需要很长时间 [英] dbf file insertion Take a long Time

问题描述

我使用OLEDB将数据插入到DB4 .dbf文件中。插入13行需要差不多1分钟，因此只有在插入一个表时才会出现问题，其中包含一个varchar 20,2个日期和一个小数。有没有其他更快的方法可以做到这一点？

这里是一些代码示例

I use OLEDB to insert data to a DB4 .dbf file. Inserting 13 row takes almost 1 minute wich is soooo loong, theis problem only accur during insertion in one table , that contain a varchar 20 , 2 dates and a decimal. Is there any alternative faster ways to do this?
here is some code samples

foreach (DataRow row in fstathotel.Rows)
               {
                   cmd.CommandText = @"insert into fstathote values (" + Convert.ToInt32(row["mpehotel"]) + ",'" + Convert.ToDateTime(row["date"]) + "','" + row["type"].ToString() + "',?,'" + Convert.ToDateTime(row["edate"]) + "')";
                   cmd.Parameters.AddWithValue("parmSlot1", Decimal.Parse(row["value"].ToString()));
                   cmd.ExecuteNonQuery();
               }

推荐答案

尝试更改设置命令的方式以使用已准备好的sql



请参阅 http： //msdn.microsoft.com/en-GB/library/system.data.oledb.oledbcommand.prepare.aspx [ ^ ]



除了制作使用（完全）参数化查询更高效的东西也有助于防止sql注入

Try changing the way you are setting up your command to use prepared sql

see http://msdn.microsoft.com/en-GB/library/system.data.oledb.oledbcommand.prepare.aspx[^]

Apart from making things more performant using (fully) parameterised queries will also help protect against sql injection

cmd.CommandText = @"insert into fstathote values (?,?,?,?,?)
foreach (DataRow row in fstathotel.Rows)
     cmd.Parameters.AddWithValue("mpehotel", Convert.ToInt32(row["mpehotel"]));
     cmd.Paremeters.AddWithValue("date", Convert.ToDateTime(row["date"]));
     cmd.Parameters.AddWithValue("type", row["type"].ToString());
     cmd.Parameters.AddWithValue("value", Decimal.Parse(row["value"].ToString())); // parmSlot1 in your code
     cmd.Parameters.AddWithValue("edate", Convert.ToDateTime(row["edate"]));
     cmd.ExecuteNonQuery();

问题是我在looop中有cmd.Parameters.AddWithValue表示参数每次迭代都会添加。

The problem was that I have cmd.Parameters.AddWithValue in the looop wish means a parameter is added at each iteration.

foreach (DataRow row in fstathotel.Rows)
{
    cmd.CommandText = @"insert into fstathote values (" + Convert.ToInt32(row["mpe"]) + ",'" + Convert.ToDateTime(row["date"]) + "','" + row["type"].ToString() + "',?,'" + Convert.ToDateTime(row["edate"]) + "')";
    cmd.Parameters.Clear(); // Clear the parameter list 
    cmd.Parameters.AddWithValue("parmSlot1", Decimal.Parse(row["value"].ToString()));
    cmd.ExecuteNonQuery();
}

这篇关于dbf文件插入需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！