August 1, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Storing Session State in a SQL Server Database

  • March 31, 2006
  • By Bipin Joshi
  • Send Email »
  • More Articles »

Configuring the Web Site to Store a Session State

Once you configure your SQL server to support session state storage, the next step is to configure your Web site. The <sessionState> tag of web.config allows you to specify information about the session store. The following table lists some of the important attributes of this tag:

Attribute Description
mode The mode attribute can take the following values:

Off: Indicates that the session state is turned off.
InProc: Indicates that the session state will be stored in the Web server's memory. This is the default setting.
StateServer: Indicates that the session state will be stored on a state server.
SQLServer: Indicates that the session state will be stored in a SQL Server database.
Custom: Indicates that you will have a custom mechanism of session storage using a provider model of ASP.NET

sqlConnectionString If the mode is set to SQLServer, you must specify this attribute. This attribute specifies the database connection string of the SQL Server database that is acting as a state store. Note that you need not specify the database name in the connection string if you are using temporary or persistent storage options (see above).
allowCustomSqlDatabase If you want to store session state in a SQL server database of your own, you must set this attribute to "true". Once this attribute is set to true, be sure to specify the name of the database the in sqlConnectionString attribute described above.

Example

To illustrate what you've learned up to now, you can develop a simple Web site and configure it to store a session state in a SQL Server database.

Configuring SQL Server

Open the Visual Studio.NET 2005 command prompt and issue the following command:

aspnet_regsql -ssadd -S .sqlexpress -E -sstype p

Here, you are using SQL Express as the database with integrated security turned on. Figure 3 shows a sample run of this tool with the session state type set to "persistent".



Click here for a larger image.

Figure 3: Sample run of ASPNET_REGSQL.EXE tool

Creating and configuring a Web site

Now, create a new web site and modify the web.config file to have the following markup:

<sessionState
mode="SQLServer"
sqlConnectionString="data source=.sqlexpress;
integrated security=true">
</sessionState>

Here, you added the <sessionState> tag and set its mode attribute to SQLServer. You also specified the sqlConnectionString attribute and pointed it to your SQL Server.

Creating test web forms

Add two Web forms in the Web site called Page1.aspx and Page2.aspx. The first Web form will display a list of products to choose from. The selected products are stored in a DataSet. The DataSet is then persisted across the requests by storing it in a session variable. The second Web form simply displays the selected products from the DataSet.

To create the first Web form, follow these steps:

  1. Drag and drop a GridView control and SQL Data Source control onto the Web form.
  2. Configure the SQL Data Source control to select the ProductID, ProductName, and UnitPrice columns from the Products table of the Northwind database. (A detailed explanation of using data source controls is out of scope of this article.)
  3. Bind the GridView to the SQL Data Source by setting its DataSourceID property to the ID property of the SQL Data Source control
  4. From the smart tags of GridView, check the "Allow Selection" checkbox.
  5. Add a HyperLink control and set its NavigateUrl property to Page2.aspx.

The following markup is generated as a result of above operations (unwanted markup removed):

...
<asp:GridView ID="GridView1" runat="server"
              AllowPaging="True" AutoGenerateColumns="False"
              CellPadding="4" DataSourceID="SqlDataSource1"
              ForeColor="#333333" GridLines="None"
              Width="100%"
              OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
              PageSize="5">
   <FooterStyle BackColor="#990000" Font-Bold="True"
                ForeColor="White" />
   <Columns>
      <asp:BoundField DataField="ProductName"
                      HeaderText="ProductName"
                      SortExpression="ProductName" />
      <asp:BoundField DataField="UnitPrice"
                      HeaderText="UnitPrice"
                      SortExpression="UnitPrice" />
      <asp:CommandField SelectText="Add to cart"
                        ShowSelectButton="True" />
   </Columns>
</asp:GridView>
...
<asp:HyperLink ID="HyperLink1" runat="server"
               NavigateUrl="~/Page2.aspx" Font-Bold="True"
               Font-Size="Large">Show my cart</asp:HyperLink>
...
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
                   ConnectionString=
                   "<%$ ConnectionStrings:NorthwindConnectionString %>"
                   SelectCommand="SELECT [ProductName],
                   [UnitPrice] FROM [Products] 
                   ORDER BY [ProductName]"></asp:SqlDataSource>

Now, add the following code to the SelectedIndexChanged event of the GridView:

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
   DataSet ds=null;
   if (Session["myds"] == null)
   {
      ds = new DataSet();
      DataTable dt = new DataTable();
      dt.Columns.Add(new DataColumn("ProductName"));
      dt.Columns.Add(new DataColumn("Qty", typeof(System.Int32)));
      ds.Tables.Add(dt);
      Session["myds"] = ds;
   }
   else
   {
      ds = (DataSet)Session["myds"];
   }
   DataRow row=ds.Tables[0].NewRow();
   row["productname"]=GridView1.Rows[GridView1.SelectedIndex].
      Cells[0].Text;
   row["Qty"]=1;
   ds.Tables[0].Rows.Add(row);
}

Here, you create a DataSet and a DataTable. The DataTable contains two DataColumns: ProducyName and Qty. You then store the DataSet in a session variable called "myds". When the user selects a product, you create a new DataRow, assign the product details to it, and then add this row to the DataTable. Thus, the user selections are persisted across multiple requests.

To create the second Web form, follow these steps:

  1. Drag and drop a GridView onto the Web form.
  2. Add two bound fields.
  3. Set the HeaderText property of the first bound field to "Product Name". Also, set its DataField property to "ProductName".
  4. Set the HeaderText property of the second bound field to "Quantity". Also, set its DataField property to "Qty".

The following markup is created as a result of above operations (unwanted markup removed):

...
<asp:GridView ID="GridView1" runat="server"
              AutoGenerateColumns="False" CellPadding="4"
              ForeColor="#333333" GridLines="None" Width="100%">
   <FooterStyle BackColor="#1C5E55" Font-Bold="True" 
                ForeColor="White" />
   <Columns>
      <asp:BoundField DataField="productname"
                      HeaderText="Product Name" />
      <asp:BoundField DataField="qty"
                      HeaderText="Quantity" />
   </Columns>
</asp:GridView>
...

Add the following code in the Page_Load event handler of the second Web form.

protected void Page_Load(object sender, EventArgs e)
{
   GridView1.DataSource=(DataSet)Session["myds"];
   GridView1.DataBind();
}

Here, you set the DataSource property of the GridView control to your DataSet stored in session. You then bind the GridView by calling its DataBind() method.





Page 2 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel