C# Local SQL rows returned if statement

Question

I'm trying to get my login system to work. Currently I think I have everything in place for it to work except the if statement conditions (if row is returned, then if statement is true, else login unsuccessful). I'm not sure how to read in the number of rows returned, I did attempt to use the ExecuteReader Method but couldn't get it to work. Appreciate any help, thanks.

Code:

private void btn_login_Click(object sender, EventArgs e)
{
    SqlCeConnection connection = new SqlCeConnection(@"Data Source=C:\\temp\\Project\\WindowsFormsApplication2\\Database.sdf");

    connection.Open();

    SqlCeCommand command = new SqlCeCommand("SELECT * FROM Technician WHERE Name = '" + txt_username.Text + "' AND Password = '" + txt_password.Text + "' ");
    SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(command);

    if ()
    {
        MessageBox.Show("Login Successful");
        System.Threading.Thread t = new System.Threading.Thread(new System.Threading.ThreadStart(MainMenuForm));
        t.Start();
        this.Close();
    }
    else
    {
        MessageBox.Show("Login Unsuccessful");
        return;
    }

    connection.Close();
}

Answer 1

I have changed your code to use a simpler ExecuteScalar that returns the first column of the first row obtained by your query

Of course, it is of extreme importance that you don't write your sql commands concatenating strings because this could fail in spectacular ways. (What if your textboxes contains a single quote and what if your user writes malicious text like this

using(SqlCeConnection connection = new SqlCeConnection(.....)) 
{
     connection.Open();
     string sqlText = "SELECT Count(*) FROM Technician WHERE Name = @name AND Password=@pwd"
     SqlCeCommand command = new SqlCeCommand(sqlText, connection);
     command.Parameters.AddWithValue("@name", txt_username.Text);
     command.Parameters.AddWithValue("@pwd", txt_password.Text);
     int result = (int)command.ExecuteScalar();
     if (result > 0)
     {
          MessageBox.Show("Login Successful");
          System.Threading.Thread t = new System.Threading.Thread(new System.Threading.ThreadStart(MainMenuForm));
          t.Start();
          this.Close();
     }
     else
     {
           MessageBox.Show("Login Unsuccessful");
           return;
     }
 }

Notice also the using statement, in your previous code you exit from the procedure if no login is found but you forget to close the connection. This could become a big problem during lifetime of your application. The Using statement prevents this

Now I should start talking about the weakness of storing and trasmitting passwords in clear text, but that is another matter