SQLBulkCopy与目标表中的标识插入 [英] SQLBulkCopy with Identity Insert in destination table
本文介绍了SQLBulkCopy与目标表中的标识插入的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试使用SQLBulkCopy在SQL Server中插入通用列表。
b
我要我的目标表生成身份字段
我应该如何处理这个,
这里是我的代码
using(var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings [ConnectionString]。ConnectionString))
{
bulkCopy.BatchSize =(int)
bulkCopy.DestinationTableName =dbo.tMyTable;
var table = new DataTable();
var props = TypeDescriptor.GetProperties(typeof(tBFFormularyStatusList))
//脏的黑客,以确保我们只有系统数据类型
//i.e。过滤掉关系/集合
.Cast< PropertyDescriptor>()
.Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals(System))
.ToArray
foreach(var propertyInfo in props)
{
bulkCopy.ColumnMappings.Add(propertyInfo.Name,propertyInfo.Name);
table.Columns.Add(propertyInfo.Name,Nullable.GetUnderlyingType(propertyInfo.PropertyType)propertyInfo.PropertyType);
}
var values = new object [props.Length];
foreach(myGenericList中的var项)
{
for(var i = 0; i {
values [i] props [i] .GetValue(item);
}
table.Rows.Add(values);
}
bulkCopy.WriteToServer(table);
}
例外
对象'ProcessFlatFiles.DetailsClass'上的属性访问器'ID'抛出以下异常:'对象与目标类型不匹配'。
我也试过了
using(var bulkCopy = new SqlBulkCopy Configuration $ .ConnectionStrings [ConnectionString]。ConnectionString,SqlBulkCopyOptions.KeepIdentity))
{
解决方案
最后,我得到了这样的工作
using(var bulkCopy = new SqlBulkCopy .ConnectionStrings [ConnectionString]。ConnectionString,SqlBulkCopyOptions.KeepNulls& SqlBulkCopyOptions.KeepIdentity))
{
bulkCopy.BatchSize =(int)DetailLines;
bulkCopy.DestinationTableName =dbo.myTable;
bulkCopy.ColumnMappings.Clear();
bulkCopy.ColumnMappings.Add(SourceColumnName,DestinationColumnName);
bulkCopy.ColumnMappings.Add(SourceColumnName,DestinationColumnName);
bulkCopy.ColumnMappings.Add(SourceColumnName,DestinationColumnName);
bulkCopy.ColumnMappings.Add(SourceColumnName,DestinationColumnName);
。
。
。
。
bulkCopy.ColumnMappings.Add(SourceColumnName,DestinationColumnName);
bulkCopy.WriteToServer(datatable);
}
I am trying to insert a Generic list to SQL Server with SQLBulkCopy,
And i have trouble wit Identity Field
I wan t my destination table to generate identity field How should i handle this, here is my code
using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
bulkCopy.BatchSize = (int)DetailLines;
bulkCopy.DestinationTableName = "dbo.tMyTable";
var table = new DataTable();
var props = TypeDescriptor.GetProperties(typeof(tBFFormularyStatusList))
//Dirty hack to make sure we only have system data types
//i.e. filter out the relationships/collections
.Cast<PropertyDescriptor>()
.Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
.ToArray();
foreach (var propertyInfo in props)
{
bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
}
var values = new object[props.Length];
foreach (var item in myGenericList)
{
for (var i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
bulkCopy.WriteToServer(table);
}
exception
Property accessor 'ID' on object 'ProcessFlatFiles.DetailsClass' threw the following exception:'Object does not match target type.'
I have also tried
using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
解决方案
Finally I got this worked this way
using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, SqlBulkCopyOptions.KeepNulls & SqlBulkCopyOptions.KeepIdentity))
{
bulkCopy.BatchSize = (int)DetailLines;
bulkCopy.DestinationTableName = "dbo.myTable";
bulkCopy.ColumnMappings.Clear();
bulkCopy.ColumnMappings.Add("SourceColumnName", "DestinationColumnName");
bulkCopy.ColumnMappings.Add("SourceColumnName", "DestinationColumnName");
bulkCopy.ColumnMappings.Add("SourceColumnName", "DestinationColumnName");
bulkCopy.ColumnMappings.Add("SourceColumnName", "DestinationColumnName");
.
.
.
.
bulkCopy.ColumnMappings.Add("SourceColumnName", "DestinationColumnName");
bulkCopy.WriteToServer(datatable);
}
这篇关于SQLBulkCopy与目标表中的标识插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文