前两天需要实现一个上移下移功能,就上网上搜了一下,没想到这个简单的小功能也没有搜到特别理想的结果,只有自己动手丰衣足食了,虽然简单,但是希望对大家有所帮助。
功能:实现任意有排序字段表单的上移和下移,并可以按分类来操作排序。
首先是存储过程:写的稍微有些繁琐,可能是半夜太困了吧
1
ALTER
PROCEDURE
[
dbo
]
.
[
Proc_Common_UpDown
]
2
3
4 @Sign int = 0 , -- 0: 上移 1:下移
5 @TableName nvarchar ( 50 ), -- 表名
6 @ItemName nvarchar ( 50 ), -- 主键字段名
7 @ItemID int , -- 主键ID
8 @SortName nvarchar ( 50 ), -- 排序ID
9 @TypeName nvarchar ( 50 ) = '' , -- 分类字段名
10 @TypeValue nvarchar ( 50 ) = '' -- 分类值
11
12 AS
13
14 BEGIN
15 SET NOCOUNT ON
16
17 DECLARE
18 @SQL nvarchar ( 4000 ),
19 @ThisSort int , -- 当前ID
20 @PREVID int , -- 前一个ID
21 @NextID int , -- 后一个ID
22 @Count int
23
24 -- 临时索引表--
25 CREATE TABLE #Tab
26 (
27 ItemID int ,
28 Sort int
29 )
30 SET @SQL = ' INSERT INTO #Tab (ItemID,Sort) SELECT '
31 + @ItemName + ' , ' + @SortName + ' FROM ' + @TableName
32
33 IF ( @TypeName <> '' AND @TypeValue <> '' ) SET @SQL = @SQL + ' WHERE ' + @TypeName + ' = ' + @TypeValue
34 SET @SQL = @SQL + ' ORDER BY Sort ASC '
35 EXEC ( @SQL )
36 SET @SQL = ''
37
38 SELECT @Count = COUNT ( * ) FROM #Tab
39 SELECT @ThisSort = Sort FROM #Tab WHERE ItemID = @ItemID
40 IF ( @ThisSort > 1 ) SELECT @PREVID = ItemID FROM #Tab WHERE Sort = ( @ThisSort - 1 )
41 IF ( @ThisSort < @Count ) SELECT @NextID = ItemID FROM #Tab WHERE Sort = ( @ThisSort + 1 )
42
43 IF ( @Sign = 0 )
44 BEGIN
45 IF ( @ThisSort > 1 )
46 BEGIN
47 SET @SQL = ' UPDATE ' + @TableName + ' SET ' + @SortName + ' = ' + CONVERT ( varchar ( 100 ),( @ThisSort - 1 )) + ' WHERE ' + @ItemName + ' = ' + CONVERT ( varchar ( 100 ), @ItemID ) + ' ; '
48 + ' UPDATE ' + @TableName + ' SET ' + @SortName + ' = ' + CONVERT ( varchar ( 100 ), @ThisSort ) + ' WHERE ' + @ItemName + ' = ' + CONVERT ( varchar ( 100 ), @PREVID )
49
50 END
51 END
52 ELSE
53 BEGIN
54 IF ( @ThisSort < @Count )
55 BEGIN
56 SET @SQL = ' UPDATE ' + @TableName + ' SET ' + @SortName + ' = ' + CONVERT ( varchar ( 100 ),( @ThisSort + 1 )) + ' WHERE ' + @ItemName + ' = ' + CONVERT ( varchar ( 100 ), @ItemID ) + ' ; '
57 + ' UPDATE ' + @TableName + ' SET ' + @SortName + ' = ' + CONVERT ( varchar ( 100 ), @ThisSort ) + ' WHERE ' + @ItemName + ' = ' + CONVERT ( varchar ( 100 ), @NextID )
58 END
59 END
60
61 EXEC ( @SQL )
62
63 END
64
2
3
4 @Sign int = 0 , -- 0: 上移 1:下移
5 @TableName nvarchar ( 50 ), -- 表名
6 @ItemName nvarchar ( 50 ), -- 主键字段名
7 @ItemID int , -- 主键ID
8 @SortName nvarchar ( 50 ), -- 排序ID
9 @TypeName nvarchar ( 50 ) = '' , -- 分类字段名
10 @TypeValue nvarchar ( 50 ) = '' -- 分类值
11
12 AS
13
14 BEGIN
15 SET NOCOUNT ON
16
17 DECLARE
18 @SQL nvarchar ( 4000 ),
19 @ThisSort int , -- 当前ID
20 @PREVID int , -- 前一个ID
21 @NextID int , -- 后一个ID
22 @Count int
23
24 -- 临时索引表--
25 CREATE TABLE #Tab
26 (
27 ItemID int ,
28 Sort int
29 )
30 SET @SQL = ' INSERT INTO #Tab (ItemID,Sort) SELECT '
31 + @ItemName + ' , ' + @SortName + ' FROM ' + @TableName
32
33 IF ( @TypeName <> '' AND @TypeValue <> '' ) SET @SQL = @SQL + ' WHERE ' + @TypeName + ' = ' + @TypeValue
34 SET @SQL = @SQL + ' ORDER BY Sort ASC '
35 EXEC ( @SQL )
36 SET @SQL = ''
37
38 SELECT @Count = COUNT ( * ) FROM #Tab
39 SELECT @ThisSort = Sort FROM #Tab WHERE ItemID = @ItemID
40 IF ( @ThisSort > 1 ) SELECT @PREVID = ItemID FROM #Tab WHERE Sort = ( @ThisSort - 1 )
41 IF ( @ThisSort < @Count ) SELECT @NextID = ItemID FROM #Tab WHERE Sort = ( @ThisSort + 1 )
42
43 IF ( @Sign = 0 )
44 BEGIN
45 IF ( @ThisSort > 1 )
46 BEGIN
47 SET @SQL = ' UPDATE ' + @TableName + ' SET ' + @SortName + ' = ' + CONVERT ( varchar ( 100 ),( @ThisSort - 1 )) + ' WHERE ' + @ItemName + ' = ' + CONVERT ( varchar ( 100 ), @ItemID ) + ' ; '
48 + ' UPDATE ' + @TableName + ' SET ' + @SortName + ' = ' + CONVERT ( varchar ( 100 ), @ThisSort ) + ' WHERE ' + @ItemName + ' = ' + CONVERT ( varchar ( 100 ), @PREVID )
49
50 END
51 END
52 ELSE
53 BEGIN
54 IF ( @ThisSort < @Count )
55 BEGIN
56 SET @SQL = ' UPDATE ' + @TableName + ' SET ' + @SortName + ' = ' + CONVERT ( varchar ( 100 ),( @ThisSort + 1 )) + ' WHERE ' + @ItemName + ' = ' + CONVERT ( varchar ( 100 ), @ItemID ) + ' ; '
57 + ' UPDATE ' + @TableName + ' SET ' + @SortName + ' = ' + CONVERT ( varchar ( 100 ), @ThisSort ) + ' WHERE ' + @ItemName + ' = ' + CONVERT ( varchar ( 100 ), @NextID )
58 END
59 END
60
61 EXEC ( @SQL )
62
63 END
64
数据层的公共类:
public
class
Common
{
public static Database db = DatabaseFactory.CreateDatabase( " Huoqk_CMS " );
/// <summary>
/// 上移下移
/// </summary>
/// <param name="Sign"> 移动方向:0 上移, 1下移 </param>
/// <param name="TableName"> 表名 </param>
/// <param name="ItemName"> 主键名 </param>
/// <param name="ItemID"> 主键ID </param>
/// <param name="SortName"> 排序字段 </param>
/// <param name="TypeName"> 类型名 </param>
/// <param name="TypeValue"> 类型ID </param>
public static void UPDown( int Sign, string TableName, string ItemName, int ItemID, string SortName, string TypeName, string TypeValue)
{
string spName = " Proc_Common_UpDown " ;
DbCommand dbCommand = db.GetStoredProcCommand(spName);
db.AddInParameter(dbCommand, " Sign " , DbType.Int32, Sign);
db.AddInParameter(dbCommand, " TableName " , DbType.String,TableName);
db.AddInParameter(dbCommand, " ItemName " , DbType.String, ItemName);
db.AddInParameter(dbCommand, " ItemID " , DbType.Int32, ItemID);
db.AddInParameter(dbCommand, " SortName " , DbType.String, SortName);
db.AddInParameter(dbCommand, " TypeName " , DbType.String, TypeName);
db.AddInParameter(dbCommand, " TypeValue " , DbType.String, TypeValue);
db.ExecuteNonQuery(dbCommand);
}
}
{
public static Database db = DatabaseFactory.CreateDatabase( " Huoqk_CMS " );
/// <summary>
/// 上移下移
/// </summary>
/// <param name="Sign"> 移动方向:0 上移, 1下移 </param>
/// <param name="TableName"> 表名 </param>
/// <param name="ItemName"> 主键名 </param>
/// <param name="ItemID"> 主键ID </param>
/// <param name="SortName"> 排序字段 </param>
/// <param name="TypeName"> 类型名 </param>
/// <param name="TypeValue"> 类型ID </param>
public static void UPDown( int Sign, string TableName, string ItemName, int ItemID, string SortName, string TypeName, string TypeValue)
{
string spName = " Proc_Common_UpDown " ;
DbCommand dbCommand = db.GetStoredProcCommand(spName);
db.AddInParameter(dbCommand, " Sign " , DbType.Int32, Sign);
db.AddInParameter(dbCommand, " TableName " , DbType.String,TableName);
db.AddInParameter(dbCommand, " ItemName " , DbType.String, ItemName);
db.AddInParameter(dbCommand, " ItemID " , DbType.Int32, ItemID);
db.AddInParameter(dbCommand, " SortName " , DbType.String, SortName);
db.AddInParameter(dbCommand, " TypeName " , DbType.String, TypeName);
db.AddInParameter(dbCommand, " TypeValue " , DbType.String, TypeValue);
db.ExecuteNonQuery(dbCommand);
}
}
数据层(DAL)具体的调用:CMS_Channel 表的排序功能
public
void
UPDown(
int
sign,
int
itemid)
{
Common.UPDown(sign, " CMS_Channel " , " CID " , itemid, " Sort " , "" , "" );
}
{
Common.UPDown(sign, " CMS_Channel " , " CID " , itemid, " Sort " , "" , "" );
}
至于业务逻辑层或者Web层的调用基本就很简单了:
UPDown(111, 0);上移
UPDown(111, 1);下移