ASP.NET: How to improve memory usage in these few lines of code?

Question

I have a database that has only one table of about 7,000 records. I have a GridView to display these records 10 rows at a time (I use the automatic paging feature provided with GridView).

Here's how I read in the table and show the records. Apparently, I read in the whole table but display only 10 rows which seems wasteful of memory. BUT I think I need to read in the whole shebang so that the paging can work. Am I wrong? If so how do I improve this code:

private void ShowGridViewData()
    {
        // I LEAVE ONLY RELEVANT CODE TO AVOID CLUTTER

        string queryString = (string)Session["queryString"];
        String connectionString = ConfigurationManager.ConnectionStrings["productsSQLConnectionString"].ConnectionString;
        DataSet ds = new DataSet();

        SqlConnection connection = new SqlConnection(connectionString);
        SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);



        // Fill the DataSet.
        adapter.Fill(ds);

        GridView1.DataSource = ds;
        GridView1.DataBind();


        adapter.Dispose();
        connection.Close(); 


    }

Additional info: I cannot change the code to use LINQ. And one more reason I had when I decided to read in the whole table is that paging would not require another database access. I might be wrong with this reasoning.

Answer 1

BUT I think I need to read in the whole shebang so that the paging can work.

Yes but you can do use paging without loading the whole dataset by using an Object Data Source see Manually setting a GridView's PageCount when DataSource doesn't return full result set?

And one more reason I had when I decided to read in the whole table is that paging would not require another database access. I might be wrong with this reasoning.

Yes and No. It really depends since your use case. Typically you should worry more about resource utilization per request, not per session. This way you don't allocate resources that don't get used (pay-per-play)

So usually I follow the pay-per-play model and only get the data I'm going to show unless we're dealing with

Expensive SQL operations If you result set is small but takes a long time to create like a summary operation I might want to cache it like Anthony Shaw's answer.

Global state If its something that shared across users then only the first user pays and the play is free for everyone else. But you still need a sensible caching policy)