萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

ASP.NET 實作上移/下移排序功能 (含預存程序)

資料庫端:預存程序 sp_BannerOrder

支援任意資料表、排序欄位、主鍵、升冪/降冪、額外 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(...);

前端呼叫 (WebForms + AjaxControlToolkit)

<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(); // 重新綁定
}

優化建議

  • 使用 `ROW_NUMBER() OVER (ORDER BY ...)` 重新編號,避免長期交換導致排序值不連續。
  • 加入交易 `TransactionScope` 確保雙 UPDATE 原子性。
  • 前端改用 jQuery Sortable 拖曳排序,批次提交陣列至後端一次性更新。
copyright © 萬盛學電腦網 all rights reserved