The GridView loads all rows. As far as I know it does not optimize anything. And if you do not disable ViewState for the GridView (or the page it is on) all those rows are added to the ViewState, resulting in a HUGE page size of way too much MB's.
The paging in the GridView is nothing more than just showing the rows 100 - 200, while the rest still is loaded.
So I would do something like this (more info). Note that order by is required in this case.
SELECT [column list]
FROM [table]
WHERE [bla]
ORDER BY [Bla]
OFFSET [Offset] ROWS FETCH NEXT [Pagesize] ROWS ONLY
Now, how to implement this for a GridView. First, create a Stored Procedure. You could do it without one, but because you also would need the total number of rows you would need two requests to the database. In this example the data is required by a userID as the WHERE parameter.
CREATE PROCEDURE [dbo].[GridView_Paging]
@offset int,
@pagesize int,
@userID int
AS
BEGIN
DECLARE @totalrows INT;
-- you will need the total amount of rows for paging to work
SELECT @totalrows = COUNT(itemID) FROM MyTable WHERE userid = @userID;
--select the data and add the total rows also
SELECT ID, name, @totalrows AS totalrows
FROM MyTable
WHERE userID = @userID
ORDER BY ID
OFFSET @offset ROWS FETCH NEXT @pagesize ROWS ONLY;
END
GO
Then add a GridView to the aspx page and make sure ViewState is off (this saves network traffic). And add a Repeater that will function as the Pager.
<asp:GridView ID="GridView1" runat="server" EnableViewState="false"></asp:GridView>
<asp:Repeater ID="Pager_GridView" runat="server">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" OnCommand="Pager_GridView_Command"
CommandArgument='<%# Container.ItemIndex %>'><%# Container.ItemIndex + 1 %>
</asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
Then a function to call the stored procedure and add the data to the GridView and create the pager. I add the data to a DataTable first instead of directly to the GridView1.DataSource because I need the total number of rows.
public void LoadGridView(int page, int userID)
{
int pagesize = 10;
double totalrows = 0;
int offset = page * pagesize;
//create a connection
using (var connection = new SqlConnection(Common.connectionString))
using (var command = new SqlCommand("GridView_Paging @offset, @pagesize, @userID", connection))
{
//add the parameters
command.Parameters.Add("@offset", SqlDbType.Int).Value = offset;
command.Parameters.Add("@pagesize", SqlDbType.Int).Value = pagesize;
command.Parameters.Add("@userID", SqlDbType.Int).Value = userID;
//open the connection
connection.Open();
using (var reader = command.ExecuteReader())
{
var dt = new DataTable();
dt.Load(reader);
//add the data to the gridview
GridView1.DataSource = dt;
GridView1.DataBind();
//get the total rows from the datatable
if (dt.Rows.Count > 0)
totalrows = (int)dt.Rows[0]["totalrows"];
}
}
//calculate the number of pages
var pages = Math.Ceiling(totalrows / pagesize);
//create the pager by binding an array of the correct size to the repeater
Pager_GridView.DataSource = new string[(int)pages];
Pager_GridView.DataBind();
}
Then add the Repeater LinkButton command
protected void Pager_GridView_Command(object sender, CommandEventArgs e)
{
LoadGridView(Convert.ToInt32(e.CommandArgument), 12345);
}
And finally Page_Load. Because I've disabled the ViewState, the data has to be bound on every page load and PostBack (which is also the same page load basically). If you would just put LoadGridView(0, 12345); then you would be double-hitting the database. Once in Page_Load and once you click a link in the pager. To prevent this, check if the __EVENTTARGET is from the Repeater
protected void Page_Load(object sender, EventArgs e)
{
string eventTarget = Request.Form["__EVENTTARGET"];
//check the eventtarget contains the repeater container to prevent double hitting
if (string.IsNullOrEmpty(eventTarget) || !eventTarget.Contains(Pager_GridView.UniqueID))
{
LoadGridView(0, 12345);
}
}
