Spread for ASP.NET 8.0 Product Documentation > Developer's Guide > Maintaining State > 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.
The advantages of loading data for every page request are:
The disadvantages of loading data for every page request are:
Load data each time the page is loaded.
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 |
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 |
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 |