上移下移—存储过程

/*****************************   创建存储过程   *******************************/

create PROCEDURE [dbo].[P_Friend_UpDown]
 @IDX INT,   --被选中的行的IDX
 @Flag INT  --移动方向：1表上上移，-1表示下移
AS

Declare @Count int--影响行数
Set @Count=0
Declare @Position int
Set @Position=0
Declare @GuildIDX int
Set @GuildIDX=0
Select @Position=Position,@GuildIDX=GuildIDX From Guild_Friend Where IDX=@IDX
IF(@Position=0)
Begin
 Return -1--没有找到相应记录
End
IF(@Flag=1)--上移
Begin
    Declare @PreIDX INT
 Set @PreIDX=0
 Select @PreIDX=IDX From Guild_Friend Where GuildIDX=@GuildIDX AND Position=@Position-1 AND Flag=1
 IF(@PreIDX=0)--没有找到当前行的前一行,无需上移
 Begin
  Return 0
 End
 Else
 Begin
  BEGIN TRAN
   UPDATE [dbo].[Guild_Friend] SET Position = @Position-1 ,[UpdateTime] = getDate() WHERE [IDX] = @IDX
   SELECT @Count=@@identity
   IF(@Count=0)
   Begin
    ROLLBACK TRANSACTION
    Return -1
   End
   Set @Count=0
   UPDATE [dbo].[Guild_Friend] SET Position = @Position ,[UpdateTime] = getDate() WHERE [IDX] = @PreIDX
   SELECT @Count=@@identity
   IF(@Count=0)
   Begin
    ROLLBACK TRANSACTION
    Return -1
   End
  COMMIT TRANSACTION
  RETURN 0    --执行成功
 End
End
Else--下移
Begin
    Declare @NextIDX INT
 Set @NextIDX=0
 Select @NextIDX=IDX From Guild_Friend Where GuildIDX=@GuildIDX AND Position=@Position+1
 IF(@NextIDX=0)--没有找到当前行的后一行,无需下移
 Begin
  Return 0
 End
 Else
 Begin
  BEGIN TRAN
   UPDATE [dbo].[Guild_Friend] SET Position = @Position+1 ,[UpdateTime] = getDate() WHERE [IDX] = @IDX
   SELECT @Count=@@identity
   IF(@Count=0)
   Begin
    ROLLBACK TRANSACTION
    Return -1
   End
   Set @Count=0
   UPDATE [dbo].[Guild_Friend] SET Position = @Position ,[UpdateTime] = getDate() WHERE [IDX] = @NextIDX
   SELECT @Count=@@identity
   IF(@Count=0)
   Begin
    ROLLBACK TRANSACTION
    Return -1
   End
  COMMIT TRANSACTION
  RETURN 0    --执行成功
 End
End

RETURN 0

 

 

/*************************    创建函数，调用存储过程    ***************************/

public static int UpDown( System.Int32? idx, System.Int32? flag)
        {
            using (FriendTableAdapter adpt = new FriendTableAdapter())
            {
                int rValue = adpt.P_Friend_UpDown(  idx,  flag);
    
    return rValue;
               
            }
        }

 

 

/*************************    在后台代码中调用上移下移函数    ***************************/

protected void rpLists_ItemCommand(object source, RepeaterCommandEventArgs e)
        {
            int idx = int.Parse(e.CommandArgument.ToString());
            if (e.CommandName == "btnUp")//上移
            {
                int retVal = FriendMgr.UpDown(idx, 1);
                if (retVal > 0)
                {
                    this.Alert("操作成功");
                    BindData();
                }
                else
                {
                    this.Alert("操作失败");
                }
            }
            else if (e.CommandName == "btnDown")//下移
            {
                int retVal = FriendMgr.UpDown(idx, -1);
                if (retVal > 0)
                {
                    this.Alert("操作成功");
                    BindData();
                }
                else
                {
                    this.Alert("操作失败");
                }
            }
        }