ObjectDataSource + Sql Server 2005 + GridView的自定义分页

ObjectDataSource + Sql Server 2005 + GridView的自定义分页

1.数据库结构:

CREATE TABLE [dbo].[test](
[id] [int] IDENTITY(100,1) NOT NULL,
[str] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[val] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

2.添加数据

declare @i int
set @i = 1
while @i < 100
begin
insert into test values(@i,'values'+cast(@i as varchar))
set @i = @i + 1
end

3.书写存储过程

ALTER procedure [dbo].[Pagination]
(
    @startRowIndex int, --开始数据行号
    @PageSize int        --每页显示的数据条数
)
AS
declare @TotalRecord int    --总的记录数
select @TotalRecord  = count(*) from test;

declare @TotalCount int
SELECT @TotalCount = @TotalRecord
    Begin
        select * from
            (
                select id,str,val,row_Number() over (order by ID) as rowIndex from test
            ) t
        where t.rowIndex > @startRowIndex and t.rowIndex < @startRowIndex+@PageSize+1
    End

return @TotalCount

4.书写分页程序

public class DBManager
{
    private int totalCount = 0;
    /**//// <summary>
    /// 自定义分页
    /// </summary>
    /// <param name="startRowIndex">提取的第一行在结果有的序号(从第几行开始取数据)</param>
    /// <param name="maximumRows">每页显多少行(每次取多少行)</param>
    /// <param name="totalCount">总记录数</param>
    /// <returns>强类型的结果集合</returns>
    [DataObjectMethod(DataObjectMethodType.Select)]
    public  List<TestObj> PageSelect(int startRowIndex, int maximumRows)
    {
        string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionString;
        SqlConnection sqlConn = new SqlConnection(strConn);
        SqlCommand sqlCmd = new SqlCommand();

        sqlCmd.Connection = sqlConn;
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.CommandText = "Pagination";

        SqlParameter startRow = sqlCmd.Parameters.Add("@startRowIndex", SqlDbType.Int);
        SqlParameter pageSize = sqlCmd.Parameters.Add("@PageSize", SqlDbType.Int);
        SqlParameter Parrtvalue = sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);

        startRow.Direction = ParameterDirection.Input;

        pageSize.Direction = ParameterDirection.Input;

        Parrtvalue.Direction = ParameterDirection.ReturnValue;

        startRow.Value = startRowIndex;
        pageSize.Value = maximumRows;


        List<TestObj> list = new List<TestObj>();
            TestObj temp = null;
        try
        {
            sqlConn.Open();
            DataSet ds = new DataSet();

            SqlDataAdapter sqlda = new SqlDataAdapter(sqlCmd);
            sqlda.Fill(ds);
            this.totalCount = (int)Parrtvalue.Value;

            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        temp = new TestObj();
                        temp.ID = (int)dr["id"];
                        temp.Str = dr["str"].ToString();
                        temp.Val = dr["val"].ToString();

                        list.Add(temp);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception("error", ex);
        }
        finally
        {
            if (sqlConn.State == ConnectionState.Open)
            {
                sqlConn.Close();
            }
        }

        return list;
    }

    public int getTotalCount()
    {
        return this.totalCount;
    }
}

实体类省去
5.web代码:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Use ObjectDataSource and Sql Server 2005 Pagination</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
                DataSourceID="ObjectDataSource1" PageSize="5" Width="70%">
                <Columns>
                    <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
                    <asp:BoundField DataField="Val" HeaderText="Val" SortExpression="Val" />
                    <asp:BoundField DataField="Str" HeaderText="Str" SortExpression="Str" />
                </Columns>
            </asp:GridView>
            <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}"
                SelectMethod="PageSelect" TypeName="DBManager" SelectCountMethod="getTotalCount" EnablePaging="True">
            </asp:ObjectDataSource>

           </div>
    </form>
</body>
</html>

注意:
ObjectDataSource关于自定义分页几个属性.
AllowPaging属性：当要实现自定义分页时必须把AllowPaging值设置为true。
SelectMethod属性：指向获取数据的方法名称，本例设置为PageSelect。其所指向的方法的参数名与ObjectDataSource的StartRowIndexParameterName、MaximumRowsParameterName、SortParameterName相对应（注意：参数不分先后，但参数名要一一对应相同，否则出错）
StartRowIndexParameterName属性：当前页的第一条数据在查询数据中的位置，它的值与SelectMethod所指向的方法的相应参数，默认值为startRowIndex。
MaximumRowsParameterName属性：当前页面的记录行数，它的值与SelectMethod所指向的方法的相应参数，默认值为maximumRows.
SelectCountMethod指获取记录总数的方法,本例为getTotalCount

本例主要结何SQL Server 2005的新函数row_Number() over (order by xx)来实现分页的!
相关阅读:
腾讯云短信接口完成验证码功能
 git使用的简要介绍
 drf分页组件补充
 drf中的jwt使用与手动签发效验
 django的认证演变过程分析
 drf三大认证补充
 drf三大认证
 IO事件
 配置Java环境变量
 各种O
原文地址：https://www.cnblogs.com/Thinking/p/710514.html