Saving Data to an SQL Database

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:

Databases are typically very secure, requiring rigorous authentication and authorization.
Databases offer large capacity.
Database information can be stored as long as you like, and it is not subject to the availability of the Web server.
Databases include various facilities for maintaining data integrity.
Data stored in your database is accessible to a wide variety of information-processing tools.
There are numerous database tools available, offering wide support and custom configurations.

The disadvantages of using a database to maintain state are:

Using a database to support state management requires more complex hardware and software configurations.
Using a database can affect performance, for example due to poor construction of the relational data model. Also, large numbers of queries to the database can adversely affect server performance.

Return to the list of options in Maintaining State.

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.

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.
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. 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=;"
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; }

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

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