CODEWITHSUNDEEP
c#sql
Elliott
Elliott

Reputation: 2729

ExecuteReader.HasRows vs ExecuteScalar() is DBNull

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?

Upvotes: 1

Views: 2769

Answers (3)

Thangamani Palanisamy
Thangamani Palanisamy

Reputation: 5290

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

Upvotes: 2

Steve
Steve

Reputation: 216243

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 
}

Upvotes: 4

Pranav
Pranav

Reputation: 8871

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

Upvotes: 0

Related Questions