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.

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.

View more solutions

Comments

[TonE]

It's worth noting that sqlCommand.ExecuteScalar() returns an Object not an int: msdn.microsoft.com/en-us/library/…

[WhySoSerious]

thanks for this great example, I have a question, from your 'using' block code, is the cast object to int missing from the userCount variable? or it isn't needed?

[Tim Schmelter]

@WhySoSerious: thanks for noting, i've added it since it was missing. As @TonE has already mentioned it's required because ExecuteScalar returns object.

[thanatorr]

im wondering would this had a drain on performance? i.e does the count return the query results for select * from ... or just an int? Meaning you would have to check for the int value, then execute a query to return that result(s)?

[Tim Schmelter]

Count just returns an int. But if you want the most efficient version use EXISTS and return a bit. It'll be more efficient because it can return the bit right after it found the first matching record while the COUNT has to evaluate all records to determine the count.

[Olli]

This is true. Some time ago i gained a significant performance-boost because i changed it.