Friday, May 28, 2010

Passing a table using Nav Automation

I just had someone comment on my previous blog post asking how to pass a table using Nav Automation. Great question.

You can pass complex objects using Nav Automation, but only ones that you define (or that have been explicitly written to work with COM). This means that you can't pass a DataTable object, but you can create your own implementation of a DataTable object and pass it.

In your complex object, you have to explicitly define every property and method that you need to call on that complex object and surface those properties and methods through COM.



Enough talk. Let's see the code. Here is some sample .Net code showing how to pass a table of addresses.
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
using System.Diagnostics;
using System.ComponentModel;

[assembly: ClassInterface(ClassInterfaceType.AutoDual)]
namespace Tim.NavAutomation
{
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
[Guid("12345678-a39f-4fde-9779-fc46749a7ff0")]
[ComVisible(true)]
public interface IAddressDetail
{
[DispId(13200)]
string Address1 { get; set; }
[DispId(13201)]
string Address2 { get; set; }
[DispId(13202)]
string City { get; set; }
[DispId(13203)]
string State { get; set; }
[DispId(13204)]
string PostCode { get; set; }
[DispId(13205)]
string Country { get; set; }

[DispId(12306)]
public bool IsValidAddress();
}

[ComVisible(true)]
[Guid("12345678-9e1f-4b4e-9a27-5e583cad9acf")]
[ClassInterface(ClassInterfaceType.None)]
public class AddressDetail : IAddressDetail
{
private string _Address1;
public string Address1
{
get { return _Address1; }
set { _Address1 = value; }
}
private string _Address2;
public string Address2
{
get { return _Address2; }
set { _Address2 = value; }
}
private string _City;
public string City
{
get { return _City; }
set { _City = value; }
}
private string _State;
public string State
{
get { return _State; }
set { _State = value; }
}
private string _PostCode;
public string PostCode
{
get { return _PostCode; }
set { _PostCode = value; }
}
private string _Country;
public string Country
{
get { return _Country; }
set { _Country = value; }
}

public bool IsValidAddress()
{
// This is where you can put your own
// logic to validate that the address
// is valid (i.e., check with USPS
// web service, etc.)
throw new NotImplementedException();
}
}

[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
[Guid("12345678-59f6-4d1e-803e-080c09572c52")]
[ComVisible(true)]
public interface IAddressTable
{
[DispId(13210)]
int Length { get; }
[DispId(13211)]
AddressDetail Item(int i);
[DispId(13212)]
void Add(Address item);
}

[ComVisible(true)]
[Guid("12345678-df87-4ab5-80ac-d979e38a3a58")]
[ClassInterface(ClassInterfaceType.None)]
public class AddressTable : IAddressTable
{
public List InternalAddressTable;

public int Length
{
get
{
if (InternalAddressTable == null)
{
return 0;
}
else
{
return InternalAddressTable.Count;
}
}
}
public TransactionDetail Item(int i)
{
if (i <>= InternalAddressTable.Count)
{
return null;
}
else
{
return InternalAddressTable[i];
}
}
public void Add(AddressDetail address)
{
if (InternalAddressTable == null)
{
InternalAddressTable =
new List();
}
InternalAddressTable.Add(Item);
}
}


[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
[Guid("12345678-6292-4F39-BB5B-1BDE0F69007B")]
[ComVisible(true)]
public interface IAddressFunctions
{
[DispId(13300)]
public AddressTable GetNewAddresses();
[DispId(13301)]
public bool SendJunkMail(AddressTable addresses);
}

[ComVisible(true)]
[Guid("12345678-B073-4C6A-B21E-6A19F3A7DE4E")]
[ClassInterface(ClassInterfaceType.None)]
public class AddressFunctions : IAddressFunctions
{
public AddressTable GetNewAddresses()
{
AddressTable t = new AddressTable();

// Here is where you could insert your
// own logic to get a table of
// addresses from somewhere
AddressDetail det = new AddressDetail;
det.Address1 = "123 Main Street";
det.City = "Baltimore";
det.State = "Maryland";
det.PostCode = "01252";
t.Add(det);

det = new AddressDetail();
det.Address1 = "5123 Governor Lane";
det.City = "Chesterville";
det.State = "Kansas";
det.PostCode = "71321";
t.Add(det);

return t;
}

public bool SendJunkMail(AddressTable addresses)
{
foreach (AddressDetail d
in addresses.InternalAddressTable)
{
// Here is where you would call some
// external function to send junk
// mail to each Addresse
}
}
}
}

So how do we work with this on the Nav side? I'm glad you asked. Here is some sample (untested) code that shows the general idea. Note that AddrTable, AddrDetail, and AddrFunc are automation objects in Nav that have been tied to the automation types defined in the .Net code from above.

First, we'll show how to take a table of data and pass it to .Net so that .Net can do something with the data.
CREATE(AddrTable);

CREATE(AddrDetail);
AddrDetail.Address1 := '5432 Small Dr.';
AddrDetail.City := "Smallsville";
AddrDetail.State := 'NY';
AddrDetail.PostCode := '12345';
AddrTable.Add(AddrDetail);

CLEAR(AddrDetail);
CREATE(AddrDetail);
AddrDetail.Address1 := '813 Garden Path';
AddrDetail.City := 'Mechanicsburg';
AddrDetail.State := 'Michigan';
AddrDetail.PostCode := '51238';
AddrTable.Add(AddrDetail);

CREATE(AddrFunc);
success := AddrFunc.SendJunkMail(AddrTable);

CLEAR(AddrFunc);
CLEAR(AddrDetail);
CLEAR(AddrTable);

And now I'll show how to retrieve a table of data from .Net and then do something with it in Nav
CREATE(AddrFunc);
AddrTable := AddrFunc.GetNewAddresses();

IF AddrTable.Length > 0 THEN BEGIN
i := 0;
REPEAT
AddrDetail := AddrTable.Item(i);
// Do something with AddrDetail.Address1,
// AddrDetail.Address2, etc. Perhaps
// you could add them to the contact table.
i := i + 1;
UNTIL i = AddrTable.Length;
END;


I hope that helps to show what you can do. Yes, it's a pain. But that's the nature of working with COM.


P.S. Sorry about the formatting. I couldn't figure out how to get Blogger.com to preserve the indentation on my code.

4 comments:

Ferry said...

Hi Tim,
really thank you for your help. you answer my question very fast.

However, I want to ask, I use your code, and found a problem ( I'm very new at c# programming ).

1. i found error return value on this block, can you advise me on this :

public TransactionDetail Item(int i)
{
if (i <>= InternalAddressTable.Count)
{
return null;
}
else
{
return InternalAddressTable[i];
}

2. also on below part,for internalAddressTable.Add(Item)

public void Add(AddressDetail address)
{
if (InternalAddressTable == null)
{
InternalAddressTable =
new List();
}
InternalAddressTable.Add(Item);
}

Thanks for your help, sorry if i have too much question.

Ferry.

Ferry said...

oh never mind my prev. question, i have found the problem.

thanks for your help Tim,
Ferry

Ferry said...

Hi Tim,
Thanks 4 your help from before. I Want to ask an advise, what data type I should use to send 1 table ( with many records/fields ) from Navision to .Net Automation.

I'm thinking about using XML. do have any idea ?

Thanks,
Ferry

Tim Larson said...

Ferry,

In the sample code posted, you can see that I passed a custom data type called AddressTable from Nav to .Net. It is a table of records that have several properties including address, city, state, zip, etc. I would recommend following the example found in the code in this blog post.

However, I suppose you could use XML to pass the data back and forth in a single string, but you'd have to parse and build XML both on the .Net side and on the C/AL side.

Tim