Monday, July 25, 2011

Using SQL Server for Session State in ASP.Net 4.0

There are many different reasons why you would want to use SQL Server to host your .Net Session State.  First and foremost, when you use SQL to house your session it remains persistent when you do web server restarts or complete server reboots.  If you deploy your application to a server farm or use multiple servers to host your application, InProc and StateServer modes become complete useless as you do not know which server will render the page.

Setting Up SQL Server For Sessions:
In the (system drive)\Windows\Microsoft.Net\Framework\ v4.0.30319 (v4.0.30319 being the version of .Net Framework you are using) You will find a file called InstallSqlState.sql.  This will create a database with two tables in it:

Remember that if you are using SQL Authentication to connect to the database, you need to give that user rights to run the procedures contained in the database.
If you are using trusted connections, you have to open a new Query and give sa (systemadmin) ownership of the database by typing:
use ASPState
exec sp_changedbowner 'sa','true'

Setting Up ASP.Net 4.0 To Use SQL:
Now that you have the SQL Side taken care of, you have to open your web.config file for modification and add the following line:
<system.web><sessionStatemode="SQLServer" sqlConnectionString="Server=server name;uid=sa;pwd=password;Initial Catalog=database name" timeout="20" allowCustomSqlDatabase="true"></sessionState>
</ system.web >
allowCustomSqlDatabase should be set to true if you define a catalog in your connection string (which is the norm).

That’s it as far as setup goes.  You have now allowed your session data to be stored in SQL.  Notice the timeout parameter, this designates when the session will expire (without user input – in the example it is set to 20 minutes).

Using Session:
In order to use Sessions in ASP.Net, you will need to reference the System.Web.HttpContext Namespace if you are working in a class library or other object outside of a web form.  This is due to the inheritance of the web form.

So you can set Session by the following:
'If you are on a web form
Session("variableName") = txtFieldName.Text
'If you are in a class library and need to reference the Namespace
System.Web.HttpContext.Current.Session("variableName") = strSomeStringValue

You can retrieve Session by using the following:
'We need to ensure our session variables has been set and has a value first -
        If Not (Session("variableName") Is Nothing) Then
            txtFieldName.Text = Session("variableName").ToString
        End If
You can also refer to the variable by it’s collection array index, for instance:

That’s all it takes to get SQL Server to host your Session State Variables.

Happy .Netting.. Saleh

No comments:

Post a Comment