SqlBulkCopy with model classes in C#
Use SqlBulkCopy with model (POCO) classes in C#
So far (based on my experience) the fastest way to insert big number of records from application to database is to use SqlBulkCopy. The downsize of this is that SqlBulkCopy uses DataTable instace as an input parameter which is not so convenient when dealing with models which are strongly typed and a lot more easy to use than iterating through the DataTable.
Since SqlBulkCopy accepts DataTable for insert you can not use models directly to insert your data. You can always do it by manually map object properties to DataTable mapping model properties to DataTable instance columns and then perform SqlBulkCopy with result DataTable object.
Make sure that table name, column order, column names and datatypes of DataTable matches the one in target table in database.
The following code is doing automatic mapping fro the marked properties into a DataTable using reflection. The reflection is not the fastest way to to map the properties t DataTable columns but is the price to pay to get more extensibility over hard-coded, single type mapping.
First thing is to add the attribute which will mark the properties for the mapping to DataTable.
namespace ModelBulkCopyTest.Mapping
{
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class ModelMapAttribute : Attribute
{
#region Fields
private string columnName;
#endregion
#region Properties
public string ColumnName
{
get
{
return this.columnName;
}
set
{
this.columnName = value;
}
}
#endregion
#region Constructors
public ModelMapAttribute(string columnName = null)
{
this.columnName = String.IsNullOrWhiteSpace(columnName) ? null : columnName;
}
#endregion
}
}
You notice that attribute class has two constructors, parametrized and parameterless one. If the columnName parameter is passed to the constructor than that will be the name of the column in the target DataTable object. Otherwise, if the column is not defined than property name will be taken for the name of the column in the result DataTable class instance.
Now the main part which will generate the DataTable for the SqlBulkCopy using the reflection and the custom attribute.
namespace ModelBulkCopyTest.Mapping
{
public static class ModelMapper
{
public static DataTable MapModel<T>(IEnumerable<T> models, String tableName) where T : class, new()
{
if (models == null || !models.Any())
{
return null;
}
else {
DataTable result = new DataTable(tableName);
IEnumerable<PropertyInfo> propertyInfos = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(ModelMapAttribute), true).Any());
//Create columns
foreach (PropertyInfo propertyInfo in propertyInfos)
{
ModelMapAttribute attribute = propertyInfo.GetCustomAttributes(typeof(ModelMapAttribute), true).First() as ModelMapAttribute;
if (!String.IsNullOrWhiteSpace( attribute.ColumnName))
{
result.Columns.Add(attribute.ColumnName);
}
else
{
result.Columns.Add(propertyInfo.Name);
}
}
//Fill the data
foreach (var model in models)
{
int matchCount = 0;
DataRow row = result.NewRow();
foreach (PropertyInfo propertyInfo in propertyInfos)
{
ModelMapAttribute attribute = propertyInfo.GetCustomAttributes(typeof(ModelMapAttribute), true).First() as ModelMapAttribute;
Object value = propertyInfo.GetValue(model);
row[!String.IsNullOrWhiteSpace( attribute.ColumnName) ? attribute.ColumnName : propertyInfo.Name] = value;
if (value != null)
{
matchCount++;
}
}
//Skip empty models
if (matchCount > 0)
{
result.Rows.Add(row);
}
}
result.AcceptChanges();
return result;
}
}
}
}
To test this I used simple model class and few lines of code to create DataTable in a simple console application. Since you can only see the result in a debug I also added a simple WinForm with a DataGrid control
namespace ModelBulkCopyTest
{
public class SampleModel
{
[Mapping.ModelMapAttribute()]
public String ID { get; set; }
[Mapping.ModelMapAttribute()]
public DateTime Time { get; set; }
[Mapping.ModelMapAttribute()]
public int DateKey { get; set; }
}
}
using System.Windows.Forms;
namespace ModelBulkCopyTest
{
class Program
{
static void Main(string[] args)
{
List<SampleModel> models = new List<SampleModel>();
for (int i = 0; i < 100; i++)
{
models.Add(new SampleModel()
{
ID = Guid.NewGuid().ToString(),
Time = DateTime.Now,
DateKey = int.Parse(DateTime.Now.ToString("yyyyMMdd"))
});
}
DataTable table = Mapping.ModelMapper.MapModel<SampleModel>(models, "SampleTable");
//Visualize the output
Application.EnableVisualStyles();
Application.Run(new ResultForm(table));
}
}
}
using System.Windows.Forms;
namespace ModelBulkCopyTest
{
public partial class ResultForm : Form
{
public ResultForm()
{
InitializeComponent();
}
public ResultForm(DataTable table) : this()
{
this.Controls.Add(new DataGrid() { Dock = DockStyle.Fill, DataSource = table });
}
private void ResultForm_Load(object sender, EventArgs e)
{
}
}
}
Since we see the result in a test form we can now write the code to perform SqlBulkCopy using the result of ModelMapper.MapModel method.
using System.Data.SqlClient;
namespace ModelBulkCopyTest.SqlClient
{
public static class ModelBulkCopy
{
public static void WriteToServer<T>(IEnumerable<T> models, String tableName, String connectionString) where T : class, new()
{
WriteToServer<T>(models, tableName, new SqlConnection(connectionString));
}
public static void WriteToServer<T>(IEnumerable<T> models, String tableName, SqlConnection connection) where T : class, new()
{
using (connection)
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection.ConnectionString))
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.WriteToServer(Mapping.ModelMapper.MapModel<T>(models, tableName));
}
connection.Close();
}
}
}
}
You can find attached whole code with the main class and test application with a sample model.
References
Disclaimer
Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.
About the author
DEJAN STOJANOVICDejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects
Comments for this article