Out of Process Session State - In SQL Server

This article is a continuation of my previous article of Storing Session in State Server @ http://www.c-sharpcorner.com/UploadFile/2124ae/out-of-process-session-state-in-state-server-in-Asp-Net/)

I would request readers to have a quick look at my previous article.

In ASP.Net, by default sessions are stored in-proc (i.e. in memory of the Web Server).

Sessions can be stored out-of-process:

• In a local or remote NT Service (@http://www.c-sharpcorner.com/UploadFile/2124ae/out-of-process-session-state-in-state-server-in-Asp-Net/)
• In a local or remote SQL Server.

Let us understand how sessions can be stored in a SQL Server with a sample demo.

To begin with, please download the sample code (OutofProcSessionState.zip) from: http://www.c-sharpcorner.com/UploadFile/2124ae/out-of-process-session-state-in-state-server-in-Asp-Net/

1. Open the project.
2. Go to the Web.Config file:
   1. We need to change the SessionState mode to "SQL Server" and also need to provide the corresponding SQLConnection String as shown below:
      <sessionStatemode="SQLServer" sqlConnectionString="server=SAURABHPC;trusted_connection=yes">
      </sessionState>
   2. Note that the SQL connection string will vary in your case.
3. Now, go to Login.aspx and run the application from Visual Studio.
   
   
   
   
4. Click "Go to Home Page" (as per the logic mentioned; the user should go to the new page (Home.aspx) and the user name "Gaurav Singh" should be retrieved from the session.
   
   
   
5. Not a good looking screen for developers!! Right… Let's sort it out!!
6. As the error screen says, it was unable to open a particular database (ASPState) in the SQL Server name "Saurabh-PC" which we specified in the web.config.
7. So, what we need to do is to open a Visual Studio Command Prompt to configure the database, as in:
   
   
   
   
   1. Here we will be running a utility called aspnet_regsql; it's a generic utility which is used for SQL related things in ASP. Net, as in:
      
      
      
      
   2. In the above command:
      
      • -S : denotes Server Name
      • Saurabh-PC : is the SQL Server name; please provide your corresponding name here.
      • -E : for Trusted connections.
      • -ssadd : for installing the session state features.
8. So now the session state should have been added to our database.
9. We can verify this by opening our database as in:
   
   
   
   1. We will see the new database was added as "ASPState"; remember, it's the same database that was shown in the Yellow Error Screen, when we ran the application above in Step 4.
   2. There will be no tables in this database.
   3. States will be stored in temp tables.
   4. We will see that there are a number of default Stored Procedures created, which would create temporary tables to store state (Session State) for the user/client.
       
10. Go to Visual Studio and run the application:
    
    1. Enter the User Name:
       
       
       
       
    2. Click "Go To Home Page":
       
       
       
       So the user name is retrieved from the session and is shown in the Home Page.
    3. To verify: go and stop the Web Development Server:
       
       
       
       
    4. Go to Visual Studio and launch the application again and go directly to the Home Page via the URL (http://localhost:8130/Home.aspx) (Note: the port # may vary) without closing the browser to keep the client session identifier the same; see:
       
       
       
       
    5. As evident from the output, we were able to retrieve the User Name from the session, in spite of restarting the Web Server, since in this case our session was stored in SQL Server.

So in this article we have seen how to store a session Out-of-process in SQL Server.

I have attached the above demonstration code.

Happy Learning!!