5.3 Tutorial: MySQL Connector/Net ASP.NET Session State Provider

MySQL Connector/Net from version 6.1 has included a MySQL Session State Provider. This provider enables you to store session state in a MySQL database. The following tutorial shows you how to prepare to use the MySQL Session State Provider, and then store session data into the MySQL database. This tutorial uses Microsoft Visual Studio 2008 Professional Edition, MySQL Connector/Net 6.1.1 and MySQL Server 5.1. This tutorial also assumes you have created an empty database, for example test, where you will store session data. You could do this using the mysql command interpreter.

  1. In Visual Studio, create a new ASP.NET web site. If you are not sure how to do this, refer to the tutorial Section 5.8, “Tutorial: Databinding in ASP.NET Using LINQ on Entities”, which demonstrates how to do this.

  2. Launch the MySQL MySQL Website Configuration tool. Due to a bug in 6.1.1, this may not appear unless you are connected to a server in the Server Explorer. If you are unfamiliar with the MySQL Website Configuration tool, consider first working through the tutorial in Section 4.12, “MySQL Website Configuration Tool”.

  3. Navigate through the wizard to the Session State page. Make sure the check box Use MySQL to manage my ASP.NET session data is selected.

  4. On the same page, configure the connection string to the database that will contain your session data. If this database is empty, MySQL Connector/Net will create the schema required to store session data.

  5. Ensure that the check box Autogenerate Schema is selected so that MySQL Connector/Net will create the schema in your database to store the session data correctly.

  6. Enter the name of your application.

  7. Click Finish. The MySQL Website Configuration tool will now update your application's web.config file with information about the connection string and default providers to be used. In this case, we have selected the MySQL Session State Provider.

At this point, you are ready to use the MySQL database to store session data. To test that the set up has worked, you can write a simple program that uses session variables.

  1. Open Default.aspx.cs. In the Page_Load method, add the following code:

    Session["SessionVariable1"] = "Test string";
    
  2. Build your solution.

  3. Run the solution (without debugging). When the application runs, the provider will autogenerate tables required in the database you chose when setting up the application.

  4. Check that the schema was in fact created. Using the MySQL Command-Line Client use the target database and then type SHOW TABLES;. You will see that MySQL Connector/Net has created the required schema automatically, as we selected this to happen in the MySQL Website Configuration tool.

  5. Now view the contents of these tables by typing SELECT * FROM my_aspnet_sessions; in the mysql command interpreter. This will display the session data our application used. Note that this is stored in binary format so some data may not display as expected.

At this point, you have installed the Session State Provider and carried out a preliminary test of the installation. You will now work a bit more with the Session State Provider.

In this part of the tutorial, you will set and retrieve a session variable. You can work with your existing project.

  1. Select the Default.aspx and switch to Design View. Add a text box and three buttons. Change the text property for the buttons to Store Session Variable, Clear Textbox, and Show Session Variable. These will be Button1, Button2 and Button3 respectively. Build your solution to ensure that no errors have been introduced.

  2. Still in the Design View, double-click Button1. Now to the Button1_Click event handler add code some the handler resembles the following:

    protected void Button1_Click(object sender, EventArgs e)
    {
        Session["SessionString"] = TextBox1.Text;
    }
    

    You have created a new Session variable accessed using the key SessionString. This will be set to the text that was entered into the text box when Button1 is clicked.

  3. In Design View, double-click Button2 to add its click event handler. This button needs to clear text from the text box. The code to do this is as follows:

    protected void Button2_Click(object sender, EventArgs e)
    {
        TextBox1.Text = "";
    }
    

    The code simply assigns an empty string to the Text property of the text box.

  4. In the Design View double-click Button3 and modify the click handler as follows:

    protected void Button3_Click(object sender, EventArgs e)
    {
        TextBox1.Text = (String)Session["SessionString"];
    }
    

    This will retrieve the session string and display it in the text box.

  5. Now modify the Page_Load method as follows:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
           TextBox1.Text = "Enter some text"; 
        }
    }
    

    This ensures that when the page loads the text box Text property is reset.

  6. Ensure that the solution is saved and then rebuild the solution.

  7. Run the solution without debugging.

  8. The form will be displayed. Enter some text into the text box. Now click Store Session Variable. At this point you have stored the string in a session variable.

  9. Now click Clear Text to clear the text box.

  10. Now click Show Session Variable to retrieve and display the session variable.

  11. Refresh the page to destroy the form and display a new form.

  12. Click Show Session Variable the text box will display the stored session variable, demonstrating that the refreshing the page does not destroy the session variable.

This illustrates that the session state data is not destroyed when a page is reloaded.