AAAAAaaaaah. I just got bitten and it hurts… This is one of those small things that just continues to endear me to VB development…
It's now turned on in the project I was working on.
"MS showed a stunning lack of confidence in their decision to incorporate strict type checking into VB.NET by leaving Option Strict off by default" - A fitting quote from a chap called Dan Appleman found here.
Saturday 18 October 2008
Wednesday 15 October 2008
Type of identity returned from SQL Stored Proc
Consider a SQL 2005 Stored Proc which is invoked from C# .net code via a SqlCommand, the stored proc inserts a row and returns the new identity, which is typed as bigint with a statement similar to:
SELECT Scope_Identity()
In the C# code the identity can be read from a call to the SqlCommand ExecuteScalar() method assume the identity in this example is a SQL bigint (which maps to a long / Int64 in dot net).
long myNewId = (long)cmd.ExecuteScalar();
// vb equiv for the cast to long is DirectCast()
(If invoked in the immediate window you’d see : “Cannot unbox 'obj' as a 'long'” )
What's going on?
The problem seems to be that whatever the type of your identity the object returned by ExecuteScalar() is in fact a Decimal, changing the code slightly to:
Object obj = cmd.ExecuteScalar();
Followed by this in debug, immediate window
? obj is System.Decimal
... Will yield "true", so the call is returning a "boxed" decimal, not a long at all… A quick look in the SQL2005-docs online and the return type is simply listed as "numeric".
After some fishing on Google, the consensus across a few posts (one example) seems to be that MS chose to use the largest type possible for the value returned by Scope_Identity() and @@IDENTITY because an identity column could be of various types, smallint, int, bigint, decimal and it made sense to make the return value the largest.
The problem - at least for the unwary developer - is that casting in the usual way (long)cmd.ExecuteScalar() won’t work, as this cast works in a similar way to the VB DirectCast() which doesn’t perform a check for fundamental types. One could argue you should always check the type before trying to unbox.
Note: VB CType() would allow unboxing of any fundamental type, to any other fundamental type, System.Convert() would also work in C#. See this good article - with examples in VB - for more info on boxing.
In the end I worked around this by defining a variable of the correct type in the stored proc and assigning the identity value to that and passing the variable back in the stored proc "select" statement.
SELECT Scope_Identity()
In the C# code the identity can be read from a call to the SqlCommand ExecuteScalar() method assume the identity in this example is a SQL bigint (which maps to a long / Int64 in dot net).
long myNewId = (long)cmd.ExecuteScalar();
// vb equiv for the cast to long is DirectCast()
(If invoked in the immediate window you’d see : “Cannot unbox 'obj' as a 'long'” )
What's going on?
The problem seems to be that whatever the type of your identity the object returned by ExecuteScalar() is in fact a Decimal, changing the code slightly to:
Object obj = cmd.ExecuteScalar();
Followed by this in debug, immediate window
? obj is System.Decimal
... Will yield "true", so the call is returning a "boxed" decimal, not a long at all… A quick look in the SQL2005-docs online and the return type is simply listed as "numeric".
After some fishing on Google, the consensus across a few posts (one example) seems to be that MS chose to use the largest type possible for the value returned by Scope_Identity() and @@IDENTITY because an identity column could be of various types, smallint, int, bigint, decimal and it made sense to make the return value the largest.
The problem - at least for the unwary developer - is that casting in the usual way (long)cmd.ExecuteScalar() won’t work, as this cast works in a similar way to the VB DirectCast() which doesn’t perform a check for fundamental types. One could argue you should always check the type before trying to unbox.
Note: VB CType() would allow unboxing of any fundamental type, to any other fundamental type, System.Convert() would also work in C#. See this good article - with examples in VB - for more info on boxing.
In the end I worked around this by defining a variable of the correct type in the stored proc and assigning the identity value to that and passing the variable back in the stored proc "select" statement.
Tuesday 14 October 2008
WMI Code Generator
Whilst writing some unit tests to exercise a set of BizTalk orchestrations, I found I needed to "stop" and "unenlist" one of the orchestrations being tested (and of course re-start after the test had finished).
The obvious choice would be WMI and MS ship some great samples in the SDK, however they're all in VBS.
I didn't want to "call" VBS files, I really wanted to wrap the WMI in a function to call from my unit test, but my unit tests are written in C#.
I did some Googling to find a quick translation for VB GetObject() to C#... There's no direct C# translation but looking at some of the posts I'm not the only one who asks this question...
However I felt that a lot of the posts missed the point, poeple were trying to use the Marshall namespace/class to reproduce GetObject() behaviour ..
Marshal.GetActiveObject() and Marshal.BindToMoniker()
Anyhow, to cut a long story short I stumbled upon a very, very nice utility from MS WMICodeGenerator (must be the last person in a long line to have done so), that generates WMI code for you in the language of your choice...
Actually it's better than that, it finds all the WMI assemblies, and when you select a namespace/class and method it'll find the relevant assemblies you have to act upon and then output the complete code snippet for you to use... Brilliant!
The obvious choice would be WMI and MS ship some great samples in the SDK, however they're all in VBS.
I didn't want to "call" VBS files, I really wanted to wrap the WMI in a function to call from my unit test, but my unit tests are written in C#.
I did some Googling to find a quick translation for VB GetObject() to C#... There's no direct C# translation but looking at some of the posts I'm not the only one who asks this question...
However I felt that a lot of the posts missed the point, poeple were trying to use the Marshall namespace/class to reproduce GetObject() behaviour ..
Marshal.GetActiveObject() and Marshal.BindToMoniker()
Anyhow, to cut a long story short I stumbled upon a very, very nice utility from MS WMICodeGenerator (must be the last person in a long line to have done so), that generates WMI code for you in the language of your choice...
Actually it's better than that, it finds all the WMI assemblies, and when you select a namespace/class and method it'll find the relevant assemblies you have to act upon and then output the complete code snippet for you to use... Brilliant!
Subscribe to:
Posts (Atom)