我使用了AspNetPager控件。
虽然已经可以分页了,但是我是用select * 这样的SQL语句实现的。
这样的话,记录多了就动不了了。
那我该怎么写呢?写存储过程吗?还是怎样?
我用的是asp.net+oracle。。
虽然已经可以分页了,但是我是用select * 这样的SQL语句实现的。
这样的话,记录多了就动不了了。
那我该怎么写呢?写存储过程吗?还是怎样?
我用的是asp.net+oracle。。
然后,建立索引,使用存储过程,不使用*,只查询需要字段 等 这些都可以起到一定的优化作用。AspNetPager具体使用参见其官方网站:www.webdiyer.comPS:个人使用oracle经验感觉,oracle想用好,还是要花点时间研究。 尤其在性能提升方便。
存储过程中设定一些Out参数(执行存储过程时用ref,就可以得到),作为AspNetPager的设定参数就可以了。
经典 这样效率比较高用select top 每页显示几条 from table where id not in ( select (当前第几页-1)*每页显示几条 id from table order by id desc) order by id desc
看好这个貌似后面少了个top
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[EmpPage]
@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
ASP.NET WebForm和ASP.NET MVC分页最终解决之道
楼主我告诉你个秘密
17楼就是AspNetPager的作者,听他的不会错!
如果你用sql server,可以用我这个工具生成分页存储过程 http://www.webdiyer.com/Products/AspNetPager/SpGenerator ,oracle已经好几年没用了,忘了怎么写分页存储过程的了,网上搜一下,应该有别人发过的。
这要在数据库优化方面想办法,用存储过程及分表保存数据等,oracle很早就支持分表了,不过几十万数据量的话一个表也慢不了多少,我操作过一百万的,稍微慢了些,但能应付,内部网用。===================================================
ASP.NET WebForm和ASP.NET MVC分页最终解决之道
ASP.NET WebForm和ASP.NET MVC分页最终解决之道
用vs自带的objectdatasource 试试
http://www.webdiyer.com/ 我的主页有
ALTER proc PageprocBySql]
(
@strSQL varchar(max),
@pageIndex int, --当前页索引
@pagesize int, --每页显示的行数
@paixu varchar(50), --排序
@pagecount int output, --总行数
@id varchar(20) --标识列(自动增长)名称
)
as
--获取总行数
declare @zonghang int;
declare @getcountsql nvarchar(max)
set @getcountsql='select @a=count(*) from ('+@strSQL+') as aa'
exec sp_executesql @getcountsql,N'@a int output',@zonghang output --把执行的结果赋给变量@zonghangdeclare @SQL nvarchar(max)
begin
if @pageIndex!=0
begin
if(@paixu!='')
begin
set @SQL='select * from (select top '+convert(varchar,@pagesize)+' * from (select * from ('+@strSQL+') as cc where '+@id+' > (select max( '+@id+' ) from (select top '+Convert(varchar,@pagesize*@pageIndex)+' '+@id+' from ('+@strSQL+') as aa order by '+@id+' asc ) as bb)) as ee order by '+@id+' asc) as vv order by '+@paixu+' desc';
print @sql
end
else
begin
set @SQL='select top '+convert(varchar,@pagesize)+' * from (select * from ('+@strSQL+') as cc where '+@id+'> (select max( '+@id+' ) from (select top '+Convert(varchar,@pagesize*@pageIndex)+' '+@id+' from ('+@strSQL+') as aa order by '+@id+' asc ) as bb)) as ee';
end
end
else
begin
if(@paixu!='')
begin
set @SQL='select top '+convert(varchar,@pagesize)+' * from (select top '+convert(varchar,@pagesize)+' * from ('+@strSQL+') as aa order by '+@id+' asc) as bb order by '+@paixu+' desc';
print @sql
end
else
begin
set @SQL='select top '+convert(varchar,@pagesize)+' * from ('+@strSQL+') as aa ';
end
end
exec sp_executesql @SQL
end
begin
if @zonghang%@pagesize!=0
begin
set @pagecount=(@zonghang/@pagesize)+1;
end
else
begin
set @pagecount=@zonghang/@pagesize;
end
end[/code]如果是oracle 把那个按照编号排序 改成 rownum 就可以了
WHERE RN BETWEEN " + a + " AND " + b + "
a,b的值变化是这样的: 1,20 21,40 41,60这样改变的。
(RN 是ROWNUM的伪列)
就是1到20的记录在第一页会显示的。点下一页的时候就没有记录了。这是为什么啊?
WHERE RN BETWEEN " + a + " AND " + b + "
a,b的值变化是这样的: 1,20 21,40 41,60这样改变的。
(RN 是ROWNUM的伪列)
就是1到20的记录在第一页会显示的。点下一页的时候就没有记录了。这是为什么啊?有人
帮我看看这个问题撒
WHERE RN BETWEEN " + a + " AND " + b + "
a,b的值变化是这样的: 1,20 21,40 41,60这样改变的。
(RN 是ROWNUM的伪列)
就是1到20的记录在第一页会显示的。点下一页的时候就没有记录了。这是为什么啊? 有人
帮我看看这个问题撒
//绑定数据的部分
public void BindData()
{
string bloodID = "";
string patientID = "";
string name = "";
string liquid_num = "";
string rack_num = "";
string box_num = "";
int firstPage = AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex - 1);
int pageSize = AspNetPager1.PageSize;
int a = AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex - 1) + 1;
int b = AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex); DataSet ds = cbb.SearchChaBlood(bloodID, patientID, name, liquid_num, rack_num, box_num, firstPage, pageSize, a, b);
if (ds != null && ds.Tables[0].Rows.Count != 0)
{
this.gvBlood.DataSource = ds.Tables[0].DefaultView;
this.gvBlood.DataBind();
}
try
{
if (ds.Tables[0].Rows.Count == 0)
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvBlood.DataSource = ds;
gvBlood.DataBind();
int columnCount = gvBlood.Rows[0].Cells.Count;
gvBlood.Rows[0].Cells.Clear();
gvBlood.Rows[0].Cells.Add(new TableCell());
gvBlood.Rows[0].Cells[0].ColumnSpan = columnCount;
gvBlood.Rows[0].Cells[0].Text = "没 有 记 录";
gvBlood.Rows[0].Cells[0].Height = 30;
gvBlood.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;
//gvBlood.Rows[0].Cells[0].ForeColor = System.Drawing.Color.Blue;
//gvBlood.Rows[0].Cells[0].BackColor = System.Drawing.Color.FromName("#fff");
}
}
catch (Exception ex)
{
}
//动态设置用户自定义文本内容
AspNetPager1.CustomInfoHTML = "记录总数:<font color=\"blue\"><b>" + AspNetPager1.RecordCount.ToString() + "</b></font> ";
AspNetPager1.CustomInfoHTML += " 总页数:<font color=\"blue\"><b>" + AspNetPager1.PageCount.ToString() + "</b></font> ";
AspNetPager1.CustomInfoHTML += " 当前页:<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>";
}//SQL语句部分
public DataSet SearchChaBlood(string bloodId, string patientId, string name, string div_id, string rack_id, string box_id, int firstPage, int pageSize, int a, int b)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT RN,BLOODID,PATIENTID,PATIENT_NAME,LESION_ORGAN,");
sb.Append(" BLOOD_PERSON,BLOOD_DATE,DIV_ID,RACK_ID,");
sb.Append(" BOX_ID,SAMPLE_XY,INBOUND_DATE, ");
sb.Append(" INBOUND_HANDLER,OUTBOUND_DATE, OUTBOUND_HANDLER,");
sb.Append(" EDIT_TIMES,LASTEDIT_DATE,LASTEDIT_HANDLER,STATE,");
sb.Append(" DELETE_HANDLER,DELETE_DATE ");
//sb.Append("FROM CTL_CHA_BLOOD, CTL_PATIENT_MESSAGE ");
sb.Append("FROM (SELECT ROWNUM RN , CCB.* FROM ");
sb.Append(" (SELECT CTL_CHA_BLOOD.*,CTL_PATIENT_MESSAGE.NAME AS PATIENT_NAME ");
sb.Append(" FROM CTL_CHA_BLOOD,CTL_PATIENT_MESSAGE ");
sb.Append(" WHERE CTL_CHA_BLOOD.PATIENTID = CTL_PATIENT_MESSAGE.PATIENTID AND CTL_CHA_BLOOD.STATE<2 ");
List<OracleParameter> lps = new List<OracleParameter>();
#region 查询条件
if (bloodId != "")
{
lps.Add(new OracleParameter(":bloodID", bloodId));
sb.Append("AND CTL_CHA_BLOOD.BLOODID=: bloodID ");
}
else
{
if (patientId != "")
{
lps.Add(new OracleParameter(":patientID", patientId));
sb.Append("AND CTL_CHA_BLOOD.PATIENTID=:patientID ");
}
if (name != "")
{
lps.Add(new OracleParameter(":name", "%" + name + "%"));
sb.Append("AND CTL_PATIENT_MESSAGE.NAME LIKE :name "); }
if (div_id != "")
{
lps.Add(new OracleParameter(":div_id", div_id));
sb.Append("AND CTL_CHA_BLOOD.DIV_ID=:div_id ");
}
if (rack_id != "")
{
lps.Add(new OracleParameter(":rack_id", rack_id));
sb.Append("AND CTL_CHA_BLOOD.RACK_ID=:rack_id ");
}
if (box_id != "")
{
lps.Add(new OracleParameter(":box_id", box_id));
sb.Append("AND CTL_CHA_BLOOD.BOX_ID=:box_id ");
}
}
#endregion
//sb.Append("ORDER BY CTL_CHA_BLOOD.ID DESC) CCB ) WHERE RN BETWEEN " + a + " AND " + b + "");
//sb.Append("ORDER BY CTL_CHA_BLOOD.ID DESC) CCB ) WHERE RN BETWEEN 0 AND 20 ");
sb.Append("ORDER BY CTL_CHA_BLOOD.ID DESC) CCB ) ");
return sqlHelper.GetDataSet(sb.ToString(), lps.ToArray(), firstPage, pageSize, "CTL_CHA_BLOOD");
}
public void BindData()
{
string bloodID = "";
string patientID = "";
string name = "";
string liquid_num = "";
string rack_num = "";
string box_num = "";
int firstPage = AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex - 1);
int pageSize = AspNetPager1.PageSize;
int a = AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex - 1) + 1;
int b = AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex); DataSet ds = cbb.SearchChaBlood(bloodID, patientID, name, liquid_num, rack_num, box_num, firstPage, pageSize, a, b);
if (ds != null && ds.Tables[0].Rows.Count != 0)
{
this.gvBlood.DataSource = ds.Tables[0].DefaultView;
this.gvBlood.DataBind();
}
try
{
if (ds.Tables[0].Rows.Count == 0)
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvBlood.DataSource = ds;
gvBlood.DataBind();
int columnCount = gvBlood.Rows[0].Cells.Count;
gvBlood.Rows[0].Cells.Clear();
gvBlood.Rows[0].Cells.Add(new TableCell());
gvBlood.Rows[0].Cells[0].ColumnSpan = columnCount;
gvBlood.Rows[0].Cells[0].Text = "没 有 记 录";
gvBlood.Rows[0].Cells[0].Height = 30;
gvBlood.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;
//gvBlood.Rows[0].Cells[0].ForeColor = System.Drawing.Color.Blue;
//gvBlood.Rows[0].Cells[0].BackColor = System.Drawing.Color.FromName("#fff");
}
}
catch (Exception ex)
{
}
//动态设置用户自定义文本内容
AspNetPager1.CustomInfoHTML = "记录总数:<font color=\"blue\"><b>" + AspNetPager1.RecordCount.ToString() + "</b></font> ";
AspNetPager1.CustomInfoHTML += " 总页数:<font color=\"blue\"><b>" + AspNetPager1.PageCount.ToString() + "</b></font> ";
AspNetPager1.CustomInfoHTML += " 当前页:<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>";
}//SQL语句部分
public DataSet SearchChaBlood(string bloodId, string patientId, string name, string div_id, string rack_id, string box_id, int firstPage, int pageSize, int a, int b)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT RN,BLOODID,PATIENTID,PATIENT_NAME,LESION_ORGAN,");
sb.Append(" BLOOD_PERSON,BLOOD_DATE,DIV_ID,RACK_ID,");
sb.Append(" BOX_ID,SAMPLE_XY,INBOUND_DATE, ");
sb.Append(" INBOUND_HANDLER,OUTBOUND_DATE, OUTBOUND_HANDLER,");
sb.Append(" EDIT_TIMES,LASTEDIT_DATE,LASTEDIT_HANDLER,STATE,");
sb.Append(" DELETE_HANDLER,DELETE_DATE ");
//sb.Append("FROM CTL_CHA_BLOOD, CTL_PATIENT_MESSAGE ");
sb.Append("FROM (SELECT ROWNUM RN , CCB.* FROM ");
sb.Append(" (SELECT CTL_CHA_BLOOD.*,CTL_PATIENT_MESSAGE.NAME AS PATIENT_NAME ");
sb.Append(" FROM CTL_CHA_BLOOD,CTL_PATIENT_MESSAGE ");
sb.Append(" WHERE CTL_CHA_BLOOD.PATIENTID = CTL_PATIENT_MESSAGE.PATIENTID AND CTL_CHA_BLOOD.STATE<2 ");
List<OracleParameter> lps = new List<OracleParameter>();
#region 查询条件
if (bloodId != "")
{
lps.Add(new OracleParameter(":bloodID", bloodId));
sb.Append("AND CTL_CHA_BLOOD.BLOODID=: bloodID ");
}
else
{
if (patientId != "")
{
lps.Add(new OracleParameter(":patientID", patientId));
sb.Append("AND CTL_CHA_BLOOD.PATIENTID=:patientID ");
}
if (name != "")
{
lps.Add(new OracleParameter(":name", "%" + name + "%"));
sb.Append("AND CTL_PATIENT_MESSAGE.NAME LIKE :name "); }
if (div_id != "")
{
lps.Add(new OracleParameter(":div_id", div_id));
sb.Append("AND CTL_CHA_BLOOD.DIV_ID=:div_id ");
}
if (rack_id != "")
{
lps.Add(new OracleParameter(":rack_id", rack_id));
sb.Append("AND CTL_CHA_BLOOD.RACK_ID=:rack_id ");
}
if (box_id != "")
{
lps.Add(new OracleParameter(":box_id", box_id));
sb.Append("AND CTL_CHA_BLOOD.BOX_ID=:box_id ");
}
}
#endregion
sb.Append("ORDER BY CTL_CHA_BLOOD.ID DESC) CCB ) WHERE RN BETWEEN " + a + " AND " + b + "");
return sqlHelper.GetDataSet(sb.ToString(), lps.ToArray(), firstPage, pageSize, "CTL_CHA_BLOOD");
}
sb.Append("ORDER BY CTL_CHA_BLOOD.ID DESC) CCB ) WHERE RN BETWEEN " + a + " AND " + b + "");