Using SQL Azure for Session State Management in Windows Azure Applications

We all know that, Inproc session management will not work in WindowsAzure. I am going to explain you how SQL Azure can be used for maintaining session state in Windows Azure Web Role Applications.

To start with let’s create a simple cloud service project in VS2010 and add a web role in it. Then I opened master page and changed the heading to “Session Management using SQL Azure Demo”. Open default.aspx and add a button and textbox. Input text written by user in textbox will be saved in session on the button click and on another button click it will be populated in label. Code on both the buttons is as follows –

protected void btnAddSession_Click(object sender, EventArgs e)

        {

            Session["Value"] = txtValue.Text;

        }

        protected void btnGetSession_Click(object sender, EventArgs e)

        {

            lblValue.Text = Convert.ToS07ing(Session["Value"]);

        }

So my final solution s07ucture and default.aspx layout is as follows –

Now session management mode is always specified in Web.config file using the tag <sessionState>.

However, if I specifiy my SQL Azure Database connection s07ing in web.config and in future if I need to change the connection s07ing to different server of database, I will need to redeploy the solution again. The alternative to the problem is specifying the connection s07ing in ServiceConfiguration.cscfg file. But ServiceConfiguration.cscfg does not support <sessionState> configuration tag. Therefore I will write the connection s07ing ServiceConfiguration.cscfg file and update the web.config to have connection s07ing tag written, before application starts.

So first open the web.config fileand write <sessionState> tag as follows –

<sessionState mode="Custom" customProvider="DefaultSessionProvider" timeout="5">

      <providers>

        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=1.0.0.0, Culture=neu07al, PublicKeyToken=31bf3856ad364e35" connectionS07ingName="DefaultConnection" applicationName="/" />

      </providers>

    </sessionState>

So in above statement I am specifying the connection s07ing key name is DefaultConnection and you can change the timeout period as per your need. However, I will add this connection s07ing DefaultConnection at runtime. So in this case your web role web config file will show error line on above statement as “The Connection s07ing at07ibute is not allowed”. Still your project will build successfully. Also most important, comment out existing <connectionS07ings> element from your web config as we will be adding it at runtime.

Add the following s07ing in web role’s both cloud and local ServiceConfiguration.cscfg files –

<Setting name="SQLConnectionS07ing" value="Data Source=YourSQLAzureServerNAme;Initial Catalog=SocialGames[Replace with your DB name];User ID=YourUserName;Password=YourPwd;MultipleActiveResultSets=True;" />

Replace the values in the connection s07ing above with your own values. Here my DB name is SocialGames. Please make note that, MultipleActiveResultSets=True; will be required in your connection s07ing.

Now our next task is to write method which will add required connection s07ing element in the web.config on application start at runtime. Therefore open global.asax.cs file and add following code on Application_Start event –

void Application_Start(object sender, EventArgs e)

        {

            // Code that runs on application startup to add connection s07ing element to web configuration file

            this.SetupConnectionS07ings();

        }

private void SetupConnectionS07ings()

        {

            s07ing connectionS07ing = RoleEnvironment.GetConfigurationSettingValue("SQLConnectionS07ing");

            // Obtain the RuntimeConfig type. and instance

            Type runtimeConfig = Type.GetType("System.Web.Configuration.RuntimeConfig, System.Web, Version=4.0.0.0, Culture=neu07al, PublicKeyToken=b03f5f7f11d50a3a");

            var runtimeConfigInstance = runtimeConfig.GetMethod("GetAppConfig", BindingFlags.NonPublic | BindingFlags.Static).Invoke(null, null);

            var connectionS07ingSection = runtimeConfig.GetProperty("ConnectionS07ings", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(runtimeConfigInstance, null);

            var connectionS07ings = connectionS07ingSection.GetType().GetProperty("ConnectionS07ings", BindingFlags.Public | BindingFlags.Instance).GetValue(connectionS07ingSection, null);

            typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.NonPublic | BindingFlags.Instance).SetValue(connectionS07ings, false);

            // Set the SqlConnectionS07ing property.

            ((ConnectionS07ingsSection)connectionS07ingSection).ConnectionS07ings.Add(new ConnectionS07ingSettings("DefaultConnection", connectionS07ing));

        }

Yellow marked area in above code is the key name we specified in <sessionState> above. So make sure that both are same.

Using this approach you can have connection s07ing in web config & it can be changed from cloud service project’s Serviceconfiguration.cscfg without need of redeployment.

Now run the project and put some value in textbox and click “Add Value to Session” button displayed in above screenshot. And then click on button “Get Session Value”.

Now connect to your SQL Azure DB server from management studio & open your database. You will observe that, Sessions table is created automatically in the database of SQL Azure. If you query that, you can view the session row created in it. If we open another browser and use the same (local or production) URL of our web role application then another en07y of session will be created in the table as shown –

Hope it helps!!

Cheers…

Happy Sessioning!!!