Spread for ASP.NET 7.0 Product Documentation
Saving Data to an SQL Database
See Also Support Options
Spread for ASP.NET 7.0 Product Documentation > Developer's Guide > Maintaining State > Saving Data to an SQL Database

Glossary Item Box

Database state management saves data to a specified database. Using an SQL database for state management is best if you are working with large amounts of data, particularly data that needs to be secure and maintain integrity.

To save data to an SQL database, you must install SQL State Management, as explained in the example.

Advantages and Disadvantages

The advantages of using a database to maintain state are:

The disadvantages of using a database to maintain state are:

Using Code

This example describes how to install SQL state management, and provides code for setting up the FpSpread component. To install SQL State Management complete the following instructions.

  1. Do one of the following:
    • If you are using the MSDE version of SQL Server that ships with Visual Studio.NET you need to open a command prompt window and navigate to the Windows\Microsoft.Net\Framework\(Version) directory. Once there, issue the following command to run the InstallSqlState.sql script: OSQL –S localhost –U sa –P <InstallSqlState.sql
    • OSQL.exe is a tool that ships with MSDE and SQL Server. It allows you to apply a T-SQL script to a SQL Server.
    • If you are using SQL Server 7 or SQL Server 2000 you can follow the directions above or you can open the Enterprise Manager, open the InstallSqlState.sql script from the Windows\Microsoft.Net\(Framework Version) directory, and execute the script from there.
    • Whichever method you choose, the script will set up an ASPState database in your SQL Server Group Databases.
  2. After you have run the script to set up SQL state management, you need to make a change to your project’s web.config file. Under the session State section, change the Mode setting from its current setting (most likely InProc) to SQL Server.
  3. Then configure the sqlConnectionString to point to the SQL Server where you installed the T-SQL script InstallSqlState.sql as follows: sqlConnectionString="data source=127.0.0.1;user id=sa;password=;"
  4. Then you need to put the following code in your FpSpread component’s SaveOrLoadSheetState event:

    The actual state information that you save is written to the tempdb database.

Example

The following sample illustrates using the SQL state management to save data.

C# Copy Code
protected void FpSpread1_SaveOrLoadSheetState(object sender, FarPoint.Web.Spread.SheetViewStateEventArgs e)
{
if (e.IsSave)
{
Session("data" + e.Index) == e.SheetView.SaveViewState();
}
else
{
object o = Session("data" + e.Index);
if ((o != null))
{
e.SheetView.LoadViewState(o);
}
}
e.Handled = true;
}
VB Copy Code
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

See Also

© 2002-2014 ComponentOne, a division of GrapeCity. All Rights Reserved.