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.

Tim Schmelter · Accepted Answer · 2014-11-11T18:36:47.093

51

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

edited Nov 11 '14 at 18:36

answered Jul 27 '13 at 22:36

Tim Schmelter

450,073
74
686
939

1

It's worth noting that sqlCommand.ExecuteScalar() returns an Object not an int: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx – TonE Nov 12 '13 at 13:56
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? – WhySoSerious Jan 05 '14 at 03:03
1

@WhySoSerious: thanks for noting, i've added it since it was missing. As @TonE has already mentioned it's required because `ExecuteScalar` returns `object`. – Tim Schmelter Jan 05 '14 at 16:57
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)? – thanatorr Jul 14 '17 at 10:36
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. – Tim Schmelter Jul 14 '17 at 10:38

score 9 · Answer 2 · answered Jul 27 '13 at 22:33

9

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

answered Jul 27 '13 at 22:33

Claudio Redi

67,454
15
130
155

score 5 · Answer 3 · answered Jan 25 '16 at 19:44

5

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

answered Jan 25 '16 at 19:44

Alireza Kousha

51
1
1

This is true. Some time ago i gained a significant performance-boost because i changed it. – Olli May 21 '19 at 13:07

score 3 · Answer 4 · answered Jul 27 '13 at 22:34

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.

score 0 · Answer 5 · answered Dec 12 '16 at 16:12

If someday you want to use EF just do:

private MyDb db = new MyDb();

public bool UserExists(string userName, string password){

   return db.Users.Any(x => x.user_name.Equals(userName, StringComparison.InvariantCultureIgnoreCase)
                         && x.password.Equals(password, StringComparison.InvariantCultureIgnoreCase));
}

Or do a generic method, so you can handle multiple entites:

public bool EntityExists<T>(Expression<Func<T, bool>> predicate) where T : class, new()
{
   return db.Set<T>().Any(predicate);
}

Usage:

EntityExists<Users>(x => x.user_name.Equals(userName, StringComparison.InvariantCultureIgnoreCase)
                      && x.password.Equals(password, StringComparison.InvariantCultureIgnoreCase));

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

5 Answers5

Linked

Related