Microsoft Dynamics Nav: .NET Interoperability

Microsoft Dynamics Interoperability with .NET

With Microsoft Dynamics Nav 2009 you can take advantage of .NET Framework interoperability and create code that allows Microsoft Dynamics NAV objects and .NET Framework objects to interact. This interoperability can be leveraged to execute Microsoft Dynamics Nav business logic from a .NET application. Here is an example of how to create a .NET Application that executes Nav code within a CodeUnit through the use of the “Microsoft Dynamics NAV Business Web Services”.

Before creating any code it is important that Microsoft Dynamics Nav is properly setup and configured to work with Web Services.

  1. Edit the CustomSettings.config in the Microsoft Dynamics Nav Service directory to work with your database and port.
    Microsoft Dynamics Interoperability with .NET
  2. Ensure that the “Microsoft Dynamics NAV Business Web Services” and Microsoft Dynamics Nav Server” are configured to use an account that has access to the database
    Microsoft Dynamics Interoperability with .NET
  3. In Microsoft Dynamics Nav create a CodeUnit with two functions that will be executed from the .NET Application
    Microsoft Dynamics Interoperability with .NET
  4. Publish the CodeUnit created in step 3 as Web Service and restart the “Microsoft Dynamics NAV Business Web Services” service. From the Classic Client fill out the appropriate data in the form.
    Microsoft Dynamics Interoperability with .NET
  5. Create a new .NET Application
  6. Add Microsoft Dynamics Nav as Web Service Reference to the project
    1. Right Click References
    2. Click Add Web Reference buttonClick Advanced buttonAdd Reference
    3. Enter the Web Service URL for your Microsoft Dynamics Nav installation
    4. Enter your Web Reference Name

    Microsoft Dynamics Interoperability with .NET

  7. Create an object to communicate with your service
    namespace BP.NavWebService
    {
        public class NavWebService
        {
            // when registering the web reference connect to the server with an address like
             //http://localhost:7047/DynamicsNAV/WS/CRONUS%20USA,%20Inc./services
    
            private Web1_Binding ws;
            public Web1_Binding Ws { get { return ws; } }
    
            public NavWebService(string url, bool usedefaultcredentials)
            {
                ws = new Web1_Binding();
                // Use default credentials for authenticating against Microsoft Dynamics NAV.
                ws.UseDefaultCredentials = usedefaultcredentials;
                ws.Url = url;
            }
        }
    }
    
  8. Interact with your service
    public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            NavWebService Nws;
    
            private void button1_Click(object sender, EventArgs e)
            {
                textBox2.Text = Nws.Ws.GetCustomerName(textBox1.Text);
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
                Nws.Ws.SetCustomerName(textBox1.Text, textBox2.Text);
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                string Url = "http://localhost:7047/DynamicsNAV/WS/CRONUS%20USA,%20Inc./Codeunit/Web1";
                Nws = new NavWebService(Url, true);
            }
        }
    

 

The sample application referenced in this post can be downloaded >>>here<<<.

Microsoft Dynamics Nav: SQL Update Posting Date Range for All Companies

This is a SQL script that changes the posting date range for all companies in a Microsoft Dynamics Nav “database”.  This script can be scheduled with a job to automate the task.  This script sets the General Ledger Setup posting date range for all companies to the current day (one day). This script can be easily modified to allow for a date range of more than one day.

declare @cmd as varchar(max)
declare @tablename as sysname
declare @ds as varchar(20)
declare @pd as date
set @pd = GETDATE()
SELECT @ds = LEFT(CONVERT(VARCHAR, @pd, 110), 10)

-- Declare a cursor.
declare tablenames insensitive scroll cursor
for
	select [name] from sys.sysobjects where (xtype = N'U') and ([name] like N'%General Ledger Setup')

-- Open the cursor.
open tablenames

-- Loop through
while (1=1)
	begin
		fetch next from tablenames into @tablename;
		if @@fetch_status <> 0 break;		
		set @cmd = 'update [' + @tablename + '] SET [Allow Posting From] = ''' + @ds+ ''', [Allow Posting To] =''' + @ds + '''' 
		exec (@cmd)
		print @cmd;
	end		


-- Close cursor		
close tablenames;
deallocate tablenames;

-- #####

Microsoft Dynamics Nav: Prevent User Login

It may be necessary at times, for example due to software data maintenance, to prevent all but a few users from logging into a Microsoft Dynamics Nav database.

There are a few options available for accomplishing this task.  If Database Authentication is used, then setting an expiration date will prevent the users from logging in.  This can be a tedious task if you have several hundred users.  If you’re using Windows Authentication you can remove the user from the Windows List or disable their Windows account (same applies to Windows groups).  This also presents an issue if you have a number of users and/or an elaborate set of security roles.  Removing the users is an impractical choice that requires the users to be added and setup again.  Disabling the Windows login is not a reasonable option as it also disables a user’s access to other resources.  Another common thought option is to put code in Codeunit 1 – ApplicationManagement.  This sounds logical, but in order for a user to run Codeunit 1 they have to be logged in.

There is another option, which only works for Dynamics Nav with a SQL database.  You can create a stored procedure sp_$ndo$loginproc. This stored procedure is executed as a user is “logging” into the Dynamics Nav database.  If you raise an error in this procedure the user will not be able to open the database with the Nav client under both Database or Windows authentication.

  1. Create a table, from within Nav, called _UserLockoutAllow.  Set DataPerCompany to No and add a field, Code 20, “User ID”.
  2. Create the sp_$ndo$loginproc stored procedure:
    CREATE PROCEDURE [dbo].[sp_$ndo$loginproc]
    @appname VARCHAR(64) = NULL,
    @appversion VARCHAR(16) = NULL
    AS
    BEGIN
    	DECLARE @cnt integer
    	SELECT @cnt = COUNT(*) FROM [_UserLockoutAllow];
    	IF (@cnt &gt; 0)
    		BEGIN
    			IF Not (Upper(SUSER_SNAME()) IN (SELECT [User ID] FROM [_UserLockoutAllow]))
    				RAISERROR ('The system is currently unavailable for scheduled maintenance.', 11, 1)
    		END
    END	
    
    
    GO
    

Adding User IDs to the table will only allow those users to access the database. If the table is empty then everyone can login. Also note that if you add a User ID to the table all logged in users will receive the error and be logged off if their User ID is not allowed to login.