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

Solution 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();
    ...
}

Solution 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

Solution 3:

I would use Select Top 1 Id rather than the count(*) because it can be much faster

Solution 4:

You should do a count(1) on the table instead of a select * and then executescalar to get that integer value.

Using your existing code I would change it to be:

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

Please note that I have used equals values instead of like values.

Also if I were do to this I would change your inline sql to use a stored procedure.