Dealing With an Identity Crisis
Working with identity columns can get complicated when using disconnected DataSets and ADO.NET.
by Bill Vaughn
VSLive! Orlando, September 17, 2002
Note: Bill Vaughn presented the full-day workshop, "Become a .NET Data Access Guru in One Day," on Sunday, September 15, at VSLive! Orlando. This discussion is excerpted from that workshop.
One of the most common questions I see on list servers and newsgroups is how to deal with identity columns. No two rows can have the same primary key, so developers often use identity values to provide this unique value automatically. Developers often need to extract the most recent identity value generated by the server when a new row is added so they can use the value to relate to newly born child rows. For example, when a new parent row is born in the Customers table, its CustID column might be assigned a value of 100. When the application wants to create an order for the new customer, it can use the new identity value (100) as the value in the CustID column in the new row in the Orders table. Make sense? If not, check out the longer discussion in my new book, ADO.NET and ADO Examples and Best Practices for VB Programmers, 2nd Edition. There's even a C# version: ADO.NET Examples and Best Practices for C# Programmers.
When working with disconnected DataSets and delayed updates, as is the norm in ADO.NET, this issue becomes more challenging. For performance reasons, we don't always execute a query to add rows to the Customers table each time a new customer is added, so we have to use some value for the newly added CustID column to continue creating new Orders rows that point back to their parent Customer row. Because a DBMS such as SQL Server creates identity values—such as CustID—for us when the INSERT is executed, we don't have to pass the new identity value as a parameter in the InsertCommand; as a matter of fact, we shouldn't. This means that the values we use before executing the DataAdapter Update method don't matter—as long as they are unique and don't conflict with identity values already in our DataTable.
To solve this problem, you can simply set two or three properties on the Column object associated with the identity column for your table:
AutoIncrement = True. This tells ADO.NET that you want it to automatically manage the value in this column as new rows are added to the local (in-memory) DataTable. Depending on the options you used when building the DataTable, this property might already be set.
AutoIncrementSeed = 0. This tells ADO.NET to begin counting the new identity values at a specific value—in this case, zero. You can start anywhere, but I recommend some value less than 1 to avoid collisions with any identity values currently in the DataTable rowset. Nope, it doesn't matter if these new numbers collide with other DataTables in other applications—they won't be saved to the database.
AutoIncrementStep = -1. This tells ADO.NET to change the automatically generated number by this amount for each new row. In this case, -1 says to make the new numbers larger (in a negative sense). Again, this prevents collision with other rows in the DataTable.
When you finally use the DataAdapter Update method, its SQL should execute INSERT statements to add the new rows—but without the locally generated identity values. They should also return (through a separate SELECT or OUTPUT parameter) the server-generated identity value for the new row. If you use the correct Parameter values (see Listing 1), these new values are posted back to the original DataTable.
I've put together a small sample application that illustrates these points. It's one of the applications available on the conference CD, and it's available for download here. A subset is shown in Listing 1.
About the Author
William (Bill) Vaughn is President of Beta V Corp. He's developed for, taught, and written about mainframe and personal computers since '72, including 14 years at Microsoft. He's the author of several best-selling books on data access. While at Microsoft, Bill taught and wrote courses at Microsoft University, wrote data access documentation for Visual Basic (versions 2-5), was the Enterprise Product Manger for Visual Studio, and spent his last two years training Microsoft developers.
Back to top
|
