Insert new master/details rows

Question

Gentle reader,
here is my today's trouble:

I have two tables in a dataset
Customers (int ID primary key, ...)
Orders (int ID primary key, int IDCust foreign key, ...)

the SQL Server fields for primary keys are set as autoincrement fields.

If I want to insert a new row in the Customers table, everything works fine. In this case, setting the ID field is useless, because it is changed by the server when the CustomerTableAdapter.Update method is called.

But,
if I want to add a new row in the Orders table, I can omit the ID field as before, but which IDCust do I have to use??????

Here is my question:
do I have to make it a two step process? First updating the Customer row, then getting the Customer ID, then create the Orders row, then again updating the Order row.
If this were the case, it would be a big limit for my work: I cannot restore the previous situation if the user decide to abort the changes instead of committing, like all other ONE TABLE CHANGES I experienced before.
I mean: I would like to make all the changes to the dataset and then decide to commit or abort in one step, but if I need to update in order to build data in relation-connected tables, this is not possible.

Is there a way to get the next autoincrement number by the server and book it somehow?

I'm a newbie here, and would like to understand the common how to in this situation.

Help me if you can.
Thanks.

---

------- Life is what happens while doing other projects -------

Answer 1

The DataSet is able to take care of this for you.  It's just a little non-obvious.

First, for your autoincrement fields, in the DataSet designer I would highly recommend that you set the AutoIncrementSeed and AutoIncrementStep both to -1 (negative 1).  This will keep it clear which IDs are temporary and which ones are actually in the database.  You might be doing this already.

Make sure that you have defined both a DataRelation with ForeignKeyConstraint line between parent Customers (IDCust) and Orders (IDCust) in the designer.  Set the UpdateRule on this object to Rule.Cascade.  This is critical as the first Update on the Customers table will, in the process, obtain the ID from the database into the Customers DataRow and cascade it into the child Orders DataRow(s) in the DataSet automatically.

In order for this to work correctly, your INSERT routine must actually do a SELECT that reports back the inserted ID.  In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate.

For example, INSERT is actually an INSERT, a semicolon, and then a SELECT.  Here is an example of wizard generated SQL for INSERT:

INSERT INTO [dbo].[Test] ([MyCol1], [MyCol2]) VALUES (@MyCol1, @MyCol2);
SELECT TestID, MyCol1, MyCol2 FROM Test WHERE (TestID = SCOPE_IDENTITY())

http://msdn.microsoft.com/en-us/library/2hh60x2k.aspx (Foreign Key Constraint Dialog Box)
http://msdn.microsoft.com/en-us/library/cs5ze1dx.aspx (Rule Enumeration)

> I would like to make all the changes to the dataset and then decide to commit or abort in one step

This always requires you to perform the commands within a transaction, such as an SqlTransaction or a TransactionScope.  Otherwise the granularity will be one row at a time.  This is how SQL Server works.

Answer 2

OK. This method you proposed is straight.
But,
is it possible to work without SQL code , without connecting to the underlying database ?
Can I achieve my goal by using instead ADO.NET objects like DataRow, DataTable and DataSet?

Is it possible to insert two brand new rows in two tables, connected by a DataRelation, and after that, have them correctly connected (primary key vs. foreign key) when the update method is called? This would be more elegant, IMHO.

For instance, I noticed the method SetParentRow....... may it be useful?

Thanks.

---

------- Life is what happens while doing other projects -------

Answer 3

I'm confused.  You naturally need to a connection to the database as you are updating data in the database.

> Can I achieve my goal by using instead ADO.NET objects like DataRow, DataTable and DataSet?

In what way has my response gone beyond these things?  (Other than admittedly the SqlTransaction/TransactionScope.  But these are necessary if you want to do all of the updates as one unit.)

Answer 4

I go and explain:
your response has gone beyond my limits when you say:

In order for this to work correctly, your INSERT routine must actually do a SELECT that reports back the inserted ID.  In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate.

For example, INSERT is actually an INSERT, a semicolon, and then a SELECT.  Here is an example of wizard generated SQL for INSERT:

INSERT INTO [dbo].[Test] ([MyCol1], [MyCol2]) VALUES (@MyCol1, @MyCol2);
SELECT TestID, MyCol1, MyCol2 FROM Test WHERE (TestID = SCOPE_IDENTITY())

I just want to use ADO.NET object, not any SQL command or stored procedure or istant connection to the db.
I want that all changes are only in the local datatables and if I want (only if) they can be committed.

I'm working on it.

See you.

---

------- Life is what happens while doing other projects -------

Answer 5

For people that are using oracle the following thread has another solution to autoincrementing columns as well.

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/fbfdc09b-41bf-4290-9dea-6a9a85d5becf

Thanks
Chris Robinson
Program Manager - DataSet

---

This posting is provided "AS IS" with no warranties, and confers no rights.