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.ToString(Session["Value"]);

        }

So my final solution structure 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 string in web.config and in future if I need to change the connection string to different server of database, I will need to redeploy the solution again. The alternative to the problem is specifying the connection string in ServiceConfiguration.cscfg file. But ServiceConfiguration.cscfg does not support <sessionState> configuration tag. Therefore I will write the connection string ServiceConfiguration.cscfg file and update the web.config to have connection string 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=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />

      </providers>

    </sessionState>

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

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

<Setting name="SQLConnectionString" 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 string above with your own values. Here my DB name is SocialGames. Please make note that, MultipleActiveResultSets=True; will be required in your connection string.

Now our next task is to write method which will add required connection string 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 string element to web configuration file

            this.SetupConnectionStrings();

        }

private void SetupConnectionStrings()

        {

            string connectionString = RoleEnvironment.GetConfigurationSettingValue("SQLConnectionString");

            // Obtain the RuntimeConfig type. and instance

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

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

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

            var connectionStrings = connectionStringSection.GetType().GetProperty("ConnectionStrings", BindingFlags.Public | BindingFlags.Instance).GetValue(connectionStringSection, null);

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

            // Set the SqlConnectionString property.

            ((ConnectionStringsSection)connectionStringSection).ConnectionStrings.Add(new ConnectionStringSettings("DefaultConnection", connectionString));

        }

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 string 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 entry of session will be created in the table as shown –

Hope it helps!!

Cheers…

Happy Sessioning!!!