Asp.net实现向上向下排序的例子__4

1、A实现向上向下排序的例子_ 工作中一些常用到的代码记录下来，便利自己查找也便利其他需要人士参考。 废话不多说，这是一个向上向下排序的功能，首先用法存储过程 整好 如下： 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)

2、, -表主键字段 keyidvalue int, -表主键字段值1 order nvarchar(20), - 列表默认的排序方式，asc或desc orderDirection nvarchar(20), -排序方向，up或down where nvarchar(2021) -查询条件 ) AS BEGIN declare ordertmp1 int; -临时排序值id1 declare ordertmp2 int; -临时排序值id2 declare tmpkeyidvaule nvarchar(50); declare sql nvarchar(2021); DECLARE ParmDe

3、finition nvarchar(500); DECLARE ParmDefinition2 nvarchar(500); if order=asc begin SET sql = NSELECT ordertmp1OUT=+colname+ from +tablename+ where +keyid+=+cast(keyidvalue as nvarchar(50); SET ParmDefinition = Nordertmp1OUT nvarchar(20) OUTPUT; EXECUTE sp_executesql sql, ParmDefinition, ordertmp1OUT=

4、ordertmp1 OUTPUT; if orderDirection=up begin SET sql = NSELECT 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 = NSELECT top 1 ordertmp2OUT=+colname+,tmpkeyidvauleOUT=+ke

5、yid+ from +tablename+ where +colname+cast(ordertmp1 as nvarchar(50)+ and +where+ order by +colname+ asc; end SET ParmDefinition = Nordertmp2OUT nvarchar(20) OUTPUT, tmpkeyidvauleOUT nvarchar(20) OUTPUT; EXECUTE sp_executesql sql, ParmDefinition, ordertmp2OUT=ordertmp2 OUTPUT, tmpkeyidvauleOUT=tmpkey

6、idvaule OUTPUT; end else begin SET sql = NSELECT ordertmp1OUT=+colname+ from +tablename+ where +keyid+=+cast(keyidvalue as nvarchar(50); SET ParmDefinition = Nordertmp1OUT nvarchar(20) OUTPUT; EXECUTE sp_executesql sql, ParmDefinition, ordertmp1OUT=ordertmp1 OUTPUT; if orderDirection=up begin SET sq

7、l = NSELECT 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 = NSELECT top 1 ordertmp2OUT=+colname+,tmpkeyidvauleOUT=+keyid+ from +tablename+ where +colname+cast(ordertmp1

8、as nvarchar(50)+ and +where+ order by +colname+ desc; end SET ParmDefinition = Nordertmp2OUT nvarchar(20) OUTPUT, tmpkeyidvauleOUT nvarchar(20) OUTPUT; EXECUTE sp_executesql sql, ParmDefinition, ordertmp2OUT=ordertmp2 OUTPUT, tmpkeyidvauleOUT=tmpkeyidvaule OUTPUT; end set sql = update +tablename+ se

9、t +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

10、 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 thi

11、s._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; / / 标题 / publi

12、c 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 keyidva

13、lue,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,key

14、id), 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代码: pu

15、blic 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=t

16、rue 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个字 上传小图： ErrorM

17、essage=请选择图片! 上传大图： ErrorMessage=请选择图片! 排 序： ControlToValidate=txborder ErrorMessage=不能为空! 链接地址： ControlToValidate=txbUrl ErrorMessage=不能为空! ControlToValidate=txbUrl ErrorMessage=填写的地址不符合规格 ValidationExpression=http(s)?:/(w-+.)+w-+(/w- ./?%=*)? onclick=btnOK_Click / onrowdatabound=gvwBannner_RowData

18、Bound 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

19、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).NamingCon