C# : how to get database column value that depends on a ID

Question

I'm having hard time trying to solved it and tried many times but still it doesn't work. Here's the scenario, I have a login form which have username and password. I have a database for creating user, users have type admin and employee. What i want to happen is to get the username of the user and the type of user and pass it to a label in another form.

Here's my code

    private static int count = 0;   

    private void btn_login_Click(object sender, EventArgs e)
    {
        using (var con = SQLConnection.GetConnection())
        {
            var selectCommand = new SqlCommand("Select * from Users_Profile where Username= @Username and Password= @Password", con);           
            selectCommand.Parameters.Add("@Username", SqlDbType.VarChar, 50).Value = txt_username.Text;
            selectCommand.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value = txt_password.Text;

            SqlDataReader dataReader;
            dataReader = selectCommand.ExecuteReader();

            var loginSuccess = false;

            while (dataReader.Read())
            {
                loginSuccess = true;
            }

            if (string.IsNullOrEmpty(txt_username.Text) || string.IsNullOrEmpty(txt_password.Text))
            {
                MetroMessageBox.Show(this, "Please input the Required Fields", "System Message:", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            else
            {
                if (loginSuccess)
                {
                    count = 0;
                    MetroMessageBox.Show(this, "Login Successful", "System Message:", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.Hide();
                    var obj = new MainForm(this);
                    obj.Closed += (s, args) => this.Close();
                    obj.Show();
                }
                else
                {
                    count += 1;

                    if (count == 3)
                    {
                        MetroMessageBox.Show(this, "You have exceeded maximum login attempts, Please wait 10 seconds", "System Message:", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                        txt_username.Enabled = false;
                        txt_password.Enabled = false;
                        btn_login.Enabled = false;
                        LoginAttempstimeOut.Start();
                    }
                    else
                    {
                        MetroMessageBox.Show(this, "Invalid Username/Password", "System Message:", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                    }

                }
            }
        }        
    }

    private void LoginAttempstimeOut_Tick(object sender, EventArgs e)
    {
        LoginAttempstimeOut.Stop();
        txt_username.Enabled = true;
        txt_password.Enabled = true;
        btn_login.Enabled = true;
        count = 0;
    }

@Z.R.T. It's obvious. that's why he's asking here. could be better if you answer his question :) — , Oct 08 '18 at 02:17
@Z.R.T. I know i just forgot it, i used it to my other code. — Anonymous, Oct 08 '18 at 02:22
[read data from sql reader](https://stackoverflow.com/questions/4018114/read-data-from-sqldatareader) — Z.R.T., Oct 08 '18 at 02:22

score 0 · Answer 1 · answered Oct 08 '18 at 12:51

I agree with anonymous' answer but I believe there are some things you can do to clean up your code.

First, why are you querying the DB before you verify you have values to pass? Check for values from your text boxes first and then if you have values, make your DB call.

Second. I would encapsulate all the logic in a separate method that returns an appropriate value and call it from the _Click() method.

private static int count = 0;   

private void btn_login_Click(object sender, EventArgs e)
{
    if (string.IsNullOrEmpty(txt_username.Text) || string.IsNullOrEmpty(txt_password.Text))
    {
        MetroMessageBox.Show(this, "Please input the Required Fields", "System Message:", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    }
    else
    {
        string Results = CheckLogin(txt_username.Text, txt_password.Text)
        //**  code to handle results from db query **//
        if (Results.Equals("Invalid"))
        {
            // handle bad password
            count++;
        }
        else
        {
            // handle good password
        }
    }    
}


private string CheckLogin(string User, string Pass)
{
    string returnstring = "Invalid";
    using (var con = SQLConnection.GetConnection())
    {
        var selectCommand = new SqlCommand("Select * from Users_Profile where Username= @Username and Password= @Password", con);           
        selectCommand.Parameters.AddWithValue("@Username", User);
        selectCommand.Parameters.AddWithValue("@Password", Pass);
        SqlDataReader dataReader;
        dataReader = selectCommand.ExecuteReader();

        var loginSuccess = false;
        while (dataReader.Read())
        {
            loginSuccess = true;
            returnstring = dataReader["Usertype"].ToString();
        }
    return returnstring;
}

I'm pretty anal about my code so I might even put the processing of the good/bad password attempts in separate methods as well. Seems like a lot of additional work but it honors the principle of keeping your methods/functions as small and concise as possible and only doing one thing inside those methods.

Also, to pile some more on: :) NEVER USE "SELECT *" in production code! as in NOT EVER.

One more thing: if you have access to the SQL server to create stored procedures, the entire SQL command should be in a stored procedure that returns an appropriate value.

I know that you will find millions of examples on the 'net that don't use stored procedures. That doesn't mean it is right. :) There are many good reasons to use Stored Procedures and it is Microsoft recommendation (at least it used to be).

C# : how to get database column value that depends on a ID

1 Answers1