新手上路,请多多指教 我的数据库是oracle 有个设备表的数据量有1000万条左右,需要绑定到gridview显示,或者绑定在别的地方也行,并在页面可以显示分页,我自己做的绑定小数据量还行,多了就不行了,有哪位达人有没好的解决方案?万分感谢!附例子最好! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 不用一次取1000万条,用select top的方式来取数据,要多少取多少。 CREATE PROCEDURE pagination @tblName varchar(255), -- 表名 @strGetFields varchar(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 "+@strWhere else set @strSQL = "select count(*) as Total from " + @tblName + " " end --以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况 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 " + @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 " + @strWhere + " " + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder end end exec (@strSQL) GO public DataSet GetList(int PageSize, int PageIndex, string strWhere) { IDataParameter[] parameters = new IDataParameter[]{ new SqlParameter("@tblName", SqlDbType.VarChar, 255), new SqlParameter("@strGetFields", SqlDbType.VarChar, 255), new SqlParameter("@fldName", SqlDbType.VarChar, 255), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int), new SqlParameter("@doCount", SqlDbType.Bit), new SqlParameter("@OrderType", SqlDbType.Bit), new SqlParameter("@strWhere", SqlDbType.VarChar,1000) }; parameters[0].Value = "CangKuStock CKS "; parameters[1].Value = " CKS.CangKuId,CKS.ProductID,CKS.Quantity,CKS.TradeTime,CKS.Man"; parameters[2].Value = "TradeTime"; parameters[3].Value = PageSize; parameters[4].Value = PageIndex; parameters[5].Value = 0; parameters[6].Value = 1; parameters[7].Value = strWhere; return DbHelperSQL.RunProcedure("pagination", parameters, "dtStock"); } 就急。获取鼠标选定的内容和该内容的链接。。 关于CKEditor无法显示的问题 公司项目中双击DataGrid单元格编辑的问题。 令人头痛的W3WP占用资源问题 请问各位datagrid和datalist的删除确定对话框是怎样写的,特别是datalist,拜托(急)在线等 在asp.net中关于sqlserver 数据库表的插入实现 购物车怎么搞 点IE上的刷新按钮和点BUTTON按钮后刷新.有什么区别没? 再问!难题!datagrid与dataset 关于asp和aspx之间相互传值的问题 没分了,提一个简单的问题 ajax控件ModalPopupExtender 的更新问题
if @doCount != 0 begin if @strWhere != " " set @strSQL = "select count(*) as Total from " + @tblName + " where "+@strWhere else set @strSQL = "select count(*) as Total from " + @tblName + " " end --以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况 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 " + @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 " + @strWhere + " " + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder end end exec (@strSQL)
GO
{
IDataParameter[] parameters = new IDataParameter[]{
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@strGetFields", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@doCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000)
};
parameters[0].Value = "CangKuStock CKS ";
parameters[1].Value = " CKS.CangKuId,CKS.ProductID,CKS.Quantity,CKS.TradeTime,CKS.Man";
parameters[2].Value = "TradeTime";
parameters[3].Value = PageSize;
parameters[4].Value = PageIndex;
parameters[5].Value = 0;
parameters[6].Value = 1;
parameters[7].Value = strWhere;
return DbHelperSQL.RunProcedure("pagination", parameters, "dtStock");
}