ExecuteReader.HasRows vs ExecuteScalar() is DBNull

Question

In an area of my site, I need to control access to a specific set of users.

This is done by checking the user's ID against a table on a SQL server database. If the ID exists then they are granted access:

SELECT 1 FROM admin WHERE userID = @userID

I notice that there are a couple of ways in which I can check for the presence of a row within the database and was wondering if there was any benefit of using either one, or if there was a standard.

The first is by checking for the existence of rows in the SqlDataReader:

if (!SqlCommand.ExecuteReader().HasRows)
{ 
    //redirect 
}

The second is to check if the returned value is DBNull using ExecuteScalar():

if (SqlCommand.ExecuteScalar() is DBNull)
{ 
    //redirect 
}

Which should I use? Is there a better way? Does it really matter?

I think the later is preferable, as you only need `true` or `false`. The former will create a bunch of objects (IE DataReader) that you won't exploit, so why adding this structure overhead? — Steve B, Nov 23 '12 at 10:39
Another which might be more readable: `SELECT CASE WHEN EXISTS(SELECT 1 FROM admin WHERE userID=@userID) THEN 1 ELSE 0 END AS IsAdmin`. Then you can use: `bool isAdmin = reader.GetBoolean(0);`. — Tim Schmelter, Nov 23 '12 at 10:47

score 4 · Accepted Answer · answered Nov 23 '12 at 10:43

The second option because you have less overhead.
However please note

ExecuteScalar returns an object that is

The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty

Your query may not return anything, so it is better to check for null instead of DBNull

if (SqlCommand.ExecuteScalar() == null)
{ 
    //redirect 
}

score 2 · Answer 2 · answered Nov 23 '12 at 11:06

Both are same in terms of the performance.

ExecuteScalar only returns the first value from the first row of the dataset. Internal it is treated just like ExecuteReader(), a DataReader is opened, the value is picked and the DataReader gets destroyed afterwards.I also always wondered about that behavior, but it has one advantage: It takes place within the Framework...and you can't compete with the Framework in manners of speed.

Following are the difference between those two:

ExecuteReader():

1.will work with Action and Non-Action Queries (Select)
2.Returns the collection of rows selected by the Query.
3.Return type is DataReader.
4.Return value is compulsory and should be assigned to an another object DataReader.

ExecuteScalar():

1.will work with Non-Action Queries that contain aggregate functions.
2.Return the first row and first column value of the query result.
3.Return type is object.
4.Return value is compulsory and should be assigned to a variable of required type.

taken from

http://nareshkamuni.blogspot.in/2012/05/what-is-difference-between.html

score 0 · Answer 3 · edited May 23 '17 at 11:48

in your case it will not affect much performance so either of them you can use .

ExecuteScalar is typically used when your query returns a single value. If it returns more, then the result is the first column of the first row. An example might be SELECT @@IDENTITY AS 'Identity'.
ExecuteReader is used for any result set with multiple rows/columns (e.g., SELECT col1, col2 from sometable).

SOURCE

ExecuteReader.HasRows vs ExecuteScalar() is DBNull

3 Answers3

Linked