c# - 使用 Dynamics AX 2009 业务连接器创建表和检索查询结果

Question

我正在编写一个 C# 命令行工具来从 AX 获取数据并将数据(创建新表)添加​​到 AX。

从 AX 表中获取数据很容易，记录在此处:http://msdn.microsoft.com/en-us/library/cc197126.aspx

向现有表添加数据也很容易:http://msdn.microsoft.com/en-us/library/aa868997.aspx

但我不知道如何做两件事:

• 创建一个新的 AX 表
• 从 AX 查询中检索数据

有人可以分享一些示例代码或提供一些关于从哪里开始寻找的指示。我在 Google 和 MSDN 上的搜索没有透露太多信息。

注意:我不是经验丰富的 AX 或 ERP 开发人员。

Answer 1

我已经在 AOT 中创建了一个查询，并且能够使用 C# 返回数据。找到下面的代码。这是一个返回我用来创建 Aging Buckets 的销售额的查询。我希望这会有所帮助。

[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;
}