如何在sqlbulkcopy中设置自动映射 [英] how to set up auto mapping in sqlbulkcopy
问题描述
在我们的项目中,我们使用Linq2SQL,但是现在我们需要有可能在数据库表中快速插入近15,000行,所以我写了一个扩展方法:
In our project we use Linq2SQL, but now we need to have a possibility to quickly insert nearly 15k rows in database table, so i wrote an extension method:
public static void BulkInsertBigData(Table<Maintenance> maintenance, IEnumerable<Maintenance> maintenancesToInsert)
{
using (var copy = new SqlBulkCopy(maintenance.Context.Connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls))
{
//copy.ColumnMappings.Add("Id", "Id");
//copy.ColumnMappings.Add("FacilityId", "FacilityId");
//copy.ColumnMappings.Add("ParentFacilityId", "ParentFacilityId");
//copy.ColumnMappings.Add("StartTime", "StartTime");
//copy.ColumnMappings.Add("EndTime", "EndTime");
//copy.ColumnMappings.Add("ExpirationTime", "ExpirationTime");
//copy.ColumnMappings.Add("DispatcherUserName", "DispatcherUserName");
copy.DestinationTableName = "dbo.Maintenance";
var data = maintenancesToInsert.ToDataTable();
copy.WriteToServer(data);
}
}
维护是自动生成的Linq2SQL类.当我取消对ColumnMappings行的注释时,此代码很好用,但是它们不合适,因为如果有人更改dbml,则此代码将不起作用(此代码不敏捷:)). ToDataTable()
是一种基于反射的方法,它将维护列表转换为数据表.
Maintanance is auto-generated Linq2SQL class. This code works great when i uncomment ColumnMappings lines, but they doesn't fit, because if somebody will change dbml this code won't work(this code isn't agile :) ). ToDataTable()
is a reflection-based method, which converts List of Maintenance to DataTable.
如果有任何帮助,我将不胜感激
I'll be gratefull for any help
推荐答案
怎么样:
public static void BulkInsertBigData<T>(Table<T> definition, IEnumerable<T> rows)
{
using (var copy = new SqlBulkCopy(definition.Context.Connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls))
{
var meta = definition.Context.Mapping.GetMetaType(typeof(T));
foreach (var col in meta.DataMembers)
{
copy.ColumnMappings.Add(col.Member.Name, col.MappedName);
}
copy.DestinationTableName = meta.Table.TableName;
var data = rows.ToDataTable();
copy.WriteToServer(data);
}
}
甚至更好:通过 FastMember :
public static void BulkInsertBigData<T>(Table<T> definition, IEnumerable<T> rows)
{
using (var copy = new SqlBulkCopy(definition.Context.Connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls))
{
var meta = definition.Context.Mapping.GetMetaType(typeof(T));
var members = new List<string>(meta.DataMembers.Count);
foreach (var col in meta.DataMembers)
{
copy.ColumnMappings.Add(col.Member.Name, col.MappedName);
members.Add(col.Member.Name);
}
copy.DestinationTableName = meta.Table.TableName;
using (var reader = ObjectReader.Create(rows, members.ToArray()))
{
copy.WriteToServer(reader);
}
}
}
这篇关于如何在sqlbulkcopy中设置自动映射的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!