I want to validate username and password i.e.I created a table with two variables username and password in sql. I want to match table username and given username as well as for password. If those matches then only it should login. Please help me with the code sir

I think I can actually help with this one. Here is the way that I did it.

I created a class with this method in it:

public static bool IsValidatedUser(string username, string password)
        {
            try
            {
                bool rv = false;

                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Huntsman_Technology.Properties.Settings.HuntTech1"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        con.Open();

                        cmd.Connection = con;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "ValidateUserLogin";

                        cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 30).Value = username;
                        cmd.Parameters.Add("@Password", SqlDbType.VarChar, 30).Value = password;

                        rv = Convert.ToBoolean(cmd.ExecuteScalar());

                        con.Close();
                    }
                }

                return rv;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error Prossessing Login");
                throw;
            }

The code block above calls this stored procedure.

ALTER procedure [dbo].[ValidateUserLogin]
  @UserName varchar(30),
  @Password varchar(30)
as
begin
  if exists (select * from UsersTable as ut
    where ut.UserName = @UserName AND ut.Password = @Password)
    select 1;
  else
    select 0;
end

I think I can actually help with this one. Here is the way that I did it.

I created a class with this method in it:

public static bool IsValidatedUser(string username, string password)
        {
            try
            {
                bool rv = false;

                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Huntsman_Technology.Properties.Settings.HuntTech1"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        con.Open();

                        cmd.Connection = con;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "ValidateUserLogin";

                        cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 30).Value = username;
                        cmd.Parameters.Add("@Password", SqlDbType.VarChar, 30).Value = password;

                        rv = Convert.ToBoolean(cmd.ExecuteScalar());

                        con.Close();
                    }
                }

                return rv;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error Prossessing Login");
                throw;
            }

The code block above calls this stored procedure.

ALTER procedure [dbo].[ValidateUserLogin]
  @UserName varchar(30),
  @Password varchar(30)
as
begin
  if exists (select * from UsersTable as ut
    where ut.UserName = @UserName AND ut.Password = @Password)
    select 1;
  else
    select 0;
end

sir this is used in stored procedure but i want to validate in normal sql db plz giv me the code in that way. Please sir

Here you go. This should work for you.

public static bool IsValidatedUser(string username, string password)
        {
            try
            {
                bool rv = false;

                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Huntsman_Technology.Properties.Settings.HuntTech1"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        con.Open();

                        cmd.Connection = con;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "if exists (select * from UsersTable as ut " +
                            "where ut.UserName = @UserName AND ut.Password = @Password) " +
                            "select 1; " +
                            "else " +
                            "select 0; ";

                        cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 30).Value = username;
                        cmd.Parameters.Add("@Password", SqlDbType.VarChar, 30).Value = password;

                        rv = Convert.ToBoolean(cmd.ExecuteScalar());

                        con.Close();
                    }
                }

                return rv;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error Prossessing Login");
                throw;
            }
        }

You can shorten your SQL to

cmd.CommandText = "SELECT count(*) from UsersTable AS ut WHERE ut.UserName = @UserName AND ut.Password = @Password"

If it's not zero, then you have 1 (or more) users.

Here you go. This should work for you.

public static bool IsValidatedUser(string username, string password)
        {
            try
            {
                bool rv = false;

                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Huntsman_Technology.Properties.Settings.HuntTech1"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        con.Open();

                        cmd.Connection = con;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "if exists (select * from UsersTable as ut " +
                            "where ut.UserName = @UserName AND ut.Password = @Password) " +
                            "select 1; " +
                            "else " +
                            "select 0; ";

                        cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 30).Value = username;
                        cmd.Parameters.Add("@Password", SqlDbType.VarChar, 30).Value = password;

                        rv = Convert.ToBoolean(cmd.ExecuteScalar());

                        con.Close();
                    }
                }

                return rv;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error Prossessing Login");
                throw;
            }
        }

Thank u for giving the reply me and this code is useful to my application

As an aside, you should generally store and check a hash of the password rather than the password itself as this is more secure.
If the answers you have been given helped, please remember to mark this thread as solved :)

As an aside, you should generally store and check a hash of the password rather than the password itself as this is more secure.
If the answers you have been given helped, please remember to mark this thread as solved :)

I want to check that if the user name and password is already stored db table now i want check the given username,password should match in the table then only it should login actually i.e. my question please help me with the code sir

Momerath already gave you a query to check for a matching username and password in the table. His query counts how many rows have a username and password that match the values you pass in...so if the count is 1 the user has entered the password correctly, if the count is 0 then the username and password dont match anythin in the table.

This article has been dead for over six months. Start a new discussion instead.