7. Querying Data Across Companies
When you log on to Dynamics AX with .NET
Business Connector, you establish a session that is dedicated to one
specific company that the Dynamics AX user has access to. (This is
similar to selecting a company in the Dynamics AX client.) At times,
however, an application you’re developing might need to access data that
resides in a number of companies. For example, you might want to query
vendors across all the companies in Dynamics AX. To address this need,
Dynamics AX 2009 supports defining and executing cross-company queries
from .NET Business Connector.
Cross-company queries can be executed in two ways. The first is to use the ExecuteStmt API. The second is to use the Query object. In the following subsections, we explain each approach, using code samples to demonstrate.
Method 1: Using ExecuteStmt
When using ExecuteStmt
to query Dynamics AX, you can extend the scope of the query to cover
more than one company. (Querying against just one company is the default
behavior.) The following code sample shows how to do this.
using System;
using Microsoft.Dynamics.BusinessConnectorNet;
namespace Demo
{
// ListVendors - shows how to retrieve and iterate through a list of Dynamics AX records
// (assumes you have two companies: DMO and DMO2 – replace with your own
// companies)
class CrossCompanyQuery
{
public static void Main(string[] args)
{
String vendAccountNumField = "AccountNum";
Object vendAccNum;
String vendNameField = "Name";
Object vendName;
using (Axapta ax = new Axapta())
{
try
{
// Logon
ax.Logon("DMO", null, null, null);
Console.WriteLine("*** List vendor records");
// Instantiate record
AxaptaRecord axRecord = ax.CreateAxaptaRecord("VendTable");
// Execute a query against VendTable
axRecord.ExecuteStmt(
"container c=['DMO','DMO2']; select crosscompany:c %1");
// Loop through found records
while (axRecord.Found)
{
vendAccNum = axRecord.get_Field(vendAccountNumField);
vendName = axRecord.get_Field(vendNameField);
Console.WriteLine(vendName + "\t" + vendAccNum);
axRecord.Next();
}
Console.ReadKey();
// Logoff
ax.Logoff();
}
catch (Exception e)
{
Console.WriteLine("Exception occurred: " + e.Message);
}
}
}
}
}
|
The key here is the special syntax within ExecuteStmt that permits multiple company IDs to be specified.
Method 2: Using the Query Object
The second method uses the Dynamics AX Query
object, which provides a lot of flexibility for constructing and
executing queries. The following code sample shows how a cross-company
query is enabled by using this approach.
using System;
using Microsoft.Dynamics.BusinessConnectorNet;
namespace Demo
{
// ListVendors - shows how to retrieve Dynamics AX records from multiple companies
// using the Query object (assumes you have two companies: DMO, and DMO2
// – replace with your own companies)
class CrossCompanyQueryUsingQueryObject
{
public static void Main(string[] args)
{
String vendAccountNumField = "AccountNum";
String vendNameField = "Name";
AxaptaObject axQuery;
AxaptaObject axQueryRun;
AxaptaObject axQueryDataSource;
using (Axapta ax = new Axapta())
{
try
{
// Logon
ax.Logon("DMO", null, null, null);
Console.WriteLine(
"*** List vendor records from DAT and DMO using Query object");
// Get the table ID of the table to query
int vendTableId = (int)ax.CallStaticClassMethod("global",
"tablename2id", "VendTable");
// Instantiate the Query object
axQuery = ax.CreateAxaptaObject("Query");
// Add a data source
axQueryDataSource = (AxaptaObject)axQuery.Call(
"AddDataSource", vendTableId);
// Enable cross company query
axQuery.Call("AllowCrossCompany", true);
// Add two companies for the cross company query
axQuery.Call("AddCompanyRange", "DMO2");
axQuery.Call("AddCompanyRange", "DMO");
// Run the query!
axQueryRun = ax.CreateAxaptaObject("QueryRun", axQuery);
while ((bool)axQueryRun.Call("next"))
{
using (AxaptaRecord axRecord =
(AxaptaRecord)axQueryRun.Call("get", vendTableId))
{
Object vendAccNum = axRecord.get_Field(
vendAccountNumField);
Object vendName = axRecord.get_Field(vendNameField);
Console.WriteLine(vendName + "\t" + vendAccNum);
}
}
Console.ReadKey();
// Logoff
ax.Logoff();
}
catch (Exception e)
{
Console.WriteLine("Exception occurred: " + e.Message);
}
}
}
}
}
|
Notice that AllowCrossCompany has to be invoked to enable a cross-company query. The call to AddCompanyRange allows an arbitrary number of companies to be added to the query.
8. Invoking Business Logic
In
addition to accessing data, you can also invoke business logic defined
in Dynamics AX directly from .NET Business Connector. In this example,
you call a method in an X++ class to update inventory item details in
Dynamics AX based on data from a separate inventory management system.
To do this, you use the CallStaticClassMethod method in the Axapta managed class, as shown in the following code.
using System;
using Microsoft.Dynamics.BusinessConnectorNet;
namespace Demo
{
// UpdateInventoryQuantity - shows how to call a static X++ class method
class UpdateInventoryQuantity
{
public static void Main(string[] args)
{
object returnValue;
using (Axapta ax = new Axapta())
{
try
{
// Logon
ax.Logon(null, null, null, null);
// InventoryManager is the class, updateInventoryQty
// is the method to be invoked
returnValue = ax.CallStaticClassMethod("InventoryManager",
"updateInventoryQty");
// Write a message according to the result of the
// class/method call
if ((Boolean)returnValue)
Console.WriteLine(
"Inventory quantity updated successfully");
else
Console.WriteLine("Inventory quantity update failed");
}
catch (Exception e)
{
Console.WriteLine("Exception occurred: " + e.Message);
}
}
}
}
}
|
The X++ class returns a Boolean result in this case, which is then used to determine the next action in the application.
As
you can see from these examples, developing applications that integrate
with Dynamics AX using .NET Business Connector is relatively
straightforward. Although real applications would use the managed
classes more extensively, the approach to accessing data and invoking
business logic would remain the same.
