Question

c#sqlite 求教 批量插入，该怎么解决

Answer 1

用事务啊，以及参数化查询，一定要，示例

internal string FastInsertMany()
{
StringBuilder builder = new StringBuilder();
using (DbTransaction dbTrans = _cnn.BeginTransaction())
{
int dtStart;
int dtEnd;

using (DbCommand cmd = _cnn.CreateCommand())
{
cmd.Transaction = dbTrans;
cmd.CommandText = "INSERT INTO TestCase(Field1) VALUES(@p1)";
DbParameter Field1 = cmd.CreateParameter();

Field1.ParameterName = "@p1";
cmd.Parameters.Add(Field1);

//Console.WriteLine(String.Format(" Fast insert using parameters and prepared statement\r\n -> (100,000 rows) Begins ... "));
dtStart = Environment.TickCount;
for (int n = 0; n < 100000; n++)
{
Field1.Value = n + 200000;
cmd.ExecuteNonQuery();
}

dtEnd = Environment.TickCount;
dtEnd -= dtStart;
builder.AppendFormat("Ends in {0} ms ... ", (dtEnd));
}

dtStart = Environment.TickCount;
dbTrans.Commit();
dtEnd = Environment.TickCount;
dtEnd -= dtStart;
builder.AppendFormat("Commits in {0} ms", (dtEnd));
}
return builder.ToString();
}