Compare a value in a table in SQL to an entry in a textbox

Question

I have a table EmployeeRank1 in SQL Server that has a column Name. Under column Name there are two pre-defined names of employees. Moreover, in the table there is a column Password, which contains a generic password, which is "123456".

In WPF I have a textbox and that asks for name and one password box that asks for password. Underneath them, there is a button that says "Login".

The questions is how do I compare the content of Name and Pasword in my table to the input in the text box and the password box?

If the Name entered exists and the Password is correct, a new WPF page will be opened. Otherwise, a message stating that either the name or the password is incorrect will be printed.

This is what I have until now:

// check if the input matches and open the new WPF Page
    private void EmployeeRank1Button_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            // create a query and select everything from the EmployeeRank1 table
            string query = "select * from EmployeeRank1";

            // create a connection to the database and run the query
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);

            // use the sqlDataAdapter
            using(sqlDataAdapter)
            {
                // create a new DataTable that allows us
                // to store data from tables within objects
                DataTable employeeRank1Table = new DataTable();

                // fill the sqlDataAdapter with all the
                // information from the query(from the employeeRank1Table)
                sqlDataAdapter.Fill(employeeRank1Table);

                // TODO: compare Name and Password entered in the TextBox and PasswordBox to the data in the table
                if (tbName.Text == *Name in Table* && pbPassword.Password == *Password in Table*)
                {
                    EmployeeRank1 employeeRank1 = new EmployeeRank1();
                    employeeRank1.Show();
                }
            }
        }
        catch(Exception exception)
        {
            MessageBox.Show(exception.ToString());
        }  
    }

Answer 1

You don't need to retrieve the whole table in memory. Just use a WHERE statement in your sql command with Name = @nameparam AND Password = @passparam, use an SqlCommand to retrieve a SqlDataReader and if the reader has a row, then bingo, the user exists.

Said that, remember that storing passwords in clear text is a big NO NO in a security concerned application. See this q/a for the reasons

private void EmployeeRank1Button_Click(object sender, RoutedEventArgs e)
{
    try
    {
        // create a query and select just the record we need 
        string query = "select * from EmployeeRank1 where Name = @name AND Password = @pass";

        // A local sqlconnection in a using statement ensure proper disposal at the end of this code 
        using SqlConnection con = new SqlConnection(connectionstring);
        con.Open();

        // Let's the database do the work to search for the password and name pair
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = tbName.Text ;
        cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = tbPassword.Text ;
        SqlDataReader reader = cmd.ExecuteReader();

        // If the reader has rows then the user/pass exists in the db table
        if(reader.HasRows)
        {
            EmployeeRank1 employeeRank1 = new EmployeeRank1();
            employeeRank1.Show();
        }
    }
    catch(Exception exception)
    {
        MessageBox.Show(exception.ToString());
    }  
}

Note also that I used a local SqlConnection and not a global one inside a using statement. This is the correct way to use a Disposable object like a connection. Keeping a global connection is prone to resource leaks and all sorts of problems if something fails.