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

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

Solution

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.