Sunday, October 13, 2013

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;

                filterList.Add(filter);

            }

            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 Reference.map. 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  http://tinyurl.com/l2tmyst.

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

GO

RECONFIGURE

GO

use master;

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

GRANT EXTERNAL ACCESS ASSEMBLY TO [domain\admin name];

-- replace database name with real database name.

ALTER DATABASE [database name] SET TRUSTWORTHY ON;

 

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.

CREATE ASSEMBLY NavSqlCLRLib

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

WITH PERMISSION_SET = EXTERNAL_ACCESS;

GO

CREATE FUNCTION NavCustomers(@city nvarchar(50))

RETURNS TABLE

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

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

AS

EXTERNAL NAME NavSqlCLRLib.NavUDFunctions.InitMethod;

GO
 

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

DROP FUNCTION NavCustomers

DROP ASSEMBLY NavSqlCLRLib

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

 

No comments:

Post a Comment