支援任意資料表、排序欄位、主鍵、升冪/降冪、額外 WHERE 條件。
ALTER PROCEDURE [dbo].[sp_BannerOrder]
@tablename nvarchar(50), -- 資料表名
@colname nvarchar(50), -- 排序欄位
@keyid nvarchar(50), -- 主鍵欄位
@keyidvalue int, -- 當前記錄主鍵值
@order nvarchar(20), -- 預設排序方向 asc/desc
@orderDirection nvarchar(20), -- 操作方向 up/down
@where nvarchar(2000) -- 額外條件 (如: status=1)
AS
BEGIN
DECLARE @ordertmp1 int, @ordertmp2 int, @tmpkeyidvalue nvarchar(50);
DECLARE @sql nvarchar(2000), @ParmDefinition nvarchar(500);
-- 取得當前記錄的排序值
SET @sql = N'SELECT @ordertmp1OUT=' + @colname + ' FROM ' + @tablename + ' WHERE ' + @keyid + '=' + CAST(@keyidvalue AS nvarchar(50));
SET @ParmDefinition = N'@ordertmp1OUT int OUTPUT';
EXEC sp_executesql @sql, @ParmDefinition, @ordertmp1OUT=@ordertmp1 OUTPUT;
-- 尋找相鄰記錄
IF @order = 'asc'
BEGIN
IF @orderDirection = 'up'
SET @sql = N'SELECT TOP 1 @ordertmp2OUT=' + @colname + ', @tmpkeyidvalueOUT=' + @keyid + ' FROM ' + @tablename + ' WHERE ' + @colname + ' < ' + CAST(@ordertmp1 AS nvarchar(50)) + ' AND ' + @where + ' ORDER BY ' + @colname + ' DESC';
ELSE
SET @sql = N'SELECT TOP 1 @ordertmp2OUT=' + @colname + ', @tmpkeyidvalueOUT=' + @keyid + ' FROM ' + @tablename + ' WHERE ' + @colname + ' > ' + CAST(@ordertmp1 AS nvarchar(50)) + ' AND ' + @where + ' ORDER BY ' + @colname + ' ASC';
END
ELSE -- desc
BEGIN
IF @orderDirection = 'up'
SET @sql = N'SELECT TOP 1 @ordertmp2OUT=' + @colname + ', @tmpkeyidvalueOUT=' + @keyid + ' FROM ' + @tablename + ' WHERE ' + @colname + ' > ' + CAST(@ordertmp1 AS nvarchar(50)) + ' AND ' + @where + ' ORDER BY ' + @colname + ' ASC';
ELSE
SET @sql = N'SELECT TOP 1 @ordertmp2OUT=' + @colname + ', @tmpkeyidvalueOUT=' + @keyid + ' FROM ' + @tablename + ' WHERE ' + @colname + ' < ' + CAST(@ordertmp1 AS nvarchar(50)) + ' AND ' + @where + ' ORDER BY ' + @colname + ' DESC';
END
SET @ParmDefinition = N'@ordertmp2OUT int OUTPUT, @tmpkeyidvalueOUT nvarchar(50) OUTPUT';
EXEC sp_executesql @sql, @ParmDefinition, @ordertmp2OUT=@ordertmp2 OUTPUT, @tmpkeyidvalueOUT=@tmpkeyidvalue OUTPUT;
-- 交換排序值
SET @sql = 'UPDATE ' + @tablename + ' SET ' + @colname + '=' + CAST(@ordertmp2 AS nvarchar(50)) + ' WHERE ' + @keyid + '=' + CAST(@keyidvalue AS nvarchar(50));
SET @sql += ' UPDATE ' + @tablename + ' SET ' + @colname + '=' + CAST(@ordertmp1 AS nvarchar(50)) + ' WHERE ' + @keyid + '=' + CAST(@tmpkeyidvalue AS nvarchar(50));
EXEC(@sql);
END
// Model
public class Banner { public int ID { get; set; } public int OrderId { get; set; } ... }
// IDAL
int Order(string table, string colname, string keyid, int keyidvalue, string order, string orderDirection, string where);
// SQLDAL
public int Order(...) {
var paras = new[] {
new SqlParameter("@tablename", table),
new SqlParameter("@colname", colname),
new SqlParameter("@keyid", keyid),
new SqlParameter("@keyidvalue", keyidvalue),
new SqlParameter("@order", order),
new SqlParameter("@orderDirection", orderDirection),
new SqlParameter("@where", where)
};
return Convert.ToInt32(SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "sp_BannerOrder", paras));
}
// BLL
public int Order(...) => dal.Order(...);
<asp:GridView ID="gv" runat="server" AutoGenerateColumns="False" DataKeyNames="ID">
<Columns>
<asp:TemplateField HeaderText="排序">
<ItemTemplate>
<asp:LinkButton ID="btnUp" runat="server" CommandName="Up" CommandArgument='<%# Eval("ID") %>' Text="▲" />
<asp:LinkButton ID="btnDown" runat="server" CommandName="Down" CommandArgument='<%# Eval("ID") %>' Text="▼" />
</ItemTemplate>
</asp:TemplateField>
...
</Columns>
</asp:GridView>
// CodeBehind
protected void gv_RowCommand(object sender, GridViewCommandEventArgs e)
{
int id = Convert.ToInt32(e.CommandArgument);
string dir = e.CommandName == "Up" ? "up" : "down";
new BannerBLL().Order("BannerTable", "OrderId", "ID", id, "asc", dir, "Status=1");
BindGrid(); // 重新綁定
}