CODEWITHSUNDEEP
c#sqliteormlite-servicestack
Jeff
Jeff

Reputation: 2071

How to batch sql calls using servicestack

I have an application that is writing records almost continuously in response to a system event. I am using C# ServiceStack Ormlite. I wonder if there is an easy way to batch these inserts such that instead of writing each one at a time, it would collect X many and then write in one transaction. This would reduce the disk usage and speed up the app (other things are going on).

Win 10, VS2019 C# Sqlite

Upvotes: 1

Views: 260

Answers (2)

Sadiqabbas Hirani
Sadiqabbas Hirani

Reputation: 387

you can use structured datatype i.e. Table datatype for bulk insert

and in stored procedure you can

insert into table (some columns) 
select some columns 
from @structured_variable (table variable)

Upvotes: 0

mythz
mythz

Reputation: 143409

You can use InsertAll() which is more efficient than individual Insert() statements as it executes all inserts within a single implicit transaction and reuses the same parameterized insert statement for each record, but it's not as efficient as constructing a single SQL statement and sending it to the RDBMS with something like:

var sb = new StringBuilder();
foreach (var row in rows)
{
    sb.Append(db.ToInsertStatement(row)).AppendLine(";");
}
db.ExecuteSql(sb.ToString());

Upvotes: 1

Related Questions