哪位大神给帖点分页代码啊(关于gridview,repeater,DetailsView的!)尽量是sql实现的啊!谢谢大神啊
解决方案 »
- jquery中的outerWidth()、outerHeight()与width()、height()的区别
- 我司网站经常卡住,原因分析?
- 关于GridView的显示排版问题
- 服务器控件 button 如何实现html控件<input type="button" value="" onclick="window.history.go(-1);"></asp:Button>
- 请教CheckBoxList 多选问题
- 如何将查出来的数据转成表二
- 为什么这段vbscript的代码不能运行
- 当aspx文件中的form启用action后,再从工具栏拖入插入控件时,action会自动消失?
- sql+gridview
- 找不到安装的 ISAM。。?
- .net 客户端播放wav wav在服务器目录下
- 【求教】关于用户登陆,web.config里面<authentication mode="Forms">和手动写session来做,有什么不一样吗?
if(object_id('p_PartPage') is not null)
drop proc [dbo].[p_PartPage]
go
create PROCEDURE [dbo].[p_PartPage]
@tableName varchar(50),
@tableId varchar(50),
@pageIndex int, -- 当前页索引
@pageSize int, -- 每页显示多少条
@strWhere varchar(500), -- 查询条件
@orderCol varchar(100), -- 排序列 [id] desc, [name] asc
@Fields varchar(1000) = '*'--读取字段
ASDECLARE @strFilter varchar(2000)
declare @sql varchar(8000)
IF @strWhere IS NOT NULL AND @strWhere != ''
BEGIN
SET @strFilter = ' WHERE ' + @strWhere + ' '
END
ELSE
BEGIN
SET @strFilter = ''
END
if @orderCol = ''
set @orderCol = @tableId
IF @pageIndex < 1
SET @pageIndex = 1if @pageIndex = 1 --第一页提高性能
begin
set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ ' from ' + @tableName + ' ' + @strFilter + ' ORDER BY '+ @orderCol
end
else
begin
/**//*Execute dynamic query*/
DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)
SET @START_ID = convert(varchar(50),(@pageIndex - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@pageIndex * @PageSize)
set @sql = ' SELECT '+@Fields+ '
FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@orderCol+') AS rownum,
'+@Fields+ '
FROM '+@tableName+' ' +@strFilter+') AS D
WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@orderCol
END
set @sql = @sql + '; SELECT Count(*) FROM ' + @tableName + @strFilter
EXEC(@sql)
go
select top 10 * from [table] where id not in (select top 10*(2-1) id from [table]) order by id desc) order by id desc10 是分页大小,2是当前页
注意排序要一样,之前我也用存储过程,但是通用的存储过程需要拼where条件,有注入漏洞
推荐:AspNetPager+存储过程
/// <summary>
/// 根据传入的sql文,获取单页数据
/// </summary>
/// <param name="strSql">用以获取数据的sql文</param>
/// <param name="spIn">传入的参数</param>
/// <param name="strOrderBy">为确定数据所在页码,需要进行排序</param>
/// <param name="iCurrPage">当前页 页码从0开始</param>
/// <param name="iPageSize">每页数据量</param>
/// <param name="iRowCount">总数据量</param>
/// <returns></returns>
public System.Data.DataTable GetDataByPage(string strSql, System.Data.SqlClient.SqlParameter[] spIn, int iCurrPage, int iPageSize, out int iRowCount)
{
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.SqlClient.SqlDataReader reader = null; conn.ConnectionString = "自己的数据库连接字符串";
comm.Connection = conn; iRowCount = 0;
try
{ if (iPageSize > 0)
{
if (iCurrPage <= 0)
{
iCurrPage = 0;
} strSql = @"select * from (select count(1) over() row_cnt
, DENSE_RANK() over(order by CENTER_ID) Center_idx
, row_number() over(order by CENTER_ID, CENTER_NAME desc, Total_Income desc) row_idx
, *
from (" + strSql + ")T_Paging_Inside)T_Paging_Outside "
+ " where row_idx > " + ((iCurrPage - 1) * iPageSize).ToString()
+ " and row_idx <= " + (iCurrPage * iPageSize).ToString(); } comm.CommandText = strSql;
comm.Parameters.AddRange(spIn);
comm.Connection.Open();
reader = comm.ExecuteReader();
dt.Load(reader); if (dt != null && dt.Rows.Count > 0)
{
if (iPageSize > 0)
{
iRowCount = int.Parse(dt.Rows[0]["row_cnt"].ToString());
}
else
{
iRowCount = dt.Rows.Count;
}
}
}
catch
{
dt = null;
} return dt;
}
strSql = @"select * from (select count(1) over() row_cnt
, row_number() over(order by 用来排序的字段,也可以做成参数传进来) row_idx
, *
from (" + strSql + ")T_Paging_Inside)T_Paging_Outside "
+ " where row_idx > " + ((iCurrPage - 1) * iPageSize).ToString()
+ " and row_idx <= " + (iCurrPage * iPageSize).ToString();
--第1页:从 1 到 10
--第2页:从 11 到 20
--第3页:从 21 到 30--找规律--A、创建视图:获取到连续并且自动增长的ID
create view vw_book
as
select row_number() over(order by Id) bookID,* from Books
go
--B、创建存储过程,设置页面的起始编号
create procedure getBooks
(
@pageIndex int,--页面索引
@pageSize int--页面显示多少条记录
)
as
begin
declare @startIndex int--每页开始编号
declare @endIndex int--每页结束编号 set @startIndex=(@pageIndex-1)*@pageSize+1
set @endIndex=@pageIndex*@pageSize select * from vw_book where bookID between @startIndex and @endIndex
end
goexecute getBooks 1,10
execute getBooks 2,10
gousing System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;namespace Demo2
{
public partial class Repeater_Advance3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["page"] = 1;//必须将ViewState放在程序首次加载中
BindBook();
} DataTable dt=DBHelper.GetDataSet("select * from categories");
this.DropDownList1.DataSource = dt; this.DropDownList1.DataTextField = "Name";
this.DropDownList1.DataValueField = "Id"; this.DropDownList1.DataBind();
}
private void BindBook()
{
SqlConnection conn = new SqlConnection("server=.;database=MyBookShop;integrated security=True");
SqlDataAdapter sda = new SqlDataAdapter("getBooks", conn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;//语句类型为存储过程类型 //添加参数:参数名称必须与数据库中存储过程的名称一致 参数的个数也必须一致 类型也必须一致
SqlParameter pageIndex = sda.SelectCommand.Parameters.Add("@pageIndex", SqlDbType.Int);
SqlParameter pageSize = sda.SelectCommand.Parameters.Add("@pageSize", SqlDbType.Int); //设置参数的值; ViewState存储值的意义是:页面全局变量并且与刷新无关
pageIndex.Value = ViewState["page"];//改变她 :页面索引
pageSize.Value = 5; DataTable dt = new DataTable();
sda.Fill(dt); this.Repeater1.DataSource = dt;
this.Repeater1.DataBind();
}
//上页
protected void btnPrev_Click(object sender, EventArgs e)
{
ViewState["page"] = Convert.ToInt32(ViewState["page"]) - 1;
BindBook();
}
//下页
protected void btnNext_Click(object sender, EventArgs e)
{
ViewState["page"] = Convert.ToInt32(ViewState["page"]) + 1;
BindBook();
}
}
}