In Microsoft Dynamics NAV, there are a wide range of methods to interface. Each method is useful for certain types of interfacing and less useful for other types. We will discuss all available methods in the C/SIDE development platform.
Flat files and XML files are both supported by Microsoft Dynamics NAV. Flat files have been available since the introduction of the product in 1995 using data ports for the classic clients.
XML support was introduced in Version 3.60 as an extra option for data ports. Version 4.0 introduced the XMLPort object that replaced the data port for importing and exporting XML files.
Currently in Microsoft Dynamics NAV 2013, XMLPort objects are used both for XML and flat files. Additionally, C/AL has a FILE object that can be used to access files directly without using XMLPort objects.
The implementation for Microsoft COM and ActiveX in Microsoft Dynamics NAV is referred to as automation control.
Automation control or ActiveX allows software applications to be reused as an embedded part of another application. Most Microsoft applications support being used in such a way. Examples are Microsoft Office, Windows Scripting Host, and ActiveX Data Objects (ADO).
Microsoft Dynamics NAV has support for automation control. Consuming automation control is done using interfaces exposing methods and properties.
The most commonly used and generic interface is iUnknown. This is also the only automation control interface supported by Microsoft Dynamics NAV. If the automation control uses other interfaces, a wrapper should be created in Visual Studio transforming the interface to iUnknown. We should also create a wrapper when the automation control needs to be embedded using a form control.
Note
More information about the iUnknown interface and COM technology can be found at http://en.wikipedia.org/wiki/IUnknown.
The support of .NET was introduced as a replacement for automation control. It is possible to use a wide range of .NET objects directly in C/AL programming language. They can be used in both server side and client side.
Within the standard application, most automation interfaces are replaced with .NET interfaces such as the Excel interface, which we will discuss later in this chapter.
There are limitations using .NET in Microsoft Dynamics NAV, which are typically solved by creating wrapper DLL objects in C#. The Excel interface is an example of that too.
Note
A good place to start learning about .NET in C/AL is www.vjeko.com. The limitations are discussed at http://vjeko.com/blog/top-10-things-i-miss-in-net-interoperability-in-nav-2013.
Using the page objects, the level of allowed creativity in the user interface is very limited since the page objects do not provide WYSIWYG capabilities or allow the developer to determine control positions. Each client determines how the UI is rendered and the developer has no control. This is solved using client extensibility. This technology allows using all UI capabilities that Visual Studio and .NET offer, however, when developing for cross-platform, JavaScript should be used.
Note
Refer to https://www.youtube.com/watch?v=WErBd1mlZFM to learn how to get started with JavaScript add-ins for Microsoft Dynamics NAV.
Open Database Connectivity (ODBC) was developed in 1992 with the goal of allowing all types of databases to exchange data in a unified way. ADO is the successor of ODBC and was developed in 1996.
ADO and ODBC for Microsoft Dynamics NAV allows both reading and writing in the application database as well as reading and writing to other databases.
Note
Using ADO and ODBC more advanced requires basic knowledge of T-SQL Statements. Refer to http://www.differencebetween.com/difference-between-odbc-and-vs-ado/ for the differences between ADO and ODBC.
To read data from the database, you only need to have a valid ODBC driver installed on the Windows machine that you are using and credentials to log in to the database.
Let's create an example to import data from Microsoft Dynamics NAV using Excel.
- Open Microsoft Excel and select Data and then select From SQL Server form From Other Sources, as shown in the following screenshot:
From SQL Server
- Select a Server name and valid Credentials:
- Select a database and the table that you want to view. In our example, we will select the Customer table. Then select Finish and OK.
- We now have the Microsoft Dynamics NAV Data in Excel.
Directly writing data to the Microsoft Dynamics NAV database using ODBC is not recommended as best practice. The reason for this is the missing business logic at this interface level.
When writing via ODBC, we directly address SQL Server without allowing the C/AL business logic to validate the data we create. The C/AL data normally ensures data integrity for the business rules we develop. The same applies when using the C/ODBC driver for the native database.
To use ODBC to read and write data from Microsoft Dynamics NAV to other databases, it is recommended to use ActiveX Data Objects (ADO). ADO is a Microsoft technology that allows using an ActiveX interface to connect using ODBC. Using ADO allows us to both read and write to the database on the other end.
We could even use ADO to connect to the Microsoft Dynamics NAV SQL Server database and run SQL Statements from C/AL code.
Since Microsoft Dynamics NAV runs on top of a SQL Server database, we can use all available technologies in SQL Server to get data in and out. This offers a wide range of options that go beyond the scope of this book, but let's briefly discuss some of them:
- Linked Servers: In SQL Server, it is possible to set up linked servers. This allows us to send queries to other databases such as other SQL Servers, MS Access, or Oracle and create views based on this data.
- Views: A view in SQL Server is a saved query with a fixed result set that can be interpreted as a table. In C/Side, we can use a view as a data source for a table using the Linked Object property and create a page or report based on this data source.
- SQL Server Integration Services: This replaces DTS Packages as the primary component for SQL Server to integrate with other databases. Using SSIS requires good knowledge and skills of both SQL Server and Microsoft Dynamics NAV.
- Reporting Services: This is a server-based reporting platform that can be integrated with SharePoint allowing users to design RDL reports based on T-SQL queries.
- Analysis Services: This is Microsoft's answer to the OLAP, BI, and data mining requirements of their customers.
Microsoft Message Queue (MSMQ) allows applications to integrate that run asynchronously with an unreliable connection. This interfacing technology is very popular for websites that use information from Microsoft Dynamics NAV and send information back to the database.
The introduction of .NET Interoperability made using MSMQ in combination with Microsoft Dynamics NAV much easier. Using System.Messaging.MessageQueue only a few lines of C/AL code are required to post a message on a queue.
MSMQ is always combined with using an application server to handle the requests sent back by the website.
The web users can be employees from the company using a web solution for timesheet registration or a PDA or customers using a web shop.
Note
This blog entry at http://mibuso.com/blogs/ara3n/2011/01/10/using-ado-on-rtc-in-nav/ explains how to get started with MSMQ using .NET.
When it comes to real-time interfacing, web services is the first technology of choice. Web services allows you to use function libraries from applications inside other applications.
Microsoft Dynamics NAV 2013 allows you to expose all C/AL code as a web service using SOAP and OData protocols.
Consuming web services is a lot more difficult than exposing one. There is no standard framework of doing so. The two most commonly used solutions are consuming using XMLDOM .NET interop objects or wrapping the web service inside a Visual Studio .dll using service references.
In Microsoft Dynamics NAV 2013, every Page object and most codeunits can be exposed as a web service. This can be done using the Web Service Table (2000000076).
To publish a web service, select the object type and object ID and find a unique service name. Then select the Published checkmark.
When publishing a web service, the URL is displayed making it easier to find it.
