Alter PROCEDURE pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(100) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名(可包含如TABLE.FLDNAME形式)
@PageSize int = 30, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回 s
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @fldName_t varchar(255) -- 在分页时用的排序字段名,不包含多表并列时的表名
set @fldName_t = right(@fldName,len(@fldName)-CHARINDEX('.',@fldName))
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_t + ') 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_t + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
go
原先 @PageSize int = 30, -- 页尺寸
这个是 @PageSize int = 10, -- 页尺寸
现在我改了个值变成 30了
但griview显示的行数还只有10条
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="None" PageSize="30" Height="323px" Width="584px">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
griview里面的pagesize我设置了30了。但每页显示的条数还是 10条。
@tblName varchar(255), -- 表名
@strGetFields varchar(100) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名(可包含如TABLE.FLDNAME形式)
@PageSize int = 30, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回 s
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @fldName_t varchar(255) -- 在分页时用的排序字段名,不包含多表并列时的表名
set @fldName_t = right(@fldName,len(@fldName)-CHARINDEX('.',@fldName))
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_t + ') 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_t + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
go
原先 @PageSize int = 30, -- 页尺寸
这个是 @PageSize int = 10, -- 页尺寸
现在我改了个值变成 30了
但griview显示的行数还只有10条
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="None" PageSize="30" Height="323px" Width="584px">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
griview里面的pagesize我设置了30了。但每页显示的条数还是 10条。
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fields">返回的字段名</param>
/// <param name="orderBy">排序的字段名</param>
/// <param name="blCount">是否返回总数,非 0 值则返回 </param>
/// <param name="orderByType">设置排序类型, 非 0 值则降序 </param>
/// <param name="aspNetPager">分页控件</param>
/// <param name="strWhere">查询条件</param>
public Pagination(string tableName, string fields, string orderBy, int orderByType, Wuqi.Webdiyer.AspNetPager aspNetPager, string strWhere)
{
this.tableName = tableName;
this.strWhere = strWhere;
this.orderBy = orderBy;
this.aspNetPager = aspNetPager;
this.fields = fields;
this.orderByType = orderByType;
//获取总数
SelectByPage(true);
//获取分页数据
SelectByPage(false);
} public void SelectByPage(bool blCount)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@tblName",SqlDbType.NVarChar),
new SqlParameter("@strGetFields",SqlDbType.NVarChar),
new SqlParameter("@fldName",SqlDbType.NVarChar),
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@PageIndex",SqlDbType.Int),
new SqlParameter("@doCount",SqlDbType.Int),
new SqlParameter("@OrderType",SqlDbType.Int),
new SqlParameter("@strWhere",SqlDbType.NVarChar),
};
parameters[0].Value = tableName;
parameters[1].Value = fields;
parameters[2].Value = orderBy;
parameters[3].Value = aspNetPager.PageSize;
parameters[4].Value = aspNetPager.CurrentPageIndex;
parameters[6].Value = orderByType;
parameters[7].Value = strWhere;
if (blCount)
{
parameters[5].Value = 1;
aspNetPager.RecordCount =(int)SqlHelper.ExecuteScalar(SqlHelper.connectionString, CommandType.StoredProcedure, "pagination", parameters);
}
else
{
parameters[5].Value = 0;
ds = SqlHelper.dataSet(SqlHelper.connectionString, CommandType.StoredProcedure, "pagination", parameters);
}
} /// <summary>
/// 绑定gridview控件
/// </summary>
/// <param name="gv"></param>
public void GvBind(System.Web.UI.WebControls.GridView gv)
{
gv.DataSource = ds;
gv.DataBind();
if (ds.Tables[0].Rows.Count <= 0)
{
DataRow dr = ds.Tables[0].NewRow();
ds.Tables[0].Rows.Add(dr);
gv.DataSource = ds;
gv.DataBind();
int columnCount = gv.HeaderRow.Cells.Count; gv.Rows[0].Cells.Clear(); gv.Rows[0].Cells.Add(new System.Web.UI.WebControls.TableCell()); gv.Rows[0].Cells[0].ColumnSpan = columnCount; gv.Rows[0].Cells[0].Text = "没有检索到符合条件的数据信息!";
}
}
调用 Pagination p = new Pagination("test", "*", "tid", 0, AspNetPager1, "");
p.GvBind(GridView1);