求SQL分页语句!!
我用的是AspNetPager这个分页控件!!
我用的是AspNetPager这个分页控件!!
解决方案 »
- 紧急求助:C++客户端和网站之间单点通讯问题
- C# 图片显示问题
- gridview连数据源后还能增加行吗
- 在别的控件对服务器回发后,如何保留验证控件的状态。
- 欢迎提宝贵意见并奉送membership大礼!!!
- asp.net程序访问慢,工作进程被频繁回收,20分钟不访问,就回收进程,重新编译
- 利用owc导出Excel时颜色的问题!!!(属性、索引器或事件“Color”不受该语言支持)
- 我的vs.net有问题啦!!!
- 这样的程序有人要吗?
- viewstate 中保存我动态生成的Table控件,提示错误让我序列化,请教?最好有序列化的代码
- Asp.net如何添加twitter功能?
- 如何把所有的TextBox的Enabled状态改为False?
CREATE PROCEDURE [dbo].[P_newpager]
@tblname VARCHAR(255), -- 表名
@strGetFields nvarchar(1000) = "*", -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回, 非0 值则返回记录总数
@OrderType bit = 0, -- 设置排序类型, 非0 值则降序
@strWhere varchar(1500) = '' -- 查询条件(注意: 不要加where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 '+ @strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end --以上代码的意思是如果@doCount传递过来的不是,就执行总数统计。以下的所有代码都是@doCount为的情况:
else
begin
if @OrderType != 0--降序
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' ' + @strGetFields + ' from [' + @tblName + '] where 1=1 ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' ' + @strGetFields + ' from [' + @tblName + '] ' + @strOrder--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where 1=1 ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and 1=1 ' + @strWhere + ' ' + @strOrder
end
if @strWhere !='' --得到记录的总行数
set @strSQL =@strSQL+ '; select count(*) as Total from [' + @tblName + '] where 1=1 '+ @strWhere
else
set @strSQL =@strSQL+ '; select count(*) as Total from [' + @tblName + ']'
end
exec (@strSQL)
RETURN2.***.aspx代码:
<asp:GridView ID="GridView1" Width="80%" runat="server"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" />
<asp:BoundField DataField="KHID" HeaderText="客户ID" />
<asp:BoundField DataField="KHDM" HeaderText="客户代码" />
<asp:BoundField DataField="MC" HeaderText="客户姓名" />
<asp:BoundField DataField="DLRQ" HeaderText="登录日期" />
</Columns>
</asp:GridView>
<webdiyer:aspnetpager id="AspNetPager1" runat="server"
ShowCustomInfoSection="Left" UrlPaging="true" ShowBoxThreshold="5" PageSize="25"
pagingbuttontype="Image" width="80%" ImagePath="../../Style/images/pager/"
ButtonImageNameExtension="n" ButtonImageExtension=".gif" AlwaysShow="true"
DisabledButtonImageNameExtension="g" NumericButtonTextFormatString="[{0}]"
CpiButtonImageNameExtension="r" PagingButtonSpacing="10px" ButtonImageAlign="left"
OnPageChanged="AspNetPager1_PageChanged"></webdiyer:aspnetpager> 3.***.aspx.cs代码:protected void Page_Load(object sender, EventArgs e)
{
GridViewBind();
} private void GridViewBind()
{ SqlConnection con = new SqlConnection("server=128.1.3.68;database=XYGL;uid=hatest;pwd=hatest");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "P_newpager";
int pageindex = 0;
string strWhere = "";
if (AspNetPager1.CurrentPageIndex < 1)
{
pageindex = 1;
}
else
{
pageindex = AspNetPager1.CurrentPageIndex;
}
SqlParameter[] parameters ={
new SqlParameter("@tblname",SqlDbType.VarChar,255),
new SqlParameter("@strGetFields",SqlDbType.VarChar,1000),
new SqlParameter("@fldName",SqlDbType.VarChar,255),
new SqlParameter("@PageSize",SqlDbType.Int,4),
new SqlParameter("@PageIndex",SqlDbType.Int,4),
new SqlParameter("@doCount",SqlDbType.Bit),
new SqlParameter("@OrderType",SqlDbType.Bit),
new SqlParameter("@strWhere",SqlDbType.VarChar,2000) };
parameters[0].Value = "usiKHKHDJ";//表或视图名
parameters[1].Value = "ID,JSID,KHID,KHDM,MC,DLRQ";//显示字段
parameters[2].Value = "ID";//以某字段排序
parameters[3].Value = AspNetPager1.PageSize;//多少条为一页
parameters[4].Value = pageindex;//索引页
parameters[5].Value = 0;//默认0就行了
parameters[6].Value = 1;//这里的1是降序,0为升序
parameters[7].Value = strWhere;//查询条件组合
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
//动态设置用户自定义文本内容
AspNetPager1.RecordCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
AspNetPager1.CustomInfoHTML = "共" + AspNetPager1.RecordCount.ToString() + "条,";
AspNetPager1.CustomInfoHTML += "" + AspNetPager1.PageCount.ToString() + "页,";
AspNetPager1.CustomInfoHTML += "第" + AspNetPager1.CurrentPageIndex.ToString() + "页";
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
GridViewBind();
}
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 1 AND Row <= 10The second page of 10 records would then be as follows:SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 11 AND Row <= 20If you have a lot of records, using TOP X in the inner SELECT clause may speed up things a bit as there is no use returning 1000 records if you are only going to grab records 11 through 20:SELECT Description, Date
FROM (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 11 AND Row <= 20We can rap this up in a Stored Procedure as follows:CREATE PROCEDURE dbo.ShowLog
@PageIndex INT,
@PageSize INT
ASBEGIN WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)SELECT Date, Description
FROM LogEntries
WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
END It is only available in SQL Server 2005, but it is a heck of a lot easier and more intuitive than creating temp tables and using other stored procedures that I have used in the past. However, if you want to target your application for SQL Server 2000 use, I would stick with a record paging solution that works for both SQL Server 2005 and SQL Server 2000 Databases.The code write in store procedure:
CREATE Procedure WebOrdersSearchOrderIdByCustomerNumberForPaging
(
@customerNumber VARCHAR(15),
@SortField VARCHAR(20) = 'orderId',
@SortType VARCHAR(10) = 'DESC',
@PageSize INT = 10,
@PageIndex INT = 0,
@TotalCount INT OUTPUT
)
AS
BEGIN
SELECT @TotalCount = count(orderId) FROM orders
WHERE customerNumber = @customerNumber
SET @sqlSelect = 'SELECT orderId FROM ('
SET @sqlSelect= @sqlSelect + 'SELECT ROW_NUMBER() OVER(ORDER BY ' + @SortField + ' ' + @SortType + ') AS rowNumber'
SET @sqlSelect= @sqlSelect + ', orderId'
SET @sqlSelect= @sqlSelect + ' FROM orders '
SET @sqlSelect= @sqlSelect + ') AS PagingTable '
SET @sqlSelect = @sqlSelect + ' WHERE rowNumber >=' + CONVERT(VARCHAR, @PageSize * @PageIndex)
SET @sqlSelect = @sqlSelect + ' AND rowNumber <' + CONVERT(VARCHAR, @PageSize * (@PageIndex + 1))
EXEC(@sqlSelect)ENDGO
绑定的是repeater
代码如下:
protected void BackMess_DataBindPager(int index)
{
int count = 10;
DataTable ds = bll.Get_Simple("view_messageList", "*","mess_time", index,3,1,"",out count);
this.rp_messbackList.DataSource = ds;
this.rp_messbackList.DataBind();
this.AspNetPager1.RecordCount = count;
//this.AspNetPager1.p
} protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
{
BackMess_DataBindPager(e.NewPageIndex);
}可是还是分不了页面,能帮帮吗,搞定了马上给分!!
protected void bind()
{
string sql = "select * from news order by newtime desc";
string con = AccessHelper.conn;
PagedDataSource ps = new PagedDataSource();
ps.DataSource = AccessHelper.ExecuteDataSet(con, sql).Tables[0].DefaultView;
AspNetPager1.RecordCount = ps.Count;
ps.CurrentPageIndex = AspNetPager1.CurrentPageIndex - 1;
ps.AllowPaging = true;
ps.PageSize = AspNetPager1.PageSize;
DataList1.DataSource = ps;
DataList1.DataBind();
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
bind();
}
AspNetPager1_PageChanged没有e.NewPageIndex这个属性
select top pangeSize * from table where id not in (select top (currentPage-1)*pageSize id from table)
高效分页
where xxxx
sql = "select distinct top " + pageSize + " * from tab_mother where mid < ( select min(mid) from (" + sql3 + ") a) order by mid desc";
SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ProductID ASC) RowNum, * FROM Production.Product ) OrderData WHERE RowNum BETWEEN @iRowCount*(@iPageNo-1)+1 and @iRowCount*@iPageNo
{
#region 分页实现
DataTable result = null;
count = 0;
SqlDataReader reader = null;
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar,255),
new SqlParameter("@strGetFields", SqlDbType.VarChar,255),
new SqlParameter("@fldName", SqlDbType.VarChar,1000),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@OrderType", SqlDbType.Int),
new SqlParameter("@strWhere", SqlDbType.VarChar,255),
new SqlParameter("@doCount",SqlDbType.Int),
//new SqlParameter("recordcount",SqlDbType.VarChar,1000,ParameterDirection.Output,count)
}; parameters[0].Value = tableName;
parameters[1].Value = strGetFields;
parameters[2].Value = fldName;
parameters[3].Value = pageIndex;
parameters[4].Value = pageSize;
parameters[6].Value = strWhere;
parameters[7].Value = count;
//try
//{
result = new DataTable(tableName);
reader = DbHelperSQL.RunProcedure("P_newpager", parameters); result.Load(reader);
count = Convert.ToInt32(parameters[7].Value);
reader.Close();
reader = null;
if (result != null && result.Rows.Count == 0 && pageIndex > 1)
{
result = Get_Simple(tableName, strGetFields, fldName, pageIndex - 1, pageSize, orderType, strWhere, out count);
} return result;
#endregion }请问这样写有错吗?
@pagesize int,
@currentpageIndex int
as
declare @sql nvarchar(500)
set @sql=' select Top '+ convert(varchar(10),@pagesize)+
' * from NewsInfo where NewsId not in (select Top '+
convert(varchar(10),(@currentpageIndex-1) * @pagesize)+
' NewsId from NewsInfo order by NewsId ASC) order by NewsId ASC '
exec sp_executesql @sql