ADO.NET CommandBuilder的，InsertCommand并将默认约束 [英] ADO.NET CommandBuilder, InsertCommand and Default Constraints

问题描述

我是从表A中的数据复制到表B. 表B有一个可空列有一个默认约束值为0。 总的来说，我用下面的访问设置列的值。

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; }
}

但我的不可以设置我的可空列的X.

But I do NOT set my nullable column X.

当我插入整行，不使用缺省值。而不是0，插入了NULL的空列。

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);

为什么ADO.NET设置为NULL我的字段名column X虽然我没有设置呢？

Why is ADO.NET setting NULL for my column X although I did NOT set it?

我认为当我不设置列X的值，ADO.NET会从给定的约束条件的默认值。

I thought that when I do not set the value of the column X, ADO.NET gets the default value from the given constraint.

时的ADO.NET CommandBuilder的能够使用默认约束？

Is the ADO.NET CommandBuilder able to use default constraints?

推荐答案

您不需要使用 CommandBuilder的来实现更新的方法为你的 DataAdapter的。该 CommandBuilder的充满了问题。您可以设置 DataAdapter.SelectCommand 和 DataAdapter.UpdateCommand 属性的DbCommand 直接指定SQL对象。这样就避免了固有的 CommandBuilder的的能力产生正确的SQL语句的问题。

You do not need to use a CommandBuilder to implement the update method for your DataAdapter. The CommandBuilder is full of issues. You can set the DataAdapter.SelectCommand and the DataAdapter.UpdateCommand properties to DbCommand objects that specify the sql directly. This avoids the problems inherent in the CommandBuilder's ability to generate proper sql statements.

更新：

有没有办法让CommandBuilder的知道你要使用的默认值。它所做的就是产生一个INSERT语句中，如果有一列是要生成它插入语句。如果该列的INSERT语句的一部分，再给予该列的任何值，甚至空，将被插入。默认值是情况下，你不包括在插入语句。它不转换成空的默认值，无论你如何试图插入项，CommandBuilder的还是不行。

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.

继续向下试图使用CommandBuilder的只会给你更多的悲伤这条道路。它甚至不能处理一个简单的连接语句的SELECT子句。这还需要一个主键。如果这些都侵犯则不能产生正确的更新，插入和删除语句。只有两个供应商，SQL Server和Oracle，曾经实现了这个类，以及Oracle一个已知有臭虫从来没有固定的上述基本问题之外。

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.

如果您使用了两个的DbCommand对象，一个选择，一个用于插入，然后通过选择的DbCommand通过的DbDataReader输出循环，你可以很容易地检查空该列，并提供了默认值零到插入的DbCommand，因为你知道它是什么。了解数据库的规则，并利用他们在必要时并不违反任何形式的code组织规则的。你要知道什么是数据库反正才能写出这样的code。

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.

如果您有SQL Server 2005或起来，另一个建议是使用的 INSERT INTO ..​​选择声明。如果您的SQL足够好，你可以使用 CASE 条款作出单独的SQL语句。

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.

这篇关于ADO.NET CommandBuilder的，InsertCommand并将默认约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！