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

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

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技术专栏