Login Page in ASP.Net C# Using Stored Procedure

Background

Identifying the user that is authentication is a very important task in any application development and I have often read forum posts asking how to create the login page using a Stored Procedure. So by considering the above requirement I have decided to write this article to help beginners, students and who wants to learn how to create a Login page using a Stored Procedure.
 
So let us start with the walkthrough.
 
First create one table named Emp_Login in the SQL database which looks as in the following image:
 
CraeteTable.png
 
Insert the One Record in the table as:

Insert into Emp_Login (userName,word) values ('vthal.wadje','vithal')
 

In the above table the usename column is used to store the User Name and word is for the user's word.
 
Now let us create a Stored Procedure named Emplogin as follows:
 
Create  procedure Emplogin
(
@Usename Varchar (20),
@word varchar (10)
)
as
Begin
Select COUNT(*)from Emp_Login where username=@Usename and word=@word 
End
 
In the above Stored Procedure named Emplogin the variable @Usename is used for the username and @word is used for the word. The select query that I have written in the beginning counts the number of users from the table whose name matches with the exact two variables; that is @Usename and @word which comes from the front end user input page.
 
Now create the project named Loginapplication or you can specify any name as:
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
  2. "File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).
  3. Give the web site a name such as Loginapplication or another as you wish and specify the location.
  4. Then right-click on Solution Explorer - "Add New Item" - Default.aspx page for Login Credential and Welcome.aspx for redirecting after successful login.
  5. Drag and drop two buttons, one Label and two textBoxes on the <form> section of the Default aspx page.
Then the  <form> section of the Default aspx page looks as in the following:
 
Default page.png
 
Then double-click on btn2 and write the following code in the Button_click function as:
 
 protected void Button2_Click (objectsender, EventArgs e)
 {
     connection();
     query = "Emplogin";   //stored procedure Name
     SqlCommand com = new  SqlCommand(query, con);
     com.CommandType= CommandType.StoredProcedure;
     com.Parameters.AddWithValue("@Usename", TextBox1.Text.ToString());   //for username 
     com.Parameters.AddWithValue("@word", TextBox2.Text.ToString());  //for word
 
     int usercount = (Int32)com.ExecuteScalar();// for taking single value
 
     if (usercount == 1)  // comparing users from table 
     {
         Response.Redirect("Welcome.aspx");  //for sucsseful login
     }
     else
     {
         con.Close();
         Label1.Text = "Invalid User Name or word";  //for invalid login
     }
}
 
The most important part of the preceding code is the If condition part, I have used the one integer variable user count which is used to count the single value from the Stored Procedure using ExecuteScalar, if the records are found in the table it returns 1 then the page is redirected to the Welcome page otherwise it gives Invalid user Name or word. 
 
Now run the application.
 
our userName is- vithal.wadje
and word is - Vithal
 
Which we are already entered into the table at the creation of the table.
 
Enter an invalid user name or word; it gives an error as shown in the following:
 
Invalid username and word.png
 
Now enter the valid username and word, that is:
 
UserName: Vithal.wadje
and word: vithal
 
Then the page is redirected to the welcome page as shown in following:
 
SuccesfulLogin.png

Note
  • For detailed code please download the sample zip file.
  • Perform the changes in the connectionStrings which is Located in the Web.config file according to your server location.
  • Set the Default.aspx page as Start page from project's Solution Explolar by right-clicking the mouse.
Summary
 
From all of the above examples we have learned how to create the Login page using a Stored Procedure. I have not used any Cookies method or any other method because my intent is to not make it too complicated so students and beginners are comfortable, so to increase the understandability for students I have not used any cookies or other methods.
 
I hope this article is useful for all readers, if you have a suggestion then please contact me.