使用 Dynamics AX 2009 业务连接器创建表并检索查询结果

发布于 2024-08-27 20:38:06 字数 576 浏览 8 评论 0 原文

我正在编写一个 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 开发人员。

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

说谎友 2024-09-03 20:38:06

我在 AOT 中创建了一个查询,并且能够使用 C# 返回数据。找到下面的代码。这是一个返回我用来创建老化桶的销售额的查询。我希望这有帮助。

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

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;
}
倾`听者〃 2024-09-03 20:38:06

以下是从 C# 创建新 AX 表的方法(这是使用扩展方法):


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

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);
}
酒与心事 2024-09-03 20:38:06

下面是在 C# 中运行查询的示例:(

注意:这是使用现有查询定义的非常简单的方法,您还可以使用 QueryBuildDataSource 对象等从头开始构建查询...)


    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();

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();
一抹苦笑 2024-09-03 20:38:06

老实说,我认为不可能使用业务连接器创建新表。它必须在 AX 和 AOT 内完成。

至于返回混合数据,我可能会使用容器对象。容器可以容纳子容器或 axapta 记录。 AxaptaRecord 包含来自一个已定义表的数据。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文