The journal of Paul M. Watson.

Monday, April 10, 2006

ExecuteScalar and null

Can anyone explain why this doesn't work:

command.CommandText = "SELECT [Token] FROM Clients WHERE 

[Token] ='" + client1.Token + "'";
Guid result = new Guid(command.ExecuteScalar() as string);

While this does work:

command.CommandText = "SELECT [Token] FROM Clients WHERE 

[Token] ='" + client1.Token + "'";
Guid result = new Guid(command.ExecuteScalar().ToString());

The first snippet returns null. The second returns the requested Guid. The only difference is as string and .ToString(). This seems to only happen on uniqueidentifier columns in .NET 2.0.

(And don't wonder why the code doesn't seem to have a purpose, it is a bit from a larger a unit test.)

Tags: , , , , ,


Anonymous Nish said...

When you use "as", it uses the isinst IL instruction to check if it is indeed a string, and if so will copy it, else it won't.

When you use ToString, it will call ToString on whatever object it is that you are calling it on.

So getting different behavior is not surprising.

11:01 PM

Blogger Paul Watson said...

Thanks for the explanation Nish. I do have to disagree though and say that "as string" should return exactly what ".ToString()" does. The concept is identical. Convert X to string. If Microsoft have implemented it two different ways then that is a mistake.

10:38 AM

Anonymous Derek Lakin said...

So are you saying that "as string" should behaviour in a special way because of "ToString" rather than the same way that all other "as" operations behave Paul?

I tend to disagree. Though it might be "nice" for "as string" to work like "ToString" I'm much happier with consistent behaviour.

1:35 PM

Blogger Paul Watson said...

"as string" returns a null when "ToString" returns the value from the database. To me that is a broken implementation.

How can "as string" returning a null be considered consistent behaviour? It is loosing data.

2:02 PM

Anonymous Derek Lakin said...

As Nish pointed out the "as" operation tests to see if the object being tested is an instance of the class you specify and if it isn't then it returns null.

So in your case the Guid isn't a string so it returns null. The reason I say this is consistent behaviour is that it behaves the same for any object, not just string. If you try to use the "as" operator on any type that isn't the type that you ask for then it will return null.

9:07 AM

Blogger Paul Watson said...

It isn't a Guid. The Value of a SqlParam is a basic Object. I doubt the as operator specifically inspects SqlParams to check what DbType they are.

"as int" and "as string" work fine even when the SqlParam.Value is holding a bigint or a bit or a char. It just doesn't work for uniqueIdentifier which says to me there is a problem in there somewhere.

10:33 AM

Anonymous Derek Lakin said...

Ah OK. I see what you're saying now. I was forgetting what the original scenario was.

Although the SqlParam.Value is an Object, for a uniqueidentifier I suspect that it is actually a Guid, hence the behaviour for the as operator. That doesn't explain why "as string" works for the other types you mention, though :S

Something's not quite right somewhere along the line. Personally I'd prefer the same behaviour as uniqueidentifier for ints, etc. As long as it's consistent it doesn't really matter, but it seems that it's not :(

11:39 AM


Post a Comment

Links to this post:

Create a Link

<< Home