Saving 20,000 rows in Viewstate why am i redirected to this page is not available

Question

I read up on best practices of asp design. One of the suggestions was to avoid using ViewState. I got quite used to using it in my applications as it makes life easier in working with information without making too many DB calls. Especially when working with GRids.

However, i now ran into a problem when loading 20,000 rows into ViewState. In page load i do the db load

IF not isPostBack then
' load viestate with 20,000 rows from db
' bind grid to viewstate
end if

The page loads fine, and all the information is displayed in the grid with no errors. However when i click on any of the server buttons i am redirected to a page that says page is not available - as if page does not exist or i lost internet connection. Note that in code behind i do not redirect to any page.

If i comment out the load viewstate or if load less than 10,000 rows into the Viewstate - this issue does not occur.

So i am confused as to why this is happening. And obviously looks like Viewstate was never designed to hold huge datatables.

So what i wanted to find out was:

1. What should i use instead of viewstate to avoid DB calls (all i am doing is basically loading the grid, then allowing the user to do CRUD operations on grid data. Once complete i save data to DB and edit Viewstate and rebind it to the Grid - IS this an effective approach.)

2. Is it better to make more DB calls in CRUD operations or is it batter to reduce DB calls and save and edit a copy of a datatable within page code behind and have it available for grid rebinding.

Answer 1

If you are trying to databind 20,000 rows to a GridView you are in for a bad time, regardless of where you are storing the data. ViewState is an especailly bad place for it as the commentors have mentioned: you are effectively sending the entire database to the user on each request, yet only allowing them to see a handful of those rows.

A more appropriate approach is to change how you are querying the database in the first place and introduce sever side pagination. This way you are only sending the user the data that will actually be displayed on the page (10 - 100 rows) and the framework wont try to cache the entire database on behalf of the user (in either ViewState or SessionState or elsewhere).

Scott Mitchell has an excellent tutorial series on this exact problem: Tutorial 25: Efficiently Paging Through Large Amounts of Data

Here's the gist:

Step 1) Update your database queries to support pagination:

 SELECT PriceRank, ProductName, UnitPrice
 FROM
     (SELECT ProductName, UnitPrice,
         ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank
      FROM Products
      ) AS ProductsWithRowNumber
WHERE PriceRank > StartRowIndex AND
PriceRank <= (StartRowIndex + MaximumRows)

Step 2) Introduce a database query that returns the total number of rows:

 SELECT COUNT(*)
 FROM Products

Step 3) Wire up the GridView to use these methods and turn on automatic pagination

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True"
      DataKeyNames="ProductID" DataSourceID="ObjectDataSource1" 
      AllowPaging="True" />

  <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
       OldValuesParameterFormatString="original_{0}" 
       SelectMethod="GetProductsPaged"
       SelectCountMethod="GetTotalProducts"
       TypeName="ProductsBLL">
    <SelectParameters>
        <asp:Parameter Name="startRowIndex" Type="Int32" />
        <asp:Parameter Name="maximumRows" Type="Int32" />
    </SelectParameters>
 </asp:ObjectDataSource>

The article series goes into incredible depth and I highly recommend exploring it.