I was looking at InsertAll and SaveAll with SQL Server using SQL Profiler and I saw that both of them use 1 INSERT each POCO I add to a table. Is it possible to use a more optimized version of InsertAll with a complexity near O(1) instead of O(N)?
I have used the SqlBulkCopy object with SQL Server when inserting large amounts of data.
Here are the extention method helpers we use:
public static async Task BulkInsertAsync<T>(this IDbConnection openconn, List<T> list)
{
using (SqlBulkCopy copy = new SqlBulkCopy(openconn.ToDbConnection() as SqlConnection))
{
copy.BulkCopyTimeout = 600;
var tableType = list.GetType().GetGenericArguments().First();
var tableName = tableType.FirstAttribute<AliasAttribute>()?.Name ?? tableType.Name;
copy.DestinationTableName = tableName;
DataTable autoTable = list.ToDataTable();
autoTable.TableName = tableName;
await copy.WriteToServerAsync(autoTable);
}
}
public static DataTable ToDataTable<T>(this IList<T> data)
{
PropertyDescriptorCollection props =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
if (prop.PropertyType.Name == "Nullable`1")
{
table.Columns.Add(prop.Name, prop.PropertyType.GenericTypeArguments.First()).AllowDBNull = true;
}
else
{
table.Columns.Add(prop.Name, prop.PropertyType);
}
}
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}
2 Likes
@rsafier thx for the SQL Server impl! Do you know if this also works on .NET Core?
If anyone’s looking someone has also published OrmLite PostgreSQL, MySQL and SQL Server examples at:
1 Like
Yep, works in .NET Core.
I found that the original code doesn’t handle the case (in MSSQL at least) where there are properties with the IGNORE and REFERENCES ormlite attributes, which then fails during the import. I was also doing my bulk inserts within an existing transaction, so I added some functionality to cater for this as well. Its a bit quick and dirty but it might be useful. I wrote all logic as extension method on the IDbConnection interface. In case it helps.
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite;
namespace lib.db.extensions
{
public static class MsSqlDbConnectionExtensions
{
/// <summary>
/// Try to get an SqlConnection from an IDbConnection by safely handling the case where it might
/// be an OrmliteConnection (if we using the microrm rather than raw sql)
/// </summary>
/// <param name="dbConn"></param>
/// <returns></returns>
public static SqlConnection AsSqlConnection(this IDbConnection dbConn)
{
return (SqlConnection)((dbConn as OrmLiteConnection)?.ToDbConnection() ?? dbConn);
}
/// <summary>
/// Try to get an SqlTransaction from an IDbTransaction by safely handling the case where it might
/// be an OrmliteTransaction (if we using the microrm rather than raw sql)
/// </summary>
private static SqlTransaction AsSqlTransaction(this IDbTransaction trans)
{
return (SqlTransaction)((trans as OrmLiteTransaction)?.ToDbTransaction() ?? trans);
}
/// <summary>
/// https://forums.servicestack.net/t/how-to-insert-multiple-records-with-1-insert/6494/2
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="openconn"></param>
/// <param name="copyOptions"></param>
/// <param name="openTransaction"></param>
/// <param name="list"></param>
/// <param name="bulkCopyTimeoutInSeconds"></param>
/// <returns></returns>
public static void BulkInsertMsSql<T>(
this IDbConnection openconn,
IEnumerable<T> list,
IDbTransaction openTransaction = null,
SqlBulkCopyOptions copyOptions = SqlBulkCopyOptions.Default,
int bulkCopyTimeoutInSeconds = 600
)
{
using (SqlBulkCopy copy = new SqlBulkCopy(openconn.AsSqlConnection(), copyOptions, openTransaction.AsSqlTransaction()))
{
copy.BulkCopyTimeout = bulkCopyTimeoutInSeconds;
var tableName = openconn.GetTableName<T>();
copy.DestinationTableName = tableName;
DataTable autoTable = list.ToList().ToDataTable();
autoTable.TableName = tableName;
copy.WriteToServer(autoTable);
}
}
/// <summary>
/// https://forums.servicestack.net/t/how-to-insert-multiple-records-with-1-insert/6494/2
/// </summary>
/// <typeparam name="TTable"></typeparam>
/// <param name="openconn"></param>
/// <param name="list"></param>
/// <param name="copyOptions"></param>
/// <param name="bulkCopyTimeoutInSeconds"></param>
/// <param name="openTransaction"></param>
/// <returns></returns>
public static async Task BulkInsertAsyncMsSql<TTable>(
this IDbConnection openconn,
IEnumerable<TTable> list,
IDbTransaction openTransaction = null,
SqlBulkCopyOptions copyOptions = SqlBulkCopyOptions.Default,
int bulkCopyTimeoutInSeconds = 600
)
{
using (SqlBulkCopy copy = new SqlBulkCopy(
openconn.AsSqlConnection(),
SqlBulkCopyOptions.Default,
openTransaction.AsSqlTransaction())
)
{
copy.BulkCopyTimeout = bulkCopyTimeoutInSeconds;
var tableName = openconn.GetTableName<TTable>();
copy.DestinationTableName = tableName;
DataTable autoTable = list.ToList().ToDataTable();
autoTable.TableName = tableName;
await copy.WriteToServerAsync(autoTable);
}
}
/// <summary>
/// https://forums.servicestack.net/t/how-to-insert-multiple-records-with-1-insert/6494/2
/// </summary>
/// <typeparam name="TTable"></typeparam>
/// <param name="data"></param>
/// <returns></returns>
public static DataTable ToDataTable<TTable>(this IEnumerable<TTable> data)
{
var insertableColumnsIndices = new HashSet<int>();
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(TTable));
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
var isPropertyBulkInsertable = IsPropertyBulkInsertable(prop);
if (isPropertyBulkInsertable)
{
insertableColumnsIndices.Add(i);
if (prop.PropertyType.Name == "Nullable`1")
{
table.Columns.Add(prop.Name, prop.PropertyType.GenericTypeArguments.First()).AllowDBNull = true;
}
else
{
table.Columns.Add(prop.Name, prop.PropertyType);
}
}
}
object[] values = new object[insertableColumnsIndices.Count];
var indexableArray = insertableColumnsIndices.ToArray();
foreach (TTable item in data)
{
for (int i = 0; i < indexableArray.Length; i++)
{
values[i] = props[indexableArray[i]].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}
/// <summary>
/// We need to explicitly skip the Ormlite-specific Ignore and Reference attributes so that BulkInsert
/// does not try to insert those as columns
/// </summary>
/// <param name="prop"></param>
/// <returns></returns>
private static bool IsPropertyBulkInsertable(PropertyDescriptor prop)
{
if (prop.Attributes.OfType<IgnoreAttribute>().Any()) return false;
if (prop.Attributes.OfType<ReferenceAttribute>().Any()) return false;
return true;
}
}
}
2 Likes
Be careful! If I’m not wrong, the added object class definition must contain fields in exactly the sam order as it is defined in SQL table. In other way the values are inserted in order of fields defined in class.
Support for RDBMS Bulk Inserts have been added for all supported RDBMS behind OrmLite’s new BulkInsert API in the latest ServiceStack v6.10 Release:
db.BulkInsert(rows);
Please see the ServiceStack v6.10 Release Notes for more info.
Which also includes a number of benchmarks we ran measuring the performance vs Single Inserts in the Which RDBMS has the fastest Bulk Insert implementation? Blog Post.