ADO.NET CommandBuilder, InsertCommand, and default restrictions

Question

I am copying data from table A to table B. Table B has a zero column, which has a default value of 0. In general, I set the column values ​​using the following accessor.

public object this[string columnName] { get { return DataTable.Rows[CurrentRow][columnName]; } set { DataTable.Rows[CurrentRow][columnName] = value; } } 

But I am NOT setting my column to zero .

When I insert the whole row, the default value is not used. Instead of 0, NULL was inserted for a column with a null value.

 _sqlCommandBuilder = new SqlCommandBuilder(_sqlDataAdapter); _sqlCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges; _sqlCommandBuilder.SetAllValues = false; _sqlDataAdapter.Update(DataTable); 

I also get the circuit:

 _sqlDataAdapter.Fill(DataTable); _sqlDataAdapter.FillSchema(_dataTable, SchemaType.Mapped); 

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

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

Can ADO.NET CommandBuilder use default constraints?

Answer 1

You do not need to use CommandBuilder to implement the update method for the DataAdapter . CommandBuilder is full of problems. You can set the DataAdapter.SelectCommand and DataAdapter.UpdateCommand properties in DbCommand objects that directly specify sql. This avoids the problems associated with the ability of CommandBuilder generate the correct SQL statements.

Update:

CommandBuilder has no way of knowing that you want to use the default value. All he does is generate an insert statement if there is a column that he is going to generate for the insert statement. If this column is part of the insert statement, then any value specified for this 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 the null value to the default value, regardless of how you try to insert elements, CommandBuilder or not.

Continuing this way, trying to use CommandBuilder, you will get more grief. It cannot even handle a simple join statement in its select clause. It also requires a primary key. If one of them is violated, then it cannot generate the correct updates, inserts and deletions of statements. Only two vendors, Sql Server and Oracle, have ever implemented this class, and Oracle, as you know, has errors that have never been fixed outside of the main problems mentioned above.

If you used two DbCommand objects, one for selection and one for insertion, and then looped on the output of the selected DbCommand using the DbDataReader, you could easily check the null value in this column and provide a default value for zero insert DbCommand, since you know , what it is. Knowing the rules of the database and using them if necessary is not a violation of any rules for organizing code. You must know what is in the database in order to write such code.

If you have a sql 2005 server or higher, another suggestion is to use INSERT INTO .. ​​SELECT . If your sql is good enough, you can use the CASE clause to make a single sql statement.

Answer 2

A small question, this may seem obvious, but it's really difficult.

 _sqlCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges; 

If I give the definition of MSDN , you can read:

 "If no PrimaryKey is defined, all searchable columns are included in the WHERE clause." 

My friend used to determine IDENTITY (1, 1) in some cameras instead of primary keys.

So few questions, are you using the primary key in your "updated" table B ??

then ....

To add information, SqlAdapter.FillSchema does not use default values. Here is the information obtained by FillSchema:

 AllowDBNull AutoIncrement.You must set AutoIncrementStep and AutoIncrementSeed separately. MaxLength ReadOnly Unique 

(Quote from MSDN )

Unique