工作中一些常用到的代码记录下来，方便自己查找也方便其他需要人士参考。
废话不多说，这是一个向上向下排序的功能，首先使用存储过程 整好 如下：
SQL:
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <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; }
}
/// <summary>
/// BANNER大图
/// </summary>
public string BigPic
{
get { return this._bigPic; }
set { this._bigPic = value; }
}
/// <summary>
/// 排序ID
/// </summary>
public int OrderId
{
get { return this._orderid; }
set { this._orderid = value; }
}
/// <summary>
/// URL地址
/// </summary>
public string Url
{
get { return this._url; }
set { this._url = value; }
}
/// <summary>
/// 标题
/// </summary>
public string Title
{
get { return this._title; }
set { this._title = value; }
}
/// <summary>
/// 描述
/// </summary>
public string Descript
{
get { return this._descript; }
set { this._descript = value; }
}
}
IDAL:
代码
/// 排序
/// </summary>
/// <param name="table">表名</param>
/// <param name="colname">排序字段</param>
/// <param name="keyid">表主键字段</param>
/// <param name="keyidvalue">表主键字段值</param>
/// <param name="order">列表默认的排序方式，asc或desc</param>
/// <param name="orderDirection">排序方向，up或down</param>
/// <param name="whe">条件</param>
/// <returns></returns>
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" %>
<asp:Content ID="ContentHaed" runat="server" ContentPlaceHolderID="head">
<script language="jscript" type="text/jscript" src="../Ajax/MemberDel.js"></script>
</asp:Content>
<asp:Content ID="ContentTitle" runat="server" ContentPlaceHolderID="ContentPlaceTitle">
前台Banner管理
</asp:Content>
<asp:Content ID="ContentMain" runat="server" ContentPlaceHolderID="ContentPlaceHolderMain">
<asp:UpdatePanel ID="UpdtPal" runat="server">
<ContentTemplate>
<table align="center" style=" 465px">
<tr>
<td> 标 题：</td>
<td><asp:TextBox ID="txbTitle" runat="server" MaxLength="15"></asp:TextBox>最多可填写15个字<asp:RequiredFieldValidator ID="rfvTitle" ControlToValidate="txbTitle" runat="server" ErrorMessage="请填写标题！"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td> 描 述：</td>
<td><asp:TextBox ID="txbDescrpt" runat="server" TextMode="MultiLine" MaxLength="20"></asp:TextBox>最多可填写20个字<asp:RequiredFieldValidator ID="rfvDescipt" ControlToValidate="txbDescrpt" runat="server" ErrorMessage="请填写描述！"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td >上传小图：</td>
<td>
<asp:FileUpload ID="flupSmallPic" runat="server" />
<asp:RequiredFieldValidator ID="rfvSmallPic" ControlToValidate="flupSmallPic" runat="server"
ErrorMessage="请选择图片！"></asp:RequiredFieldValidator>
</td>
</tr>
<tr><td>上传大图：</td>
<td>
<asp:FileUpload ID="flupBigPic" runat="server" />
<asp:RequiredFieldValidator ID="rfvBigPic" ControlToValidate="flupBigPic" runat="server"
ErrorMessage="请选择图片！"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td >
排 序：
</td>
<td>
<asp:TextBox ID="txborder" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvOrder" runat="server"
ControlToValidate="txborder" ErrorMessage="不能为空！"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td >链接地址：</td>
<td><asp:TextBox ID="txbUrl" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvUrl" runat="server"
ControlToValidate="txbUrl" ErrorMessage="不能为空！"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="revUrl" runat="server"
ControlToValidate="txbUrl" ErrorMessage="填写的地址不符合规格"
ValidationExpression="http(s)?://([\w-]+\.)+[\w-]+(/[\w- ./?%&=]*)?"></asp:RegularExpressionValidator>
</td>
</tr>
<tr align="center"><td colspan="2">
<asp:Button ID="btnOK" runat="server" Text="确定" Width="78px" Height="28px"
onclick="btnOK_Click" />
</td>
</tr>
</table>
<hr />
<table align="center">
<tr>
<td>
<asp:GridView ID="gvwBannner" runat="server" AutoGenerateColumns="False"
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">
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="ImgUrl" runat="server"></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="labUpSmall" Text="请选择小图:" runat="server"></asp:Label>
<asp:FileUpload ID="ImgUpload" runat="server" />
<asp:Label ID="labUpBig" Text="请选择大图:" runat="server"></asp:Label>
<asp:FileUpload ID="ImgBigPic" runat="server" />
</EditItemTemplate>
</asp:TemplateField>
<%--<asp:ImageField DataAlternateTextField="id"
DataAlternateTextFormatString="这是{0}的图" DataImageUrlField="smallPic"
HeaderText="图片">
<ControlStyle Height="50px" Width="50px" />
</asp:ImageField>--%>
<asp:TemplateField><ItemTemplate>
<img src='http://www.cnblogs.com/<%#Eval("smallPic") %>' height="50" width="50" /></ItemTemplate></asp:TemplateField>
<asp:BoundField DataField="url" HeaderText="链接地址" />
<asp:BoundField DataField="orderid" HeaderText="排序" />
<asp:TemplateField HeaderText="向上" ShowHeader="False">
<ItemTemplate>
<asp:Button ID="Button1" runat="server" CausesValidation="false" CommandName="up"
Text="向上" onclick="Button1_Click" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="向下" ShowHeader="False">
<ItemTemplate>
<asp:Button ID="Button2" runat="server" CausesValidation="false" CommandName="down"
Text="向下" OnClick="Button2_Click" />
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" ButtonType="Button" />
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:Button ID="Button3" runat="server" CausesValidation="False"
CommandName="Delete" Text="删除" OnClientClick="return confirm('是否刪除？');" > </asp:Button>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<AlternatingRowStyle BackColor="#F7F7F7" />
</asp:GridView>
</td></tr>
</table>
</ContentTemplate>
</asp:UpdatePanel>
</asp:Content>
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();
}
}
至此完毕。。。
第一次写BLOG 还不太熟悉怎么整，请见谅！
如果看不明白那不好意思，我觉得我已经很详细了。。。