I have an ASP.NET driven CMS application I developed for a small online retailer of kids t-shirts.

The CMS attempts to use an ADO.NET dataset to duplicate the functionality of the underlying database. The database has 2 tables:

    Categories
    Products

Pretty standard, huh? The primary key in Categories is called pkCategoryID, which has a foreign-key relationship to a field in the Products table called fkCategoryID (I like to use the pk and fk prefixes, I'm not sure why).

Anyway the dillema is that the CMS allows my client to Add new categores and products, make edits and do deletions, all using a dataset stored in the Session object. Then, when they're all done they click the 'Save All' button, which then updates the changes/deletions/additions in the dataset back to the underlying database.

This worked great until my client added a new Category and then deleted it.

Now when she adds a new Category, the dataset thinks the pkCategoryID should be 9 (there were 8 in the database already). But the database itself will ultimately wind up using a pkCategoryID value of 10, because once you delete a row with an identity value, it doesn't repeat that value, but goes onto the next one.

Does that make sense: If you add a row, and the ID is X, and then you delete it, the next time you add a row, the ID does not come up as X, but X+1. But my dataset does not seem to know about this behavior.

Then, when my client then adds a Product to that Category, it winds up with a fkCategoryID value of 9, which is what the dataset expected, rather than the 10 that the database wound up actually using. The end result is that my client's customers click on a Link to Category 9, and then they get a blank screen, because the Products are actually coded to Category 10.

So far what I've been doing is manually changing the fkCategoryID values of the new products to the correct CategoryID, but that's a drag. I've also asked my client to (for now) add the Category, then click 'Save All', then add the Products, then click 'Save All' again. But this makes me look foolish.

Is there a dataset-enabled solution to this? A way for the dataset to know what the next identity value WILL be? Or is there a way to tell my database (SQL Server 2000) to start recylcling old identity values? Or is the answer to just not use a dataset for additions, but execute them directly using a SQL command?

Thanks for your help!

Aaron

If the application functions thru the dataset for all its changes, then it may stand to reason that a suitable solution would be to not use an identity column in the database. If the DataSet can perform the incrementing of the column value then let it and just save whatever it comes up with to the database.

This solution aside, your methodology could run into problems. What happens if two people are working on the same system simultaneously? Couldn't you run into a situation where both will add a new category, the DataSet generates a new ID for them and that ID will be the same for both because they were both working from the same original data from the database? This situation might not be much of a concern if you know you'll only have 1 person doing these types of updates. Just something to consider that might support the argument of making updates to data directly against the database as the user works to avoid this and the original problem.

-Peter

Thanks Peter,

That makes a lot of sense. In this particular case, only one person will ever be using the CMS at any time. However, as I would like to recycle my code in the future, I should follow one of your suggestions.

Which approach is considered better?

BTW Is there really no way to get the dataset to replicate the behavior of the ID column completely, including skipped values?

Thanks again.

Aaron

I do database inserts by direct insert queries so I haven't worked with the dataset "add new" functionality. Due to this I'm not familiar with what the dataset class is capable of doing with regards to auto incrementing columns.

-Peter

The dataset can duplicate almost, but not quite all, of the behaviors of the database, including auto-incrementing columns. You can set this behavior up manually, or you can get it directly from the database by using the Dataset.FillSchema method.

The functionality that it doesn't duplicate is when you delete a record (specifically the last record) from your table that has an auto-increment field. If you had 17 rows, and the value of your Auto-increment field was 17, after you delete it, the next time you add a row that field will not re-use the value of 17, but skips it and goes on to 18.

I'm not sure why this behavior exists at all, frankly, and I don't blame the designers of the dataset for not duplicating it. But it does create a challange when dealing with related tables in a disconnected environment.

Aaron