Spread for ASP.NET 18 Product Documentation

Loading Data for Each Page Request

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:

No server resources are required.
Because the view state is small, pages load quickly.

The disadvantages of loading data for every page request are:

The programmers must code more to handle the UpdateCommand, InsertCommand, and DeleteCommand events to update the database. In addition, the programmers might need to set up row, column, or cell styles for each page request.
This method requires more database access if the FpSpread component is bound.

Using Code

Load data each time the page is loaded.

Example 1

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

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.

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

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

Advantages and Disadvantages
Using Code
Example 1
Example 2
Example 3