Check if record in a table exist in a database through ExecuteNonQuery

Question

in my program i need to check if a record in the database already exists in the table using the if statement. using c# i am trying to do this through an sql connection. as i supposed that the ExecuteNonQuery(); command returns an integer value, if my supposing is true, i want to know what value is true to know that a certain record exists in the table or not. here's a sample of my code:

using (SqlConnection sqlConnection = dbUtil.GetSqlConnection(dbUtil.GetConnectionStringByName("NonConnectionString")))
{
    using (SqlCommand sqlCommand = new SqlCommand("SELECT * from users where user_name like 'Adam' AND password like '123456'", sqlConnection))
    {
        sqlresult = sqlCommand.ExecuteNonQuery();
    }
}

considering sqlresult has been initialized previously in the main as int sqlresult; so i would like to know, that if this user 'Adam' exists in the database or not. and if he exists, then i want to proceed with an 'if' statement saying for example:

if(sqlresult == 0)
{
   MessageBox.Show("Adam exists!");
}

so i just don't know the integer that it should return, and i am either not sure that this is the proper way to do it so.

thank you.

Answer 1

If you want to check if the user exists, you have to change your sql and use COUNT or EXISTS:

So instead of

SELECT * from users where user_name like 'Adam' AND password like '123456'

this

SELECT COUNT(*) from users where user_name like 'Adam' AND password like '123456'

Now you can use ExecuteScalar to retrieve the count of users with this username and password:

int userCount = (int) sqlCommand.ExecuteScalar();
if(userCount > 0)
    // user exists ....

Note that you should use sql-parameters to prevent sql-injection:

using (SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) from users where user_name like @username AND password like @password", sqlConnection))
{
    sqlConnection.Open();
    sqlCommand.Parameters.AddWithValue("@username", userName);
    sqlCommand.Parameters.AddWithValue("@password", passWord);
    int userCount = (int) sqlCommand.ExecuteScalar();
    ...
}

Answer 2

You should be using ExecuteScalar for cheking if the record exists. ExecuteNonQuery runs a transact-SQL statement against the connection and returns the number of rows affected for an UPDATE, INSERT, or DELETE. It doesn't apply for SELECT statements