Incremental ID's and adding new records - give me your thoughts
Posted on 2006-04-20
Using .NET 2.0 here.
This is a problem I have had for a year now and never got it properly resolved. I prefer to have all my database records identified by integers rather than GUID's, and have them increment to the next highest value on adding to a database. But there are a couple of issues to this approach that I am wanting advice on, and perhaps some code to supplement.
1). When I open an existing record from, say, my customer list, it opens a detailed form view by calling new formCustomer(CustomerID). I have set the data source for the 'details' form to be that one customer rather than pulling the whole table in, and have done this through a stored procedure called through the customerTableAdapter. Usage: this.customerTableAdapter.FillCustomerForm(this.erpDBDataSet.Customer, formID, "CustomerID");
The form is bound to this via a customerBindingSource. Clicking the 'New' button calls the customerBindingSource.AddNew(). I have set the CustomerID in the Customer DataTable to autoincrement. This is a very handy function, except for one thing - because it was bound to one record, and I have maybe viewed CustomerID 150 out of 200, the ID of the new record is set to 151 instead of 201 because that's all the form can see.
What should I do to retrieve the proper highest value, ie. 201? I was thinking that if I called new formCustomer(0) the form could detect a new record being added and set the BindingSource to pull in every customer. That way it will get the right ID but is very inefficient.
2) The above approach would be very bad if a user clicked New, got 201 assigned to the new record, but if it took them an hour before they saved it - any number of people could have added new customer records before then. When the first person goes to save it, would they not overwrite a record which has just been saved to 201? Therefore would it be better not to assign any record at all until the record is first saved? Or would this be dealt with in some way by the TableAdapter class? If so, how would I go about this.
Or if any of you have better suggestions altogether for this approach of auto-incrementing ID's, that would be even better.