How to insert record into table having identity column

[Sudha]

Hi,
What i need to do is to Insert a record into a primary table and then
insert a corresponding record into the child table.My problem is that the
Primary column is an identity column so after inserting into the primary
table when i need to insert into the child table i need to know the the
primary columns value(which is auto generated by the database) and then use
this value to inert into the child table.Any one who faced this problem
earlier.

As for example how do i insert records into Northwind's Order and [Order
details] table .

Rgds,
Sudha

 

[Anatoly]

http://support.microsoft.com/default.aspx?scid=kb;en-us;320897

 

[Perley]

The short answer is to define the relationship in the
dataset and have the autogenerated value returned from the
insert. If the releationship is correctly defined the
foreign key value will automatically be inserted into the
second table.

 

[Pete Wright]

Perley's answer is almost there. However, the problem you will have with
this is concurrency. Let's say your parent table in the database has one row
in it. You insert a row in your dataset, which is disconnected, and at the
same time another user inserts a row in the actual database. Your dataset
will auto-assign an ID of say 2, but the insert that already took place for
the other user already generated a record with an id of 2.

The way around this is actually quite simple. The stored procedure or sql
statement that you are using to insert records into the table from the data
adapter just needs to ignore the ID column on both parent and child records.
That way, when you do the insert, SQL Server will assign its own correct ID
to the parent record, so all you need to do is a reload of the dataset to be
back in sync.

The problem here though is that you still have no way of knowing what to
assign to the child row's ID. The only solution that I can see here is
either to use a stored procedure (which is able to use @@identity to grab
the identity of the new parent record and assign that to the new child
records within the stored proc).

A far far better solution though to the whole problem is not to use standard
identity columns that increment one by one on each insert. Instead, use
GUIDS. These are globally unique. Using Guids your application needs to
generate the guid in code (there is a framework class for this I think) and
assign it to the parent and child records int he dataset. The guids can then
be sent directly to the database on your inserts without concurrency fears.

Hope that helps (and makes sense).


--
Peter Wright
Author of ADO.NET Novice To Pro, from Apress Inc.


_____________________________

The short answer is to define the relationship in the
dataset and have the autogenerated value returned from the
insert. If the releationship is correctly defined the
foreign key value will automatically be inserted into the
second table.

-----Original Message-----
Hi,
What i need to do is to Insert a record into a primary table and then
insert a corresponding record into the child table.My problem is that the
Primary column is an identity column so after inserting into the primary
table when i need to insert into the child table i need to know the the
primary columns value(which is auto generated by the database) and then use
this value to inert into the child table.Any one who faced this problem
earlier.

As for example how do i insert records into Northwind's Order and [Order
details] table .

Rgds,
Sudha


.

 

[William Stacey]

Couldn't they also use negative numbers in the disconnected keys. That way
you can add new records all day locally without having to refresh after each
add. Then when you send the data in batch to the server, it will replace
the negative numbers with autonums using stored proc.

Perley's answer is almost there. However, the problem you will have with
this is concurrency. Let's say your parent table in the database has one row
in it. You insert a row in your dataset, which is disconnected, and at the
same time another user inserts a row in the actual database. Your dataset
will auto-assign an ID of say 2, but the insert that already took place for
the other user already generated a record with an id of 2.

The way around this is actually quite simple. The stored procedure or sql
statement that you are using to insert records into the table from the data
adapter just needs to ignore the ID column on both parent and child records.
That way, when you do the insert, SQL Server will assign its own correct ID
to the parent record, so all you need to do is a reload of the dataset to be
back in sync.

The problem here though is that you still have no way of knowing what to
assign to the child row's ID. The only solution that I can see here is
either to use a stored procedure (which is able to use @@identity to grab
the identity of the new parent record and assign that to the new child
records within the stored proc).

A far far better solution though to the whole problem is not to use standard
identity columns that increment one by one on each insert. Instead, use
GUIDS. These are globally unique. Using Guids your application needs to
generate the guid in code (there is a framework class for this I think) and
assign it to the parent and child records int he dataset. The guids can then
be sent directly to the database on your inserts without concurrency fears.

Hope that helps (and makes sense).


--
Peter Wright
Author of ADO.NET Novice To Pro, from Apress Inc.


_____________________________

The short answer is to define the relationship in the
dataset and have the autogenerated value returned from the
insert. If the releationship is correctly defined the
foreign key value will automatically be inserted into the
second table.

-----Original Message-----
Hi,
What i need to do is to Insert a record into a primary table and then
insert a corresponding record into the child table.My problem is that the
Primary column is an identity column so after inserting into the primary
table when i need to insert into the child table i need to know the the
primary columns value(which is auto generated by the database) and then use
this value to inert into the child table.Any one who faced this problem
earlier.

As for example how do i insert records into Northwind's Order and [Order
details] table .

Rgds,
Sudha


.

 

[David Sceppa]

Sudha,

This is a fairly common scenario that ADO.NET handles more
elegantly than its predecessors.

On the identity column in the parent DataTable, set
AutoIncrement to True, and both AutoIncrementSeed and
AutoIncrementStep to -1. This will cause ADO.NET to generate
placeholder values of -1, -2, -3, ..., ensuring that the
placeholder values do not match any actual values in your
database. As Pete pointed out, you want to let the back-end
generate the new identity value. However, you can still use
ADO.NET's auto-increment features to generate placeholder values
for your new rows.

As Perley pointed out, the DataRelation is an integral part
of handling the scenario you described. In the Order - Order
Details scenario, you would define a DataRelation between the two
DataTables on the OrderID column. As you add new Orders to the
DataSet, ADO.NET generates placeholder values for the OrderID
column. As you add new Order Details to the DataSet, use the
appropriate placeholder value for OrderID and the DataRelation
will relate the rows.

The DataRelation also simplifies the process of submitting
the pending new rows in both tables. The link that Anatoly
provided in a reply shows how you can generate DataAdapter
updating logic to omit the identity column in the INSERT query
and then fetch the value that the server generated for the new
row. Use a DataAdapter to submit the pending parent rows. If
you configure the DataAdapter to fetch the server-generated value
for the identity column and assign that value to the
corresponding column in the DataRow, the DataRelation will
automatically cascade that value down to the corresponding child
rows. From there, you can safely submit the pending child rows.

I hope this information proves helpful. For more
information see "Retrieving Newly Generated AutoIncrement Values"
and "Submitting Hierarchical Changes" in Chapter 11 of "Microsoft
ADO.NET".

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[David Sceppa]

Well said, William. Between the info you, Anatoly, and
Perley supplied, you have the scenario covered.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[Pete Wright]

heheh glad we could be of help eventually David, and thanks for jumping in
and cleaning up the combined confusion with a wonderfully elegant summary at
the end

--
Peter Wright
Author of ADO.NET Novice To Pro, from Apress Inc.


_____________________________