Int32.TryParse() or (int?)command.ExecuteScalar()
Asked Answered
M

7

17

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
}
Megawatt answered 23/7, 2009 at 22:50 Comment(2)
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%."Pallid
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.Megawatt
M
23

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).

Mientao answered 23/7, 2009 at 22:57 Comment(1)
ExecuteScalar() is a huge win in vb, where you can just do a CInt() or CStr() on it.Wellbred
A
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);
Akanke answered 23/7, 2009 at 23:29 Comment(5)
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.Mientao
but then again it's and ID (I at least read that as identity/key) so DBNull is not an issueReconstructionism
@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.Akanke
@Akanke 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 :)Reconstructionism
Could you reduce that to int? id = (int?)command.ExecuteScalar() ?? null; ?Younker
B
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());
Bunnell answered 15/7, 2012 at 14:11 Comment(0)
R
3
int Result = int.Parse(Command.ExecuteScalar().ToString());

will work in C#.

Ria answered 4/9, 2012 at 16:44 Comment(1)
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.Megawatt
W
2

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

Wellbred answered 23/7, 2009 at 22:52 Comment(2)
The first method is from Satan for many reasons. It's slower AND less readable. Yuck.Fatuous
Convert.ToInt32() worked for me whereas a cast would not, with command.ExecuteScalar();Slily
E
1

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

Eucalyptus answered 23/7, 2009 at 22:59 Comment(0)
S
-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") ;
}
Soerabaja answered 16/8, 2014 at 5:52 Comment(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'Streusel

© 2022 - 2024 — McMap. All rights reserved.