Creating Tables and Retrieving Query results with Dynamics AX 2009 business connector

Question

I'm writing a C# command line tool to fetch data from AX and to add data (create new tables) to AX.

Fetching data from an AX table is easy and documented here: http://msdn.microsoft.com/en-us/library/cc197126.aspx

Adding data to an existing table is also easy: http://msdn.microsoft.com/en-us/library/aa868997.aspx

But I cannot figure out how to do two things:

• Create a new AX Table
• Retrieve data from an AX Query

Can someone please share some sample code or give some pointers on where to start looking. My searches on Google and MSDN have not revealed much.

NOTE: I am not an experienced AX or ERP developer.

Answer 1

I have created a query in the AOT and was able to use C# to return the data. Find the code below. It's a query that returns the sales that I create Aging Buckets with. I hope this helps.

[DataMethod(), AxSessionPermission(SecurityAction.Assert)]
public static System.Data.DataTable GetCustBuckets(String AccountNum)
{
    //Report Parameters
    Dictionary<string, object> d = new Dictionary<string, object>();
    d.Add("CustTransOpen.AccountNum",AccountNum);

    // Create a data table. Add columns for item group and item information. 
    DataTable table = new DataTable();
    table = AxQuery.ExecuteQuery("SELECT * FROM epcCustomerAging",d);


    DataTable tableBucket = new DataTable();
    DataRow rowBucket;

    tableBucket.Columns.Add("Current", typeof(double));
    tableBucket.Columns.Add("Bucket31to60", typeof(double));
    tableBucket.Columns.Add("Bucket61to90", typeof(double));
    tableBucket.Columns.Add("Bucket91to120", typeof(double));
    tableBucket.Columns.Add("Over120", typeof(double));

    //Variables to hold BUCKETS
    double dCurrent = 0;
    double dBucket31to60 = 0;
    double dBucket61to90 = 0;
    double dBucket91to120 = 0;
    double dOver120 = 0;

    // Iterate through the results. Add the item group to the data table. Call the display method 
    foreach (DataRow TransRow in table.Rows)
    {

        DateTime TransDate = Convert.ToDateTime(TransRow["TransDate"].ToString());
        double AmountCur = Convert.ToDouble(TransRow["AmountCur"].ToString());

        DateTime Today= Microsoft.VisualBasic.DateAndTime.Now;
        long nDays = Microsoft.VisualBasic.DateAndTime.DateDiff(Microsoft.VisualBasic.DateInterval.Day, TransDate, Today, 0, 0);

        if (nDays <= 30)
        {               
            dCurrent += AmountCur; 
        }
        else if (nDays <= 60)
        {
             dBucket31to60 += AmountCur ; 
        }
        else if (nDays <= 90)
        {
            dBucket61to90 += AmountCur;
        }
        else if (nDays <= 120)
        {
            dBucket91to120 += AmountCur;
        }
        else
        {
            dOver120 += AmountCur;    
        }
    }

    rowBucket = tableBucket.NewRow();
    rowBucket["Current"] = dCurrent;
    rowBucket["Bucket31to60"] = dBucket31to60;
    rowBucket["Bucket61to90"] = dBucket61to90;
    rowBucket["Bucket91to120"] = dBucket91to120;
    rowBucket["Over120"] = dOver120;

    tableBucket.Rows.Add(rowBucket);

    return tableBucket;
}

Answer 2

Here is an example of running a query in C#:

(Note: this is a very simplistic method by using an existing query definition, you could also build a query from scratch using QueryBuildDataSource objects, etc...)

    Axapta ax = new Axapta();
    ax.Logon("", "", "", "");

    //Create a query object based on the customer group query in the AOT
    AxaptaObject query = ax.CreateAxaptaObject("Query", "CustGroupSRS");

    //Create a queryrun object based on the query to fecth records
    AxaptaObject queryRun = ax.CreateAxaptaObject("QueryRun", query);

    AxaptaRecord CustGroup = null;
    ;
    while (Convert.ToBoolean(queryRun.Call("next")))
    {
        //GetTableId function is defined here: .Net Business Connector Kernel Functions
        CustGroup = (AxaptaRecord)queryRun.Call("get", ax.GetTableId("CustGroup"));
        System.Diagnostics.Debug.WriteLine(CustGroup.get_Field("Name").ToString());
    }
    CustGroup.Dispose();
    queryRun.Dispose();
    query.Dispose();

    ax.Logoff();
    ax.Dispose();

Answer 3

Here is a way to create a new AX table from C# (this is using an extension method):

public static bool CreateAXTable(this Axapta ax)
{
    string TableName = "MyCustomTable"; 
    string size = "255"; //You could load this from a setting
    bool val = false;
    if (!ax.TableExists(TableName))
    {
        AxaptaObject TablesNode = (AxaptaObject)ax.CallStaticClassMethod("TreeNode", "findNode", @"\Data Dictionary\Tables");
        AxaptaObject node;
        AxaptaObject fields;
        AxaptaObject fieldNode;

        TablesNode.Call("AOTadd", TableName); 
        node = (AxaptaObject)ax.CallStaticClassMethod("TreeNode", "findNode", "\\Data dictionary\\Tables\\" + TableName);        
        fields = (AxaptaObject)ax.CallStaticClassMethod("TreeNode", "findNode", "\\Data dictionary\\Tables\\" + TableName + "\\Fields");

        fields.Call("addString", "String1"); //add a string field
        fieldNode = (AxaptaObject)fields.Call("AOTfindChild", "String1"); //grab a reference to the field
        fieldNode.Call("AOTsetProperty", "StringSize", size);
        fieldNode.Call("AOTsave");

        fields.Call("addString", "String2"); //add a string field
        fieldNode = (AxaptaObject)fields.Call("AOTfindChild", "String2"); //grab a reference to the field
        fieldNode.Call("AOTsetProperty", "StringSize", size);
        fieldNode.Call("AOTsave");

        fields.Call("addString", "String3"); //add a string field
        fieldNode = (AxaptaObject)fields.Call("AOTfindChild", "String3"); //grab a reference to the field
        fieldNode.Call("AOTsetProperty", "StringSize", size);
        fieldNode.Call("AOTsave");

        fields.Call("addString", "String4"); //add a string field
        fieldNode = (AxaptaObject)fields.Call("AOTfindChild", "String4"); //grab a reference to the field
        fieldNode.Call("AOTsetProperty", "StringSize", size);
        fieldNode.Call("AOTsave");

        fields.Call("addReal", "Real1");
        fields.Call("addReal", "Real2");
        fields.Call("addReal", "Real3");
        fields.Call("addReal", "Real4");

        fields.Call("addDate", "Date1");
        fields.Call("addDate", "Date2");
        fields.Call("addDate", "Date3");
        fields.Call("addDate", "Date4");

        fields.Call("AOTsave");
        node.Call("AOTsave");

        AxaptaObject appl = ax.GetObject("appl");
        appl.Call("dbSynchronize", Convert.ToInt32(node.Call("applObjectId")), false);

        val = true;
    }
    else //Table already exists
    {
        val = true;
    }
    return val;
}


public static bool TableExists(this Axapta ax, string tableName)
{
    return ((int)ax.CallStaticClassMethod("Global", "tableName2Id", tableName) > 0);
}

Answer 4

I honestly don't think it's possible to create new tables using the business connector. It has to be done within AX and the AOT.

As for returning mixed data, I would probably use a container object for that. Containers can hold sub containers, or axaptarecords. An AxaptaRecord contains data from one defined table.