给gridview添加上下移动功能

给gridview添加上下移动功能

存储过程代码：

CREATE PROCEDURE [sp_trans_dept]
@now_id int,
@upside_id int

AS
declare @tmp_ordering int       --临时变量
declare @sqlstr varchar(1000) --sql语句
declare @table_name varchar(500)
declare @column_name varchar(500)

set @table_name='tb_dept'
set @column_name='deptID'

select @tmp_ordering=ordering from tb_dept where deptID=@now_id

set @sqlstr='
update '+@table_name+' set ordering=(select ordering from '+@table_name+' where '+@column_name+'='+convert(varchar(50),@upside_id)+')
where '+@column_name+'='+convert(varchar(50),@now_id)
exec(@sqlstr)

set @sqlstr='
update '+@table_name+' set ordering = '+convert(varchar(50),@tmp_ordering)+'
where '+@column_name+'='+convert(varchar(50),@upside_id)
exec (@sqlstr)
GO

触发器代码：

CREATE TRIGGER TRI_tb_dept ON dbo.tb_dept
FOR INSERT
AS
update dbo.tb_dept
set ordering=inserted.deptID
from inserted
where tb_dept.deptID=inserted.deptID

程序后台代码：

SQLOperation opera = new SQLOperation();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        int index = Convert.ToInt32(e.CommandArgument);
        if (e.CommandName == "row_up")
        {
            if ((index - 1) >= 0) {
                int now_id = (int)GridView1.DataKeys[index].Value;
                int upside=(int)GridView1.DataKeys[index-1].Value;
                try
                {
                    opera.StoredProcedureOrdering("sp_trans_dept", "@now_id", "@upside_id", now_id, upside);
                    GridView1.DataBind();
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                    Response.End();
                }
            }
            else
            {
                Alert("已经是首行，不能上移");
            }
        }
        else if (e.CommandName=="row_down")
        {
            int rowsCount = GridView1.Rows.Count;
            if ((index+1)<rowsCount)
            {
                int now_id = (int)GridView1.DataKeys[index].Value;
                int upside = (int)GridView1.DataKeys[index + 1].Value;
                try
                {
                    opera.StoredProcedureOrdering("sp_trans_dept", "@now_id", "@upside_id", now_id, upside);
                    GridView1.DataBind();
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                    Response.End();
                }
            }
            else
            {
                Alert("已经是尾行，不能下移");

            }
        }
    }

    #region   警告信息

    public void Alert(string message)
    {
        string outstr = "";
        outstr += "<script   type='text/javascript'>";
        outstr += "alert('" + message + "');";
        outstr += "</script>";
        Response.Write(outstr);
    }

    #endregion

程序前台代码：

<asp:ButtonField CommandName="row_up" Text="上移">
                  <ItemStyle Width="40px" />
              </asp:ButtonField>
              <asp:ButtonField CommandName="row_down" Text="下移">
                  <ItemStyle Width="40px" />
              </asp:ButtonField>

C#调用存储过程代码：

public override void StoredProcedureOrdering(string sp_Name,string para_name1,string para_name2,int value_1,int value_2)
        {
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sp_Name, conn);
            if (conn.State.ToString() == "Closed")
                conn.Open();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(para_name1, SqlDbType.Int, 4).Value = value_1;
            cmd.Parameters.Add(para_name2, SqlDbType.Int, 4).Value = value_2;

           
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message.ToString());

            }