工作中一些常用到的代码记录下来，方便自己查找也方便其他需要人士参考。

废话不多说，这是一个向上向下排序的功能，首先使用存储过程 整好 如下：

SQL:

-- =============================================

-- Author:

-- Create date:

-- Description:

-- =============================================

ALTER PROCEDURE [dbo].[sp_BannerOrder]

-- Add the parameters for the stored procedure here

(

@tablename nvarchar(50), --表名

@colname nvarchar(50), --排序字段

@keyid nvarchar(50), --表主键字段

@keyidvalue int, --表主键字段值1

@order nvarchar(20), -- 列表默认的排序方式，asc或desc

@orderDirection nvarchar(20), --排序方向，up或down

@where nvarchar(2000) --查询条件

)

AS

BEGIN

declare @ordertmp1 int; --临时排序值id1

declare @ordertmp2 int; --临时排序值id2

declare @tmpkeyidvaule nvarchar(50);

declare @sql nvarchar(2000);

DECLARE @ParmDefinition nvarchar(500);

DECLARE @ParmDefinition2 nvarchar(500);

if @order='asc'

begin

SET @sql = N'SELECT @ordertmp1OUT='+@colname+' from '+@tablename+' where '+@keyid+'='+cast(@keyidvalue as nvarchar(50));

SET @ParmDefinition = N'@ordertmp1OUT nvarchar(20) OUTPUT';

EXECUTE sp_executesql @sql, @ParmDefinition, @ordertmp1OUT=@ordertmp1 OUTPUT;

if @orderDirection='up'

begin

SET @sql = N'SELECT top 1 @ordertmp2OUT='+@colname+',@tmpkeyidvauleOUT='+@keyid+' from '+@tablename+' where '+@colname+'<'+cast(@ordertmp1 as nvarchar(50))+' and '+@where+' order by '+@colname+' desc';

end

else

begin

SET @sql = N'SELECT top 1 @ordertmp2OUT='+@colname+',@tmpkeyidvauleOUT='+@keyid+' from '+@tablename+' where '+@colname+'>'+cast(@ordertmp1 as nvarchar(50))+' and '+@where+' order by '+@colname+' asc';

end

SET @ParmDefinition = N'@ordertmp2OUT nvarchar(20) OUTPUT, @tmpkeyidvauleOUT nvarchar(20) OUTPUT';

EXECUTE sp_executesql @sql, @ParmDefinition, @ordertmp2OUT=@ordertmp2 OUTPUT, @tmpkeyidvauleOUT=@tmpkeyidvaule OUTPUT;

end

else

begin

SET @sql = N'SELECT @ordertmp1OUT='+@colname+' from '+@tablename+' where '+@keyid+'='+cast(@keyidvalue as nvarchar(50));

SET @ParmDefinition = N'@ordertmp1OUT nvarchar(20) OUTPUT';

EXECUTE sp_executesql @sql, @ParmDefinition, @ordertmp1OUT=@ordertmp1 OUTPUT;

if @orderDirection='up'

begin

SET @sql = N'SELECT top 1 @ordertmp2OUT='+@colname+',@tmpkeyidvauleOUT='+@keyid+' from '+@tablename+' where '+@colname+'>'+cast(@ordertmp1 as nvarchar(50))+' and '+@where+' order by '+@colname+' asc';

end

else

begin

SET @sql = N'SELECT top 1 @ordertmp2OUT='+@colname+',@tmpkeyidvauleOUT='+@keyid+' from '+@tablename+' where '+@colname+'<'+cast(@ordertmp1 as nvarchar(50))+' and '+@where+' order by '+@colname+' desc';

end

SET @ParmDefinition = N'@ordertmp2OUT nvarchar(20) OUTPUT, @tmpkeyidvauleOUT nvarchar(20) OUTPUT';

EXECUTE sp_executesql @sql, @ParmDefinition, @ordertmp2OUT=@ordertmp2 OUTPUT, @tmpkeyidvauleOUT=@tmpkeyidvaule OUTPUT;

end

set @sql = 'update '+@tablename+' set '+@colname+'='+cast(@ordertmp2 as nvarchar(50))+' where '+@keyid+'='+cast(@keyidvalue as nvarchar(50));

set @sql = @sql + ' update '+@tablename+ ' set '+@colname+'='+cast(@ordertmp1 as nvarchar(50))+' where '+@keyid+'='+cast(@tmpkeyidvaule as nvarchar(50));

--select @ordertmp1,@ordertmp2,@tmpkeyidvaule,@sql

exec(@sql);

END

MODEL：

public class Banner

{

public Banner()

{ }

private int _id;

private string _smallPic;

private string _bigPic;

private int _orderid;

private string _url;

private string _title;

private string _descript;

//字增量ID

public int ID

{

get { return this._id; }

set { this._id = value; }

}

//BANNER小图

public string SmallPic

{

get { return this._smallPic; }

set { this._smallPic = value; }

}

///

/// BANNER大图

///

public string BigPic

{

get { return this._bigPic; }

set { this._bigPic = value; }

}

///

/// 排序ID

///

public int OrderId

{

get { return this._orderid; }

set { this._orderid = value; }

}

///

/// URL地址

///

public string Url

{

get { return this._url; }

set { this._url = value; }

}

///

/// 标题

///

public string Title

{

get { return this._title; }

set { this._title = value; }

}

///

/// 描述

///

public string Descript

{

get { return this._descript; }

set { this._descript = value; }

}

}

IDAL代码:

/// 排序

///

/// 表名

/// 排序字段

/// 表主键字段

/// 表主键字段值

/// 列表默认的排序方式，asc或desc

/// 排序方向，up或down

/// 条件

///

int Order(string table,string colname,string keyid,int keyidvalue,string order,string orderDirection,string whe);

SQLDAL代码：

public int Order(string table,string colname,string keyid,int keyidvalue,string order,string orderDirection,string whe)

{

SqlParameter[] paras = {

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",whe)

};

return Convert.ToInt32( SqlHelper.ExecuteNonQuery(Configuration.ConnectionString, CommandType.StoredProcedure, "sp_BannerOrder", paras));

BLL代码:

public int Order(string table, string colname, string keyid, int keyidvalue, string order, string orderDirection, string whe)

{

return bner.Order(table, colname, keyid, keyidvalue, order, orderDirection, whe);

}

WEB:

aspx代码:

<%@ Page Language="C#" MasterPageFile="~/Admin/Masterpage/Page.Master" AutoEventWireup="true" CodeBehind="Banner.aspx.cs" Inherits="YXShop.Web.Admin.Article.Banner" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

<%@ Register assembly="FredCK.FCKeditorV2" namespace="FredCK.FCKeditorV2" tagprefix="FCKeditorV2" %>

前台Banner管理

  标  题： 最多可填写15个字

  描  述： 最多可填写20个字

上传小图： 

ErrorMessage="请选择图片!">

上传大图： 

ErrorMessage="请选择图片!">

  排  序：

ControlToValidate="txborder" ErrorMessage="不能为空!">

链接地址： 

ControlToValidate="txbUrl" ErrorMessage="不能为空!">

ControlToValidate="txbUrl" ErrorMessage="填写的地址不符合规格"

ValidationExpression="http(s)?://([w-]+.)+[w-]+(/[w- ./?%&=]*)?">

onclick="btnOK_Click" />

onrowdatabound="gvwBannner_RowDataBound" BackColor="White" DataKeyNames="ID"

BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3"

GridLines="Horizontal" onrowcancelingedit="gvwBannner_RowCancelingEdit"

onrowdeleting="gvwBannner_RowDeleting" onrowediting="gvwBannner_RowEditing"

onrowupdating="gvwBannner_RowUpdating" AllowSorting="True" Width="551px">

<%--

DataAlternateTextFormatString="这是{0}的图" DataImageUrlField="smallPic"

HeaderText="图片">

--%>

Text="向上" onclick="Button1_Click" />

Text="向下" OnClick="Button2_Click" />

CommandName="Delete" Text="删除" OnClientClick="return confirm('是否刪除?');" >

CS代码:

protected void Button1_Click(object sender, EventArgs e)

{

int keyidvlue = Convert.ToInt32(gvwBannner.DataKeys[((GridViewRow)((Button)sender).NamingContainer).RowIndex].Value); //获取主键值

int row = Convert.ToInt32(((GridViewRow)((Button)sender).NamingContainer).RowIndex);//获取行号

if (row == 0)

{

YXShop.Common.alert.show("已经最前了!");

}

else

{

bll.Order("banner", "orderid", "id", keyidvlue, "desc", "up", "1=1");

this.Bind();

}

}

//向下

protected void Button2_Click(object sender, EventArgs e)

{

//int keyidvlue = ((GridViewRow)((Button)sender).NamingContainer).RowIndex;

int keyidvlue = Convert.ToInt32(gvwBannner.DataKeys[((GridViewRow)((Button)sender).NamingContainer).RowIndex].Value);

int row1 = Convert.ToInt32(((GridViewRow)((Button)sender).NamingContainer).RowIndex);

if (row1 == this.gvwBannner.Rows.Count-1)

{

YXShop.Common.alert.show("已经最后了!");

}

else

{

bll.Order("banner", "orderid", "id", keyidvlue, "desc", "down", "1=1");

this.Bind();

}

}

至此完毕。

更多信息请查看IT技术专栏