Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
SQL Server

Store Session State in SQL Server ASP.NET

Author: Nikki Smith

Many times you need to store session state out of process in SQL Server. You will learn how to configure SQL Server and web.config file to store Session state in this article.


First you need to configure SQL Server to store session state. Below are the steps to configure SQL Server:

  1. Go to
    System Drive\Windows\Microsoft.NET\Framework\Version number
  2. You will find “InstallSqlState.sql” there. Open this file in query Analyzer of SQL Server
  3. Click Execute to run the script. It will create a database ASPState.
  4. Now you can configure web.config file of your application. I will show you later in this article.
  5. After doing all the processing you need to remove this database
  6. Open command prompt and type
    net stop w3svc
    You will see a message that w3svc is stopped
  7. Now Go to
    System Drive\Windows\Microsoft.NET\Framework\Version number
  8. You will find “UninstallSqlState.sql” there. Open this file in query Analyzer of SQL Server
  9. Click Execute to run the script. It will remove the database ASPState
  10. Now go to Command prompt again and type
    net start w3svc
  11. Now the SQL Server configuration process is complete
Complete example to save Session state in SQL Server
  1. Create a new Web Site in Visual Studio 2010 either in Visual Basic or Visual C#.
  2. Add two Web Forms in the Web Site.
  3. Write below   <sessionState> tag in between <System.web> tag in web.config file

           <sessionState mode="SQLServer" sqlConnectionString="Data Source=DEV01-PC;Integrated Security=True" cookieless="false" timeout="20">

    Notice here, I have set the “mode” attribute to “SQLServer” and provides the SQL connection string only with data source. You must have to change SQL connection string according to your data source.
  4. Write code below in Default.aspx page

    <asp:Label ID="Label1" runat="server" Text="First Name:"></asp:Label>
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <br />
    <asp:Label ID="Label2" runat="server" Text="Last Name:"></asp:Label>
    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
    <br />
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click"
                Text="Save Session Values in SQL Server" />
    <br />
    <asp:Label ID="lblMessage" runat="server"></asp:Label>
    <br />
    <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/Default2.aspx">See Session Values on Second Page</asp:HyperLink>
  5.  The button click event of the code behind file (Default.aspx.vb or Default.aspx.cs) will be like this:

    Visual Basic
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim firstName As String = TextBox1.Text
        Dim lastName As String = TextBox2.Text
        Session("FirstName") = firstName
        Session("LastName") = lastName
        lblMessage.Text = "Session Values are stored in SQL Server"
    End Sub
    Visual C#
    protected void Button1_Click(object sender, EventArgs e) {
        string firstName = TextBox1.Text;
        string lastName = TextBox2.Text;
        Session["FirstName"] = firstName;
        Session["LastName"] = lastName;
        lblMessage.Text = "Session Values are stored in SQL Server";
  6.  Write code below in Default2.aspx page

    <asp:Label ID="Label1" runat="server"></asp:Label>
    <br />
    <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/Default.aspx">Back to First Page</asp:HyperLink>
  7.  Write below in Page Load event in code behind file (Default2.aspx.vb or Default2.aspx.cs)

    Visual Basic
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        If Session("FirstName") IsNot Nothing And Session("LastName") IsNot Nothing Then
            Dim firstName As String = CType(Session("FirstName"), String)
            Dim lastName As String = CType(Session("LastName"), String)
            Label1.Text = "Your First Name is: " + firstName
            Label1.Text += " , Your Last Name is: " + lastName
            Label1.Text = "Session values are not available"
        End If
    End Sub
    Visual C#
    protected void Page_Load(object sender, EventArgs e) {
        if (Session["FirstName"] != null && Session["LastName"] != null) {
            string firstName = Session["FirstName"].ToString();
            string lastName = Session["LastName"].ToString();
            Label1.Text = "Your First Name is: " + firstName;
            Label1.Text += " , Your Last Name is: " + lastName;
        else {
            Label1.Text = "Session values are not available";
  8. Press F5 and see the web site in browser. Provide values, click on button. Now click on the link and go the next page. You can see the values here retrieved from session objects. These session values are stored in and retrieved from SQL Server.


Add Article Comment:
Name :
Email Address :
Comments :
<< How to use Cookies in ASP.NET

Disclaimer - Privacy
© 2002-2018