ADO.NET CommandBuilder、InsertCommand 和默认约束
我正在将数据从表 A 复制到表 B。 表 B 有一个可为空列，其默认约束值为 0。 一般来说，我使用以下访问器设置列的值。
public object this[string columnName]
{
get { return DataTable.Rows[CurrentRow][columnName]; }
set { DataTable.Rows[CurrentRow][columnName] = value; }
}
但我不设置我的可空列X。
当我插入整行时，不使用默认值。对于可为空的列，插入了 NULL，而不是 0。
_sqlCommandBuilder = new SqlCommandBuilder(_sqlDataAdapter);
_sqlCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
_sqlCommandBuilder.SetAllValues = false;
_sqlDataAdapter.Update(DataTable);
我还得到了架构：
_sqlDataAdapter.Fill(DataTable);
_sqlDataAdapter.FillSchema(_dataTable, SchemaType.Mapped);
为什么 ADO.NET 为我的列 X 设置 NULL，尽管我没有设置它？
我认为当我不设置 X 列的值时，ADO.NET 从给定的约束中获取默认值。
ADO.NET CommandBuilder 是否能够使用默认约束？
I ' am copying data from table A to table B.
Table B has a nullable column that has a default constraint value of 0.
In general I set values of columns using the following accessor.
public object this[string columnName]
{
get { return DataTable.Rows[CurrentRow][columnName]; }
set { DataTable.Rows[CurrentRow][columnName] = value; }
}
But I do NOT set my nullable column X.
When I insert the whole row, the default value is not used. Instead of 0, NULL was inserted for the nullable column.
_sqlCommandBuilder = new SqlCommandBuilder(_sqlDataAdapter);
_sqlCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
_sqlCommandBuilder.SetAllValues = false;
_sqlDataAdapter.Update(DataTable);
I also get the schema:
_sqlDataAdapter.Fill(DataTable);
_sqlDataAdapter.FillSchema(_dataTable, SchemaType.Mapped);
Why is ADO.NET setting NULL for my column X although I did NOT set it?
I thought that when I do not set the value of the column X, ADO.NET gets the default value from the given constraint.
Is the ADO.NET CommandBuilder able to use default constraints?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您不需要使用
CommandBuilder来实现DataAdapter的更新方法。CommandBuilder充满了问题。您可以将DataAdapter.SelectCommand和DataAdapter.UpdateCommand属性设置为直接指定sql 的DbCommand对象。这避免了 CommandBuilder 生成正确 sql 语句的能力所固有的问题。更新：
CommandBuilder 无法知道您要使用默认值。它所做的只是生成一个插入语句，如果有一个列，它将为插入语句生成它。如果该列是插入语句的一部分，则将为该列指定的任何值（甚至 null）都将被插入。默认值适用于不将其包含在插入语句中的情况。无论您如何尝试插入项目（无论是否使用 CommandBuilder），它都不会将 null 转换为默认值。
继续尝试使用 CommandBuilder 只会给您带来更多痛苦。它甚至无法处理其 select 子句中的简单连接语句。它还需要一个主键。如果违反其中任何一个，则它无法生成正确的更新、插入和删除语句。只有两个提供商，Sql Server 和 Oracle，曾经实现过此类，并且众所周知，Oracle 存在除了上述基本问题之外从未修复过的错误。
如果您使用两个 DbCommand 对象，一个用于选择，一个用于插入，然后通过 DbDataReader 循环遍历 select DbCommand 的输出，您可以轻松检查该列中的 null 并向 insert DbCommand 提供默认值零，因为你知道它是什么。了解数据库的规则并在必要时使用它们并不违反任何类型的代码组织规则。无论如何，您必须知道数据库中有什么才能编写此类代码。
如果您有 sql server 2005 或更高版本，另一个建议是使用 INSERT INTO .. SELECT 语句。如果您的 sql 足够好，您可以使用 CASE 子句来创建单个sql语句。
You do not need to use a
CommandBuilderto implement the update method for yourDataAdapter. TheCommandBuilderis full of issues. You can set theDataAdapter.SelectCommandand theDataAdapter.UpdateCommandproperties toDbCommandobjects that specify the sql directly. This avoids the problems inherent in theCommandBuilder's ability to generate proper sql statements.Update:
There is no way for the CommandBuilder to know that you want to use the default value. All it does is generate an insert statement, if there is a column it is going to generate it for the insert statement. If that column is part of the insert statement, then any value given for that column, even null, will be inserted. The default value is for situations where you do not include it in the insert statement. It does not convert null into the default value, no matter how you try to insert the items, CommandBuilder or not.
Continuing down this path of trying to use the CommandBuilder is only going to give you more grief. It can't even handle a simple join statement in its select clause. It also requires a primary key. If either of these are violated then it cannot generate correct update, insert, and delete statements. Only two providers, Sql Server and Oracle, have ever implemented this class, and the Oracle one is known to have bugs that were never fixed outside of the basic problems mentioned above.
If you used two DbCommand objects, one for selecting and one for inserting, and then looped through the output of the select DbCommand via a DbDataReader, you could easily check for null in that column and provide the default value of zero to the insert DbCommand, since you know what it is. Knowing the rules of the database and using them when necessary is not a violation of any kind of code organization rules. You have to know what is in the database anyways in order to write this kind of code.
If you have sql server 2005 or up, another suggestion is to use the INSERT INTO .. SELECT statement. If your sql is good enough you can use a CASE clause to make a single sql statement.
一个小问题，看似显而易见，但实际上却很棘手。
如果我引用 MSDN 定义 你可能会读到：
我的一个朋友，曾经在一些camses中定义IDENTITY(1, 1)而不是主键。
所以小问题是，您是否在“更新的”表 B 中使用主键？
然后....
要添加信息，SqlAdapter.FillSchema 不使用默认值
以下是 FillSchema 检索到的信息：（
引用自 MSDN网站）
独特
A little question, it may seem obvious, but in fact it's tricky.
If I quote MSDN definition you may read :
A friend of mine, used to define IDENTITY(1, 1) in some camses instead of Primary keys.
So little question is, do you use primary key in your "updated" table B ??
then....
To add informations, SqlAdapter.FillSchema does not use default values
Here are informations retrieved by FillSchema :
(Quote from MSDN site)
Unique