Bulk insert - Microsoft.Data.Sqlite
SQLite doesn't have any special way to bulk insert data. To get optimal performance when inserting or updating data, ensure that you do the following:
- Use a transaction.
- Reuse the same parameterized command. Subsequent executions will reuse the compilation of the first one.
using (var transaction = connection.BeginTransaction())
{
var command = connection.CreateCommand();
command.CommandText =
@"
INSERT INTO data
VALUES ($value)
";
var parameter = command.CreateParameter();
parameter.ParameterName = "$value";
command.Parameters.Add(parameter);
// Insert a lot of data
var random = new Random();
for (var i = 0; i < 150_000; i++)
{
parameter.Value = random.Next();
command.ExecuteNonQuery();
}
transaction.Commit();
}