Sudhir Singh: Login Page in C#.Net Using Stored Procedure

March 2, 2016

Login Page in C#.Net Using Stored Procedure

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.

walk-through

Step -1 : Create a table

CREATE TABLE [dbo].[Login] (
    [UserName]         NVARCHAR (50)  NULL,
    [Password]         NVARCHAR (50)  NULL,
    [SecurityQuestion] NVARCHAR (MAX) NULL,
    [SecurityAnswer]   NVARCHAR (MAX) NULL
);

Step -2 : Now let us create a Stored Procedure named sp_Validateuser as follows:

CREATE PROCEDURE sp_ValidateUser
(
@User_name VARCHAR(50)
,@User_password VARCHAR(50)
)
AS
BEGIN
SELECT UserName
  ,Password
FROM Login_Details
WHERE UserName=@User_name AND Password=@User_password
END

Step -3 : Create a Login Form

Write the following code in the Button1_click function as:

if (txtUserName.Text == "")
            {
                MessageBox.Show("Please enter the user name", "OOPPS", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else if (txtPwd.Text == "")
            {
                MessageBox.Show("Please enter the password", "OOPPS", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                DataTable dt = new DataTable();
                SqlDataAdapter adp = new SqlDataAdapter();
                try
                {
                    SqlCommand cmd = new SqlCommand("sp_ValidateUser", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@User_name ", txtUserName.Text.Trim());
                    cmd.Parameters.AddWithValue("@User_password ", txtPwd.Text.Trim());
                    adp.SelectCommand = cmd;
                    adp.Fill(dt);
                    cmd.Dispose();
                    if (dt.Rows.Count > 0)
                    {
                        // Perform the action if username and password is correct
                    }

                    else
                    {
                        MessageBox.Show("User Name or Password is wrong", "Please check", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                        txtUserName.Text = "";
                        txtPwd.Text = "";
                        txtUserName.Focus();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    dt.Clear();
                    dt.Dispose();
                    adp.Dispose();
                }
            }

Sudhir Singh