Identity column question

[Tom Archer]

Scenario:

Using DataSet, DataAdapter and CommandBuilder

If I insert a record into an SQLServer table and specify
the column value for a column defined as an Identity
column, the row is actually inserted (correctly) with the
next sequential value regardless of what I pass.

My question is What is doing this? Is the CommandBuilder
seeing the schema and not passing the information or is
SQLServer ignoring the passed value?

 

[Programatix]

SQL Server will generate its own number by incrementing the number from the
last generated number for that table.

 

[Tom Archer]

Thanks.

I know it wouldn't be used regardless as the column is
autoinc, but I'm curious to know specifically if the
CommandBuilder is sending the info to the database server
or not.

 

[Tom Archer]

So then the SQLCommandBuilder is passing the value and it's being
ignored?

I do realize that if I do not pass the value, it will autoinc. I'm just
curious to know if when I specify this value if it's being passed or
not.

Cheers,
Tom Archer - Author
Inside C#
Extending MFC Applications with the .NET Framework

 

[Ogi Ivanov]

I would suggest to run the SQL Profiler and capture the query that is
executed against the SQL Server. That will tell you for sure what values are
passed to the SQL Server. If a column is an Identity Column in SQL Server it
will not allow you to pass a value any way because it will assign the value
automaticaly.

 

[David Browne]

So then the SQLCommandBuilder is passing the value and it's being
ignored?

I do realize that if I do not pass the value, it will autoinc. I'm just
curious to know if when I specify this value if it's being passed or
not.

SQLServer will not allow inserts against identity columns unless you first
"set identity insert on".

It's the SQLCommandBuilder that omits the column. The behaviour you noticed
is there to facilitate the "negative autonumber workaround" for disconnected
autonumber inserts. It works like this.

In your dataset set the identity column to autonumber, seed -1, step -1.
New rows will get negative autonumbers which don't collide with the real
identity values.
On insert the CommandBuilder will omit the identity columns and SQLServer
will assign identity values.

Then you must requery the dataset, or edit the DataAdapter.InsertCommand to
return the @@IDENTITY into your autonumber column (using an output paramater
with a SourceColumn = [identity column]), in order to retrieve the new
identity values.

BTW, I liked "Inside C#".

David

 

[Tom Archer]

This was exactly the level of detail I was looking for. Thanks David!!
Cool. Did you get the first or second edition? (Email me offline if you
want - (e-mail address removed))

Cheers,
Tom Archer - Author
Inside C#
Extending MFC Applications with the .NET Framework

 

[Tom Archer]

I am a little confused about the "negative autonumber workaround". Why
would I need to do this if the SqlCommandBuilder is ignoring my values
anyways and SQL Server is automatically generating the correct value?

Also when inspecting the DataTable (Northwind->Employees), the
EmployeeID column states that it's not AutoInc ?! Yet I can pass any
value in the DataRow item array (including leaving it blank) and SQL
Server will auto inc this column.

Cheers,
Tom Archer - Author
Inside C#
Extending MFC Applications with the .NET Framework

 

[David Browne]

I am a little confused about the "negative autonumber workaround". Why
would I need to do this if the SqlCommandBuilder is ignoring my values
anyways and SQL Server is automatically generating the correct value?

Because your DataTable may have a primary key on the autonumber column, or a
foreign-key relationship refrencing the autonumber column. If so the
DataSet will not let you insert a row with a null or duplicate value in the
field. You must have some value, but since you're disconnected, and the
database will eventually assign the value, you can't assign the final value.
Thus the temporary fake autonumbers.

Also when inspecting the DataTable (Northwind->Employees), the
EmployeeID column states that it's not AutoInc ?! Yet I can pass any
value in the DataRow item array (including leaving it blank) and SQL
Server will auto inc this column.

If you want to use the "negative autonumber workaround", you must set the
column to AutoIncrement yourself, either in the DataSet designer or in code.
If not, then you can just put any old value in the column.
Whatever value you stick in for EmployeeID, the SQLCommandBuilder still
recognizes that EmployeeID is an IDENTITY column and omits it from the
insert statement (check SQLCommandBuilder.InsertCommand.CommandText).

The values only need to be non-null and unique when using DataTable primary
keys or in a DataSet containing multiple related DataTables. For instance
if you added Orders to your DataSet along with Employees, and wanted to
insert a new Employee and enter some Orders for that employee, you would
need to create a temporary EmployeeID and use that in Orders.EmployeeID to
relate the rows. Then after flushing the Employees table back to the
database, you would need to retrieve the permanent EmployeeID and update the
Orders DataTable before flushing it back to the Database. Setting
Employees.EmployeeID to Autoincrement and cascading updates along the
relationship just help to automate this process.


David

 

[Tom Archer]

I don't think I'm explaining what I'm after very well.

Let's say I do the following:

DataRow* newRow1 = employeesTable->NewRow();

Object* values[] = new Object*[3];
values[1] = S"Tom";
values[2] = S"Archer";
newRow1->set_ItemArray(values);

employeesTable->Rows->Add(newRow1);

Note that I'm intentionally leaving the first array element blank. When
I call the adapter's Update method, a new number is automatically
generated for new record's EmployeeId column in SQL Server. Viewing the
Employees table in SQL Server confirms this. This is *exactly* what I'd
expect - and want.

In addition, inspecting the command builder's InsertCommand confirms
that the value for EmployeeId (values[0]) is not being sent. Once again,
this makes sense and what I want.

Only one problem: When I look at the DataColumn::AutoIncrement property,
it's set to False for the EmployeeId. How can that be? At some level the
command builder must know the truth because this column is not included
in the InsertCommand value. Why then does the DataColumn incorrectly
state that the column is not auto-inc?

Cheers,
Tom Archer - Author
Inside C#
Extending MFC Applications with the .NET Framework

 

[David Browne]

I don't think I'm explaining what I'm after very well.

Let's say I do the following:

DataRow* newRow1 = employeesTable->NewRow();

Object* values[] = new Object*[3];
values[1] = S"Tom";
values[2] = S"Archer";
newRow1->set_ItemArray(values);

employeesTable->Rows->Add(newRow1);

Note that I'm intentionally leaving the first array element blank. When
I call the adapter's Update method, a new number is automatically
generated for new record's EmployeeId column in SQL Server. Viewing the
Employees table in SQL Server confirms this. This is *exactly* what I'd
expect - and want.

In addition, inspecting the command builder's InsertCommand confirms
that the value for EmployeeId (values[0]) is not being sent. Once again,
this makes sense and what I want.

Only one problem: When I look at the DataColumn::AutoIncrement property,
it's set to False for the EmployeeId. How can that be? At some level the
command builder must know the truth because this column is not included
in the InsertCommand value. Why then does the DataColumn incorrectly
state that the column is not auto-inc?

The SQLCommandBuilder knows, but it figured it out by examining the
DataAdapter.SelectCommand, and then querying the SQLServer metadata. Not by
looking at the DataTable.

It would be nice if the commandBuilders just used the DataTable's metadata
to construct the insert,update and delete commands, instead of making a
round trip to the database for it, but the dataTable is a little too
abstracted into CLR types to reliably generate DML statements from it's
design.


David