articles

Home / DeveloperSection / Articles / SQLServer session state in asp.net

SQLServer session state in asp.net

SQLServer session state in asp.net

Anchal Kesharwani8916 25-Jun-2014

In this article describe the concept of the SQLServer session state mode in asp.net. Here we understand the SQLServer session state mode and learn from the example.

ASP.NET supports various Session State Modes depending on various storage options for session data. The following are the available Session State Modes in ASP.NET:

· InProc

· StateServer

· SQLServer

· Custom

Let’s we talk about SQLServer session or SQLServer mode session in asp.net:SQLServer mode stores session state in a SQL Server database. Using this mode ensures that session state is preserved if the Web application is restarted and also makes session state available to multiple Web servers in a Web farm.

Objects stored in session state must be serializable if the mode is SQL Server. For information on serializable objects, see the SerializableAttribute class.

To use SQLServer mode, you must first be sure the ASP.NET session state database is installed on SQL Server. You can install the ASP.NET session state database using the Aspnet_regsql.exe tool, as described later in this topic.

Here we give the example of SQLServer session state mode.

Create a web form in asp.net as User.aspx

<div>

    User Name: <asp:TextBox ID="txt_name" runat="server"></asp:TextBox>

    <br />

    <br />

        <asp:Button ID="btn_submit" runat="server" Text="Submit"OnClick="btn_submit_Click" />

    </div>

 

Code on submit button in User.aspx.cs

protected void btn_submit_Click(object sender, EventArgs e)

    {

        Session["Username"] = txt_name.Text;

        Response.Redirect("UserDetails.aspx");

    }

 

Add another web form as UserDetail.aspx and code into UserDetail.aspx.cs

 

if (Session["Username"] != null && Session["Username"] != "")

        {

            string name = Session["Username"].ToString();

            Response.Write(String.Format("User name is: {0}", name));

        }

        else

        {

            Response.Write(String.Format("There are no user!"));

        }

 

Here we code the web.config file to set session mode:

<configuration>

  <system.web>

    <sessionState mode="SQLServer" timeout="30" sqlConnectionString="Integrated Security=SSPI; Data Source=MINDSTICK-PC\ MINDSTICK;" />

  </system.web>

</configuration>

 

Run the application and run first User.aspxSQLServer session state in asp.net

And click on submit button then we get the error cannot open the database “ASPState” requested by the login. The login failed. Login failed for user ‘MINDSTICK-PC\ MINDSTICK’.

Here we have not installed InstallSqlState.sql.

Open visual studio command prompt and we will be running a utility called aspnet_regsql. It's a generic utility which is used for SQL related things in ASP. Net.

SQLServer session state in asp.net

The command type and the description as given below to understand:

 -S <server>: Species the IP address or the name of SQL server in which you want to store the session state

-U:  Specifies the user ID to be used when connecting to the SQL Server

-P:  Specifies the password to be used when connecting to the SQL Server

-E: Indicates that you want to use integrated security when connecting to the SQL Server

-ssadd: Adds support for the SQLServer mode session state

-ssremove: Removes support for the SQLServer mode session state

-sstype: Type of session state support. This option can be:

t: for temporary storage (Stores session data in the SQL Server tempdb database)

p: for persistent storage (Stores session data in the ASPState database)

c:  for custom storage (Stores session data in a custom database)

-d:  <database>

The name of the custom database to use if -sstype switch is "c"

Now session state will be added in your database.

SQLServer session state in asp.net

Here the ‘ASPState’ database added in the database. Remember is in the error the

database was ASPState.

There are no tables in database and here temprory as temp Table that stores the

states.

Here are the system stored procedure that help to create temp table to store state.

Run again the application as User.aspx form:

SQLServer session state in asp.net

Click on submit button to go to UserDetail.aspx

SQLServer session state in asp.net

Stop the development server and run directly UserDetail.aspx from visual studio.

SQLServer session state in asp.net

There are no error. So successfully completed this example.

Advantages of SQLServer Session state
·         SQL Server is most reliable of all modes. Session data are intact if ASP.NET
restarts, but also if SQL Server restarts.
·         SQL Server is also most scalable option.
·         SQL Server is often available in shared hosting scenario.
Disadvantages of SQLServer Session state
·         It requires most configuration.
·         If you already don't use SQL Server on database driven website, it will require
additional cost to obtain license.
·         SQL Server is slowest of all options.

Updated 10-Feb-2020

Leave Comment

Comments

Liked By