一，首先创建分页存储过程
CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR(300), --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空
@Fields VARCHAR(350), --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT, --每页记录数
@CurrentPage INT, --当前页，0表示第1页
@Filter VARCHAR(200) = '', --条件，可以为空，不用填 where
@Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by
@Order VARCHAR(200) = '', --排序，可以为空，为空默认按主键升序排列，不用填 order by
@RecordCount int = 0 output
AS
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
DECLARE @TmpSelect NVarchar(200)
IF @Fields = ''
SET @Fields = '*'
IF @Filter = ''
SET @Filter = 'WHERE 1=1'
ELSE
SET @Filter = 'WHERE ' + @Filter
IF @Group <>''
SET @Group = 'GROUP BY ' + @Group
IF @Order <> ''
BEGIN
DECLARE @pos1 INT, @pos2 INT
SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
IF CHARINDEX(' DESC', @Order) > 0
IF CHARINDEX(' ASC', @Order) > 0
BEGIN
IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
SET @Operator = '<='
ELSE
SET @Operator = '>='
END
ELSE
SET @Operator = '<='
ELSE
SET @Operator = '>='
SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
SET @pos1 = CHARINDEX(',', @SortColumn)
IF @pos1 > 0
SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
SET @pos2 = CHARINDEX('.', @SortColumn)
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
IF @pos1 > 0
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
ELSE
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator = '>='
END
DECLARE @type varchar(50)
DECLARE @prec int
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1
print @TopRows
print @Operator
EXEC('
DECLARE @SortColumnBegin ' + @type + '
SET ROWCOUNT ' + @TopRows + '
SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
SET ROWCOUNT ' + @PageSize + '
SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
')
DECLARE @str_Count_SQL nvarchar(500)
SET @str_Count_SQL= 'SELECT @TotalCount=count('+@PrimaryKey+') FROM ' + @TableNames + ' ' + @Filter
EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
End
GO
二，编写sqlHelper方法
/// <summary>
/// 执行存储过程返回 DataTable
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTableByProc(string storedProcName, SqlParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = command;
da.Fill(ds);
DataTable dt = ds.Tables[0];
return dt;
}
}
/// <summary>
/// 执行存储过程返回SqlDataReader
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteSqlDataReaderByProc(string storedProcName, SqlParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, SqlParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
command.Parameters.Add( parameter );
return command;
}
数据库层代码如下：
public class Notice:INotice
{
public Notice() { }
public int GetMaxId()
{
StringBuilder strSQL = new StringBuilder();
strSQL.Append("select max(NoticeID)+1 from Notice");
object obj = DbHelperSQL.ExecuteScalar(strSQL.ToString());
if (obj == null)
return 1;
else
return Convert.ToInt32(obj);
}
public bool Exists(int NoticeID)
{
StringBuilder strSQL = new StringBuilder();
strSQL.Append("select count(1) from Notice ");
strSQL.Append("where NoticeID=@NoticeID");
SqlParameter parm = new SqlParameter("@NoticeID", SqlDbType.Int, 4);
parm.Value = NoticeID;
object obj = DbHelperSQL.ExecuteScalar(strSQL.ToString(), parm);
int result;
if ((object.Equals(obj, null)) || (object.Equals(obj, System.DBNull.Value)))
result = 0;
else
result = Convert.ToInt32(obj.ToString());
if (result == 0)
return false;
else
return true;
}
public void Add(Model.Notice model)
{
StringBuilder strSQL = new StringBuilder();
strSQL.Append("insert into Notice values (");
strSQL.Append("@NoticeTitle,@TitleColor,@NoticeContent,@Disabled,@IsTop,@AddDate,@AddAdminName)");
SqlParameter[] parlist ={
new SqlParameter("@NoticeTitle",SqlDbType.VarChar,100),
new SqlParameter("@TitleColor",SqlDbType.VarChar,50),
new SqlParameter("@NoticeContent",SqlDbType.Text),
new SqlParameter("@Disabled",SqlDbType.Bit,1),
new SqlParameter("@IsTop",SqlDbType.Bit,1),
new SqlParameter("@AddDate",SqlDbType.DateTime,8),
new SqlParameter("@AddAdminName",SqlDbType.VarChar,50),
};
parlist[0].Value = model.NoticeTitle;
parlist[1].Value = model.TitleColor;
parlist[2].Value = model.NoticeContent;
parlist[3].Value = model.Disabled;
parlist[4].Value = model.IsTop;
parlist[5].Value = model.AddDate;
parlist[6].Value = model.AddAdminName;
DbHelperSQL.ExecuteNonQueryBySQL(strSQL.ToString(), parlist);
}
public void Update(Model.Notice model)
{
StringBuilder strSQL = new StringBuilder();
strSQL.Append("update Notice set ");
strSQL.Append("NoticeTitle=@NoticeTitle,");
strSQL.Append("TitleColor=@TitleColor,");
strSQL.Append("NoticeContent=@NoticeContent,");
strSQL.Append("Disabled=@Disabled,");
strSQL.Append("IsTop=@IsTop,");
strSQL.Append("AddDate=@AddDate,");
strSQL.Append("AddAdminName=@AddAdminName ");
strSQL.Append("where NoticeID=@NoticeID");
SqlParameter[] parlist ={
new SqlParameter("@NoticeTitle",SqlDbType.VarChar,100),
new SqlParameter("@TitleColor",SqlDbType.VarChar,50),
new SqlParameter("@NoticeContent",SqlDbType.Text),
new SqlParameter("@Disabled",SqlDbType.Bit,1),
new SqlParameter("@IsTop",SqlDbType.Bit,1),
new SqlParameter("@AddDate",SqlDbType.DateTime,8),
new SqlParameter("@AddAdminName",SqlDbType.VarChar,50),
new SqlParameter("@NoticeID",SqlDbType.Int,4),
};
parlist[0].Value = model.NoticeTitle;
parlist[1].Value = model.TitleColor;
parlist[2].Value = model.NoticeContent;
parlist[3].Value = model.Disabled;
parlist[4].Value = model.IsTop;
parlist[5].Value = model.AddDate;
parlist[6].Value = model.AddAdminName;
parlist[7].Value = model.NoticeID;
DbHelperSQL.ExecuteNonQueryBySQL(strSQL.ToString(), parlist);
}
public void Delete(int NoticeID)
{
StringBuilder strSQL = new StringBuilder();
strSQL.Append("delete from Notice ");
strSQL.Append("where NoticeID=@NoticeID");
SqlParameter parm = new SqlParameter("@NoticeID", SqlDbType.Int, 4);
parm.Value = NoticeID;
DbHelperSQL.ExecuteNonQueryBySQL(strSQL.ToString(), parm);
}
public Model.Notice GetModel(int NoticeID)
{
StringBuilder strSQL = new StringBuilder();
strSQL.Append("select NoticeTitle,TitleColor,NoticeContent,Disabled,IsTop,AddDate,AddAdminName from Notice ");
strSQL.Append("where NoticeID=@NoticeID");
SqlParameter parm = new SqlParameter("@NoticeID", SqlDbType.Int, 4);
parm.Value = NoticeID;
Model.Notice model = new Model.Notice();
//using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderBySQL(strSQL.ToString(), parm))
//{
// if (dr.Read())
// {
// model.NoticeTitle = dr.GetString(0);
// model.TitleColor = dr.GetString(1);
// model.NoticeContent = dr.GetString(2);
// model.Disabled = dr.GetBoolean(3);
// model.IsTop = dr.GetBoolean(4);
// model.AddDate = dr.GetDateTime(5);
// model.AddAdminName = dr.GetString(6);
// }
//}
//return model;
DataSet ds = DbHelperSQL.ExecuteDataSetBySQL(strSQL.ToString(), parm);
if (ds.Tables[0].Rows.Count > 0)
{
model.NoticeTitle = ds.Tables[0].Rows[0]["NoticeTitle"].ToString();
model.TitleColor = ds.Tables[0].Rows[0]["TitleColor"].ToString();
model.NoticeContent = ds.Tables[0].Rows[0]["NoticeContent"].ToString();
model.AddAdminName = ds.Tables[0].Rows[0]["AddAdminName"].ToString();
if ((ds.Tables[0].Rows[0]["Disabled"].ToString() == "1") || (ds.Tables[0].Rows[0]["Disabled"].ToString().ToLower() == "true"))
model.Disabled = true;
else
model.Disabled = false;
if ((ds.Tables[0].Rows[0]["IsTop"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsTop"].ToString().ToLower() == "true"))
model.IsTop = true;
else
model.IsTop = false;
if (ds.Tables[0].Rows[0]["AddDate"].ToString() != "")
model.AddDate = DateTime.Parse(ds.Tables[0].Rows[0]["AddDate"].ToString());
return model;
}
else
return null;
}
public DataSet GetDataSet(string strWhere)
{
StringBuilder strSQL = new StringBuilder();
strSQL.Append("select * from Notice");
if (strWhere.Trim() != "")
strSQL.Append(" where " + strWhere);
strSQL.Append(" order by NoticeID desc");
return DbHelperSQL.ExecuteDataSetBySQL(strSQL.ToString());
}
public int GetRecordCount(string strWhere)
{
int result = 0;
SqlParameter[] parlist ={
new SqlParameter("@tblName",SqlDbType.VarChar,50),
new SqlParameter("@strWhere",SqlDbType.VarChar,1000),
};
parlist[0].Value = "Notice";
parlist[1].Value = strWhere;
using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderByProc("GetRecordCount", parlist))
{
if (dr.Read())
result = dr.GetInt32(0);
}
return result;
}
public IList<Model.Notice> GetList(string filename, string sortfilename, int pageSize, int pageIndex, int OrderType, string strWhere)
{
IList<Model.Notice> notices = new List<Model.Notice>();
Model.Notice model = null;
SqlParameter[] parlist ={
new SqlParameter("@tblName",SqlDbType.VarChar,255),
new SqlParameter("@strGetFields",SqlDbType.VarChar,1000),
new SqlParameter("@fldName",SqlDbType.VarChar,255),
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@PageIndex",SqlDbType.Int),
new SqlParameter("@OrderType",SqlDbType.Int),
new SqlParameter("@strWhere",SqlDbType.VarChar,500),
};
parlist[0].Value = "Notice";
parlist[1].Value = filename;
parlist[2].Value = sortfilename;
parlist[3].Value = pageSize;
parlist[4].Value = pageIndex;
parlist[5].Value = OrderType;
parlist[6].Value = strWhere;
using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderByProc("UP_GetRecordByPage", parlist))
{
while (dr.Read())
{
model = new Model.Notice();
model.NoticeID = dr.GetInt32(0);
model.NoticeTitle = dr.GetString(1);
model.TitleColor = dr.GetString(2);
model.NoticeContent = dr.GetString(3);
model.Disabled = dr.GetBoolean(4);
model.IsTop = dr.GetBoolean(5);
model.AddDate = dr.GetDateTime(6);
model.AddAdminName = dr.GetString(7);
notices.Add(model);
}
}
return notices;
}
public IList<Model.Notice> GetListByWhere(string strWhere, string Order, int PageSize, int CurrentPage, out int Record)
{
IList<Model.Notice> notice = new List<Model.Notice>();
Model.Notice model = null;
SqlParameter[] parlist ={
new SqlParameter("@TableNames",SqlDbType.VarChar,300),
new SqlParameter("@PrimaryKey",SqlDbType.VarChar,100),
new SqlParameter("@Fields",SqlDbType.VarChar,350),
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@CurrentPage",SqlDbType.Int),
new SqlParameter("@Filter",SqlDbType.VarChar,200),
new SqlParameter("@Group",SqlDbType.VarChar,200),
new SqlParameter("@Order",SqlDbType.VarChar,200),
new SqlParameter("@RecordCount",SqlDbType.Int),
};
parlist[0].Value = "Notice";
parlist[1].Value = "NoticeID";
parlist[2].Value = "NoticeID,NoticeTitle,TitleColor,NoticeContent,AddDate,IsTop";
parlist[3].Value = PageSize;
parlist[4].Value = CurrentPage;
parlist[5].Value = strWhere;
parlist[6].Value = "";
parlist[7].Value = Order;
parlist[8].Direction = ParameterDirection.Output;
using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderByProc("usp_PagingLarge", parlist))
{
while (dr.Read())
{
model = new Model.Notice();
model.NoticeID = dr.GetInt32(0);
model.NoticeTitle = dr.GetString(1);
model.TitleColor = dr.GetString(2);
model.NoticeContent = dr.GetString(3);
model.AddDate = dr.GetDateTime(4);
model.IsTop = dr.GetBoolean(5);
notice.Add(model);
}
}
Record = Convert.ToInt32(parlist[8].Value);
return notice;
}
public DataTable GetListByWhereDataTable(string strWhere, string Order, int PageSize, int CurrentPage, out int Record)
{
DataTable dt = new DataTable();
SqlParameter[] parlist ={
new SqlParameter("@TableNames",SqlDbType.VarChar,300),
new SqlParameter("@PrimaryKey",SqlDbType.VarChar,100),
new SqlParameter("@Fields",SqlDbType.VarChar,350),
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@CurrentPage",SqlDbType.Int),
new SqlParameter("@Filter",SqlDbType.VarChar,200),
new SqlParameter("@Group",SqlDbType.VarChar,200),
new SqlParameter("@Order",SqlDbType.VarChar,200),
new SqlParameter("@RecordCount",SqlDbType.Int),
};
parlist[0].Value = "Notice";
parlist[1].Value = "NoticeID";
parlist[2].Value = "NoticeID,NoticeTitle,TitleColor,NoticeContent,AddDate,IsTop";
parlist[3].Value = PageSize;
parlist[4].Value = CurrentPage;
parlist[5].Value = strWhere;
parlist[6].Value = "";
parlist[7].Value = Order;
parlist[8].Direction = ParameterDirection.Output;
dt = DbHelperSQL.ExecuteDataTableByProc("usp_PagingLarge", parlist);
Record = Convert.ToInt32(parlist[8].Value);
return dt;
}
}
前台页面代码：
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>存储过程分页</title>
<meta http-equiv="Content-Language" content="zh-CN" />
<meta content="all" name="MyWeb" />
<meta name="author" content="晴儿" />
<meta name="Copyright" content="版权归晴儿所有" />
<meta name="description" content="晴儿个人网站系统" />
<meta content="文章,新闻,相册,实用工具" name="keywords" />
<meta content="文章,新闻,相册,实用工具" name="searchtitle" />
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<link href="../images/style.css" type="text/css" rel="stylesheet" />
<script language="javascript" type="text/javascript" src="../Script/DateOperate.js"></script>
<script language="javascript" type="text/javascript" src="../Script/calendar.js"></script>
<script language="javascript" type="text/javascript">
function check()
{
var checks=false;
var innar=document.getElementsByTagName("input");
for(var i=0;i<innar.length;i++)
{
if(innar.type=="checkbox")
{
if(innar.checked==true)
checks=true;
}
}
if(checks==false)
{
alert("您未选中任何记录！");
return false;
}
else
{
return confirm('您确定要删除所有选中的记录吗?');
return true;
}
}
function quanxuan()
{
var innar=document.getElementsByTagName("input");
for(var i=0;i<innar.length;i++)
{
if(innar.type=="checkbox")
innar.checked=true;
}
}
function fanxuan()
{
var innar=document.getElementsByTagName("input");
for(var i=0;i<innar.length;i++)
{
if(innar.type=="checkbox")
{
if(innar.checked==true)
innar.checked=false;
else
innar.checked=true;
}
}
}
function yanzheng()
{
if(isDate(document.getElementById("txtStartTime").value)==false)
{
alert('开始日期时间格式错误！正确格式为：2000-12-12 00:00:00');
return false;
}
if(isDate(document.getElementById("txtEndTime").value)==false)
{
alert('结束日期时间格式错误！正确格式为：2000-12-12 00:00:00');
return false;
}
if(compareDate(document.getElementById("txtStartTime").value,document.getElementById("txtEndTime").value)=="0")
{
alert('查询开始日期必须小于结束日期！');
return false;
}
}
</script>
</head>
<body>
<form id="form2" runat="server">
<div id="hearder">
<span>公告管理</span></div>
<div id="site">
您的位置：<a href="../index.aspx"> 管理首页 </a>>> 公告管理
</div>
<table class="table1" border="1" cellpadding="2" cellspacing="1">
<tr class="tr1">
<td class="td1">
公告标题：
</td>
<td class="td2">
<asp:TextBox ID="txtKey" runat="server" CssClass="input1" Width="150px"></asp:TextBox>
</td>
<td class="td1">
状态：
</td>
<td class="td2">
<select id="Status" runat="server" class="select1">
<option selected="selected" value="">所有</option>
<option value="1">置顶</option>
<option value="0">不置顶</option>
</select>
</td>
<td rowspan="2" class="td3">
<asp:Button ID="btnSearch" runat="server" Text="查询" CssClass="button" OnClientClick="return yanzheng();"
OnClick="btnSearch_Click" />
</td>
</tr>
<tr class="tr1">
<td class="td1">
时间段：
</td>
<td class="td2">
<table cellpadding="0" cellspacing="0" border="0">
<tr>
<td>
<asp:TextBox ID="txtStartTime" CssClass="input1" Width="140px" runat="server"></asp:TextBox>
</td>
<td>
<a href="javascript:void(0);" onclick="javascript:setday(this,txtStartTime,true,false);"
style="font-weight: bold; color: Red;">选择</a>
</td>
<td>
---
</td>
<td>
<asp:TextBox ID="txtEndTime" CssClass="input1" Width="140px" runat="server"></asp:TextBox>
</td>
<td>
<a href="javascript:void(0);" onclick="javascript:setday(this,txtEndTime,true,false);"
style="font-weight: bold; color: Red;">选择</a>
</td>
<td style="font-weight: bold; color: Fuchsia; font-size: 13px">(默认查询一个月记录)</td>
</tr>
</table>
</td>
</tr>
</table>
<table class="table2">
<tr class="tablehearder4">
<td class="tablehearder1" align="center">
选择
</td>
<td class="tablehearder1" align="center">
公告标题
</td>
<td class="tablehearder1" align="center">
公告信息
</td>
<td align="center" class="tablehearder1" >
是否置顶
</td>
<td align="center" class="tablehearder1">
添加时间
</td>
<td align="center" class="tablehearder1">
修改
</td>
<td align="center" class="tablehearder1">
删除
</td>
</tr>
<asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand">
<ItemTemplate>
<tr class="tr2" onmouseover="this.style.backgroundColor='#fffff2';this.style.color='red'"
onmouseout="this.style.backgroundColor='#ffffff';this.style.color=''">
<td height="25">
<asp:CheckBox ID="CheckBox1" runat="server" /><asp:HiddenField ID="HiddenField1"
runat="server" Value='<%#Eval("NoticeID") %>' />
</td>
<td>
<span style='color: <%#Eval("TitleColor") %>'>
<%#PageValidate.CutString(Eval("NoticeTitle").ToString(),12)%>
</span>
</td>
<td align="center">
<%#PageValidate.CutString(Eval("NoticeContent").ToString(),20)%>
</td>
<td align="center">
<%#WebUtinity.GetIsTop(Eval("IsTop").ToString())%>
</td>
<td align="center">
<%#Eval("AddDate")%>
</td>
<td align="center">
<a href='#'>
<img src="../images/icon_xg.gif" alt="点击修改公告" /></a>
</td>
<td align="center">
<asp:ImageButton ID="imbDelete" runat="server" CommandName="delete" CommandArgument='<%#Eval("NoticeID") %>'
ImageUrl="../images/icon_sc.gif" AlternateText="删除" OnClientClick="return confirm('确认删除此公告吗? 删除后不可恢复！');">
</asp:ImageButton>
</td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr class="tr2" onmouseover="this.style.backgroundColor='#fffff4';this.style.color='red'"
onmouseout="this.style.backgroundColor='#EEF7FF';this.style.color=''">
<td height="25">
<asp:CheckBox ID="CheckBox1" runat="server" /><asp:HiddenField ID="HiddenField1"
runat="server" Value='<%#Eval("NoticeID") %>' />
</td>
<td>
<span style='color: <%#Eval("TitleColor") %>'>
<%#PageValidate.CutString(Eval("NoticeTitle").ToString(),12)%>
</span>
</td>
<td align="center">
<%#PageValidate.CutString(Eval("NoticeContent").ToString(),20)%>
</td>
<td align="center">
<%#WebUtinity.GetIsTop(Eval("IsTop").ToString())%>
</td>
<td align="center">
<%#Eval("AddDate")%>
</td>
<td align="center">
<a href='#'>
<img src="../images/icon_xg.gif" alt="点击修改公告" /></a>
</td>
<td align="center">
<asp:ImageButton ID="imbDelete" runat="server" CommandName="delete" CommandArgument='<%#Eval("NoticeID") %>'
ImageUrl="../images/icon_sc.gif" AlternateText="删除" OnClientClick="return confirm('确认删除此公告吗? 删除后不可恢复！');">
</asp:ImageButton>
</td>
</tr>
</AlternatingItemTemplate>
</asp:Repeater>
<asp:Panel ID="Panel3" runat="server">
<tr class="tablehearder2">
<td align="center" colspan="10">
<asp:LinkButton ID="lbtDeleteAll" runat="server" OnClick="lbtDeleteAll_Click" OnClientClick="return check();"
ForeColor="#0066cc" Font-Bold="true">删除选中</asp:LinkButton>
<a href="javascript:quanxuan();" style="font-weight: bold; color: #0066cc">全选</a>
<a href="javascript:fanxuan();" style="font-weight: bold; color: #0066cc">反选</a>
</td>
</tr>
</asp:Panel>
<asp:Panel ID="Panel1" runat="server" Visible="false">
<tr class="tablehearder2">
<td colspan="10" class="tablehearder3">
没有查询到符合该查询条件的记录！<a href="#" style="color: Blue; font-weight: bold">点击此处</a>添加公告
</td>
</tr>
</asp:Panel>
</table>
分页样式一：<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
<asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>
分页样式二：<asp:Label ID="Label4" runat="server" Text="Label"></asp:Label>
<asp:Label ID="Label5" runat="server" Text="Label"></asp:Label>
</form>
</body>
</html>
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class Admin_noticeManage_Notice : System.Web.UI.Page
{
BLL.Notice notice = new BLL.Notice();
// 总记录
private int Record;
// 每页显示记录数
private int PageSize = 10;
// 当前页
private int CurrentPage = 1;
private string strUrl = "";
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["page"] != null)
{
if (!Int32.TryParse(Request.QueryString["page"].ToString(), out CurrentPage))
{
ClientScript.RegisterStartupScript(GetType(), "", "alert('请输入整数页！');", true);
return;
}
}
if (!IsPostBack)
{
BindData();
BindSOSOPager();
BindPager();
if (Repeater1.Items.Count < 1)
{
Panel1.Visible = true;
Panel3.Visible = false;
}
}
}
private void BindData()
{
try
{
int y = Convert.ToInt32(DateTime.Now.Year.ToString());
int m = Convert.ToInt32(DateTime.Now.Month.ToString());
int d = Convert.ToInt32(DateTime.Now.Day.ToString());
if (m == 1)
txtStartTime.Text = Convert.ToString(y - 1) + "-" + "12" + "-" + d.ToString() + " " + DateTime.Now.ToLongTimeString();
else
txtStartTime.Text = y.ToString() + "-" + Convert.ToInt32(m - 1) + "-" + d.ToString() + " " + DateTime.Now.ToLongTimeString();
txtEndTime.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString();
string strWhere = "";
if (Request.QueryString["key"] != null && Request.QueryString["istop"] != null && Request.QueryString["starttime"] != null && Request.QueryString["endtime"] != null)
{
txtKey.Text = Request.QueryString["key"];
Status.Value = Request.QueryString["istop"];
txtStartTime.Text = Request.QueryString["starttime"];
txtEndTime.Text = Request.QueryString["endtime"];
strUrl = string.Format("&key={0}&istop={1}&starttime={2}&endtime={3}", Request.QueryString["key"], Request.QueryString["istop"], Request.QueryString["starttime"], Request.QueryString["endtime"]);
if (Status.SelectedIndex == 0)
strWhere = "NoticeTitle like '%" + Request.QueryString["key"] + "%' and AddDate between '" + Request.QueryString["starttime"] + "' and '" + Request.QueryString["endtime"] + "'";
else
strWhere = "NoticeTitle like '%" + Request.QueryString["key"] + "%' and IsTop=" + byte.Parse(Status.Value) + " and AddDate between '" + Request.QueryString["starttime"] + "' and '" + Request.QueryString["endtime"] + "'";
}
else
strWhere = "AddDate between '" + txtStartTime.Text + "' and '" + txtEndTime.Text + "'";
//这里用 notice.GetListByWhereDataTable(); 方法也可以，但IList效率要高
Repeater1.DataSource = notice.GetListByWhere(strWhere, "IsTop desc,AddDate desc", PageSize, CurrentPage, out Record);
Repeater1.DataBind();
}
catch
{
ClientScript.RegisterStartupScript(GetType(), "", "alert('出现错误！');", true);
}
}
private void BindPager()
{
// 偏移量
int Step = 10;
// 左页码
int LeftNum = 0;
// 右页码
int RightNum = 0;
// 获取当前请求的虚拟路径
string PageUrl = Request.FilePath;
// 获取总的页数
int PageCount = (int)Math.Ceiling((double)(Record) / PageSize);
// 如果当前页码小于等于偏移量，就把左页码设置为1
if (CurrentPage - Step < 1)
LeftNum = 1;
// 否则左页码=当前页码-偏移量
else
LeftNum = CurrentPage - Step;
// 如果总页码减去偏移量小于等于当前页码，就把右页码设置为总页码
if (CurrentPage + Step > PageCount)
RightNum = PageCount;
// 否则把右页码设置为当前页码+偏移量
else
RightNum = CurrentPage + Step;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
// 循环生成页码
for (int i = LeftNum; i <= RightNum; i++)
{
// 如果是本页，字体变红，并且没有链接
if (i == CurrentPage)
{
// 注意这里的 margin-left 是让他们之间产生间隙，也可以用 代替
sb.Append("<span style='margin-left:3px;color:red'>");
sb.Append(i.ToString());
sb.Append("</span>");
}
// 如果不是本页，字体颜色不变，链接到相应页码
else
{
sb.Append("<a style='margin-left:3px;' href='");
sb.Append(PageUrl);
sb.Append("?page=");
sb.Append(i.ToString());
// 查询条件
sb.Append(strUrl);
sb.Append("'>");
sb.Append(i.ToString());
sb.Append("</a>");
}
}
// 如果当前页不是第一页，则左边出现“上一页”，注意这里是用 sb.Insert 方法在 sb 最前面插入
if (CurrentPage > 1)
{
sb.Insert(0, string.Format("<a href='{0}?page={1}{2}'>上一页 </a>", PageUrl, (CurrentPage - 1), strUrl));
}
// 如果当前页不是最后一页，则出现“下一页”
if (CurrentPage < PageCount)
{
sb.Append("<a href='");
sb.Append(PageUrl);
sb.Append("?page=");
sb.Append(CurrentPage + 1);
sb.Append(strUrl);
sb.Append("'> 下一页</a>");
}
Label3.Text = string.Format("总记录数：<span style='color:red'>{0}</span> 总页数：<span style='color:red'>{1}</span> 当前页码：<span style='color:red'>{2}</span>", Record, PageCount, CurrentPage);
Label4.Text = sb.ToString();
}
private void BindSOSOPager()
{
// 获取当前请求的虚拟路径
string PageUrl = Request.FilePath;
// 获取总的页数
int PageCount = (int)Math.Ceiling((double)(Record) / PageSize);
System.Text.StringBuilder sb = new System.Text.StringBuilder();
if (Request.QueryString["page"] == null || Request.QueryString["page"] == "1")
{
if (PageCount > 1)
{
if (PageCount < 12)
{
for (int i = 1; i <= PageCount; i++)
{
if (i == 1)
sb.Append("<span style='color:red; font-weight:bold'> 1</span>");
else
sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString()));
}
}
else
{
for (int i = 1; i <= PageCount; i++)
{
if (i < 11)
{
if (i == 1)
sb.Append("<span style='color:red; font-weight:bold'> 1</span>");
else
sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString()));
}
else if (i == PageCount)
sb.Append(string.Format(" ... [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString()));
}
}
sb.Append(string.Format(" <a href='{0}?page=2{1}'>下一页</a>", PageUrl, strUrl));
}
}
else if (Request.QueryString["page"] != null || Request.QueryString["page"] != "")
{
if (PageCount < 12)
{
if (CurrentPage == 1)
{
for (int i = 1; i <= PageCount; i++)
{
if (i == 1)
sb.Append("<span style='color:red; font-weight:bold'> 1</span>");
else
sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}]</a>", PageUrl, i.ToString(), strUrl, i.ToString()));
}
sb.Append(string.Format(" <a href='{0}?page={1}{2}'>下一页</a>", PageUrl, Convert.ToString(CurrentPage + 1), strUrl));
}
else if (CurrentPage == PageCount)
{
sb.Append(string.Format(" <a href='{0}?page={1}{2}'>上一页</a>", PageUrl, Convert.ToString(CurrentPage - 1), strUrl));
for (int i = 1; i <= PageCount; i++)
{
if (i == PageCount)
sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString()));
else
sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString()));
}
}
else
{
sb.Append(string.Format(" <a href='{0}?page={1}{2}'>上一页</a>", PageUrl, Convert.ToString(CurrentPage - 1), strUrl));
for (int i = 1; i <= PageCount; i++)
{
if (i == CurrentPage)
sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString()));
else
sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString()));
}
sb.Append(string.Format(" <a href='{0}?page={1}{2}'>下一页</a>", PageUrl, Convert.ToString(CurrentPage + 1), strUrl));
}
}
else
{
if (CurrentPage != 1)
sb.Append(string.Format(" <a href='{0}?page={1}{2}'>上一页</a>", PageUrl, Convert.ToString(CurrentPage - 1), strUrl));
if (PageCount > 11 && CurrentPage > 6 && CurrentPage < PageCount - 5)
{
for (int i = 1; i <= PageCount; i++)
{
if (i == 1 && CurrentPage > 6 && PageCount > 11)
sb.Append(string.Format(" <a href='{0}?page=1{1}'>[1]</a> ...", PageUrl, strUrl));
else if (i == PageCount && CurrentPage > 6 && PageCount > 11)
sb.Append(string.Format(" ... <a href='{0}?page={1}{2}'>[{3}]</a>", PageUrl, i.ToString(), strUrl, i.ToString()));
else if (i.ToString() == Request.QueryString["page"].ToString())
sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString()));
else if (i < CurrentPage + 5 && i > CurrentPage - 5)
sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString()));
}
}
else if (PageCount > 11 && CurrentPage > 6 && CurrentPage >= PageCount - 5)
{
for (int i = 1; i <= PageCount; i++)
{
if (i == 1 && CurrentPage > 6 && PageCount > 11)
sb.Append(string.Format(" <a href='{0}?page=1{1}'>[1]</a> ...", PageUrl, strUrl));
else if (CurrentPage == PageCount && i.ToString() == Request.QueryString["page"].ToString())
sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString()));
else if (i.ToString() == Request.QueryString["page"].ToString())
sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString()));
else if (CurrentPage > PageCount - 6 && i > PageCount - 10)
sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString()));
else if (i < CurrentPage + 5 && i > CurrentPage - 5)
sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString()));
}
}
else if (PageCount > 11 && CurrentPage <= 6 && CurrentPage < PageCount - 5)
{
for (int i = 1; i <= PageCount; i++)
{
if (i == 1 && CurrentPage == 1)
sb.Append("<span style='color:red; font-weight:bold'> 1</span>");
else if (i.ToString() == Request.QueryString["page"].ToString())
sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString()));
else if (i == PageCount)
sb.Append(string.Format(" ... <a href='{0}?page={1}{2}'>[{3}]</a>", PageUrl, i.ToString(), strUrl, i.ToString()));
else if (CurrentPage < 7 && i < 11)
sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString()));
else if (i < CurrentPage + 5 && i >= CurrentPage - 5)
sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString()));
}
}
if (CurrentPage != PageCount)
sb.Append(string.Format(" <a href='{0}?page={1}{2}'>下一页</a>", PageUrl, Convert.ToString(CurrentPage + 1), strUrl));
}
}
Label1.Text = string.Format("总记录数：<span style='color:red'>{0}</span> 总页数：<span style='color:red'>{1}</span> 当前页码：<span style='color:red'>{2}</span>", Record, PageCount, CurrentPage);
Label2.Text = sb.ToString();
}
protected void btnSearch_Click(object sender, EventArgs e)
{
Response.Redirect("Notice.aspx?key=" + txtKey.Text + "&istop=" + Status.Value + "&starttime=" + txtStartTime.Text + "&endtime=" + txtEndTime.Text);
}
protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
{
try
{
notice.Delete(Convert.ToInt32(e.CommandArgument.ToString()));
ClientScript.RegisterStartupScript(GetType(), "", "alert('删除成功！');", true);
BindData();
BindSOSOPager();
BindPager();
}
catch
{
ClientScript.RegisterStartupScript(GetType(), "", "alert('出现错误！');", true);
}
}
protected void lbtDeleteAll_Click(object sender, EventArgs e)
{
try
{
for (int i = 0; i < Repeater1.Items.Count; i++)
{
if (((CheckBox)Repeater1.Items.FindControl("CheckBox1")).Checked == true)
notice.Delete(Convert.ToInt32(((HiddenField)Repeater1.Items.FindControl("HiddenField1")).Value));
}
ClientScript.RegisterStartupScript(GetType(), "", "alert('删除成功！');", true);
BindData();
BindSOSOPager();
BindPager();
}
catch
{
ClientScript.RegisterStartupScript(GetType(), "", "alert('出现错误！');", true);
}
}
}