Forms authentication against users in database table Part 92

Text version of the video
http://csharp-video-tutorials.blogspot.com/2012/12/forms-authentication-against-users-in.html

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

Slides
http://csharp-video-tutorials.blogspot.com/2013/08/part-92-forms-authentication-against.html

All ASP .NET Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-aspnet-video-tutorial.html

All ASP .NET Slides
http://csharp-video-tutorials.blogspot.com/p/aspnet-slides.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists

At the following link, you can find the text version of this video. You will also find the code samples used in the demo.
http://csharp-video-tutorials.blogspot.com/2012/12/forms-authentication-against-users-in.html

In Part 90, we have discussed about authenticating users against a list stored in web.config file. In Part 91, we have discussed about, registering users, if they do not have a username and password to log in. In this session, we will disuss about authenticating users against a list stored in a database table.

This is continuation to Part 91. Please watch Part 91, before proceeding with this video. Authenticating users against a list stored in web.config file is very easy. FormsAuthentication class exposes a static method Authenticate(), which does all the hardwork of authenticating users.

Part 90 - Forms authentication using user names list in web.config
http://www.youtube.com/watch?v=AoRWKBbc6QI

Part 91 - Forms authentication in asp.net and user registration
http://www.youtube.com/watch?v=_Nz4ynsDq3s

If we want to authenticate users against a list stored in a database table, we will have to write the stored procedure and a method in the application to authenticate users.

First let us create a stored procedure, that accepts username and password as input parameters and authenticate users.
Create Procedure spAuthenticateUser
@UserName nvarchar(100)
@Password nvarchar(100)
as
Begin
Declare @Count int

Select @Count = COUNT(UserName) from tblUsers
where [UserName] = @UserName and [Password] = @Password

if(@Count = 1)
Begin
Select 1 as ReturnCode
End
Else
Begin
Select -1 as ReturnCode
End
End

Copy and paste the following private method in Login.aspx.cs page. This method invokes stored procedure 'spAuthenticateUser'.
private bool AuthenticateUser(string username, string password)
{
// ConfigurationManager class is in System.Configuration namespace
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
// SqlConnection is in System.Data.SqlClient namespace
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spAuthenticateUser", con);
cmd.CommandType = CommandType.StoredProcedure;

// FormsAuthentication is in System.Web.Security
string EncryptedPassword = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "SHA1");
// SqlParameter is in System.Data namespace
SqlParameter paramUsername = new SqlParameter("@UserName", username);
SqlParameter paramPassword = new SqlParameter("@Password", EncryptedPassword);

cmd.Parameters.Add(paramUsername);
cmd.Parameters.Add(paramPassword);

con.Open();
int ReturnCode = (int)cmd.ExecuteScalar();
return ReturnCode == 1;
}
}

Invoke AuthenticateUser() method, in the login button click event handler
if (AuthenticateUser(txtUserName.Text, txtPassword.Text))
{
FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, chkBoxRememberMe.Checked);
}
else
{
lblMessage.Text = "Invalid User Name and/or Password";
}