What is the most efficient way to check an ExecuteScalar result for existence?

Question

I'm seeing this code:

object objvalid = cmd.ExecuteScalar();
//made it this far - must not have thrown an exception
retVal = true;

...but am thinking one of these might be better:

object objvalid = cmd.ExecuteScalar();
retVal = (null != objvalid);

...

Int32 anybodyThere = (Int32) cmd.ExecuteScalar();
retVal = anybodyThere > 0;

Define `check ExecuteScalar for existence` first. What sql are you using, which value are you selecting? What do you want to check? It it returns a numeric value (e.g. from `COUNT`) and you want to know if it's greater than zero, just check that. Then the last approach is fine. If it can return `null` the second approach is more appropriate. — Tim Schmelter, Mar 12 '13 at 23:13
What I meant was, does it return a value? This code is from the "IsValidField()" method, which has this SQL: string.Format("SELECT {0} FROM {1}", fieldName, tableName); — B. Clay Shannon, Mar 12 '13 at 23:14
@ClayShannon So do you want to efficiently check if a column exists on a table? — p.s.w.g, Mar 12 '13 at 23:21
@p.s.w.g: Yes, the method simply determines whether the column exists. — B. Clay Shannon, Mar 12 '13 at 23:24
@ClayShannon Okay, then strongly I recommend against using this approach. See my updated answer for some alternatives. — p.s.w.g, Mar 12 '13 at 23:29
Great use of example code, but could you explain in words what you are trying to accomplish? Future questioners might be able to use this question to solve their problems that way. — Derek W, Mar 12 '13 at 23:32
@Derek: The code is supposed to simply answer the question: Is column X in Table Y? — B. Clay Shannon, Mar 12 '13 at 23:49

p.s.w.g · Accepted Answer · 2013-03-12T23:27:11.073

1

I think you answered your own question. You can't get much better than this

object objvalid = cmd.ExecuteScalar();
retVal = (null != objvalid);

However, from your comment, it seems like what you really want is to know if a column name exists on a table. For this purpose I suggest you look into using DbDataAdapter.FillSchema or DbConnection.GetSchema instead. Both options allow you to execute a single query against the database for all columns rather than re-querying the database for every column.

edited Mar 12 '13 at 23:27

answered Mar 12 '13 at 23:16

p.s.w.g

136,020
27
262
299

This method is called infrequently - just to see if the user's copy of the database has a newly-added column once in blue moon (it's not something that gets called over and over). – B. Clay Shannon Mar 12 '13 at 23:48
@ClayShannon Fair enough. :) – p.s.w.g Mar 12 '13 at 23:54

score 1 · Answer 2 · edited May 23 '17 at 12:28

First, your approaches do not make any (measurable) difference in terms of efficiency, it's just irrelevant in this case.

What do you want to check?

If it can return null and you want to know that, check that. Then your second aproach is the best.
If the query returns a numeric value (e.g. from COUNT) and you want to know if it's greater than zero, just check that. Then the last approach is fine.

However, you first approach is not good practise, don't rely on exceptions in your normal flow of control.

What is the most efficient way to check an ExecuteScalar result for existence?

2 Answers2

Linked