How to Insert multiple records with 1 INSERT

ServiceStack.OrmLite
2

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