How to use a SQL server database for state management in ASP.NET

Software developer can instruct ASP.NET to use a SQL database to store session information with option SqlServer: When software developer is going to use this mode, the objects he store in the session state must be serializable. Otherwise ASP.NET will not be able to store the object in the database.

 

<?xml version=”1.0” encoding=”utf-8” ?>
<configuration>
     <system.web>
     <!— other settings are omitted. —>
     <sessionState
        cookieless=”AutoDetect” cookieName=”ASP.NET_SessionID”
        regenerateExpiredSessionID=”false”
        timeout=”20”
        mode=”SqlServer”
        stateConnectionString=”tcpip=127.0.0.1:42424”
        stateNetworkTimeout=”10”
        sqlConnectionString=”data source=127.0.0.1;Integrated Security=SSPI”
        sqlCommandTimeout=”30” allowCustomSqlDatabase=”false”
        customProvider=””
     />
     </system.web>
</configuration>

 

SQL Server database is identified by the sqlConnectionString attribute. This is the slowest, but the most robust state store. To use this method, software developer will need to have an installed SQL Server. Software developer has to specify the data source (the server address) and a user ID and password, unless he’s using SQL integrated security.
In addition, software developer need to install the special stored procedures and temporary session databases. These stored procedures will take care of storing and restoring the session information. ASP.NET includes a Transact-SQL script called InstallSqlState.sql which is in C:\[WinDir]\Microsoft.Net\Framework\[Version] folder. The script can be executed only once using OSQL.exe or Query Analyzer.
By default the database is always named ASPState. As a result, the connection string in the web.config simply specifies the location of the server and type of the authentication that will be used:
 

sqlConnectionString=”data source=127.0.0.1;Integrated Security=SSPI”

 
If software developer wants to use a different database he has to use the following:
 

allowCustomSqlDatabase=”true” sqlConnectionString=”data source=127.0.0.1;Integrated Security=SSPI;Initial Catalog= CustDatabase”

 
With the option SqlServe, software developer can also set an option sqlCommandTimeout which specifies the maximum number of seconds to wait for the database to respond before canceling the request. The default value is 30 seconds.