Storing Session State in SQL Server
Typically we used in process sessions to store our variables. ASP.NET provides following options
for storing session states :
- State Server
- SQL Server
InProc sessions denote the sessions
that we are familiar with in traditional ASP. State server is a machine whose
memory is used to store just the state of the session from some other
machine. We can also use SQL server as session state store. The later options
are more suitable if you are developing web farm kind of applications. In this
article we will take a look at storing the session state in a SQL server
Locate and run InstallSqlState.sql file
Locate and run InstallSqlState.sql file found in WINNT\Microsoft.NET folder
e.g. on my test machine it is located in
E:\WINNT\Microsoft.NET\Framework\v1.0.2914\ folder. This file is provided
by Microsoft itself and contains a full set of T-SQL statements that create
certain database and two tables in tempdb database. Following figure shows
design view of the table that stores session state :
Modify your web.config file and specify session mode and SQL server
In my file sessionstate section looks like this :
sa;password=" cookieless= "false"timeout=
Create your ASP.NET web forms
Create your ASP.NET web forms as usual and access the session inside the
pages. For illustration purpose I have created a VS.NET solution that does that.
Following is the screen shot of the web form. This page simply allows you
to store string values to session and then display the value in a label.
Now all the session variables are stored in the table rather than in memory.
You can open the table ASPStateTempSessions and verify the same.
Un installing the database and tables
If you want to remove all the databases and tables created during install
process Microsoft has provided UninstallSQLState.sql file in the same location