How do I save changes to a sequential list of names to sql database?

Question

OK, here's the scenario. I think it's pretty common, and I have solved it in a prior life using a brute-force approach, but I'm thinking there has to be a better way.

Given an ordered list of names, and a UI where the user can re-order, delete, edit and add names to the list, how would you update the list stored in a SQL database?

Here's the table definition:

CREATE TABLE [dbo].[AssyLines](
  [AssyLineID] [int] IDENTITY(1,1) NOT NULL,
  [ShortName] [varchar](16) NOT NULL,
  [LongName] [varchar](45) NOT NULL,
  [Seq] [tinyint] NOT NULL,
CONSTRAINT [PK_AssyLines] PRIMARY KEY CLUSTERED 

What I want to do is read all entries from the table and display them in Seq order in the UI. The user can then edit ShortName or LongName, re-order the list, add new entries or delete existing entries, then save the modified list back to the database.

This is a client-server application, if that makes any difference. The WCF service handles the database interaction, the client just ships an array of records to and from the service.

My brute-force approach involves locking the table, deleting all entries, then re-writing the new records out to the table -- all inside a transaction, of course, using a stored procedure and either a table variable or temp table. Crude but effective, yet I can't help feeling there is a fairly standard way to do this without nuking the table and re-creating it every time someone makes a minor edit.

Any ideas? I have 4 or 5 such lists in the application I am currently working on.

Thanks, Dave

Answer 1

Why not just run an UPDATE query for each record (identified by AssyLineID, which shouldn't change) that updates the other columns to what the user specified? No need to delete anything, and you can reduce the amount of DB operations by keeping track of what the user changed.

If you're worried about reordering the Seq part, I think this will work for that:

if (newSeq < oldSeq)
{
    sql = "UPDATE AssyLines SET Seq = Seq + 1 WHERE Seq >= @seq AND Seq < @oldSeq AND AssyLineID <> @lineID";
}
else if (newSeq > oldSeq)
{
    sql = "UPDATE AssyLines SET Seq = Seq - 1 WHERE Seq <= @seq AND Seq > @oldSeq AND AssyLineID <> @lineID";
}

Maybe I don't understand why you feel you need to nuke the table every time. It sounds like your basic select-modify-save operation other than the Seq part.

Answer 2

I'm not a .NET developer, but one more elegant way to do this would be to have 3 listeners on the table; an add listener, an update listener, and a delete listener. The listeners would trigger an INSERT, an UPDATE, and a DELETE, respectively.

Sorting the list has no effect on the data stored on the database.

Answer 3

The approach I would take with this is to use the primary key to tie updates, inserts and deletes to the changes, and make a couple of passes through the data.

1. Load the list from the database into the UI, and store the ID against the item. Also, store a "changed" flag which is initially set to false for every item.
2. Make any changes to the data in the UI like this:
   • Additions to the data by adding them, setting the ID (primary key) to -1, and the changed flag to true.
   • Updates to the data by editing them, setting the changed flag to true.
   • Deletes from the data by just removing them.
3. Iterate through each item in the list where the changed flag=true and perform the following:
   • If ID = -1, then add a new record using INSERT.
   • If ID > -1, then update the existing record matching the ID against the record you want to update using an UPDATE.
4. Then re-read all the ID's of the records that exist in the database, loop through each one and check the list to see if it exists. If not, then delete it from the database using a DELETE.

Step 4 could become quite slow if you have a lot of data. A slightly different more efficient way would depend on if it's possible to 'mark' a record as deleted in the UI and hide it from view without removing it. Then you could replace the changed flag with a status that is either set to 'I', 'U' or 'D' depending on if it's an insert, update or delete. Then you'd only need one pass through the data.