2

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

DaveN59
  • 3,638
  • 8
  • 39
  • 51
  • Forgot to mention, the Seq value is supplied by the UI. Basically, I load a listbox from the incoming array, sorted in Seq order. On save, I read the listbox list back into a new array, incrementing the Seq value as I go, then save the array. – DaveN59 Feb 11 '11 at 16:44
  • are you able to change your table design? this sounds like a linked-list structure more than what you are showing. – Randy Feb 11 '11 at 16:49
  • Absolutely I can change the design. The project is still in its infancy, and I control both the database and the application. Please explain how that could work... – DaveN59 Feb 11 '11 at 16:53
  • Is there a flavor of Data Access your prefer, Classic Ado.Net, nHibernate, Linq to SQL, Entitiy Framework? Also you should probably edit your tags to include .NET and I'm guessing SQL-Server. (This really isn't a just SQL question) – Conrad Frix Feb 11 '11 at 17:04
  • Added SQL-Server and .NET tags, thanks. I am using Mindscape LightSpeed (LS) as the ORM, which incorporates a lot of Linq-to-SQL, but I was thinking this was a more generic problem and didn't want to have people look at that and avoid the question based on unfamiliarity with LS. – DaveN59 Feb 11 '11 at 17:11

3 Answers3

0

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.

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
  • That might work except for the client-server thing. By the time I'm hitting the database, I'm working with an array of records... – DaveN59 Feb 11 '11 at 17:52
  • The server will process the listeners in the order in which they are fired. Yes, there will be a lag before the state of the database matches the state of the client. – Gilbert Le Blanc Feb 11 '11 at 17:55
0

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.

Justin Morgan - On strike
  • 30,035
  • 12
  • 80
  • 104
  • I think that might work, with the exception of records the user deleted. For new records I can replace the UPDATE statements with INSERT statements, but I still need a way to figure out what records may have been deleted... putting my magic thinking cap back on. – DaveN59 Feb 11 '11 at 17:28
  • @DaveN59: Keep track of the original list you displayed. That will tell you which records were changed/deleted, and also tell you which records are brand new. If you're using ASP.NET, you can store it in the ViewState. – Justin Morgan - On strike Feb 11 '11 at 17:33
  • @DaveN59: You can also add event handlers to track changes your user makes. This might be a better way, but just make sure you catch them all. – Justin Morgan - On strike Feb 11 '11 at 17:36
  • It seems the fact I am using an ORM does make a difference. I am marking this as the answer because the ideas here led me to the solution that is going to work. Using the ORM I can read the "old" list and compare, update and delete from that list based on the "new" list, then save the changes back to the database with most of the detail work done for me. Thanks for the brainstorming! – DaveN59 Feb 11 '11 at 17:58
  • You could also use a MERGE statement. – HLGEM Feb 11 '11 at 18:10
0

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.

BG100
  • 4,481
  • 2
  • 37
  • 64