Sunday, October 13, 2013

Dynamics NAV Main Page

Consuming Dynamics NAV 2013 OData by Visual Studio 2012 Light Switch HTML Client.

Part 1, Integrating Visual Studio 2012 LightSwitch and Dynamics NAV Odata

Part 2, step by step developing Navision objects

Part 3, step by step developing Visual Studio 2012 LightSwitch application

SQL CLR and Dynamics NAV web services walkthrough

Dynamics NAV Report Last Page Footer walkthrough

SQL CLR and Dynamics NAV web services walkthrough

This blog is about how to access Dynamics NAV data from SQL Server using user defined function (SQL CLR), with this approach SQL developer will be able to use NAV with no deep knowledge about NAV business logic and data structure.

Accessing NAV data directly from SQL server has been there long time ago by writing T-SQL code to access NAV tables with advantage of better retrieving performance compared to the one presented in this blog, yet has 2 issues to consider: first it's really not recommended to update NAV tables directly from SQL cause you will be taking out NAV business code from the update so you are almost guaranteed to break the application, Second you still can read data from tables (NAV SQL Database) but not all fields return right values for example Balance field in Customer table is a Flow Field which means you don't have the value directly stored in the table customer.

Using SQL CLR user defined function provides high abstraction to NAV data and business logic which means SQL server developer does not need to have deep understanding on Dynamics NAV structure as stated earlier.

Below part is repeated in step 9 below, but because I always prefer to display (if possible) of the results before digging in implementation details:

We will create NavCustomers user defined function and call it from Select statement to get list of customers.

Now let's start:

1- Create List Page in Dynamics NAV

First we will start with Dynamics NAV, to create Customers List web service, create a list page from Customer table, save it with name as wsCustomer and 50010 as an ID (or any another available object ID in your database)


2- Add to Web Services List in NAV

Make sure you check Published check box after you enter Customer page. To access NAV web services follow the breadcomb shown below.

3- Start by building class library

Now will work with Visual Studio 2012, will start by creating Class Library Project.

4- Add Static Methods

Add a new class (NavUDFunction) with methods signatures and Class attribute (SqlFunction).

Full source code listed in next steps.


5- Add NAV web service to Visual Studio project

Right click on Service References Node and click Add Service Reference, then click on Advanced

6- Click Add Web Reference

If you installed NAV on same machine then you can use below shown URL to access Customers List Web Service (we will call it) NAVCustomer because we can also use this page to add or update customers (not covered in this walk through).


7- Implement NavUDFunctions methods

Below is NavUDFunctions class full source code

  public class NavUDFunctions


        [SqlFunction(FillRowMethodName = "FillRow")]

        public static IEnumerable InitMethod(String city)


            return GetNavCustomers(city) ;



        public static void FillRow(Object obj , out SqlChars Num, out SqlChars Name, out SqlChars       Address, out SqlChars City ,  out SqlChars SalesPerson ,  out decimal Balance)


            Customer navCustomer = (Customer)obj;

            Num = new SqlChars(navCustomer.No);

            Name = new SqlChars(navCustomer.Name);

            Address = new SqlChars(navCustomer.Address);

            City = new SqlChars(navCustomer.City);

            SalesPerson = new SqlChars(navCustomer.Salesperson_Code);

            Balance = navCustomer.Balance;



        private static IEnumerable GetNavCustomers(String city)


            Customer_Service svc = new Customer_Service();

            svc.Credentials = new NetworkCredential(@"domain\username", "password");

            List<Customer_Filter> filterList = new List<Customer_Filter>();

            if (!string.IsNullOrEmpty(city))


                Customer_Filter filter = new Customer_Filter();

                filter.Field = Customer_Fields.City;

                filter.Criteria = city;



            return svc.ReadMultiple(filterList.ToArray(), null, 0);




8- Modify Customer_Service default code

Modify References.cs class so that Customer_Service class Url property is assigned a string value instead of App Setting value, Reference.cs class can be found in solution explorer, Web References, NAV Customer under This change is just to get going with walkthrough, in real life application though you will have to populate SQL Config file as explain in the article

public Customer_Service() {

//Becasue app.config does not get deployed, we will have to assign string value to get going with the walkthrough

//this.Url global::NavSqlCLRLib.Properties.Settings.Default.NavSqlCLRLib_NAVCustomer_Customer_Service;







9- SQL Server part

Below is an illustration of what we are trying to achieve in this walkthrough, so we will create NavCustomers user defined function and call it from Select statement to get list of customers.


10- Prepare database for assembly deployment

This step and the next one code to be executed in sql T-SQL editor (Query Analyzer).

-- apply below using sa login

sp_configure 'clr enabled' , 1




use master;

-- replace domain\admin name with real domain admin login


-- replace database name with real database name.



11- Create NavCustomers Function

-- Now you need to run this under the authorized but not like above user [SPF2013VS2012\Administrator

-- Modify the next line to use the appropriate database.


FROM 'C:\Workarea\TestArea\TestProjects\Nav2013SqlCLR_Blog02\NavSqlCLRLib\NavSqlCLRLib\bin\Debug\NavSqlCLRLib.dll'



CREATE FUNCTION NavCustomers(@city nvarchar(50))


(Num nvarchar(20),Name nvarchar(100),Address nvarchar(100),

       City nvarchar(50), SalesPerson nvarchar(20) , Balance decimal(8,2))


EXTERNAL NAME NavSqlCLRLib.NavUDFunctions.InitMethod;


12- Re-deploy NavCustomers Function (if needed)

Use below code to drop the function then the assembly and to re-deploy follow above steps (10 & 11).



We have completed the blog and I hope it was a help to your research efforts.


Saturday, January 19, 2013

Part 3, step by step developing Visual Studio 2012 LightSwitch application.

This is the last part in this blog, below are the links to the previous parts:
Integrating Visual Studio 2012 LightSwitch and Dynamics NAV OData (Part 1)
Step by step developing Dynamics NAV objects (Part 2)

3- Build Visual Studio 2012 LightSwitch Html Client application
Create a new LightSwitch HTML Application, and Save it.

4- Add NAV OData to
Right click on Server Node in Solution Explorer and click Add Data Source..., select OData Service and click next.

Enter http://localhost:7048/DynamicsNAV70/OData/ in Specify the OData service endpoint and keep attach to this data source as read-only checked.

Click next and expand Entities node then select Customer & SalesPerson entities, note we won't require selecting Company entity since we have one Company in NAV database (Cronus International Ltd.). Click finish.

Expand Server node, then expand NAVData node to see the selected entities listed up there. You may close Application 1 Designer* Tab in the designer tab area.

5- Build Sales Persons & Customers browsing screen
Right click Client (Startup) node and click on Add Screen. In Add New Screen select Browse Data Screen and in Screen Data drop down list select NAVData.SalesPerson and click OK.

Press F5 to run the application, we haven't finished yet, but to only to see how Sales Persons screen is displayed by default.

Close the browser (above screen) and go back to Visual Studio screen. Click Sales Person Node and select Rows Layout.

Click on SalesPersons DataItem (Left Panel) and click Add Data Item... in the designer tab.

From Add Data Item screen select Query option, and NAVData.Customers row. Click OK.

Click List Sales Persons then select Customers from Add drop down list.

Select Rows Layout under List Customers node, save all changes.

Run the application and notice that all customers are listed regardless to what Sales Person you select. Next step we will connect Customers list to select Sales Person.

6- Connect Sales Persons List with related Customers

Query Parameter and Parameter Binding will come to the rescue of relating entities in the screens that have data items without referential integrity.
Click Edit Query link beside Customers Data Item (Left Panel)


In Parameter section type SalesPersonParam, then from Filter section select Where, Salesperson_code, = and from the drop down select Parameter and the in the last drop down list select SalesPersonParam.

Save all changes and re-open BrowseSalesPersons screen. Expand Query Parameters node under Customers data item and click on SalesPersonParam. In the properties window click on Parameter Binding field and select SalesPersons from the drop down list.

After selecting SalesPersons type .selectedItem.Code (intellisense works here), so you should end up having SalesPersons.selectedItem.Code value for Parameter Binding property.

Save all changes and run the application, click on different Sales Persons items and notice the filter working on underneath customers list.

We have completed Browsing Sales Person Customers Application, thanks for viewing the blow and I hope this blog has assist you in your research.