Create tables and retrieve query results using the Dynamics AX 2009 business connector

Question

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

Retrieving data from an AX table is simple 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 can’t figure out how to do two things:

• Create a new AX table
• Get data from an AX request

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

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

Answer 1

Here is a way to create a new AX table from C # (this uses the 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 2

I created a request in AOT and was able to use C # to return data. Find the code below. This is a query that returns the sales with which I create Aging Buckets. 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 3

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

(Note: this is a very simplified method using the existing query definition, you can also build the 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 4

I honestly do not think it is possible to create new tables using a business connector. This should be done in AX and AOT.

Regarding the returned mixed data, I would probably use a container object for this. Containers may contain subcontainers or axaptarecords. AxaptaRecord contains data from one specific table.