William (Bill) Vaughn's Musings

I’ve written any number of articles on the CommandBuilder and included a volume of information about it in my books. These detail the cornucopia of issues raised by the CommandBuilder including the inability to return a generated Identity column value. Did your solution address this issue? Well, I expect that it has not. Consider that if you don’t fetch the Identity value just as it’s created, another user can step in and create another Identity value so the one you fetch an instant later is not correct.
Ideally, as I describe in my article on managing identity columns (see http://www.betav.com/Files/Content/whitepapers.htm) you need to combine the INSERT query with a SELECT query to insert the row and return the latest Identity value. In my new book (still in edit), I discuss a number of techniques that eliminate the need for this approach or any approach that requires clients to fetch newly created Identity value after an insert. Basically, these techniques are patterned after age-old paper-based systems. One technique that I call the “waitress order-book” system has clients fetch “blank” order forms that have the Identity values “pre-printed” (already imbedded in the row). This means that the client requests a block of pre-populated rows and simply updates these rows with new customer data. Because these rows are “owned” by a specific client, updates can’t collide with other parallel operations.
As I said, these techniques are discussed in more detail in my new book.