Spread for ASP.NET 13 Product Documentation
Spread for ASP.NET 13 Product Documentation / Developer's Guide / Maintaining State / Loading Data for Each Page Request
In This Topic
    Loading Data for Each Page Request
    In This Topic

    When you load data for each page request, you are not maintaining state, rather, you are re-creating each page as it is requested. Load data for every page request when there is a large data set and you must minimize the use of server resources.

    To load data for every page request, set the IsTrackingViewState property for the active sheet to False.

    Advantages and Disadvantages

    The advantages of loading data for every page request are:

    The disadvantages of loading data for every page request are:

    Using Code

    Load data each time the page is loaded.

    Example 1

    The following sample code illustrates loading data for every page request.

    VB
    Copy Code
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      ' Put user code to initialize the page here.
      OleDbDataAdapter1.Fill(DataSet11, "Orders")
      FpSpread1.ActiveSheetView.DataKeyField = "OrderID"
      FpSpread1.ActiveSheetView.IsTrackingViewState = False
      Me.DataBind()
    End Sub
    
    Private Sub FpSpread1_UpdateCommand(ByVal sender As Object, ByVal e As FarPoint.Web.Spread.SpreadCommandEventArgs) Handles FpSpread1.UpdateCommand
      Dim conn As New OleDb.OleDbConnection()
      conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\test\NW" & _
      "ind.mdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database=""""" & _
      ";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type" & _
      "=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLE" & _
      "DB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Creat" & _
      "e System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Lo" & _
      "cale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:S" & _
      "FP=False"
      Dim cmdText As String = "UPDATE Orders SET CustomerID = ?, EmployeeID = ?, Freight = ?, OrderDate = ?, Req" & _
    "uiredDate = ?, ShipAddress = ?, ShipCity = ?, ShipCountry = ?, ShipName = ?, Shi" & _
    "ppedDate = ?, ShipPostalCode = ?, ShipRegion = ?, ShipVia = ? WHERE (OrderID = ?)"
      Dim updateCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, conn)
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("CustomerID", System.Data.OleDb.OleDbType.VarWChar, 5, "CustomerID"))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("EmployeeID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "EmployeeID", System.Data.DataRowVersion.Current, Nothing))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Freight", System.Data.OleDb.OleDbType.Currency, 0, System.Data.ParameterDirection.Input, False, CType(19, Byte), CType(0, Byte), "Freight", System.Data.DataRowVersion.Current, Nothing))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("OrderDate", System.Data.OleDb.OleDbType.DBDate, 0, "OrderDate"))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("RequiredDate", System.Data.OleDb.OleDbType.DBDate, 0, "RequiredDate"))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("ShipAddress", System.Data.OleDb.OleDbType.VarWChar, 60, "ShipAddress"))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("ShipCity", System.Data.OleDb.OleDbType.VarWChar, 15, "ShipCity"))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("ShipCountry", System.Data.OleDb.OleDbType.VarWChar, 15, "ShipCountry"))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("ShipName", System.Data.OleDb.OleDbType.VarWChar, 40, "ShipName"))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("ShippedDate", System.Data.OleDb.OleDbType.DBDate, 0, "ShippedDate"))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("ShipPostalCode", System.Data.OleDb.OleDbType.VarWChar, 10, "ShipPostalCode"))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("ShipRegion", System.Data.OleDb.OleDbType.VarWChar, 15, "ShipRegion"))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("ShipVia", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "ShipVia", System.Data.DataRowVersion.Current, Nothing))
      updateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("OrderID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "OrderID", System.Data.DataRowVersion.Original, Nothing))
      Dim sv As FarPoint.Web.Spread.SheetView = e.SheetView
      Dim keyValue As String = sv.GetDataKey(e.CommandArgument)
      ' Find the row.
      Dim rowFlag As Boolean = False
      Dim keyCol As Integer = 4 ' order id
      Dim r As Integer
      For r = 0 To sv.RowCount - 1
        Dim tmp As String = sv.GetValue(r, 4)
        If (tmp = keyValue) Then
          rowFlag = True
          Exit For
        End If
      Next
      If Not rowFlag Then
        Return
      End If
    
    Dim i As Integer
    For i = 0 To sv.ColumnCount - 1
      Dim colName As String = sv.GetColumnLabel(0, i)
      If (Not e.EditValues.Item(i) Is FarPoint.Web.Spread.FpSpread.Unchanged) Then
        updateCmd.Parameters(colName).Value = e.EditValues.Item(i) ElseIf (OleDbUpdateCommand1.Parameters.Contains(colName)) Then
        updateCmd.Parameters(colName).Value = sv.GetValue(r, i)
      End If
    Next
    
    Try
      conn.Open()
      i = updateCmd.ExecuteNonQuery()
      conn.Close()
      conn.Dispose()
    Catch ex As Exception
      ' Update database failed.
      conn.Close()
      conn.Dispose()
    End Try
    End Sub
    

    Example 2

    The following sample code illustrates loading data for every page request. This example requires more coding, but provides a more efficient application. To further speed up page loading, you can use the "where" clause in the SQL statements to retrieve one page of records so that database server does not have to return a large data set.

    VB
    Copy Code
    Private topRow As Integer
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      topRow = FpSpread1.Sheets(0).TopRow
      SetDataModel(topRow, topRow)
    End Sub
    
    Private Sub FpSpread1_TopRowChanged(ByVal sender As Object, ByVal e As FarPoint.Web.Spread.SpreadCommandEventArgs) Handles FpSpread1.TopRowChanged
      SetDataModel(topRow, e.SheetView.TopRow)
    End Sub
    
    Public Sub SetDataModel(ByVal oldTopRow As Integer, ByVal newTopRow As Integer)
      Dim firstOrderID As Integer = -1
      Dim lastOrderID As Integer = -1
      If Not ViewState("lastOrderID") Is Nothing Then
        lastOrderID = ViewState("lastOrderID")
      End If
      If Not ViewState("firstOrderID") Is Nothing Then
        firstOrderID = ViewState("firstOrderID")
      End If
      Dim ps As Integer = FpSpread1.Sheets(0).PageSize
      If newTopRow > oldTopRow Then
        Me.OleDbSelectCommand1.CommandText = "SELECT Top " & ps & " CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate, ShipAdd" & _
    "ress, ShipCity, ShipCountry, ShipName, ShippedDate, ShipPostalCode, ShipRegion, " & _
    "ShipVia FROM Orders" & " Where OrderID >" & lastOrderID & " Order by OrderID"
        ElseIf newTopRow = oldTopRow Then
        Me.OleDbSelectCommand1.CommandText = "SELECT Top " & ps & " CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate, ShipAdd" & _
    "ress, ShipCity, ShipCountry, ShipName, ShippedDate, ShipPostalCode, ShipRegion, " & _
    "ShipVia FROM Orders" & " Where OrderID >=" & firstOrderID & " Order by OrderID"
      Else
        Me.OleDbSelectCommand1.CommandText = "SELECT Top " & ps & " CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate, ShipAdd" & _
    "ress, ShipCity, ShipCountry, ShipName, ShippedDate, ShipPostalCode, ShipRegion, " & _
    "ShipVia FROM Orders" & " Where OrderID <" & firstOrderID & " Order by OrderID DESC"
      End If
    FpSpread1.Sheets(0).IsTrackingViewState = False
    DataSet31.Tables(0).Clear()
    
    If newTopRow < oldTopRow Then
      ' Reverse the order.
      Dim tmpTable As Data.DataTable = DataSet31.Tables(0).Clone()
      OleDbDataAdapter1.Fill(tmpTable)
    
      Dim dr As Data.DataRow
      Dim i As Integer
      For i = 0 To tmpTable.Rows.Count - 1
        dr = tmpTable.Rows(tmpTable.Rows.Count - 1 - i)
        DataSet31.Tables(0).ImportRow(dr)
      Next
    Else
      OleDbDataAdapter1.Fill(DataSet31)
    End If
    
    Dim model As MyModel = New MyModel(DataSet31, String.Empty)
    model.TopRow = newTopRow
    
    Dim dbCmd As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("select count(*) from orders", OleDbConnection1)
    OleDbConnection1.Open()
    model.RowCount = CType(dbCmd.ExecuteScalar(), Integer)
    OleDbConnection1.Close()
    
    FpSpread1.Sheets(0).DataModel = model
    ViewState("firstOrderID") = DataSet31.Tables(0).Rows(0).Item("OrderID")
    Dim dtcount As Integer = DataSet31.Tables(0).Rows.Count
    ViewState("lastOrderID") = DataSet31.Tables(0).Rows(dtcount - 1).Item("OrderID")
    End Sub
    'PageLoad
    

    Example 3

    The following sample code illustrates loading data for every page request. This example requires more coding, but provides a more efficient application. To further speed up page loading, you can use the "where" clause in the SQL statements to retrieve one page of records so that database server does not have to return a large data set.

    VB
    Copy Code
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      'Put user code to initialize the page here.
      Dim topRow As Integer = FpSpread1.Sheets(0).TopRow
      SetDataModel(topRow)
    End Sub
    
    Public Sub SetDataModel(ByVal topRow As Integer)
      Dim ps As Integer = FpSpread1.Sheets(0).PageSize
    
      Me.OleDbSelectCommand1.CommandText = "SELECT CategoryID, Discontinued, ProductID, ProductName, QuantityPerUnit, Reorder" & _
    "Level, SupplierID, UnitPrice, UnitsInStock, UnitsOnOrder FROM Products" & " Where ProductID >=" & topRow & " and ProductID <= " & (topRow + ps) & " Order by ProductID"
    
    FpSpread1.Sheets(0).IsTrackingViewState = False
    DataSet41.Tables(0).Clear()
    OleDbDataAdapter1.Fill(DataSet41)
    
    Dim model As MyModel = New MyModel(DataSet41, String.Empty)
    model.TopRow = topRow
    Dim dbCmd As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("select count(*) from Products", OleDbConnection1)
    OleDbConnection1.Open()
    model.RowCount = CType(dbCmd.ExecuteScalar(), Integer)
    OleDbConnection1.Close()
    FpSpread1.Sheets(0).DataModel = model
    End Sub
    
    Private Sub FpSpread1_TopRowChanged(ByVal sender As Object, ByVal e As FarPoint.Web.Spread.SpreadCommandEventArgs) Handles FpSpread1.TopRowChanged
    SetDataModel(e.SheetView.TopRow)
    End Sub
    
    Public Class MyModel
    Inherits FarPoint.Web.Spread.Model.BaseSheetDataModel
    
    Private dataset As Data.DataSet = Nothing
    Private datamember As String = String.Empty
    Private trow As Integer = 0
    Private rCount As Integer = 0
    
    Public Sub New(ByVal ds As Data.DataSet, ByVal dm As String)
      dataset = ds
      datamember = dm
    End Sub
    
    Public Overrides Function GetValue(ByVal row As Integer, ByVal col As Integer) As Object
    Dim dt As Data.DataTable = Me.GetDataTable()
    If dt Is Nothing Then
      Return Nothing
    Else
    If row < TopRow Or row >= TopRow + dt.Rows.Count Then
      Return Nothing
    Else
      Dim r As Integer = row - TopRow
      Return dt.Rows(r).Item(col)
    End If
    End If
    End Function
    
    Public Overrides Function IsEditable(ByVal row As Integer, ByVal col As Integer) As Boolean
    Return True
    End Function
    
    Public Function GetDataTable() As Data.DataTable
    If dataset Is Nothing Then
      Return Nothing
    Else
    If datamember Is Nothing Or datamember = String.Empty Then
      Return dataset.Tables(0)
    Else
      Return dataset.Tables(datamember)
    End If
    End If
    End Function
    
    Public Property TopRow() As Integer
    Get
      Return trow
    End Get
    Set(ByVal Value As Integer)
      trow = Value
    End Set
    End Property
    
    Public Overrides Property RowCount() As Integer
    Get
      Return rCount
    End Get
    Set(ByVal Value As Integer)
      rCount = Value
    End Set
    End Property
    
    Public Overrides Property ColumnCount() As Integer
    Get
    Dim dt As Data.DataTable = GetDataTable()
    
    If (dt Is Nothing) Then
      Return 0
    Else
      Return dt.Columns.Count
    End If
    End Get
    Set(ByVal Value As Integer)
    End Set
    End Property
    
    End Class