Is there any way to map the returning identity values of a table with the negative placeholders created for the identity values in a datatable when inserting rows?

I have a detail table A in a grid where each record has a detail table B associated with it (in another grid). I am not using any binding code but relating the tables thru code.

When records are added to the main detail table A, the identity primary key is given a negative number as a placeholder. Once I update this table the negative numbers are replaced with the new identity keys created by sql server for the newly inserted rows into that table.

However, I have been saving my second detail table B rows by associating them with the negative numbers from the parent detail table A . So these are the second tables temporary parent keys. I now need to replace these keys with the real parent keys. But the original negative keys from that table are gone. So there is no way to match them.

I could save these negative keys in another non-identity field in the parent detail table Aand then use these to match with the second detail table B.

Or rather than do an update on the main detail datatable, I could go one by one thru the table, inserting each row into the sql server table, retrieving the new identity key and then updating its corresponding detail table B.

Is there another way to do this or are these the alternatives?

Thanks for any ideas on this.

smHaig

The merge-command is super powerful, and does more than one might quickly glean from the documentation.

The key to being a DataSet guru is to thoroughly understand the concept of DataRowState and DataRowVersion.

DataRowState

When you add a row to a DataSet, it is DataRowState.Added.

When you delete a row from a DataSet, it is in DataRowState.Deleted.*

When you modify a row in a DataSet, it goes to DataRowState.Modified.

Unmodified rows are in DataRowState.Unchanged.

*Caveat: If the row you delete was in DataRowState.Added, then it does no go to DataRowState.Deleted but instead is totally removed from the DataSet. This reflects the fact that the row never made it to the database, so there is no SQL delete to be performed. There is also a Remove method that you can use if you ever want to completely remove a row yourself.

DataRowVersion

When you modify or delete a row, the old data is retained. The DataRow.HasVersion and DataRow.Item(DataColumn, DataRowVersion) members let you access this. If you make multiple modifications, only the original old data (which corresponds to what is actually in the database) is what is retained as DataRowVersion.Original.

DataAdapter.Update

When DataAdapter is inserting new rows (DataRowState.Added) and you use a combined INSERT/SELECT statement, the row gains a DataRowVersion.Original containing the data you specified and the DataRowVersion.Current is set to what comes back from SELECT (including the identity value). Thus, when DataAdapter.Update completes, you can access DataRowVersion.Original to see the negative identity and DataRowVersion to see the assigned positive identity.*

When DataAdapter is updating existing rows (DataRowState.Modified)and you use a combined UPDATE/SELECT statement, the old row remains and the new row is altered to contain the data returned by the SELECT part.*

When DataAdapter is deleting existing rows, it doesn't make any changes to the DataSet.*

*However, by default, DataAdapter does an AcceptChanges right before it returns to you. Disable this via DataAdapter.AcceptChangesDuringUpdate. Otherwise, the multi-versions will go away. Once you are done examining the output of DataAdapter.Update, call AcceptChanges yourself. This will make the DataRowState.Deleted records actually go away, etc. You are then free to use that DataSet to perform additional operations on the database.

Merge

DataTable.Merge copies records from one DataTable to another. Here are some important points:

1. By default, all versions of the record are copied, so if a row has a DataRowVersion.Original and a DataRowVersion.Current, it will have these versions in the destination as well. The preservedChanges flag lets you make this work differently.

2. Merge will correlate records by primary key to overwrite a destination record that already exists. If the destination record being replacedhas an Original version, the correlation happens using the Original version of the destination primary key. Sometimes this fact is very important when doing advanced Merges.

Are both tables in the same DataSet? If so, youcan probablydefine aForeignKeyConstraint in the DataSetbetween the two columns. Set the UpdateRule on this object to Rule.Cascade.

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 orstored 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://msdn2.microsoft.com/en-us/library/2hh60x2k(VS.80).aspx(DataSet UI for ForeignKeyConstraint)

http://msdn2.microsoft.com/en-us/library/cs5ze1dx(VS.80).aspx(Rule Enumeration)

If this doesn't do it for you, write back. I think there are lots of options. There are almost definitely ways to avoid the "save these negative keys in another non-identity field" workaround.

No, these are totally separate datasets and they are not strongly typed and are created in code with stored procedures.

I had not been using the additional select statement in the insert stored procedure but had been recalling the select statement. I realize that including the addtional select statement saves a trip to the server. I do this separately though because there is alot of stuff that has to be done before the data is displayed and it just seemed logically easier to run the same procedure that does this elsewhere.

This also would allow me to separately do something about the placeholder identities before refreshing the grid.

If I do include both tables in the same dataset as you suggest,how would this affect, if at all, the use of these 2 tables as datasources for the two grids?

As the user moves down the rows of the main gridA, would the second grid B automatically show records with foreign key equal to the primary key of gridA, i.e. the negative identity keys since these are new records?

Various things also need to be done with thedata of the previous rowwhen a new row is selected on gridA, so I need to be able to access the data in these two tables before the new rowA and its box data appear in the grids. Can this be done if a foreignkeyconstraint is set in the dataset between the primary key and the foreign key of the tables?

To simplify this, I had separated the adding of the box grid from that of the item details. Then once all item details were saved to the server, inserting into the box grid, where applicable, could occur easily since I have the foreign keys now.

Even if your data is in separate DataSets, you can always use the Merge methods on DataSet/DataTable to shuffle the data around, for example to move it in and out of "one big" DataSet. Given how fast computers are, this generally does not perform as bad as it sounds.

Placing both of the tables in the same DataSet should not affect the user interface. Whether it automatically filters the detail grid based on the chosen master record depends on how you set up the DataSource/DataMembers and your BindingSource objects (if you are using BindingSource).

If you don't do the SELECT in the same statement as the INSERT, you're going to have more work to do. Have you investigated loading DataAdapter.AcceptChangesOnUpdate?If you change this to False,.NET will preserve both the "old" and "new" DataRowVersion (before and after it gets assigned the new IDs from the database server). You can later do a DataSet or DataTable.AcceptChanges to discard the old versions from memory, once you've done all the processing that you need regarding those values.

If any of these ideas sound promising and you need more information on them, please post.

I looked at the merge command. It seemed at first that what I could do was to have one big box dataset as you suggestand then create subsets of this to make up an independent boxgrid datasource (which would be empty if I was inserting new records). Then when the user went to the next row of the main Grid A, I could use merge to update the big box dataset from the separate one I used as gridB datasource -- which only holds child records for one record from gridA.

But this was only if merge actually would add the new box records, change the existing ones (including deteting them) in the main box ds holding all records. I am not sure whether the merge has this super strength from my reading. Also if this would work with the negative place holders.

The only other alternative I saw was doing a check in code and reconciling this big box ds which then would be what would be updated to the server.

I have never used bindingsource because it seems that if you want to get inside and do something to the data before a row changes or in between binding to an object,it is difficult to do so.if it is easiest way tothis, I will have learn how to do it.

I looked up AcceptchangesDuringUpdate (is it the same) on MSDN and it seems exactly what I need.

I have been time trying to figure the best approach. I came across datarow.getchildrows and I am wondering if this would be a way to gather all the boxrowsfor the selected row in grid A for the boxgird. I looked at examples but this was not clear.

Thanks for all of this. You have given me some great leads.

The merge-command is super powerful, and does more than one might quickly glean from the documentation.

The key to being a DataSet guru is to thoroughly understand the concept of DataRowState and DataRowVersion.

DataRowState

When you add a row to a DataSet, it is DataRowState.Added.

When you delete a row from a DataSet, it is in DataRowState.Deleted.*

When you modify a row in a DataSet, it goes to DataRowState.Modified.

Unmodified rows are in DataRowState.Unchanged.

*Caveat: If the row you delete was in DataRowState.Added, then it does no go to DataRowState.Deleted but instead is totally removed from the DataSet. This reflects the fact that the row never made it to the database, so there is no SQL delete to be performed. There is also a Remove method that you can use if you ever want to completely remove a row yourself.

DataRowVersion

When you modify or delete a row, the old data is retained. The DataRow.HasVersion and DataRow.Item(DataColumn, DataRowVersion) members let you access this. If you make multiple modifications, only the original old data (which corresponds to what is actually in the database) is what is retained as DataRowVersion.Original.

DataAdapter.Update

When DataAdapter is inserting new rows (DataRowState.Added) and you use a combined INSERT/SELECT statement, the row gains a DataRowVersion.Original containing the data you specified and the DataRowVersion.Current is set to what comes back from SELECT (including the identity value). Thus, when DataAdapter.Update completes, you can access DataRowVersion.Original to see the negative identity and DataRowVersion to see the assigned positive identity.*

When DataAdapter is updating existing rows (DataRowState.Modified)and you use a combined UPDATE/SELECT statement, the old row remains and the new row is altered to contain the data returned by the SELECT part.*

When DataAdapter is deleting existing rows, it doesn't make any changes to the DataSet.*

*However, by default, DataAdapter does an AcceptChanges right before it returns to you. Disable this via DataAdapter.AcceptChangesDuringUpdate. Otherwise, the multi-versions will go away. Once you are done examining the output of DataAdapter.Update, call AcceptChanges yourself. This will make the DataRowState.Deleted records actually go away, etc. You are then free to use that DataSet to perform additional operations on the database.

Merge

DataTable.Merge copies records from one DataTable to another. Here are some important points:

1. By default, all versions of the record are copied, so if a row has a DataRowVersion.Original and a DataRowVersion.Current, it will have these versions in the destination as well. The preservedChanges flag lets you make this work differently.

2. Merge will correlate records by primary key to overwrite a destination record that already exists. If the destination record being replacedhas an Original version, the correlation happens using the Original version of the destination primary key. Sometimes this fact is very important when doing advanced Merges.

From what you are saying it seems that I can create a dataset with 2 datatables (master for gridA and detail for gridB-boxgrid). I can create a relation object too, though now sure if I need this.

Then as I go from row to row on the mastergrid, I can create a dataview and using dv.totable, to create a sub table as the datasource for gridB. Then I can do whatever I which with data entered into the detail grid, total, change text objects, etc.

When I more to a new row on the master table, I think, from what you say, that I can use the merge command to merge the additions and modifications from the sub table back to the detail table. And I presume that merges can be made many times on the main detail table and each one will change whatever currently exits in the main table.

However, first, what about deletions that are made to the rows on the grid. I cannot see where merge handles this.

Also, I read that the rowstate of the detail table after the merge will be markedunchanged, so it presents the problem of how to save the detail table changes back to the server. I looked at the preservechanges argument of the merge set to true but it was not clear if that would help.

So the question is whether the merge will handle deletions and how to proceed once merges are made to the main detail table.

Thank you so much for this information, it is really helping me to understand what needs to be done.

Please let me know where to have ti special treatement, is the Skipcurrentrow will do the same?