ASP.Net Role Based Login Form Using SQL Procedure

This article shows how to create an employee designation by role and by city. In this form select the first Employee's location and after login. Also Login User name is stored in the session and displayed in a redirect page.

Step 1: Database Side

In this section create an Employee Designation, City and a User login table.

Table 1: Designation

The following creates the Designation table.

  1. create table Designation  
  2. (  
  3.    Designationid int identity(1,1) primary key,  
  4.    Designation varchar(30)  
  5. )  

Table 2: City

The following creates the Usercity table.

  1. create table Usercity  
  2. (  
  3.    cityid int identity(1,1) primary key,  
  4.    city varchar(20)  
  5. )  

Table 3: User Login

The following creates the UserLogin table.

  1. create table UserLogin  
  2. (  
  3.    UserId int identity(1,1) primary key,  
  4.    UserName varchar(20),  
  5.    Password varchar(20),  
  6.    Designationid int foreign key references Designation(Designationid),  
  7.    cityid int foreign key references UserCity(cityid)  
  8. )  

Step 2: Create SQL Procedure

Now also create two select procedures in SQL as in the following.

Step 1: Select City

The following will select a City.

  1. create procedure sp_GetCity  
  2. as  
  3. begin  
  4.    set nocount on;  
  5.    select cityid, city from Usercity  
  6. End  

Step 2: Select User Login

The following will select a User Login.

  1. create procedure sp_CheckUser  
  2. as   
  3. begin  
  4.    set nocount on;  
  5.    select UserId,UserName,Password,Designationid,cityid from UserLogin  
  6. End  
Step 3: Visual Studio

 

  1. Go to Visual Studio.
  2. Add a new project.
  3. Right-click on the project in the Solution Explorer.
  4. Add a new item.

    Add Web Form
    Figure 1: Add Web Form

Step 4: UI Design Side

In this page design side add a TextBox control, dropdown list and button control.

UI Design Code

  1. <%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="UserLogin.aspx.cs" Inherits="UI_UserLogin" %>  
  2.   
  3. <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">  
  4. </asp:Content>  
  5. <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">  
  6.   
  7. <link href="../Styles/site.css" rel="stylesheet" />  
  8.   
  9. <div class="page">  
  10.     <h1 class="header" style="color: #FFFFFF"> Role Based Login Form Example</h1>  
  11. <div>  
  12.     <div class="login">  
  13.     <fieldset><legend>User Login</legend>  
  14.        <asp:Table runat="server" >  
  15.            <asp:TableRow>  
  16.                <asp:TableCell>City</asp:TableCell><asp:TableCell><asp:DropDownList runat="server" ID="ddlcity" ></asp:DropDownList></asp:TableCell>  
  17.            </asp:TableRow>  
  18.            <asp:TableRow>  
  19.                <asp:TableCell>UserName</asp:TableCell><asp:TableCell><asp:TextBox runat="server" ID="txtUser"></asp:TextBox></asp:TableCell>  
  20.            </asp:TableRow>  
  21.            <asp:TableRow>  
  22.                <asp:TableCell>Password</asp:TableCell><asp:TableCell><asp:TextBox runat="server" ID="txtpassword" TextMode="Password"></asp:TextBox></asp:TableCell>  
  23.            </asp:TableRow>  
  24.            <asp:TableRow>  
  25.                <asp:TableCell></asp:TableCell><asp:TableCell><asp:Button runat="server" ID="btnlogin" Text="Login" OnClick="btnlogin_Click" /></asp:TableCell>  
  26.            </asp:TableRow>  
  27.        </asp:Table>  
  28.     </fieldset>  
  29. </div>  
  30.     </div>  
  31. </div>  
  32. </asp:Content>  
Design

Login Design
Figure 2: Login Design

Also add another something from which to redirect after the user login.

Add Role form
Figure 3: Add Role form

Step 5: Database Table

Now your table's records are filled in as in the following:

 

  1. Designation Table:

    Designation Data
    Figure 4: Designation Data

  2. City Table:

    City Data
    Figure 5: City Data
  3. User Login Table:

    User name and Password
    Figure 6: User name and Password

Step 6: UI Code

  1. Define a database connection.

    Maintain a database connection string in the project's web.config file as in the following:
    1. <connectionStrings>  
    2.    <add name="connstring" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=password" providerName="System.Data.SqlClient"/>  
    3. </connectionStrings>  
  2. Code.

    In this section first show the bound City dropdown list from the database using the preceding select city procedure. Then provide the login code for a login button event.
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Web;  
    5. using System.Web.UI;  
    6. using System.Web.UI.WebControls;  
    7. using System.Configuration;  
    8. using System.Data;  
    9. using System.Data.SqlClient;  
    10.   
    11. public partial class UI_UserLogin : System.Web.UI.Page  
    12. {  
    13.     string connection = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;  
    14.     protected void Page_Load(object sender, EventArgs e)  
    15.     {  
    16.         BindCity();  
    17.     }  
    18.     public void BindCity()  
    19.     {  
    20.         using (SqlConnection con = new SqlConnection(connection))  
    21.         {  
    22.             using (SqlCommand cmd = new SqlCommand("sp_GetCity", con))  
    23.             {  
    24.                 cmd.CommandType = CommandType.StoredProcedure;  
    25.                 SqlDataAdapter dt = new SqlDataAdapter(cmd);  
    26.                 DataSet dst = new DataSet();  
    27.                 dt.Fill(dst);  
    28.                 ddlcity.DataSource = dst.Tables[0];  
    29.                 ddlcity.DataTextField = "city";  
    30.                 ddlcity.DataValueField = "cityid";  
    31.                 ddlcity.DataBind();  
    32.                 ddlcity.Items.Insert(0, new ListItem("---Select City---"));  
    33.             }  
    34.         }  
    35.     }  
    36.   
    37.     protected void btnlogin_Click(object sender, EventArgs e)  
    38.     {  
    39.         int RowCount;  
    40.         string User, Password, Cityid;  
    41.         using (SqlConnection con = new SqlConnection(connection))  
    42.         {  
    43.             using (SqlCommand cmd = new SqlCommand("sp_CheckUser", con))  
    44.             {  
    45.                 using (SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText, con))  
    46.                 {  
    47.                     DataTable dt = new DataTable();  
    48.                     da.Fill(dt);  
    49.                     RowCount = dt.Rows.Count;  
    50.                     for (int i = 0; i < RowCount; i++)  
    51.                     {  
    52.                         User = dt.Rows[i]["UserName"].ToString();  
    53.                         Password = dt.Rows[i]["Password"].ToString();  
    54.                         Cityid = dt.Rows[i]["cityid"].ToString();   
    55.   
    56.                         if (User == txtUser.Text && Password == txtpassword.Text  )  
    57.                         {  
    58.                             Session["UserName"] = User;  
    59.                             if (dt.Rows[i]["Designationid"].ToString() == "1")  
    60.                                 Response.Redirect("~/Roles/Manager.aspx");  
    61.                             else if (dt.Rows[i]["Designationid"].ToString() == "2")  
    62.                                 Response.Redirect("~/Roles/HR.aspx");  
    63.                             else if (dt.Rows[i]["Designationid"].ToString() == "3")  
    64.                                 Response.Redirect("~/Roles/Account.aspx");  
    65.                         }  
    66.                         else  
    67.                         {  
    68.                                lblmsg.Text = "UserName or Password Not Correct.....!";  
    69.                         }  
    70.                     }  
    71.                 }  
    72.             }  
    73.   
    74.         }  
    75.     }  
    76. }  
    Now your code is ready to run.

Step 7: Account.aspx Form

UI Design

  1. <%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="Account.aspx.cs" Inherits="Roles_Account" %>  
  2.   
  3. <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">  
  4. </asp:Content>  
  5. <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">  
  6.       <link href="../Styles/site.css" rel="stylesheet" />  
  7. <div class="page">  
  8.     <h1 class="title">Welcome to Account Page...........!!</h1>  
  9.     <span class="title"><asp:Label ID="lblName" runat="server" ></asp:Label></span>  
  10. </div>  
  11.   
  12. </asp:Content>  
UI Code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7.   
  8. public partial class Roles_Account : System.Web.UI.Page  
  9. {  
  10.     protected void Page_Load(object sender, EventArgs e)  
  11.     {  
  12.         lblName.Text = "<b><font color=Brown>" + "WELCOME: " + "</font>" + "<b><font color=white>" + Session["UserName"] + "</font>";  
  13.     }  
  14. }  
Step 8: HR.aspx Form

UI Design
  1. <%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="HR.aspx.cs" Inherits="Roles_HR" %>  
  2.   
  3. <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">  
  4. </asp:Content>  
  5. <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">  
  6.   
  7.     <link href="../Styles/site.css" rel="stylesheet" />  
  8. <div class="page">  
  9.     <h1 class="title">Welcome to HR Page...........!!</h1>  
  10.      <span class="title"><asp:Label ID="lblName" runat="server" ></asp:Label></span>  
  11. </div>  
  12.   
  13. </asp:Content>  
UI Code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7.   
  8. public partial class Roles_HR : System.Web.UI.Page  
  9. {  
  10.     protected void Page_Load(object sender, EventArgs e)  
  11.     {  
  12.         lblName.Text = "<b><font color=Brown>" + "WELCOME: " + "</font>" + "<b><font color=white>" + Session["UserName"] + "</font>";  
  13.     }  
  14. }  
Step 9: Manager.aspx Form

UI Design
  1. <%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="Manager.aspx.cs" Inherits="Roles_Manager" %>  
  2.   
  3. <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">  
  4. </asp:Content>  
  5. <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">  
  6.   
  7.     <link href="../Styles/site.css" rel="stylesheet" />  
  8. <div class="page">  
  9.     <h1 class="title">Welcome to Manager Page...........!!</h1>  
  10.      <span class="title"><asp:Label ID="lblName" runat="server" ></asp:Label></span>  
  11. </div>  
  12.   
  13. </asp:Content>  
UI Code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7.   
  8. public partial class Roles_Manager : System.Web.UI.Page  
  9. {  
  10.     protected void Page_Load(object sender, EventArgs e)  
  11.     {  
  12.         lblName.Text = "<b><font color=Brown>" + "WELCOME: " + "</font>" + "<b><font color=white>" + Session["UserName"] + "</font>";  
  13.     }  
  14. }  
Step 10: Browser side

Login Page
Figure 7: Login Page

Select the location and then fill in the user information for the login.

Step 11: Account Level Login

Account branch login
Figure 8: Account branch login

Account Welcome Page
Figure 9: Account Welcome Page

Step 12: HR Level Login

HR Login
Figure 10: HR Login

HR Welcome Page
Figure 11: HR Welcome Page

Step 13: Manager Level Login

Manager Login
Figure 12: Manager Login

Manager welcome page
Figure 13: Manager welcome page

I hope you understood how to create a role-based login form using SQL procedure with the user name stored in the session and displayed in a welcome page.