Once upon a time, a smart bloke named Saikichi Toyoda came up with a 5 Whys troubleshooting technique. It postulates that you only need to ask “why” five times to get to the root cause of any problem in a cause-and-effect sequence.
However, with .NET Interoperability in Microsoft Dynamics NAV (pick your version here), I’ve only had to ask “why” three times today, and unfortunately I could not get to the root cause, except – poor design decisions.
It all has to do with a simple .NET class: System.DBNull. The first “why” is simple: why is there no support for fields on .NET types. The second “why” is even simpler: why don’t we have a null constant in C/AL. And the last “why” downright falls into the “what the heck” category: why did somebody think it was a smart idea to replace DBNull value with null.
I had to write a piece of C/AL.NET code that uses ADO.NET to write to a SQL Server database, and occasionally, this piece of code had to write a null value into a field. Of course, to write a null value into a field, you cannot just not assign a value, you must set the value to DBNull.Value. However, doing so from C/AL proved impossible to me (at least in three different attempts, using three completely different approaches, including a sure-fire approach that otherwise always works).
You could think that you can do something like this:
Well, in normal languages, it would be no problem. However, in C/AL you cannot do that, because Value is not a property of the DBNull type, it’s a field. Unfortunately, there is no support for fields in C/AL, and I never forget to mention this during my .NET classes. It’s an important, and a completely unreasonable limitation of the .NET interoperability in NAV.
My first thought was: “so what, I can use reflection.” Whenever a member of a type is not available directly, I use reflection to access it, and normally it works. Essentially, I needed to write the equivalent of this, only in C/AL:
C/AL is a bit less elegant here, but the following code should, in theory, do exactly the same thing:
However, after this executes, the value of Object is not an instance of the DBNull class, but an unexpected one: null. Object actually contains no instance, nothing, nada. Even though this:
… is equivalent to this:
… the former (C#) shows this:
… and the latter (C/AL) shows this:
My first frustration came again from the fact that in C/AL we have no graceful way of specifying the constant null, as in C#, but we must pass non-instantiated variables into the methods instead of null parameters. I attempted multiple things, such as providing null in different ways into the GetValue method (which is mandatory, since Value is a static member) in C/AL, but also providing a variable to which I assigned the null value in C#. In all situations, C# always ended up with an instance of System.DBNull as the result, and C/AL always failed with System.NullReferenceException.
At this point I thought there may be some unexpected glitch with reflection and static fields; maybe there was a reason why fields are unsupported, after all. No problem, I’ll try a different workaround.
That different workaround, theoretically, went like this: I’ll instantiate a DataTable, create a single column of whatever type to it, and then create a row with no data in it. When I read the value from the first row, and the single column, it should contain DBNull.Value.
To prove my theory, I wrote this in C#:
And again, I got this, as expected:
However, when I translated this to C/AL:
Again, I got the same error:
Again, after the assignment (which succeeded, by the way), the Object variable contained a null, not an instance of DBNull, as it does in C#.
Now I was really suspicious. From whatever I could see, there was something, some gizmo deep in the .NET Interoperability implementation in NAV, that intentionally changed DBNull instance into a null. I hoped I was wrong, and I wanted to prove myself wrong, so I wrote a .NET wrapper class in C#, which would return an instance of DBNull to the caller:
This must work. I am not doing fancy workarounds to get to the instance of the DBNull type, no reflection, no null assignment to a DataTable. I am returning the DBNull.Value itself. Cannot fail.
And then I called it from C/AL:
And guess what? This happened, again:
Now I was 100% convinced: for whatever inexplicable reason (and someone obviously wrote this intentionally, because at this point it was obvious it was not a bug, but an intentional feature) the NAV runtime intentionally replaces the instance value of a DBNull type with null. Just like that.
So, at this point, instead of writing plain C/AL code, I am forced to write a full wrapper which handles the whole ADO.NET thing inside of it, hidden away from reach of the NAV runtime. And I hate it, because it requires me to deploy extra, and utterly unnecessary DLLs to the production servers.
Why, oh why:
- Can’t we access fields of types?
- Can’t we specify null constant?
- Did Microsoft think it was a good thing to replace DBNull instancess with null at runtime?
If you have an answer to any of these, please don’t be ashamed to reply. I wasn’t ashamed of exposing my inability to get hold of an instance of a DBNull class.
(And yes, if you know how to get an instance of DBNull into a C/AL variable, please enlighten me, and the world, because at this moment my brain hurts.)
This Post Has 10 Comments
How about using powershell? no need to deploy it.
ara3n, what??? 🙂 How about what exactly about using powershell? Give me an example of how I can use powershell to assign an instance of System.DBNull to a variable (or property) where I need to, and I build you a golden real-life statue of powershell assigning a value to a .NET variable or property.
Pingback: ThreE Whys about .NET Interoperability – 10/7, Navigate Into Success |
at first: i can feel your pain, i had such moments myself, .net interop is all nice and well but the further you dig the more such barriers you discover… being unable to pass “null” in a elegant way is a MUST for example..
Any way, about powershell: You could invoke a powershell script (which does what you want) by .net interop i guess? hacky but should work…
And there is also the possiblity to create & compile a .net assembly at runtime trough .net interop (http://mibuso.com/forum/viewtopic.php?p=259208#p259208)
devch, thanks for the comment. Using powershell in this case is nothing else but blackboxing the whole thing into another external dependency. The beauty of the .Net interop is that integrates with C/AL at the execution and process level – your code is a part of the NAV process. Regarding the runtime compilation, I’ve already done a very similar thing: https://vjeko.com/blog/web-services-black-belt-consuming-nav-web-services-using-pure-cal
Still, all three of my whys remain valid points for Microsoft to consider.
Hmm, that’s a nice brainer. I guess your hands are tied to rewrite the origonal line, so that you don’t have to use SQLCommand.Parameters.Add(…) method? Maybe replace this with some good old plain text SQL within NAV?
Btw, what .NET type is is that variable?
Vytenis, my workaround will be to create two versions of the SQL command, one that simply ignores that parameter that needs to be set to null, and the other which assigns a value to it. It’s only one field that needs to be assigned null, so it’s a simple solution. By the way, the “good old plain text SQL within NAV” is much more error prone than using SQL commands, and in any case, it would be a complicated matter to use plain text to provide a value for a BLOB field, it’s much simpler with parameters. Which variable you are referring to?
I was referring to SQLCommand variable. I’ve replicated your code and error, except for that first line part.
It’s this one: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand%28v=vs.110%29.aspx
Will this still be a problem in 2016?