Int32.TryParse() or (int?)command.ExecuteScalar()

Question

I have a SQL query which returns only one field - an ID of type INT.

And I have to use it as integer in C# code.

Which way is faster and uses less memory?

int id;
if(Int32.TryParse(command.ExecuteScalar().ToString(), out id))
{
  // use id
}

or

int? id = (int?)command.ExecuteScalar();
if(id.HasValue)
{
  // use id.Value
}

or

int? id = command.ExecuteScalar() as int?;
if(id.HasValue)
{
  // use id.Value
}

I know this is old, but relevant Donald Knuth quote: "Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%." — Slothario, Jan 16 '17 at 21:58
Hey @DannyNeumann, in general, I don't think it makes much sense to post it here since it a question from 2009. In particular, writing efficient code is still important. And this is how I learned to do so. I everyday see horrible, horrible code that would suit Knuth's quote perfectly well. That being said, one should not spend much time on premature optimization of non critical parts, but in the same time one should not write non critical parts inefficiently. — abatishchev, Jan 17 '17 at 18:33

Sam Saffron · Accepted Answer · 2009-07-23T23:13:36.487

The difference between the three performance wise is negligible. The bottleneck is moving the data from the DB to your app, not a trivial cast or method call.

I would go with:

int? id = (int?)command.ExecuteScalar();
if(id.HasValue)
{
  // use id.Value
}

It fails earlier, if one day people change the command to return a string or a date, at least it will crash and you will have a chance to fix it.

I would also just go with a simple int cast IF I always expected the command to return a single result.

Note, I usually prefer returning an out param than doing the execute scalar, execute scalar feels fragile (the convention that the first column in the first row is a return value does not sit right for me).

ExecuteScalar() is a huge win in vb, where you can just do a CInt() or CStr() on it. — Joel Coehoorn, Jul 23 '09 at 22:59

score 19 · Answer 2 · answered Jul 23 '09 at 23:29

19

If you expect the command to return null, you should keep in mind that database null (DBNull) is not the same as .NET null. So, conversion of DBNull to int? would fail.

I'd suggest the following:

object result = command.ExecuteScalar();
int? id = (int?)(!Convert.IsDBNull(result) ? result : null);

answered Jul 23 '09 at 23:29

VladV

10,093
3
32
48

2

Keep in mind that if execute scalar returns no rows, you will get a null. You are correct that if the first row in the first column is null you could be in trouble. – Sam Saffron Jul 23 '09 at 23:33
1

but then again it's and ID (I at least read that as identity/key) so DBNull is not an issue – Rune FS Jul 24 '09 at 06:07
@Sam Saffron, agreed on empty rowset. @Rune FS, we don't know the underlying logic here. I wouldn't assume whether a query might return null or not, based only on a variable name. – VladV Jul 24 '09 at 06:19
@VladV agreed you should second guess on variable names but I was referring to the fact that it's an ID being returned (says so in the question) and since ID is short of identity and identity in (MS)DB teminology are unique non null values the question is either ambigious or the query will not return DBNulls :) – Rune FS Jul 25 '09 at 18:56
Could you reduce that to `int? id = (int?)command.ExecuteScalar() ?? null;` ? – Zorgarath Oct 25 '16 at 18:31

score 5 · Answer 3 · edited Jul 15 '12 at 14:54

5

If none of the above works (especially for users who are battling with MySQL) why don't you try the following?

int id = Convert.ToInt32(cmd.ExecuteScalar().ToString());

edited Jul 15 '12 at 14:54

Kijewski

25,517
12
101
143

answered Jul 15 '12 at 14:11

hungrycoder

507
2
9
24

score 3 · Answer 4 · edited Jul 12 '17 at 10:55

3

int Result = int.Parse(Command.ExecuteScalar().ToString());

will work in C#.

edited Jul 12 '17 at 10:55

Soner Gönül

97,193
102
206
364

answered Sep 04 '12 at 16:44

Sin

1,836
2
17
24

2

It's a bad approach first dump to string then to parse when object it's already an int, it just requires to be casted. – abatishchev Sep 04 '12 at 18:27

score 2 · Answer 5 · answered Jul 23 '09 at 22:52

2

The latter. Convert.ToInt32() is also an option.

answered Jul 23 '09 at 22:52

Joel Coehoorn

399,467
113
570
794

1

The first method is from Satan for many reasons. It's slower AND less readable. Yuck. – Dave Markle Jul 23 '09 at 22:55
Convert.ToInt32() worked for me whereas a cast would not, with command.ExecuteScalar(); – JYelton Apr 21 '10 at 22:13

score 1 · Answer 6 · answered Jul 23 '09 at 22:59

1

Use id.HasValue for maximum Nullable Type cool-factor!

answered Jul 23 '09 at 22:59

xyz

27,223
29
105
125

score -2 · Answer 7 · edited Aug 16 '14 at 22:27

-2

if ((Int32)cmd.ExecuteScalar () ** 1) //en esta parece qu esta el error pero no lo veo
{
    Response.Redirect("Default.aspx");
}
else
{
    Response.Redirect("error.htm") ;
}

edited Aug 16 '14 at 22:27

kmatyaszek

19,016
9
60
65

answered Aug 16 '14 at 05:52

legc

1

1

Is this an answer? Or are you trying to get help? Because that Spanish comment means *'Looks like the error is here but I can't see it'* – brasofilo Aug 16 '14 at 08:10

Int32.TryParse() or (int?)command.ExecuteScalar()

7 Answers7

Linked