SQLBulkCopyOptions with Default values

[Guest]

Hello.

I'm changing my code which copies data from memory to a SQL Server from
using a data adapter's update method to using the writeToServer method of the
SQLBulkCopy object.

The update method of the data adapter worked fine, but I expect the
SQLBulkCopy to perform better.

The problem is I'm getting an error message that one of the fields doesn't
allow null values.

With the data adapter, I didn't explicitly have to set the fields to
anything, as the fields have a default value of zero defined on the server.

I'm using the SQLBulkCopyOption KeepIdentity and even tried using (Not
SqlBulkCopyOptions.KeepNulls), but still get the error.

Does SQLBulkCopy not recognize default values unless the field value is
explicitly set to null? That's the next thing I'm going to try, and if it
doesn't work, I'm going back to using the data adapter.

Thanks for any information you can provide.

 

[Brian Lampe]

Hello.

I'm changing my code which copies data from memory to a SQL Server from
using a data adapter's update method to using the writeToServer method of the
SQLBulkCopy object.

The update method of the data adapter worked fine, but I expect the
SQLBulkCopy to perform better.

The problem is I'm getting an error message that one of the fields doesn't
allow null values.

With the data adapter, I didn't explicitly have to set the fields to
anything, as the fields have a default value of zero defined on the server.

I'm using the SQLBulkCopyOption KeepIdentity and even tried using (Not
SqlBulkCopyOptions.KeepNulls), but still get the error.

Does SQLBulkCopy not recognize default values unless the field value is
explicitly set to null? That's the next thing I'm going to try, and if it
doesn't work, I'm going back to using the data adapter.

Thanks for any information you can provide.

Have you tried bulk coying a strongly typed DataSet? You can set it
up to deal with the nulls on load. Give those columns a default
value.

Brian

 

[Guest]

Thanks for replying, Brian.

I've been doing more research and I think the problem is from a limitation
of the fill method of the dataadapter. The filled datatable doesn't include
the column's default values, even when telling the dataadapter to add the
missing schema details.

What's surprising about all this, then, is that it worked at all by not
explicitly referencing the columns with default values before adding newrows
to the datatable.

I don't want to reproduce the default values anywhere outside of the SQL
Server, so I'll go back to using the data adapter's update method and not
specifying values for fields with default values defined.

Too bad, I was hoping to use sqlBulkCopy consistently.

Thanks again for your response.

-Beth