.net 批量插入sqlite

1.应该先插入数据，再建立索引

2.应该把插入放到transaction中，提高效率。若不采用transaction，每秒插入为20条

CREATE TABLE [Data] ([ID] VARCHAR(100)  NULL,[Raw] VARCHAR(200)  NULL)
CREATE INDEX IDLookup ON Data(ID ASC)

public class Data
{
  public string IDData { get; set; }
  public string RawData { get; set; }
}   

string connectionString = @"Data Source=" + Path.GetFullPath(AppDomain.CurrentDomain.BaseDirectory + "\\dbimport");
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connectionString);
conn.Open();

//Dropping and recreating the table seems to be the quickest way to get old data removed
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(conn);
command.CommandText = "DROP TABLE Data";
command.ExecuteNonQuery();
command.CommandText = @"CREATE TABLE [Data] ([ID] VARCHAR(100)  NULL,[Raw] VARCHAR(200)  NULL)";
command.ExecuteNonQuery();
command.CommandText = "CREATE INDEX IDLookup ON Data(ID ASC)";
command.ExecuteNonQuery();

string insertText = "INSERT INTO Data (ID,RAW) VALUES(@P0,@P1)";

SQLiteTransaction trans = conn.BeginTransaction();
command.Transaction = trans;

command.CommandText = insertText;
Stopwatch sw = new Stopwatch();
sw.Start();
using (CsvReader csv = new CsvReader(new StreamReader(@"C:\Data.txt"), false))
{
   var f = csv.Select(x => new Data() { IDData = x[27], RawData = String.Join(",", x.Take(24)) });

   foreach (var item in f)
   {
      command.Parameters.AddWithValue("@P0", item.IDData);
      command.Parameters.AddWithValue("@P1", item.RawData);
      command.ExecuteNonQuery();
   }
 }
 trans.Commit();
 sw.Stop();
 Debug.WriteLine(sw.Elapsed.Minutes + "Min(s) " + sw.Elapsed.Seconds + "Sec(s)");
 conn.Close();

参考文献

http://stackoverflow.com/questions/8091978/improve-large-data-import-performance-into-sqlite-with-c-sharp