Swapping record order during runtime

Built in ASP.NET, C#, using SQL and Linq...

I want to be able to let a user view images and image headings in a grid, and have up and down arrows where they can click to move an image record up or down. There is a "ShowOrder" field in the SQL database that indicates the sort order of the images.

So... in a nutshell, the users sees a grid with the images, clicks the up arrow for the last image, and the images swap the values in the "ShowOrder" field, the new values are written to the database, and the grid is refreshed reflecting the changes. The up arrow on the first record, and the down arrow on the last record should be disabled.

Any routines that come to mind?

Thanks in advance for any help...
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
The OnRowDataBound event would be able to handle hiding the up and down arrows. You would use the e eventarg (which I believe is of the flavor RowDataBoundEventArg) and has a property for the row index. If the row index is 0, hide the up arrow. If the row index = the max number of rows - 1, hide the down arrow.

For handling moving the rows arrow, there you are probably looking at the RowCommand event.
IntelOneAuthor Commented:
Any thought on the best way to swap the records via the "ShowOrder" field in the database?
I was thinking of something like this...
Say a record was being moved up, setting the the current "ShowOrder" value of the record being moved to whatever it's "ShowOrder" value is -1, and the record being moved down to it's "ShowOrder" value +1, then writh back to the database.
Shaun KlineLead Software EngineerCommented:
If you are keeping the ShowOrder number in the grid via a hidden column or field, you can just swap the values between the two rows that are being changed. If you are not keeping the value in the grid, I would suggest having the database swap the values via a stored procedure.

The reason I suggest the swap technique is that you do not have to worry about gaps that may occur when rows are deleted. For example, if you have three rows with ShowOrder values of 1, 2, and 3 and row 2 is deleted, you now have ShowOrder values of 1 and 3. If you were to just add/subtract 1, you would end up with two rows with ShowOrder numbers of 2 and 2.

Another benefit of doing this is that if you are using an identify field, on an insert, you can put the identity value in the ShowOrder field and it will make the new row the last row.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

IntelOneAuthor Commented:
I don't think I can use the identity field, as I don't want to swap them as they are tied to other records such as the user table via relationships. I would like to reorder them if one is deleted, so if there is 1-10, and one is deleted, the "ShowOrder" field needs to be 1-9. The "ShowOrder" field is a "required", "Unique" int field.
I'm assuming I have to do this in the grid and then write the changes to the database.
How do you suggest the swap code work? It would have to identify the current row, and the row above or below depending on whether the UP or DOWN button was clicked before swapping the values of the "ShowOrder" field.
Any code samples would be appreciated.
IntelOneAuthor Commented:
Another thought...
Will swapping the field values be a problem with the "Unique" requirement? If so, I may have to write a temp value to one of the records so the other record will accept the value and then change the temp value to what it needs to be.
Shaun KlineLead Software EngineerCommented:
With reference to the Identity field, I meant that you could store the value in that field in the ShowOrder field as well. Because your ShowOrder field is required, that would not work as you wouldn't have the identity value until after the insert occurred.

Back to the issue at hand. Because the ShowOrder field is set as unique, it will make the solution slightly more complex. (It will require three updates instead of two.)

For simplicity's sake, let's call the row where the arrow was clicked the target row, and the other row that will be changed the affected row. To perform the swap, you will need to need to store the ShowOrder value of both of the rows in variables. You then update the target row to a value that would not be in the ShowOrder field, say -1. Then, update the affected row's ShowOrder value with the original ShowOrder value from the target row. Finally, update the target row's ShowOrder value with the original ShowOrder value from the affected row. Note that when I say update, that's a database call. (Actually, that's pretty much what you said in your last comment.)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IntelOneAuthor Commented:
I just went back and checked the database, and I removed the "Unique" requirement, as that would make this easier.
So basically, to move the record, store the "ShowOrder" value of the Target row to a var, copy the Affected row "ShowOrder" value to the Target Row, put the value of the Var into Affected row, update the database.
It seams like this would be good functionality to build into a grid, you would just chose the "ShowOrder" row in the Grid properties and it would handle the rest.
Thanks for all the time, I upped the points.
Have a great weekend...
IntelOneAuthor Commented:
Great Help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.