SQLBulkCopy与目标表中的标识插入 [英] SQLBulkCopy with Identity Insert in destination table

问题描述

我尝试使用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屋！