存储过程代码： 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()); }
}