Query not executing as expected, returns -1 no matter what

I’m working on a user login system for a semester final. I am using C# in Visual Studio with ADO.NET. I have a query that I use on a database table named Credentials:

SELECT * FROM Credentials WHERE Username = @Username AND Password = @Password

​x
 
SELECT * FROM Credentials WHERE Username = @Username AND Password = @Password​

I also have the string connection as

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|UserCredentials.mdf;Integrated Security=True;

 
Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|UserCredentials.mdf;Integrated Security=True;​

I then run the query against the table with int count = selectCommand.ExecuteNonQuery();. selectCommand is the SqlCommand object with the above query in it. No matter what, count will equal -1 even if I enter an existing username and password. I want the count variable to be 1 when someone enters in a correct username and password combo.

class DatabaseConnection {
    public static SqlConnection GetConnection() {
        SqlConnection connection = new SqlConnection(
            "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|UserCredentials.mdf;Integrated Security=True;"
        );
        return connection;
    }
}

class LoginDB {
    public static bool IsUser(string username, string password) {
        SqlConnection connection = DatabaseConnection.GetConnection();

        string selectStatement = @"SELECT * FROM Credentials 
                                   WHERE Username = @Username AND Password = @Password";
        SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
        selectCommand.Parameters.AddWithValue("@Username", username);
        selectCommand.Parameters.AddWithValue("@Password", password);

        try {
            connection.Open();
            int count = selectCommand.ExecuteNonQuery();
            MessageBox.Show(count.ToString());
            if (count > 0) {
                return true;
            }
            else {
                return false;
            }
        }
        catch (SqlException ex) {
            throw ex;
        }
        finally {
            connection.Close();
        }
    }
}

 
class DatabaseConnection {    public static SqlConnection GetConnection() {        SqlConnection connection = new SqlConnection(            "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|UserCredentials.mdf;Integrated Security=True;"        );        return connection;    }}​class LoginDB {    public static bool IsUser(string username, string password) {        SqlConnection connection = DatabaseConnection.GetConnection();​        string selectStatement = @"SELECT * FROM Credentials                                    WHERE Username = @Username AND Password = @Password";        SqlCommand selectCommand = new SqlCommand(selectStatement, connection);        selectCommand.Parameters.AddWithValue("@Username", username);        selectCommand.Parameters.AddWithValue("@Password", password);​        try {            connection.Open();            int count = selectCommand.ExecuteNonQuery();            MessageBox.Show(count.ToString());            if (count > 0) {                return true;            }            else {                return false;            }        }        catch (SqlException ex) {            throw ex;        }        finally {            connection.Close();        }    }}​

And then finally in the login form when the user presses login (The message box is just a placeholder for now):

private void btnLogin_Click(object sender, EventArgs e) {
        if (LoginDB.IsUser(txtUsername.Text, txtPassword.Text)) {
            MessageBox.Show("Logged in!");
        }
    }

 
private void btnLogin_Click(object sender, EventArgs e) {        if (LoginDB.IsUser(txtUsername.Text, txtPassword.Text)) {            MessageBox.Show("Logged in!");        }    }​

Obviously this is just for a college project so don’t say anything about how this is clearly an unsafe way to log users into a system.

Answer

ExecuteNonQuery is not what you want to execute. It is a query. You should use ExecuteReader which returns a SqlDataReader and then you can read how many rows are returned. Or ExecuteScalar will return only one column of one row.

Advertisement

Answer