Blog

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