Validate username/password vs SQL Db

[anitha10]

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

[Rhuntsman21]

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

[anitha10]

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

[Rhuntsman21]

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;
            }
        }

[Momerath]

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.

[anitha10]

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

[Geekitygeek]

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 :)

[anitha10]

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

[Geekitygeek]

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.