web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Count all record from ...
Microsoft Dynamics AX (Archived)

Count all record from QueryRun in Business connector ASP.NET C#

(0) ShareShare
ReportReport
Posted on by Gelo Tabon 53

Good Day Sir/Mam!

How I count my total record without using query count("field") or Looping in Business connector Asp.net?

Please see my sample code, not my actual code.

AxaptaObject qr;

int totalRecord=0;

 qr =(bcn.AxaptaObject) axp.CallStaticClassMethod("GST_callPurch", "getData", true, start, length, txtSortCol, isortDir, search);

totalRecord=filteredCount(qr);

Response.write("Total Records: "+totalRecord);

Thanks in Advance :)

*This post is locked for comments

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    Why do you want to use Business Connector instead of web services? Business Connector is deprecated and has quite a few limitations regarding where from where you can call it.

    Why don't you use the query service or a custom service?

  • Gelo Tabon Profile Picture
    Gelo Tabon 53 on at

    Because i want to use Classes, Methods , and etc of Dynamics AX from Dot.Net

  • Gelo Tabon Profile Picture
    Gelo Tabon 53 on at

    But, Is there other way to get the value from my AX variable?

    sample code below:

    AX Code:

    Public Static getData(boolean _check,int _start,int _length,str _sortCol,str _sortDir,str _purchId)

    {

           totalRec= SysQuery::countLoops(qr);

           qr.enablePositionPaging(true);

           qr.addPageRange(_start,_length);

         while(qr.next())

    {

    // other code here...

    }

    return qr;

    }

    my Dot.net Code.

    AxaptaObject qr;

    int totalRecord;

     qr =(bcn.AxaptaObject) axp.CallStaticClassMethod("GST_callPurch", "getData", true, start, length, txtSortCol, isortDir, search);

    totalRecord=(int)qr.call("get",axp.CallStaticClassMethod("Global","int","totalRec"));

    response.write("My total Record is:"+totalRecord);

    This code is just a sample, not my actual code..

    By the way sir. Drab thank you for your reply.

    Thanks  :)

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    You're wrong in thinking that your requirement "i want to use Classes, Methods , and etc of Dynamics AX from Dot.Net" requires Business Connector and that there is no non-deprecated technology allowing you to do it.

    As I mentioned, AX 2012 offers web services that you can call from .NET and this is exactly what replaces Business Connector. Business Connector shouldn't be used anymore.

    You can use the query service to run queries, including counting records. When you want to expose business logic in classes and other objects, you can utilize custom services. For completeness, there are also document services for dealing with whole business documents, such as sales orders.

  • Gelo Tabon Profile Picture
    Gelo Tabon 53 on at

    can you please give me the link of query service , custom services and document services?

    Thanks  Sir Martin

  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    Regarding your current code, it would helped if you either showed us your actual code or provided an example that make sense.

    I see that getData() is static, therefore totalRec would have to be declared inside the method and there is no way how to access it from outside. In fact, the variable don't exist anymore after you call the method. You would have to either return the value or use a class-level ^

    variable (getData() couldn't be static in such a case).

    axp.CallStaticClassMethod("Global","int","totalRec")) makes no sense either, because there is no int() method and even if it existed, converting the string "totalRec" to int would give you nothing useful.

    The main problem is that the whole logic isn't designed in a way that could work; you'll have to think it through again. It's not a problem of Business Connector as such, but using Business Connector brings you an additional layer of problems.

  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    I already gave you all the links; all you need is to click on them.

  • Gelo Tabon Profile Picture
    Gelo Tabon 53 on at

    when i clicked it, There is no Found Page

  • Gelo Tabon Profile Picture
    Gelo Tabon 53 on at

    Ok I will give my code.

    AX Classes code:

    public static QueryRun getData(boolean _check,int _start,int _length,str _sortCol,str _sortDir,str _purchId)

    {

       PurchTable                      purchTable;

       VendInvoiceJour                 vendInvoiceJour;

       VendInvoiceTrans                vendInvoiceTrans;

       Query                           query;

       QueryRun                        qr;

       QueryBuildDataSource            qbds1,qbds2,qbds3;

       QueryBuildRange                 qbr;

       int                             totalRec;

       externalPurchTableTmp           ExternalPurchTableTmp;

       FieldId                         sortCol;

       TableId                         myTableId;

       SortOrder                       mySortDir=_sortDir=='Ascending' ? SortOrder::Ascending : SortOrder::Descending;

       str                             myTable;

           query=new Query();

           qbds1=query.addDataSource(tableNum(VendInvoiceTrans));

           qbds2=qbds1.addDataSource(tableNum(VendInvoiceJour));

           qbds3=qbds2.addDataSource(tableNum(PurchTable));

           qbds2.relations(false);

           qbds2.addlink(fieldNum(VendInvoiceTrans,InvoiceId),fieldNum(VendInvoiceJour,InvoiceId));

           qbds3.relations(false);

           qbds3.addlink(fieldNum(VendInvoiceJour,PurchId),fieldNum(PurchTable,PurchId));

           if(_sortCol=="PurchId" || _sortCol=="InvoiceId" || _sortCol=="InvoiceDate" || _sortCol=="DeliveryDate" || _sortCol=="ItemId" || _sortCol=="ItemName")

           {

               myTable="VendInvoiceTrans";

               myTableId=tableName2id(myTable);

               sortCol=fieldName2id(myTableId,_sortCol);

               qbds3.addSortField(sortCol, mySortDir);

           }

           else if(_sortCol=="InvoiceAmount" || _sortCol=="OrderAccount")

           {

               myTable="VendInvoiceJour";

               myTableId=tableName2id(myTable);

               sortCol=fieldName2id(myTableId,_sortCol);

               qbds3.addSortField(sortCol, mySortDir);

           }

           else if(_sortCol=="PurchName")

           {

               myTable="PurchTable";

               myTableId=tableName2id(myTable);

               sortCol=fieldName2id(myTableId,_sortCol);

               qbds3.addSortField(sortCol, mySortDir);

           }

           qbds3.firstOnly(_length);

           if(_purchId!="null")

           {

               qbr=qbds3.addRange(fieldNum(VendInvoiceTrans,PurchId));

               qbr.value(strFmt('(PurchId=="%1")',queryValue(_purchId)));            

           }

           qr      = new QueryRun(query);

           totalRec= SysQuery::countLoops(qr);

           qr.enablePositionPaging(true);

           ExternalPurchTableTmp.clear();

           qr.addPageRange(_start,_length);

       return qr;

    }

    ASP.NET c# code..

    public void ProcessRequest(HttpContext context)
    {
    GlobalClass Con = new GlobalClass();
    string myId = context.Request.QueryString["purchId"];
    string isortDir = context.Request["sSortDir_0"].ToString();
    string search = context.Request["sSearch"];
    string isortColumn = context.Request["iSortCol_0"].ToString();
    string fromDate = context.Request["fromDate"].ToString();
    string toDate = context.Request["toDate"].ToString();
    int start =int.Parse(context.Request["iDisplayStart"]);
    int length =int.Parse(context.Request["iDisplayLength"]);
    string txtSortCol ="0";
    DateTime InvoiceDated;
    int filteredCount = 0;
    int totalRec = 0;
    List<listPurch> ListPurchTable = new List<listPurch>();
    bcn.AxaptaRecord purchTable,vendInvoiceJour,vendInvoiceTrans;
    bcn.Axapta axp;
    bcn.AxaptaObject qr;

    axp = new bcn.Axapta();
    axp=Con.OpenConn();
    if (isortColumn == "0")
    {
    txtSortCol = "PurchId";
    }
    else if (isortColumn == "1")
    {
    txtSortCol = "InvoiceId";
    }
    else if (isortColumn == "2")
    {
    txtSortCol = "InvoiceDate";
    }
    else if (isortColumn == "3")
    {
    txtSortCol = "InvoiceAmount";
    }
    else if (isortColumn == "4")
    {
    txtSortCol = "OrderAccount";
    }
    else if (isortColumn == "5")
    {
    txtSortCol = "PurchName";
    }
    else if (isortColumn == "6")
    {
    txtSortCol = "DeliveryDate";
    }
    else if (isortColumn == "7")
    {
    txtSortCol = "ItemId";
    }
    else if (isortColumn == "8")
    {
    txtSortCol = "DeliveryDate";
    }
    start = start + 1;
    if (search.Length == 0)
    {
    search = "null";
    }
    qr =(bcn.AxaptaObject) axp.CallStaticClassMethod("GST_callPurch", "getData", true, start, length, txtSortCol, isortDir, search);
    while(Convert.ToBoolean(qr.Call("next")))
    {
    purchTable = (bcn.AxaptaRecord)qr.Call("get", (int)axp.CallStaticClassMethod("Global", "tableName2Id","PurchTable"));
    vendInvoiceJour = (bcn.AxaptaRecord)qr.Call("get", (int)axp.CallStaticClassMethod("Global", "tableName2Id", "VendInvoiceJour"));
    vendInvoiceTrans = (bcn.AxaptaRecord)qr.Call("get", (int)axp.CallStaticClassMethod("Global", "tableName2Id", "VendInvoiceTrans"));
    listPurch ListPurch = new listPurch();
    ListPurch.PurchId = purchTable.get_Field("PurchId").ToString();
    ListPurch.InvoiceId = vendInvoiceJour.get_Field("InvoiceId").ToString();
    InvoiceDated = DateTime.Parse(vendInvoiceJour.get_Field("InvoiceDate").ToString());
    ListPurch.InvoiceDate = InvoiceDated.Month + "-" + InvoiceDated.Day + "-" + InvoiceDated.Year;
    ListPurch.InvoiceAmount = float.Parse(vendInvoiceJour.get_Field("InvoiceAmount").ToString());
    ListPurch.OrderAccount = purchTable.get_Field("OrderAccount").ToString();
    ListPurch.PurchName = purchTable.get_Field("PurchName").ToString();
    ListPurch.DeliveryDate = purchTable.get_Field("DeliveryDate").ToString();
    ListPurch.ItemId = fromDate;//vendInvoiceTrans.get_Field("ItemId").ToString();
    ListPurch.ItemName = vendInvoiceTrans.Call("ItemName").ToString();
    //totalRec = 200;
    ListPurchTable.Add(ListPurch);
    filteredCount++;
    }

    var result = new { iTotalRecords = filteredCount, iTotalDisplayRecords = totalRec, aaData = ListPurchTable };
    JavaScriptSerializer js = new JavaScriptSerializer();
    context.Response.Write(js.Serialize(result));

    }

  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    Sorry, there was a little typo in the URLs; it's now fixed. Note that you could also put the keywords to a search engine to find documentation pages you need.

    I suggest you start with the query service, because your getData() method looks like you're trying to write something like what the query service already does out of the box.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans