The Beauty and The Beast: NAV and .NET

imageIf there wasn’t one already, someone should have invented Belgium. There are two things in this world that I love, and probably shouldn’t (and an oversized red speaker’s shirt I got from Luc today did a darned god job at concealing the unlucky consequences of overly indulging in both of them): beer and chocolate. Boy, do Belgians know their beer and chocolate!

But they know their NAV, too, and after NAV TechDays 2011, which have just ended in Antwerp, and two days of top NAV content, I can only say – great job, Luc and the team, and please make it a tradition.

If you attended my presentation about .NET interoperability, then there are a couple of demos I couldn’t deliver due to time constraints, and I promised to blog it. So, here we go.

It’s about streams. You already know that in NAV there are two data types, InStream and OutStream, that allow you to stream data in and out of generic sources or destinations. They are a fantastic tool, because they require you to know nothing about the type of source or destination, and you can store and retrieve data without having to care if the data comes from Internet, or a BLOB field, or is it written to a file, or transported over an XMLport. Stream makes it abstract and allows you to simply handle the data, and make the object itself care about the specifics.

In .NET, streams a bit more complex, or comprehensive if you wish, than in C/AL, and there are specific types of streams which do not exist in C/AL. For example, System.IO.FileStream and System.IO.MemoryStream are two stream types which handle specifically file data, or in-memory data. Luckily, they both inherit from System.IO.Stream, and System.IO.Stream is fully and bi-directionally mappable to InStream and OutStream of C/AL.

If you don’t see a galaxy of opportunities here, then just follow this demo. Let’s make a totally useless, but cool and simple, demo with streams. Let’s stream data from a blog directly into NAV.

1. Create the table and UI in NAV

A chore first, fun later. To store any kind of data in NAV, you need a table, so let’s create one:

image

No triggers or anything funny. Just what you see above. Save it, and create a page of ListPart type:

image

For the last field, set the ExtendedDataType to URL. Just in case, make the page non editable.

Finally, design the page 9006 Order Processor Role Center and add page 50001 Blog Posts as a Part control of SubType Page to the top of the second Group. It’s simple, so no screenshots here. Life’s tough.

If everything was done correctly, this will be your role center:

image

Notice the Blog Posts in the top right. It’s empty, and at this point it’s pretty much by design. Patience, we are almost there.

2. Review the blog stream and create an XMLport

Navigate to http://NavigateIntoSuccess.com/feed/ and take a look at the source. Obviously, a blog’s feed is a simple XML file. So, a million dollar question (only I don’t have that million, yet): how do you import an XML into NAV?

You’ve got it right: the XMLport. A nice little gizmo which lets you model NAV data as XML and then stream it in and out. Apart from them being able to natively snakker XML, there is one particular feature that makes them extremely suitable for this demo. They handle streams, not files.

So, create this XMLport:

image

Must have taken all 56 seconds of your life, eh?

3. Read the feed

There is nothing simpler than this. Create a new Codeunit, save it as 50001 Blog Post Management.

Create a new function called ImportRss, have it receive a parameter called Url of type Text[1024]. Declare a new local variable of type DotNet, call it XRss, and set its subtype to ‘System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Xml.Linq.XDocument

The body of the function should look like this:

XRss := XRss.Load(Url);

That’s right. That thing will have your feed loaded. If you don’t believe, give it a try. Add the following line below:

MESSAGE(XRss.ToString);
To make it work, go back to your page 50001, add the global variable named BlogPostMgt of type Codeunit 50001, and in the OnOpenPage trigger write the following:
BlogPostMgt.ImportRss('http://navigateintosuccess.com/feed');
CurrPage.UPDATE(FALSE);

 

Yes, I know – I’ve violated my own hardcoding rules by hardcoding the URL of my blog – but that was on purpose. If I have you make setup for this, I bet you a glass of Duvel and a box of Godiva that you would put some other feed’s URL. As if you can’t change this one, but I know you won’t, will you?

Go. Restart your RTC and watch! I’d put a screenshot here, but it’d be very large and equally as useless.

But now there is a problem. The feed you have in front of you doesn’t exactly match the blueprint laid out by this XMLport, does it? There are plenty more elements in the feed, that NAV or database couldn’t care less about, so there are two choices here: either specify all that unneeded extra info right here in the XMLport (which will take like half an hour of your time, at best), or make a quick .NET function to strip that extra info away.

Since this post is about .NET, and not about stuffing up XMLports, you’ve got it again: we take the .NET path.

4. Create a .NET object to transform the feed

It’s far simpler than it sounds. Start Visual Studio, create a new C# project of type Class Library (or use the Microsoft Dynamics NAV .NET Interoperability Assembly template I give you, for free, at the end of the post in the Downloads section), call it XmlHelper, and create the following class:

using System;using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace XmlHelper
{
    public class XmlHelper
    {
        public static XDocument SimplifyRss(XDocument xrss)
        {
            return new XDocument(
                new XElement("rss",
                    new XElement("channel",
                        from x in xrss.Element("rss").
                            Element("channel").Elements("item")
                        select new XElement("item",
                            new XElement("link", x.Element("link").Value),
                            new XElement("title", x.Element("title").Value)
                        )
                    )
                )
            );
        }
    }
}

The core of it is this simple statement:

return new XDocument(
    new XElement("rss",
        new XElement("channel",
            from x in xrss.Element("rss").
                Element("channel").Elements("item")
            select new XElement("item",
                new XElement("link", x.Element("link").Value),
                new XElement("title", x.Element("title").Value)
            )
        )
    )
);

Since this is about .NET, and most of NAV folks aren’t too .NET savvy, let me explain how it works. It creates a new XDocument instance (a class which handles XML data in a nice and simple way), and makes it have the same structure as our feed:

<rss>
    <channel>
        <item>
            <link>...</link>
            <title>...</title>
        </item>
    </channel>
</rss>
It does so by creating a root element “rss”, to which it adds a child element “channel”, and here comes a hefty feature of .NET which is unavailable in C/AL: Linq. It allows you to query a collection and transform it in a single go, so it basically selects all elements of name “item” which are children of “channel”, which is a child of “rss”, and for each such element in that collection it creates a new element of name “item”, and adds two child elements to it: “link” and “title”. I could have done most of this in C/AL using interop and DotNet variables, but it would have required about two dozen variables and two pages of code.
Now compile the new assembly, and copy the resulting dll to your Add-ins subfolder of Client and Service folders of your NAV installation.
5. Complete the import
Now that the scaffolding is in place and we have all the ingredients, let’s complete the import. Back in your codeunit 50001, declare a new DotNet variable, name it XmlHelper, and select this subtype: ‘XmlHelper, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’.XmlHelper.XmlHelper. Since you have deployed it in Add-ins folder, it is now accessible under Dynamics NAV tab of your Assembly List window. Also, while there in locals list, create a copy of XRss variable, and name it XRssSimplified (it must be of the same subtype as XRss, so copying is the simplest way of achieving it).
Append and modify the code of ImportRss to look like this:
XRss := XRss.Load(Url);
XRssSimplified := XmlHelper.SimplifyRss(XRss);
MESSAGE(XRssSimplified.ToString);

 

Reload the role center, by either navigating elsewhere, then returning to it, or restart the RTC, and notice the change in displayed message – it now contains only the elements that our XMLport can cope with. We are almost done.
To feed this XML data to our XMLport, do the following: declare a new variable of type XMLport, name it ImportBlog and set its subtype to 50001; then declare a new DotNet variable, name it MemoryStream, and give it the following subtype: ‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.IO.MemoryStream; and finally a new DotNet variable named Encoding of subtype ‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Text.UTF32Encoding
Finally, remove the MESSAGE line of code, and append the following to the end of your ImportRss function:
ImportBlog.SETSOURCE(
  MemoryStream.MemoryStream(
    Encoding.UTF32Encoding.GetBytes(XRssSimplified.ToString)));
ImportBlog.IMPORT;
This is kind of a hacker’s approach, and slightly less readable, but blog friendlier. Otherwise, I would have had you declare a couple more variables, and you’d have to write a couple of more lines of code.
Basically, here we have constructed a memory stream from an array of bytes returned by an instance of the UTF32 encoder class. .NET allows you to do these things easily inline, so I’ve skipped declaring the byte[] (which would otherwise have been a DotNet variable of subtype System.Array, and instantiate the UTF32 encoder instance into the variable.
Having done that, there are no obstacles to assigning the result of the MemoryStream constructor (an instance of a System.IO.MemoryStream class) directly as a source of your XMLport and simply call IMPORT method. Remember, any System.IO.Stream instance or its descendant can be used in place of InStream and OutStream variables in C/AL, or directly assigned in both directions.
Now that the theory is done, restart your RTC, and savor the moment.
There is only one more thing you need to do to make sure it works every time: go back to your XMLport 50001, declare a variable of type Record 50001, name it BlogPost, and add the following line of code to your OnPreXMLport trigger:
BlogPost.DELETEALL;

 

There. You can now have your role center nicely adorned with the latest feed from my blog (or any other, if you want to cheat Smile), and stay up to date with, well, whatever it is to stay up to date, right from your role center.

And last, but not least, here is the ZIP file with all the demos.

I hope this little walkthrough helps you understand the concept of streaming using .NET interop better.

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 19 Comments

  1. Jules Winnfield

    Vjekoslav, thanks for your great session at NAVtechdays and also for this post.
    The zip-download seems to be incomplete.
    Can you please check the contents of the zip?

    Jules

  2. Vjekoslav Babic

    Jules: thanks for comments, and I’m glad you liked the session. I’ve just checked, and the ZIP file is complete. I’ve unpacked it, tested the content, and everything is okay. Can you retry downloading? Clear the cache, or try another browser. If it still fails, please let me know.

  3. Hans H. Fiddelke

    Hello,

    I hav a question on NAV and .NET- Interop.
    We tried to recreate the Excel-Buffer in NAV with .NET to check if it is faster than the automation. I tried the following C#- Code as example for my C/AL experiment:

    xlApp = new Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Open(“csharp.net-informations.xls”, 0, true, 5, “”, “”, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, “t”, false, false, 0, true, 1, 0);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    But when the appropriate C/AL- Code in the last line of the example is executed, i get the errror:
    “Type NAVAutmation is unknown”

    I think this is because “xlWorkBook.Worksheets.get_Item(1)” returns a “System.Object” not a “Excel.Worksheet” and NAV converts System.Object to NAVAutomation which is not assignable to Excel.Worksheet.

    Do you know a workaround for this bahavior?

    Thanks
    Hans H.

    1. Vjekoslav Babic

      Hans H.: you may be right about the reason and casting issue, but before I can give you the answer, I need to see the whole code, both in C# and C/AL. This above is only a fragment, and only in C#, and it may or may not be correct – and error may be on C# or C/AL level, and it’s impossible to tell exactly where it is from this little information you’ve given me. Your explicit casting will fail if object boxed in the result of get_Item contains something different than Excel.Worksheet. I don’t have my Visual Studio at hand, but what is expected as result of get_Item method? Is it Excel.Worksheet? Try rewriting the last line as:

      xlWorksheet = xlWorkBook.Worksheets.get_Item(1) as xlWorksheet;

      Then see if xlWorksheet is null after this line executes. If it is, then get_Item has not returned anything castable to Excel.Worksheet, which means the error is in .NET level, not C/AL level. If xlWorksheet is not null, then there is some casting error which happens after NAV receives the object – but then I need to see the C/AL code.

      Can you give me the whole context – your C/AL code that calls .NET, and your whole C# function being called from C/AL?

  4. Hans H. Fiddelke

    Hello Th C#- Example is from here:
    http://csharp.net-informations.com/excel/csharp-open-excel.htm

    The C/AL- Code is this:
    Variables:

    Name DataType Subtype Length
    Name DataType Subtype Length
    xlx DotNet ‘Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’.Microsoft.Office.Interop.Excel.ApplicationClass xl_App DotNet ‘Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’.Microsoft.Office.Interop.Excel.Application
    Missing DotNet ‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Reflection.Missing
    xl_WrkBk DotNet ‘Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’.Microsoft.Office.Interop.Excel.Workbook
    xl_WrkBks DotNet ‘Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’.Microsoft.Office.Interop.Excel.Workbooks
    Xl_WrkSht DotNet ‘Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’.Microsoft.Office.Interop.Excel.Worksheet
    Xl_WrkShts DotNet ‘Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’.Microsoft.Office.Interop.Excel.Worksheets

    xl_App := xlx.ApplicationClass();
    xl_App.Visible := FALSE;
    xl_WrkBk := xl_App.Workbooks.Open(ServerFileName, 2, TRUE, 5, ”, ”, TRUE, 2, ‘t’, FALSE, FALSE, Missing, TRUE,TRUE, 0);
    i := 1;
    EndOfLoop := xl_WrkBk.Worksheets.Count;
    Xl_WrkShts := xl_WrkBk.Worksheets;
    WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN
    xl_wkshv := xl_WrkBk.Worksheets.Item(i); <<<< here is the crash (it's the same as get_item)
    Xl_WrkSht := xl_wkshv;
    IF SheetName = Xl_WrkSht.Name THEN
    Found := TRUE;
    i := i + 1;
    END;

    1. Vjekoslav Babic

      This is just a wild guess from staring at the code you put there – and I may be totally wrong here – but is it because i starts at 1 and goes to Count, and it maybe has to start at 0 and go to Count – 1?

    2. Vjekoslav Babic

      I’ve played with this for a couple of minutes, and I believe there is nothing to gain with switching automation for .NET here. The reason is that Microsoft.Office.Interop.Excel library is not a native .NET library but a COM interop library. By using .NET interop to a COM interop object in .NET you are actually adding two extra layers (COM interop in .NET, and .NET interop in NAV) on top of the same automation object that you are trying to replace in the first place. I can’t see how possibly you can get any perfomance benefits here.
      On another hand – I believe there may be issues with how NAV interop handles COM interop objects in .NET, and it may be that the problem you are experiencing is related to the fact that COM interop objects have not been properly tested.
      I hope this helps.

  5. Hans H. Fiddelke

    Thanks for your Information.
    So i’ll switch back to COM in Excel-Buffer, and try another way getting my data faster in NAV 😉

  6. Dynamics NAV Enthusiast

    Chocolate, beer and NAV – I would say that’s a perfect combination for making a really good conference!

    @vjekob it’s not good to leave everything for the last night?
    @navision No, but I just have a habit of changing the demos in the last moment.

    Your presentation was superb!

    1. Vjekoslav Babic

      @Enthusiast: Thanks for comment, and for linking back to my posts. Why didn’t you come and meet me after the presentation? It would have been a pleasure to talk to you in person.

  7. Henrik Langbak

    Hi Vjekoslav
    I’ve just finished watching your great presentation on .NET Interop from NAVTechDays 2011 (recorded).

    Thank you very much for at well executed and presented session with lots of very useful demos.

    Henrik

Leave a Reply