Best way to manage state when Spread has large data- Part II

Continuing my previous blog here we would discuss other two approaches of managing state with ComponentOne Spread for ASP.Net i.e. 1. Saving Data to SQL Database. 2. Loading data for each Page Request. 1. Saving Data to SQL Database. Using ComponentOne Spread you can also manage state by saving data to SQL database. This is considered as the best way to manage state when dealing with large DataSets. To configure session state on SQL server it has a few prerequisite like .Net FrameWork and SQL Server . You must have these two installed on your system in order to use SQL Server Session Management. Once you have the SQL server setup for maintaining state you need to make changes the web.config file of your project. In web.config change the "Mode" to SQL Server' under Session State tag. And then again in Spread's SaveOrLoadSheetState event please use the code as follows:

 Private Sub FpSpread1_SaveOrLoadSheetState(ByVal sender As Object, ByVal e As FarPoint.Web.Spread.SheetViewStateEventArgs) Handles FpSpread1.SaveOrLoadSheetState  
  If (e.IsSave) Then  
    Session("data"&e.Index) = e.SheetView.SaveViewState()  
  Else  
    Dim o As Object = Session("data"&e.Index)  
    If Not o Is Nothing Then  
      e.SheetView.LoadViewState(0)  
    End If  
  End If  
  e.Handled = True  
End Sub  

This is it. Now the Spread state is loaded and saved to and from SQL server. The main disadvantage to use the method for maintaining state is that it requires a lot of consumption of both hardware and software. Also if you have too many queries to to fetch data from data base the performance may go down. 2. Loading data for each Page Request. As the name suggest it is a supply on demand feature where data is loaded on each page request. This method is very handy when you have large data set. Also it helps you to minimize the consumption of server resources. You need to handle all data activity in code, for example updating, deleting, inserting and adding when using this process. There have been few requests by customers where they need a Custom Paging , this method gives you opportunity to handle paging manually. As you may fetch only viewable rows at a time from data base. Here is a piece of code which gives you an idea of handling paging in code completely:

   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load  
        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  
        DataSet1 = New DataSet()  
        Dim strConnect As New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = |DataDirectory|\\NWind.mdb")  
        Dim objOleDBAdapter As New OleDbDataAdapter("SELECT CategoryID, Discontinued, ProductID, ProductName, QuantityPerUnit, Reorder" & "Level, SupplierID, UnitPrice, UnitsInStock, UnitsOnOrder FROM Products" & " Where ProductID >=" & topRow & " and ProductID <= " & (topRow + ps) & " Order by ProductID", strConnect)  
        objOleDBAdapter.Fill(DataSet1)  
        FpSpread1.Sheets(0).IsTrackingViewState = False  
        Dim model As MyModel = New MyModel(DataSet1, String.Empty)  
        model.TopRow = topRow  
        Dim dbCmd As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("select count(*) from Products", strConnect)  
        strConnect.Open()  
        model.RowCount = CType(dbCmd.ExecuteScalar(), Integer)  
        strConnect.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  

This also requires to create a custom data model for Spread and use it for fetching rows from DataSource on each page request. Please download the sample application to see above implementation in action and feel free to post your comments in case you have nay doubts or queries. Download Sample Application