case式を使って条件分岐するupdate文を試す （ドラッグ&ドロップで並び替えする際のデータの更新を1つのupdate文で）

gistfile1.sql

create table #Sortable(
	Name nvarchar(5),
	Ordinal int,
	constraint UQ_Ordinal unique(Ordinal));
insert into #Sortable
values
	('Item1', 1),
	('Item2', 2),
	('Item3', 3),
	('Item4', 4),
	('Item5', 5);
select * from #Sortable order by Ordinal;
/*
Name  Ordinal
----- -----------
Item1 1
Item2 2
Item3 3
Item4 4
Item5 5
*/


-- Item4をItem2の位置にドラッグ&ドロップしたとする
declare @from int = 4;	-- 移動するItemのOrdinal
declare @to int = 2;	-- 移動先のItemのOrdinal


update #Sortable
set Ordinal = case
	-- 移動するItem->移動先のOridnalを
	when Ordinal = @from then @to
	-- 移動元と移動先の間のItem
	when @to < @from then Ordinal + 1	-- 上方向への移動->Ordinalを+1
	else Ordinal - 1 end			-- 下方向への移動->Ordinalを-1
where
	-- 移動元と移動先の間にあるItemのみを対象
	Ordinal between (case when @to > @from then @from else @to end)
		and (case when @to > @from then @to else @from end)
select * from #Sortable order by Ordinal;
/*
Name  Ordinal
----- -----------
Item1 1
Item4 2
Item2 3
Item3 4
Item5 5
*/


drop table #Sortable;

Test.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Dapper;

namespace UnitTest {
	public static class AssertHelper {
		public static void IsSequenceEqualTo<T>(this IEnumerable<T> first, IEnumerable<T> second) {
			Assert.IsTrue(first.SequenceEqual(second));
		}
	}

	[TestClass]
	public class Test {
		private readonly string _connectionString =
			new SqlConnectionStringBuilder {
				DataSource = ".",
				InitialCatalog = "tempdb",
				IntegratedSecurity = true,
			}.ConnectionString;

		private SqlConnection _connection;


		[TestInitialize]
		public void Prepare() {
			_connection = new SqlConnection(_connectionString);
			_connection.Open();

			_connection.Execute(@"
create table #Sortable(
	Name nvarchar(5),
	Ordinal int,
	constraint UQ_Ordinal unique(Ordinal));");

			_connection.Execute(@"insert into #Sortable values(@name, @ordinal);",
				Enumerable.Range(1, 5)
					.Select(index => new { name = "Item" + index.ToString(), ordinal = index }));
		}

		[TestCleanup]
		public void Clear() {
			_connection.Execute(@"drop table #Sortable;");

			_connection.Close();
		}

		private IEnumerable<string> GetNames() {
			return _connection.Query<string>(@"select Name from #Sortable order by Ordinal;");
		}

		private void SwapItems(int from, int to) {
			_connection.Execute(@"
update #Sortable
set Ordinal = case
	when Ordinal = @from then @to
	when @to < @from then Ordinal + 1
	else Ordinal - 1 end
where
	Ordinal between (case when @to > @from then @from else @to end)
				and (case when @to > @from then @to else @from end);",
				new { from, to });
		}

		[TestMethod]
		public void TestDefaults() {
			GetNames().IsSequenceEqualTo(new[] { "Item1", "Item2", "Item3", "Item4", "Item5" });
		}

		[TestMethod]
		public void Test4to2() {
			SwapItems(4, 2);
			GetNames().IsSequenceEqualTo(new[] { "Item1", "Item4", "Item2", "Item3", "Item5" });
		}

		[TestMethod]
		public void Test2to4() {
			SwapItems(2, 4);
			GetNames().IsSequenceEqualTo(new[] { "Item1", "Item3", "Item4", "Item2", "Item5" });
		}

		[TestMethod]
		public void Test1to5() {
			SwapItems(1, 5);
			GetNames().IsSequenceEqualTo(new[] { "Item2", "Item3", "Item4", "Item5", "Item1" });
		}

		[TestMethod]
		public void Test5to1() {
			SwapItems(5, 1);
			GetNames().IsSequenceEqualTo(new[] { "Item5", "Item1", "Item2", "Item3", "Item4" });
		}
	}
}