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.

Table 2: City

The following creates the Usercity table.

Table 3: User Login

The following creates the UserLogin table.

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.

Step 2: Select User Login

The following will select a User Login.

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.
   
   
   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

Design


Figure 2: Login Design

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


Figure 3: Add Role form

Step 5: Database Table

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

1. Designation Table:
   
   
   Figure 4: Designation Data
   
   
2. City Table:
   
   
   Figure 5: City Data
   
3. User Login Table:
   
   
   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;
   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. }
   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();
   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. }
   74. }
   75. }
   76. }
   Now your code is ready to run.

Step 7: Account.aspx Form

UI Design

UI Code
Step 8: HR.aspx Form

UI Design
UI Code
Step 9: Manager.aspx Form

UI Design
UI Code
Step 10: Browser side


Figure 7: Login Page

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

Step 11: Account Level Login


Figure 8: Account branch login


Figure 9: Account Welcome Page

Step 12: HR Level Login


Figure 10: HR Login


Figure 11: HR Welcome Page

Step 13: Manager Level Login


Figure 12: Manager Login


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.