How to do pagination in DataGridView but not fetch all data at first

Question

I want to do pagination in C# on datagridview. I did some research and all of them like this i have to fetch all data first from sql to c# and then do pagination. I want to fetch the records on demand when the page number achieves to it. For example if my PageSize is 10 i don't want to fetch next 10 records when i'm showing the first 10s and as soon as the user clicks on "Next" fetch the next 10 records using some sql query or stored procedure. Im using Sql Server 2018 My goal is to minimize the memory footprint so i can improve the speed of my application when dealing with large amount of data. Currently im using direct sql command and fetching data through a stored procedure to a datagridview; this SP has a recursive command and a little heavy for large data. As time passes the database grows and fetching all data takes time and so GUI slows down. So i thought i have to do pagination; but as long as i know in default pagination all data still get fetched into memory. But I want to fetch as how many rows as im planning to show in every page in C# WinForm

If you want to now the total number you can first fetch a count. If you want to fetch a block from the middle of the result set you need a server that supports this maybe with a orderby, fetch and offset. - You should tell us which DBMS you are using! — TaW, Oct 04 '19 at 07:25
So you could try this: `SELECT * FROM sales ORDER BY sale_date DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY` — TaW, Oct 04 '19 at 10:01
Thank you for taking the time to share your problem. But there is something missing from your question. What is your goal? What is your difficulty? What have you done so far? Please try to better explain your issue, your dev environment and share more code or some screenshot of your screeen. To help you improve the content, title and tags of your query, consider reading the *[How do I ask a good question](https://stackoverflow.com/help/how-to-ask)* which is in the help center. — , Oct 04 '19 at 12:30
What is the type of the datasource ? A ado.net dataset, a linq query, a direct sqlcommand...? — , Oct 04 '19 at 12:30
My goal is to minimize the memory footprint so i can improve the speed of my application when dealing with large amount of data. Currently im using direct sql command and fetching data through a stored procedure to a datagridview; this SP has a recursive command and a little heavy for large data. As time passes the database grows and fetching all data takes time and so GUI slows down. So i thought i have to do pagination; but as long as i know in default pagination all data still get fetched into memory. But I want to fetch as how many rows as im planning to show in every page in C# WinForm. — Safa Seed, Oct 04 '19 at 18:22

score -1 · Answer 1 · edited Oct 04 '19 at 07:37

https://learn.microsoft.com/en-us/aspnet/web-forms/overview/data-access/paging-and-sorting/efficiently-paging-through-large-amounts-of-data-cs

I read this article, So you need to create a Custom Paging. It 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.

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
OldValuesParameterFormatString="original_{0}" TypeName="ProductsBLL"
SelectMethod="GetProductsPaged" EnablePaging="True"
SelectCountMethod="TotalNumberOfProducts">
</asp:ObjectDataSource>

How to do pagination in DataGridView but not fetch all data at first

1 Answers1