又要请教大家一个问题了!进入正题~我现在想让查询出来的数据分页显示~要手写的!用ado.net!下面是查询的后台代码:
string connStr = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Fom1.mdf;Integrated Security=True;User Instance=True"; SqlConnection conn = new SqlConnection(connStr);
conn.Open();
string SQL = string SQL = "select * from menu";
SqlCommand comm = new SqlCommand(SQL, conn);
SqlDataReader da = comm.ExecuteReader();
while (da.Read())
{
Response.Write("<table>");
Response.Write("<tr><td>" + da[0] + "</td><td>" + da[1] + "</td><td>" + da[2] + "</td></tr>"); }
Response.Write("</table>");
}
然后怎么写可以达到用GridView控件自带的那种分页效果~麻烦大家了!在线等!
string connStr = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Fom1.mdf;Integrated Security=True;User Instance=True"; SqlConnection conn = new SqlConnection(connStr);
conn.Open();
string SQL = string SQL = "select * from menu";
SqlCommand comm = new SqlCommand(SQL, conn);
SqlDataReader da = comm.ExecuteReader();
while (da.Read())
{
Response.Write("<table>");
Response.Write("<tr><td>" + da[0] + "</td><td>" + da[1] + "</td><td>" + da[2] + "</td></tr>"); }
Response.Write("</table>");
}
然后怎么写可以达到用GridView控件自带的那种分页效果~麻烦大家了!在线等!
解决方案 »
- 求问大家 两个表中的非主键字段 如何关联?
- 请教下这个sql语句怎么写
- 急 发表评论后,在刷新页面,评论内容又重复的发表了?? 请大虾们解决~!~
- 谁能告诉我海盗船U盘价格是多少?
- aspx文件改成htm后字会变小,为什么?
- 这是一datagrid点击datagrid中的数据一下就可以更新,2下就可以保存的代码,我搞不懂为什么我的总是保存不了,请高手指点
- 在ACCESS中日期时间的比较
- 高手救命:SQL Server的newid()在程序中生成的随机记录产生重复
- 关于DataGrid自动创建列的问题
- datagrid中的问题:删除datagrid中某行时出现的问题。
- 求日期控件
- 怎么样处理点击视频扣点值的问题
<asp:LinkButton ID="First" runat="server" CommandArgument="first" OnClick="PagerButton_Click">首 页</asp:LinkButton>
<asp:LinkButton ID="Prev" runat="server" CommandArgument="prev" OnClick="PagerButton_Click">上一页</asp:LinkButton>
<asp:LinkButton ID="Next" runat="server" CommandArgument="next" OnClick="PagerButton_Click">下一页</asp:LinkButton>
<asp:LinkButton ID="Last" runat="server" CommandArgument="last" OnClick="PagerButton_Click">尾 页</asp:LinkButton>protected void griew_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
griew.PageIndex = e.NewPageIndex;
BindData();
}
http://topic.csdn.net/u/20100302/09/9a6bd89a-019c-4fc6-9a4d-c30501d35ec5.html
我想建SQL语句,将WHERE子句以及ORDER BY子句和TOP谓词组合起来
等来达到分页效果!也可完全自定义,来写分页功能!麻烦大家帮忙!不管大家用什么方法!希望可以把前台和后台的代码都贴出来,我是新手!要是大家嫌要不够的话,我可以再加!谢谢大家了!
我想建SQL语句,将WHERE子句以及ORDER BY子句和TOP谓词组合起来
等来达到分页效果!也可完全自定义,来写分页功能!麻烦大家帮忙!不管大家用什么方法!希望可以把前台和后台的代码都贴出来,我是新手!要是大家要嫌分不够的话,我可以再加!谢谢大家了!
<asp:Label ID="lblMessage" runat="server" Text="Label"></asp:Label>
<asp:Label ID="lblCurrentPage" runat="server"></asp:Label><br />
<asp:LinkButton ID="lbFirst" runat="server" OnClick="lbFirst_Click">第一页</asp:LinkButton>
<asp:LinkButton ID="lbPre" runat="server" OnClick="lbPre_Click">上一页</asp:LinkButton>
<asp:LinkButton ID="lbNext" runat="server" OnClick="lbNext_Click">下一页</asp:LinkButton>
<asp:LinkButton ID="lbLast" runat="server" OnClick="lbLast_Click">最后一页</asp:LinkButton><br /> public void DataBind(DataTable dt)
{
Pds = new PagedDataSource();
// mop.ShowIpTime(Session["jur"].ToString(), Convert.ToDateTime(todayDate)).DefaultView
Pds.DataSource = dt.DefaultView;
int recCount = Pds.Count;//总的记录数
Pds.AllowPaging = true;
Pds.PageSize = 25;//每页显示25条记录
int pageCount = Pds.PageCount; //页数
this.lblMessage.Text = "(共<font color='red'>" + recCount.ToString() + "</font>条记录 <font color='red'>" + pageCount.ToString() + "</font>页)";
this.lblCurrentPage.Text = "当前第<font color='red'>" + (Pds.CurrentPageIndex + 1).ToString() + "</font>页"; dgMemberInfo.DataSource = Pds;
dgMemberInfo.DataBind();
}protected void lbFirst_Click(object sender, EventArgs e)
{
Pds.CurrentPageIndex = 0;
this.dgMemberInfo.DataSource = Pds;
this.dgMemberInfo.DataBind();
this.lbFirst.Enabled = false;
this.lbPre.Enabled = false;
this.lbNext.Enabled = true;
this.lbLast.Enabled = true;
this.lblCurrentPage.Text = "当前第<font color='red'>" + (Pds.CurrentPageIndex + 1).ToString() + "</font>页";
}
protected void lbPre_Click(object sender, EventArgs e)
{
if (Pds.CurrentPageIndex > 0)
{
if (Pds.CurrentPageIndex == Pds.PageCount - 1)
{
this.lbNext.Enabled = true;
this.lbLast.Enabled = true;
}
Pds.CurrentPageIndex--;
this.dgMemberInfo.DataSource = Pds;
this.dgMemberInfo.DataBind();
this.lblCurrentPage.Text = "当前第<font color='red'>" + (Pds.CurrentPageIndex + 1).ToString() + "</font>页";
}
//else
if (Pds.CurrentPageIndex == 0)
{
this.lbFirst.Enabled = false;
this.lbPre.Enabled = false;
}
}
protected void lbNext_Click(object sender, EventArgs e)
{
if (Pds.CurrentPageIndex < Pds.PageCount - 1)
{
if (Pds.CurrentPageIndex == 0)
{
this.lbPre.Enabled = true;
this.lbFirst.Enabled = true;
}
Pds.CurrentPageIndex++;
this.dgMemberInfo.DataSource = Pds;
this.dgMemberInfo.DataBind();
this.lblCurrentPage.Text = "当前第<font color='red'>" + (Pds.CurrentPageIndex + 1).ToString() + "</font>页";
}
//else
if (Pds.CurrentPageIndex==Pds.PageCount - 1)
{
this.lbNext.Enabled = false;
this.lbLast.Enabled = false;
}
}
protected void lbLast_Click(object sender, EventArgs e)
{
Pds.CurrentPageIndex = Pds.PageCount - 1;
this.dgMemberInfo.DataSource = Pds;
this.dgMemberInfo.DataBind();
this.lbNext.Enabled = false;
this.lbLast.Enabled = false;
this.lbFirst.Enabled = true;
this.lbPre.Enabled = true;
this.lblCurrentPage.Text = "当前第<font color='red'>" + (Pds.CurrentPageIndex + 1).ToString() + "</font>页";
}PagedDataSource 主要用到了这个分页类
ASP.NET分页select top 10 * from table where id not in (select top '(页下标-1)*页大小)' id from table order by id) 后台类里要用ViewSate页面保持对像来保持页大小
Repeater.PageIndex = e.NewPageIndex;
BindData();
<asp:HyperLink ID="lnkPrev" runat="server">上一页</asp:HyperLink>
<asp:HyperLink ID="lnkNext" runat="server">下一页</asp:HyperLink>后台代码
DataSet ds = DataAccessHelper.RunQuery(sSql); //获取Dataset PagedDataSource pds = new PagedDataSource();
pds.DataSource = ds.Tables[0].DefaultView; //设置数据源
pds.AllowPaging = true; //启动分页
pds.PageSize = 18; //在单页显示的项数 if (IsPostBack) //判断是否第一次访问
{
CurrPage = 1; //第一页,currPage是变量,为当前页数的
}
else
{
if (Request.QueryString["Page"] != null) //获取并判断传值"Page",
CurrPage = Convert.ToInt32(Request.QueryString["Page"]);
else
CurrPage = 1; }
pds.CurrentPageIndex = CurrPage - 1; //lblCurrPage.Text = "当前页: " + CurrPage.ToString(); if (CurrPage < pds.PageCount) //当前页小于总页数时将,设置lnkNext控件的NavigateUrl属性,并传值"Page"
{
int NextPage = CurrPage + 1;
lnkNext.NavigateUrl = "Products.aspx?Page=" + NextPage.ToString();
}
if (CurrPage > 1 && CurrPage < pds.PageCount) //当前页大于1,小于总页数时将,设置lnkPrev控件的NavigateUrl属性,并传值"Page"
{
int PrevPage = CurrPage - 1;
lnkPrev.NavigateUrl = "Products.aspx?Page=" + PrevPage.ToString();
} lstProducts.DataSource = pds;
lstProducts.DataBind();
-- 作者: <Jason>
-- 创建时间: <2007-9-05>
-- 描述: <高效分页存储过程,仅适用于Sql2005>
-- Notes: <排序字段强烈建议建索引>
-- =============================================
alter Procedure [dbo].[DataPage]
@TableName varchar(50), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere nvarchar(4000)= Null ,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
--@TotalPage int output, --返回总页数
@OrderType bit -- 设置排序类型,1 升序 0 值则降序
as
begin
declare @strOrder varchar(400) -- 排序类型
declare @TotalPage int
Begin Tran --开始事务
Declare @sql nvarchar(4000);
Declare @totalRecord int;
--计算总记录数
if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
--计算总页数
--@totalRecord
select @TotalPage= CEILING((@totalRecord+0.0)/@PageSize) if @OrderType = 0
begin
set @strOrder = ' order by [' + @OrderField +'] desc'
--如果@OrderType是0,就执行降序,这句很重要!
end
else
begin
set @strOrder = ' order by [' + @OrderField +'] asc'
end
if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over( '+@strOrder+' ) as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over( '+@strOrder+' ) as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1
if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage --处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1 if @OrderType = 0
begin
set @strOrder = ' order by rowid desc'
--如果@OrderType是0,就执行降序,这句很重要!
end
else
begin
set @strOrder = ' order by rowid asc'
end
--继续合成sql语句
set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) + ' '+@strOrder
-- print @sql
Exec(@Sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @TotalPage ---返回记录总数@totalRecord
End
end这个是调用函数 /// <summary>
/// 分页函数
/// </summary>
/// <param name="_keys"></param>
/// <param name="_tableName">表名</param>
/// <param name="_orderField">排序字段</param>
/// <param name="_backupTime"></param>
/// <param name="_pageIndex">索引页</param>
/// <param name="_totalPage">总页码</param>
/// <returns></returns>
public static DataTable DatapageTable(string _keys, string _tableName, string _orderField, DateTime _backupTime, int _pageIndex, ref int _totalPage, ref int _recordCount)
{
try
{
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@TableName", SqlDbType.VarChar, 50),
new SqlParameter("@Fields",SqlDbType.VarChar,1000),
new SqlParameter("@OrderField", SqlDbType.VarChar, 1000),
new SqlParameter("@sqlWhere",SqlDbType.NVarChar,1000),
new SqlParameter("@pageSize", SqlDbType.Int),
new SqlParameter("@pageIndex",SqlDbType.Int),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@TotalPage",SqlDbType.Int),
new SqlParameter("@recordCount",SqlDbType.Int)
};
para[0].Value = _tableName; para[1].Value = "*";
para[2].Value = _orderField; para[3].Value = "keys='" + _keys + "' and datediff(n,backupTime,'" + _backupTime.ToString() + "')=0";
para[4].Value = BackupPhoneMsg_tools.pageSize; para[5].Value = _pageIndex;
para[6].Value = 1;
para[7].Direction = ParameterDirection.ReturnValue; para[8].Direction = ParameterDirection.Output;
DataTable tbRead = DataOp.ExecuteDataSet(DataOp.connBackupPhoneMsg, CommandType.StoredProcedure, "DataPage", para).Tables[0];
_totalPage = int.Parse(para[7].Value.ToString());
_recordCount=int.Parse(para[8].Value.ToString());
return tbRead;
}
catch
{
return null;
}
}
DataTable tbRead = BackupPhoneMsg_db_op.DatapageTable(_boxType, _keys, "smsTable", "date1 desc, time1 desc", _backupTime, _pageIndex, ref totalPage, ref recordCount);