Dynamic SQL insert, varied number of params

[LoveToCode]

I have a table that looks like this:

ItemID int
SortOrder int

I am using ASP.NET (C#) to update a table, with any given number of rows, to reflect a sort order specified by the user. The problem is if I use a function like this:

UpdateItem(ItemId, SortOrder) {
  SQL = "UPDATE theTable (SortOrder) VALUES (@SortOrder) where ItemID = @ItemID";
}

and there is 100 items, to call this function over and over it is redundant and obviously there is a much better way to do it.

How can I do a bulk update, without knowing the exact number of params I will need at run time? Can I pass an array or hashtable to SQL or something like that?

Thanks,

-- jenni

 

[AlexCuse]

I do not think that what you have there would work (but I don't really understand what your theTable exists for either - why are you changing the sort order of data in a table, rather than just changing how it comes across in the query to display it?)

You should look into using a stored procedure that accepts a comma-separated (or separated by anything else) string from your app as a parameter. Then you build this string in your app and pass it in. Stored procedure could split the string and build you an appropriate order by clause using dynamic SQL. (I think you only need a select, but if you need to change the natural sort order of the table you will need a more than that).

If you could post back with more on your problem, that might help.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Flofy]

What version of SQL Server are you using? and what version or ASP?
And can you please show me an example of the result that you were expecting with that query to be honest I didn't understand it very well.

 

[LoveToCode]

Sorry - Please let me explain what I'm trying to do.

Here is a link to the page; it might explain it a little better:

http://wisetopic.com/SETtings/music/temp_songs.aspx

There is a list of songs, you can drag and order them. The list is dynamically generated from a database table. When you clikc "Save" it creates an array to with the songId, and the index of the song in the array. I need to record this ordering information into the db table that contains this list.

There is a column next to the songId called SortOrder. I figured I can store the index position there, then when I get the list, just order by SortOrder to have a properly ordered list.

Like I said, saving each song's data one at a time seems like bad practice to me. I'd like to do it all in one query.

Thanks!

 

[LoveToCode]

BTW -- Its SQL 2005 with ASP.NET 2.0.

 

[rac2]

I think AlexCuse has a good approach to a solution to this problem. So far as I know SQL Server does not accept an array as an argument for a stored procedure. So building the comma delimited string gets the data into a single argument.

Writing code to string-ify, then de-string-ify the order will be a pain. So much so that I have always decided to execute an UPDATE for those items that have new sort order values. Realistically, who is going to re-order 100 items on a list then click submit. It wont happen too often, I dont believe.

I imagine you are working on something like Netlfix allows me to re-order my queue. I actually do have six-dozen movies in my queue. When I re-order them, it is usually to move one to the top of the list. Sometimes, I re-order the whole list to get variety because we have added a bunch in the same category. The nice thing about the Netflix process is that it does a reasonable jog of re-ordering even when I have entered the same value for more than one movie. Something to think about as you work this out.