Stored Procedures for DAAB's SqlHelper.UpdateDataset

The Microsoft Data Access Application Block (DAAB) provides a great way to keep .NET DataSet's synchronized with a SQL Server database without tons of code. However, there are a few gotchas, etc. along the way. One of the hurdles is how to persist a new DataSet row (DataRow) to the backend. In particular, if you are using an identity column as the primary key, you'll run into a few problems:

• You can't add a row to the DataTable with a null value for the identity column (since you should have set the DataTable's PrimaryKey to the identity column)
• Using a flag value (e.g., -1) for the new row's identity column works fine until you try to add more than one row. (An exception occurs announcing a primary key violation due to the 2nd new row's identity column being -1)
• Trying to manually update the persisted new row's identity column in the DataSet, although a good first guess, is not a good way to go

The big trick for inserts is to structure the stored procedure (sproc) to update the data, and then return the entire row for the updated item. (Just returning the new identity value doesn't suffice). Consider a simple table: Phonelist (EntryId, Name, PhoneNumber). This table contains three columns:

1. EntryId -- an auto-generated identity column for uniqueness.
2. Name -- a person's name
3. PhoneNumber -- the person's phone number

An appropriate sproc for adding new rows to this table would look like this:

create stored procedure Phonelist
   (@name varchar(50), @phoneNumber varchar(15))
as
   insert into Phonelist (Name, PhoneNumber)
      values (@name, @phoneNumber)

select EntryId, Name, PhoneNumber
   from Phonelist
      where EntryId = @@IDENTITY
return

Notice that each of the columns used to originally populate the DataTable in the DataSet are selected and returned filled with the data just inserted (including the auto-generated identity column). Once you set up the correct SqlCommands for each of the three types of operations (insert, delete, update), then just call SQLHelper.UpdateDataset. The underlying DataSet will auto-magically populate the identity column for the new row based on the row returned from the sproc. Very nice!

One last point: Don't forget to grant Execute permission to the appropriate user account (i.e., \ASPNET).