Three Whys about .NET Interoperability

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:

image

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:

image

C/AL is a bit less elegant here, but the following code should, in theory, do exactly the same thing:

image

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:

image

… is equivalent to this:

image

… the former (C#) shows this:

image

… and the latter (C/AL) shows this:

image

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#:

image

And again, I got this, as expected:

image

However, when I translated this to C/AL:

image

Again, I got the same error:

image

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:

image

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:

image

And guess what? This happened, again:

image

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

Vjeko

Vjeko has been writing code for living since 1995, and he has shared his knowledge and experience in presentations, articles, blogs, and elsewhere since 2002. Hopelessly curious, passionate about technology, avid language learner no matter human or computer.

This Post Has 10 Comments

  1. dynamicnav

    How about using powershell? no need to deploy it.

    1. Vjeko

      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.

  2. devch

    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)

    1. Vjeko

      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.

  3. Vytenis Jakas

    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?

    1. Vjeko

      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?

      1. Vytenis Jakas

        I was referring to SQLCommand variable. I’ve replicated your code and error, except for that first line part.

  4. Capone

    Will this still be a problem in 2016?

Leave a Reply to CaponeCancel reply