C# Login form with User Authentication with SQL Server database

Question

I am new to SQL Server and C#. I am working on a login form that checks if the user is an admin or a basic user. In my SQL Server, I created a table that stores username, password, and role (admin or basic user). The saved data are the following:

• For admin: username = admin, password = admin, role = admin
• For basic user: username = user, password = user, role = user

If the user enters username and password "admin" it should be directed to the admin page else it would be user page.

This is my code:

string query = "SELECT * from tbl_login WHERE Username = @username and password=@password";

con.Open();

SqlCommand sqlcmd = new SqlCommand(query, con);
sqlcmd.Parameters.AddWithValue("@username", tbusername.Text);
sqlcmd.Parameters.AddWithValue("@password", tbpswlog.Text);
DataTable dtbl = new DataTable();

SqlDataAdapter sqlsda = new SqlDataAdapter(sqlcmd);
sqlsda.Fill(dtbl);

con.Close();

if (dtbl.Rows.Count == 1)
{
    this.Hide();

    if (tbusername.Equals("admin"))
    {
        MessageBox.Show("You are logged in as an Admin");
        AdminHome fr1 = new AdminHome();
        fr1.Show();
        this.Hide();
    }
    else
    {
        MessageBox.Show("You are logged in as a User");
        UserHome fr2 = new UserHome();
        fr2.Show();
        this.Hide();
    }
}
else
{
    MessageBox.Show("Incorrect username or password");
}

I know this code lacks and wrong.

EDIT:

I tried this code below

if (dtbl.Rows.Count == 1)
{
    this.Hide();

    if (dtbl.Rows[0]["role"].ToString().Equals("admin"))
    {
        MessageBox.Show("You are logged in as an Admin");
        AdminHome fr1 = new AdminHome();
        fr1.Show();
        this.Hide();
    }
    else
    {
        MessageBox.Show("You are logged in as a User");
        UserHome fr2 = new UserHome();
        fr2.Show();
        this.Hide();
    }
}
else
{
    MessageBox.Show("Incorrect username or password");
}

But even if I entered username admin and password admin, it would say "You entered as a user".

Answer 1

You only need to retrieve the value of the role. Don't retrieve unnecessary data. Check the name of the column in the database and correct if necessary.

Keep you database objects local so you can be sure they are closed and disposed. The using blocks that care of this even if there is an error.

You don't need a DataTable or a DataAdapter. Using .ExecuteScalar will return the first column of the first row of the result set which is exactly what you want to know. The role.

    private void ValidateUser()
    {
        string query = "SELECT role from tbl_login WHERE Username = @username and password=@password";
        string returnValue = "";
        using (SqlConnection con = new SqlConnection("YourConnectionString"))
        {
            using (SqlCommand sqlcmd = new SqlCommand(query, con))
            {
                sqlcmd.Parameters.Add("@username", SqlDbType.VarChar).Value = tbusername.Text;
                sqlcmd.Parameters.Add("@password", SqlDbType.VarChar).Value = tbpswlog.Text;
                con.Open();
                returnValue = (string)sqlcmd.ExecuteScalar();
            }
        }
//EDIT to avoid NRE 
        if (String.IsNullOrEmpty(returnValue))
        {
            MessageBox.Show("Incorrect username or password");
            return;
        }
        returnValue = returnValue.Trim();
        if (returnValue == "Admin")
        {
            MessageBox.Show("You are logged in as an Admin");
            AdminHome fr1 = new AdminHome();
            fr1.Show();
            this.Hide();
        }
        else if (returnValue == "User")
        {
            MessageBox.Show("You are logged in as a User");
            UserHome fr2 = new UserHome();
            fr2.Show();
            this.Hide();
        }
    }