具有默认约束的BulkCopy和不可为空的列 [英] BulkCopy and Non-Nullable Columns with Default Constraint

问题描述

我遇到了SqlBulkCopy类的问题，我无法弄清楚如何绕过。

我动态构建一个DataTable：

dataTable = new DataTable（）;

dataTable.TableName = bindingTableName;

string sql =" SELECT * FROM" + bindingTableName +" WITH（NOLOCK）WHERE 1 = 2";

SqlCommand command = new SqlCommand（sql，connection，transaction）;
command.CommandTimeout = Constants.commandTimeout;

SqlDataAdapter adapter = new SqlDataAdapter（command）;
adapter.Fill（dataTable）;

这会创建DataTable中具有正确数据类型的列，并且一切正常，除非其中一列是NOT NULL并且具有与之关联的DEFAULT约束。如果其中一个列没有在DataRow中显式赋值，我希望应用数据库中的DEFAULT约束，但是在调用WriteToServer（）方法时遇到以下异常：

System.InvalidOperationException：列'myColumn'不允许DBNull.Value。
在System.Data.SqlClient.SqlBulkCopy.ConvertValue（对象值，_SqlMetaData元数据）
的Courier"> System.Data.SqlClient.SqlBulkCopy.WriteToServer（DataTable表中的.sqlBulkCopy.WriteRowSourceToServer（Int32 columnCount）
DataRowState rowState）

我是cre使用默认选项访问SqlBulkCopy对象：

SqlBulkCopy bulkCopy = new SqlBulkCopy（connection，SqlBulkCopyOptions.Default，transaction）;

鉴于我没有指定 SqlBulkCopyOptions.KeepNulls 选项，我会期望DBNulls在写入数据库时​​被DEFAULT约束替换，但事实并非如此。

有没有人对这个异常发生的原因以及如何解决这个问题有任何想法


谢谢，
Chris

I'm having a problem with the SqlBulkCopy class which I can't figure out how to get around.

I build a DataTable dynamically like this:

dataTable = new DataTable();

dataTable.TableName = bindingTableName;

string sql = "SELECT * FROM " + bindingTableName + " WITH (NOLOCK) WHERE 1=2";

SqlCommand command = new SqlCommand(sql, connection, transaction);
command.CommandTimeout = Constants.commandTimeout;

SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dataTable);

This creates the columns with the correct datatypes within the DataTable automatically, and everything works fine except when one of the columns is NOT NULL and has a DEFAULT constraint associated with it. If one of these columns is not explicitly assigned a value in the DataRow, I would expect the DEFAULT constraint in the database to apply, however I am getting the following exception when calling the WriteToServer() method:

System.InvalidOperationException: Column 'myColumn' does not allow DBNull.Value.
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metaData)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)

I'm creating the SqlBulkCopy object with default options:

SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction);

Given I am NOT specifying the SqlBulkCopyOptions.KeepNulls option, I would expect the DBNulls to be replaced by the DEFAULT constraint when writing to the database but this doesn't appear to be the case.

Does anyone have any ideas on why this exception is occurring and how I can get around this?

Thanks,
Chris

推荐答案

我遇到了同样的问题，你找到了解决方案吗？

这篇关于具有默认约束的BulkCopy和不可为空的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！