ASP.NET Creating and retrieving Sessions with MS SQL server

Post Views: 433

ASP.NET Creating and retrieving Sessions with MS SQL server

Sessions in ASP.NET are used to store and retrieve values and information for a user. ASP.NET stores sessions data for each user on server-side memory, hence session provide a powerful option of storing information of a transactions made by different user on a web page.Basic example of ASP.NET sessions without Database.ASP.NET Creating and retrieving Sessions with MS SQL server

Sessions expire after a certain amount of time. You can define this duration through the code file or web.config file in your .NET project.

How to store and retrieve values from session? :

Let’s take a example of a login page in ASP.NET page. On Login page code file, a session with UserId which can be a primary key can be stored in sessions.

Code for storing session :

Session[“userid”] = userid;// value retrieve from database after successful authorization

And we can retrieve this userid on our Index or Homepage to make loading secure only to specific users :

if(Session[“userid”]==null)

Response.Redirect(“Login.aspx”);

else

lblWelcome.Text = “Welcome user”;

Now we will create a working example to see the working of sessions in ASP.NET.

In your MS SQL server, create a database with name parallel. You can also use your existing database. I’m using SQL server 2005 edition as my DB. Below is my script for database creation with create table statement and insert statement.

Database script :

create database parallel;
use parallel;
create table tblUsers
(
UserId int identity(1,1) not null primary key,
[Name] nvarchar(50) not null,
Username  nvarchar(50) not null,
Password nvarchar(50) not null,
Role  nvarchar(50) not null
);
insert into tblUsers ([Name],Username,Password,Role) values ('Steve','Steve1','steve','Admin');
insert into tblUsers ([Name],Username,Password,Role) values ('John','John1','john','User');
select * from tblUsers

Project :

Create a new ASP.NET website project and create two new pages with name :

FrmLogin.aspx
Index.aspx

Open the FrmLogin.aspx design page and edit it as following :

FrmLogin.aspx :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="FrmLogin.aspx.cs" Inherits="FrmLogin" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Login Page</title>
</head>
<body>
    <form id="form1" runat="server" style="padding: 5px; font-family: Arial; color: #1c1c1c;">
    <div>
        <h2>
            Enter your credentials</h2>
        <hr />
        Username : &nbsp;&nbsp;
        <asp:TextBox runat="server" ID="txtusername" />
        <br />
        <br />
        Password : &nbsp;&nbsp;&nbsp;
        <asp:TextBox runat="server" ID="txtpassword" TextMode="Password"/>
        <br />
        <br />
        Select a color : &nbsp;&nbsp;
        <asp:DropDownList runat="server" ID="ddColor">
            <asp:ListItem Text="Red" Value="Red"></asp:ListItem>
            <asp:ListItem Text="Green" Value="Green"></asp:ListItem>
            <asp:ListItem Text="Black" Value="Black"></asp:ListItem>
            <asp:ListItem Text="Purple" Value="Purple"></asp:ListItem>
            <asp:ListItem Text="Yellow" Value="Yellow"></asp:ListItem>
            <asp:ListItem Text="Blue" Value="Blue"></asp:ListItem>
            <asp:ListItem Text="Violet" Value="Violet"></asp:ListItem>
            <asp:ListItem Text="Orange" Value="Orange"></asp:ListItem>
        </asp:DropDownList>
        <asp:Button runat="server" Text="Login" ID="btnLogin" OnClick="btnLogin_Click" />&nbsp;&nbsp;
        <asp:Button runat="server" Text="Clear" ID="btnClear" OnClick="btnClear_Click" />
        <br />
        <br />
        <asp:Label runat="server" ID="lblMessage" />
    </div>
    </form>
</body>
</html>

This page contains two text boxes for entering user id and password of the user and a drop down list for selecting a specific color from the given list of colors. Also two buttons, one for proceeding and other for clearing the entered values. We will store ASP.NET sessions values in form of String and Integer from this page.

ASP.NET Creating and retrieving Sessions with MS SQL server 01

frmLogin.aspx

And edit the code file as following :

FrmLogin.aspx.cs:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
public partial class FrmLogin : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnLogin_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=hitesh\\sqlexpress;Initial Catalog=parallel;User ID=hitesh;Password=789;"); // connectionstring
        con.Open();
        SqlCommand cmd = new SqlCommand("Select * from tblUsers where Username=@Username and Password=@Password", con);
        cmd.Parameters.AddWithValue("@Username", txtusername.Text.ToString());
        cmd.Parameters.AddWithValue("@Password", txtpassword.Text.ToString());
        SqlDataReader reader = cmd.ExecuteReader();
        if (reader.Read())
        {
           Session["userid"] = Convert.ToInt32(reader["userid"].ToString());
            // storing int value in sessions
            Session["Name"] = reader["Name"].ToString();
            // storing string value in sessions
            Session["Role"] = reader["Role"].ToString();
            Session["color"] = ddColor.Text.ToString();
            reader.Close();
            cmd.Dispose();
            con.Close();
            Response.Redirect("index.aspx");
        }
        else
        {
            reader.Close();
            cmd.Dispose();
            con.Close();
            lblMessage.Text = "Invalid credentials";
        }
    }
    protected void btnClear_Click(object sender, EventArgs e)
    {
      txtusername.Text = "";
      txtpassword.Text = "";
    }
}

Now open the index.aspx design page and edit it as following. This will be like a basic homepage to show to the user on successful login. This page will show user, his/her name, id and his/her role in the website.

Index.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Index.aspx.cs" Inherits="Index" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Homepage</title>
</head>
<body>
    <form id="form1" runat="server" style="padding: 5px; font-family: Arial; font-weight:bold;">
    <div>
        <asp:Label runat="server" ID="lbluserInfo"></asp:Label>
    </div>
    </form>
</body>
</html>

This page contains one label for showing the ASP.NET sessions values which will be retrieved on its page load method.

ASP.NET Creating and retrieving Sessions with MS SQL server 02

Index.aspx

And edit the code file as following :

Index.aspx.cs:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Drawing;
public partial class Index : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        String userid = Convert.ToString((int) Session["userid"]);
        String username = Session["Name"].ToString();
        String userrole = Session["Role"].ToString();
        lbluserInfo.Text = "Welcome, " + username + ". Your userid is " + userid + " and your role is " + userrole + ".";
        lbluserInfo.ForeColor = Color.FromName(Session["color"].ToString());
    }
}

Set the FrmLogin.aspx as your startpage. Now run the project.

ASP.NET Creating and retrieving Sessions with MS SQL server