opitmize binding large datatable to DataGrid

ybao · December 20th, 2004, 06:15 PM

My report application takes a long time to bind extremely large table data to DataGrid control, for e.g. 65,000.00 rows. So even though the Oracle database responses pretty fast, the response time from the ASP.NET application is too long for the customers to bare. Anybody has any great advice on this, I would appreciate.
Yubo

mehdi62b · December 22nd, 2004, 03:42 PM

you can use Paging technique in your grid,take a look at this example,

Code:

<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>

<Script Runat="Server">

Dim conNorthwind As SqlConnection
Dim strSelect As String
Dim intStartIndex As Integer
Dim intEndIndex As Integer

Sub Page_Load
  Dim cmdSelect As SqlCommand

  conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
  If Not IsPostBack Then
    ' Get Total Pages
    strSelect = "Select Count(*) From Products"
    cmdSelect = New SqlCommand( strSelect, conNorthwind )
    conNorthwind.Open()
    dgrdProducts.VirtualItemCount = ( cmdSelect.ExecuteScalar() / dgrdProducts.PageSize )
    conNorthwind.Close()
    BindDataGrid
  End If
End Sub

Sub BindDataGrid
  Dim dadProducts As SqlDataAdapter
  Dim dstProducts As DataSet

  intEndIndex = intStartIndex + dgrdProducts.PageSize
  strSelect = "Select * From Products Where ProductID > @startIndex " _
    & "And ProductID <= @endIndex Order By ProductID"
  dadProducts = New SqlDataAdapter( strSelect, conNorthwind )
  dadProducts.SelectCommand.Parameters.Add( "@startIndex", intStartIndex )
  dadProducts.SelectCommand.Parameters.Add( "@endIndex", intEndIndex )
  dstProducts = New DataSet
  dadProducts.Fill( dstProducts )

  dgrdProducts.DataSource = dstProducts
  dgrdProducts.DataBind()
End Sub

Sub dgrdProducts_PageIndexChanged( s As Object, e As DataGridPageChangedEventArgs )
  intStartIndex = ( e.NewPageIndex * dgrdProducts.PageSize )
  dgrdProducts.CurrentPageIndex = e.NewPageIndex
  BindDataGrid
End Sub

</Script>

<html>
<head><title>DataGridCustomPaging.aspx</title></head>
<body>
<form Runat="Server">

<asp:DataGrid
  ID="dgrdProducts"
  AllowPaging="True"
  AllowCustomPaging="True"
  PageSize="3"
  OnPageIndexChanged="dgrdProducts_PageIndexChanged"
  PagerStyle-Mode="NumericPages"
  CellPadding="3"
  Runat="Server" />

</form>
</body>
</html>

as you see in above example,it requeries database and binds the grid just with limited rows in every request for a new page.

_____________________________
Mehdi.
software engineering student.
Looking for a good job for summer 2005.