Bulk Insert of Generic List C# into SQL Server
25,810
Solution 1
You may map your list to a datatable and then use SqlBulkCopy to insert all the rows at once.
Solution 2
4 years later this is my contribution. I had the same problem, I wanted to to bulk insert but passing over some fields that weren't going to be in the database, specifically EF navigation properties, so I wrote this generic class:
/// <summary>
/// This class is intended to perform a bulk insert of a list of elements into a table in a Database.
/// This class also allows you to use the same domain classes that you were already using because you
/// can include not mapped properties into the field excludedPropertyNames.
/// </summary>
/// <typeparam name="T">The class that is going to be mapped.</typeparam>
public class BulkInsert<T> where T : class
{
#region Fields
private readonly LoggingService _logger = new LoggingService(typeof(BulkInsert<T>));
private string _connectionString;
private string _tableName;
private IEnumerable<string> _excludedPropertyNames;
private int _batchSize;
private IEnumerable<T> _data;
private DataTable _dataTable;
#endregion
#region Constructor
/// <summary>
/// Initializes a new instance of the <see cref="BulkInsert{T}"/> class.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="data">The data.</param>
/// <param name="excludedPropertyNames">The excluded property names.</param>
/// <param name="batchSize">Size of the batch.</param>
public BulkInsert(
string connectionString,
string tableName,
IEnumerable<T> data,
IEnumerable<string> excludedPropertyNames,
int batchSize = 1000)
{
if (string.IsNullOrEmpty(connectionString)) throw new ArgumentNullException(nameof(connectionString));
if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException(nameof(tableName));
if (data == null) throw new ArgumentNullException(nameof(data));
if (batchSize <= 0) throw new ArgumentOutOfRangeException(nameof(batchSize));
_connectionString = connectionString;
_tableName = tableName;
_batchSize = batchSize;
_data = data;
_excludedPropertyNames = excludedPropertyNames == null ? new List<string>() : excludedPropertyNames;
_dataTable = CreateCustomDataTable();
}
#endregion
#region Public Methods
/// <summary>
/// Inserts the data with a bulk copy inside a transaction.
/// </summary>
public void Insert()
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default | SqlBulkCopyOptions.KeepIdentity, transaction))
{
bulkCopy.BatchSize = _batchSize;
bulkCopy.DestinationTableName = _tableName;
// Let's fix tons of mapping issues by
// Setting the column mapping in SqlBulkCopy instance:
foreach (DataColumn dataColumn in _dataTable.Columns)
{
bulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);
}
try
{
bulkCopy.WriteToServer(_dataTable);
}
catch (Exception ex)
{
_logger.LogError(ex.Message);
transaction.Rollback();
connection.Close();
}
}
transaction.Commit();
}
}
#endregion
#region Private Helper Methods
/// <summary>
/// Creates the custom data table.
/// </summary>
private DataTable CreateCustomDataTable()
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
var table = new DataTable();
foreach (PropertyDescriptor prop in properties)
{
// Just include the not excluded columns
if (_excludedPropertyNames.All(epn => epn != prop.Name))
{
if (prop.PropertyType.Name == "DbGeography")
{
var type = typeof(SqlGeography);
table.Columns.Add(prop.Name, type);
}
else
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
}
}
foreach (T item in _data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
// Just include the values in not excluded properties
if (_excludedPropertyNames.All(epn => epn != prop.Name))
{
if (prop.PropertyType.Name == "DbGeography")
{
row[prop.Name] = SqlGeography.Parse(((DbGeography)prop.GetValue(item)).AsText()).MakeValid();
}
else
{
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
}
}
}
table.Rows.Add(row);
}
return table;
}
#endregion
}
It's usage would be like:
//1st.- You would have a colection of entities:
var myEntities = new List<MyEntity>();
// [...] With thousands or millions of items
// 2nd.- You would create the BulkInsert:
myEntityTypeBulk = new BulkInsert<MyEntity>(_connectionString, "MyEntitiesTableName", myEntities, new[] { "ObjectState", "SkippedEntityProperty1", "SkippedEntityProperty2" });
// 3rd.- You would execute it:
myEntityTypeBulk.Insert();
The performance gained and the reusability of this class were worth this message. I hope it helps:
Juan
Author by
David Johnson
Updated on July 09, 2022Comments
-
David Johnson almost 2 years
How can I bulk insert a generic list in c# into SQL Server, rather than looping through the list and inserting individual items one at a time?
I currently have this;
private void AddSnapshotData() { var password = Cryptography.DecryptString("vhx7Hv7hYD2bF9N4XhN5pkQm8MRfxi+kogALYqwqSuo="); var figDb = "ZEUS"; var connString = String.Format( "Data Source=1xx.x.xx.xxx;Initial Catalog={0};;User ID=appuser;Password={1};MultipleActiveResultSets=True", figDb, password); var myConnection = new SqlConnection(connString); myConnection.Open(); foreach (var holding in _dHoldList) { lbStatus.Text = "Adding information to SQL for client: " + holding.ClientNo; _reports.AddZeusData("tblAllBrooksHoldingsSnapshot", "CliNo, SEDOL, ISIN, QtyHeld, DateOfSnapshot", "'" + holding.ClientNo + "','" + holding.Sedol + "','" + holding.ISIN + "','" + holding.QuantityHeld + "','" + DateTime.Today.ToString("yyyyMMdd") + "'", false, myConnection); } myConnection.Close(); lbStatus.Visible = false; }
Where
dHoldList
is a list ofDHOLDS
;public class DHOLDS : ExcelReport { public String ClientNo { get; set; } public String Sedol { get; set; } public Double QuantityHeld { get; set; } public Double ValueOfStock { get; set; } public String Depot { get; set; } public String ValC4 { get; set; } public String StockR1 { get; set; } public String StockR2 { get; set; } public Double BookCost { get; set; } public String ISIN { get; set; } }