Normally we are using Gridview control for Data Binding in ASP.NET.
Below is the sample code for Data Binding in Asp.Net using Gridview Control.

      Sub BindData()
                    Dim con As SqlConnection, sqldad As SqlDataAdapter, ds As DataSet
                    Dim strcon As String
                    Dim server As String
                    Dim database_name, user_name, user_password As String
                    Try
                        server = "your server name"
                        database_name = "database name"
                        user_name = "database user name"
                        user_password = "database password"
                        strcon = "SERVER=" & server & ";DATABASE=" & database_name & 
                                  ";UID=" & user_name & ";PWD=" & user_password
                        con = New SqlConnection(strcon)
                        sqldad = New SqlDataAdapter("select * from Employee", strcon)
                        ds = New DataSet
                        sqldad.Fill(ds)
                        Gridview1.DataSource = ds.Tables(0)
                        Gridview1.DataBind()
                   
                    Catch ex1 As SqlException
                        Response.Write(ex1.Message)
                    Catch ex As Exception
                        Response.Write(ex.Message)
                    End Try
             End Sub      
        

To enable paging for Gridview, just set AllowPaging property to True and give page size.
Assume page size is 10 and we need to implement the gridview’s PageIndexChanging method.

        Protected Sub Gridview1_PageIndexChanging(ByVal sender As Object, ByVal  e As
             System.Web.UI.WebControls.GridViewPageEventArgs) Handles Gridview1.PageIndexChanging
             Gridview1.PageIndex = e.NewPageIndex
             BindData()	
        End Sub
        

That’s it, Gridview paging is working.
It is ok for small amount of data,but it is notsuitable for bulk data.

Why because, for every page it binds total records and showing only particular page records.

Take for example, if our query giving the 10,000 records and we want to show 10 records at a time. What we are doing , we are binding 10,000 records for each requet and we are showing 10 records only. i.e, we are Unneceesarly binding 9990 records. It is giving the more load on Database server for every page request and on webserver also.(because we are binding 10,000 records show just 10 records per page)

Is there any solution to avoid to binding unnecessary records.
Yes, we have a solution by using custom paging with SQL Server 2005.

Custom Paging improves the performance of default paging by retrieving only those records from the database that need to be displayed for the particular page of data requested by the user; however, custom paging involves a bit more effort to implement than default paging.

Implementing Custom Paging:

We need three variables to implement custom paging.
Those are
start row index
end row index
total records

start row index: This is the index of the first row in the page of data to display. This index can be calculated by multiplying the page index by the records to display per page and adding one. For example, when paging through records 10 at a time, for the first page (whose page index is 0), the Start Row Index is 0 * 10 + 1, or 1; for the second page (whose page index is 1), the Start Row Index is 1 * 10 + 1, or 11.

end row index: This is the index of the last row in the page of data to display. This index can be calculated by adding the page size to the start row index-1(already you got this one).For example our page size is 10 and start row index is 31(3rd page) , then our end row index is 40(31-1+10).

total records: This is total number of records being paged through. It dictates the paging interface. For example if the total records are 80 then we came to know that we casme to know that page size is 8.

We can get total records by simple query.

Select count(1) from emp1

Row_Number(): In Sql server 2005 Row_Number() keyword is giving ranking for each Returned record based on some ordering. Use primary key column for ordering.

Sample query: select * from (select *,row_number() over(Order by id) as Rank from emp1) as A

the above query returns all records and row number for each record based on column id(in emp1 table id is primary key coumn).

By using all things above we can do effective pageing.

We need to caluculate total records before doing the pageing.

        

     Private Sub BindData(ByVal startindex As Integer, ByVal endindex As Integer)
        Dim con As SqlConnection, sqldad As SqlDataAdapter, ds As DataSet
        Dim strcon As String
        Dim server As String
        Dim database_name, user_name, user_password As String
        Dim strsql As String
        Try
            server = "your server name"
            database_name = "database name"
            user_name = "database user name"
            user_password = "database password"
            strcon = "SERVER=" & server & ";DATABASE=" & database_name & 
            strcon = strcon & " ;UID=" & user_name & ";PWD=" & user_password
            
            con = New SqlConnection(strcon)

            strsql = "select * from (select *,row_number() over(Order by id) as Rank from "
            strsql = strsql & " emp1) as A where Rank between " & startindex & "and " & endindex"
            
            sqldad = New SqlDataAdapter(strsql, strcon)
            ds = New DataSet
            sqldad.Fill(ds)
            Gridview1.DataSource = ds.Tables(0)
            Gridview1.DataBind()

        Catch ex1 As SqlException
            Response.Write(ex1.Message)
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
    End Sub
    

If you observe query in above code, we put the condition as Rank between starteindex and endindex. i.e, we are retrieving rows only what we required.