SQLBulkCopy with Identity Insert in destination table

c#-4.0 sqlbulkcopy sql-server-2008

Question

With SQLBulkCopy, I'm attempting to insert a Generic list into SQL Server.

I too have issues with the Identity Field.

I want the identification field to be generated in my destination table. What should I do about this? This 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.'

Additionally, I attempted

using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, SqlBulkCopyOptions.KeepIdentity))
                {
1
5
6/18/2012 2:27:26 PM

Accepted Answer

Finally, I managed to make things work in this manner.

    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);
                }
14
6/19/2012 4:14:12 PM

Popular Answer

I am aware that this is an old query, but I felt it was worthwhile to include this alternative: (Skip 1, 2, and 3 if you already have the right schema.

  1. Return a datatable with the schema of the target table by doing a straightforward TOP 1 pick from the table.
  2. Employ DataTable'sClone a process for creating a datatable with the same structure but no data
  3. Put your information in this table.
  4. the SqlBulkCopy commandWriteToServer (The identity values may be read if the column ordering are correct. If the option isn't specified in SqlBulkCopy's constructor, the destination will be used by default, which will be to disregard these data.

The crucial thing to remember is that everything is taken care of for you if the columns are in the right sequence, including identification columns.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow