Connect to Dynamics NAV with the CData OData Provider

This article will demonstrate how to set up the Dynamics NAV OData services and how to connect to these services using the CData ADO.NET Provider for OData. While this article uses the ADO.NET provider, you can also connect from other technologies, for example, ODBC or JDBC.

Note that some Dynamics NAV instances require secure authentication that is not available with the free Community Edition.

Set up the Dynamics NAV Web Services

Follow the steps below to enable the OData services in the Dynamics NAV administration tool.

1. In the Administrator tool expand the OData Services node and select the Enable OData Services box if it is not selected.

2. In Dynamics NAV, search for 'Web Services'. A list will be displayed of all the pages that are currently exposed through the OData service.
3. Click the New button to add a new service.
4. Select the Object Type of the data you want to retrieve.
5. Select the Object Id for the data you are looking for.

For example, to get a listing of Customers, you can choose to expose a page with an Object Id of 22. The Service Name will appear as the table name when you retrieve data from Dynamics NAV with the CData provider. You can then connect to Dynamics NAV via OData.

Connect to Dynamics NAV

The provider requires the following properties in order to connect to Dynamics NAV; this information can be found in the Dynamics NAV Administration tool.

• URL: The URL will be in the following form:

  http://<Server>:<WebServicePort>/<ServerInstance>/OData

• User: The username to authenticate the user to Dynamics NAV.
• Password: The password to authenticate the user to Dynamics NAV.
• AuthScheme: The authentication scheme will depend on how your instance of NAV is configured but you will most likely need to use either NTLM or NEGOTIATE authentication if you use Windows Authentication.

With these connection properties, you can connect to Dynamics NAV from Server Explorer. You can access the services you set up in the previous section.

For a guide to connecting from Server Explorer, see the "Getting Started" chapter in the help documentation. For more information on the available connection properties, see the "Connection String Options" chapter.

Connect to Different Companies

If you issue queries directly against the table, Dynamics NAV will only display data from the currently active company. You can use the DirectURL pseudo column to retrieve data from a company which is not currently active.

Set the DirectURL pseudo column to the full URL of an OData service. The DirectURL will override the value set in the URL connection property. In Include the Company parameter in the DirectURL. For example:

SELECT * FROM Customers WHERE DirectURL='http://<Server>:<WebServicePort>/<ServerInstance>/OData/Company(''CData'')/Customers'

Then it is just a matter of providing the path to the particular Web service you are interested in. You will need to SQL-encode the apostrophe in the URL: Enclose the company name with two single apostrophes. You will also need to URL-encode the value in the company parameter.