Microsoft Dynamics AX (Archived)

select from ax table in C#

(0) Share

Report

Posted on by Community Member

Hello,

I found a blog: http://axaptanotes.blogspot.co.il/2014/03/ax-2012-running-ax-query-from-c-data.html

I need to do the same - selecting from table, such as this example, but I need a deeper example.

How can I resolve rows names?

How can I resolve the data?

Thanks :)

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (4)

Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

It returns a collection of DataRow objects. Reading the documentation should answer your questions.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
I got the following:

The data method getData invocation failed. Fix the data method so that it returns a System.Data.DataTable type or an IEnumerable of type System.Data.DataRow before binding the report to the data method. Make sure that the method returns data.

Original error message: The AX query string select * from MyTable where dataAreaid = '' and MyColumn = '' is invalid. The valid format is 'SELECT [fields] FROM [AX query name]', where [fields] can be either '*' or a comma separated field list.

Query is valid, since I run it in sql.

getData looks like:

using MIL = Microsoft.Dynamics.AX.ManagedInterop; ... public static MIL.Session axSession = null; [DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")] public static DataTable getData(string env, string lang, string companyName, string transportationId) { DataTable t; ReportsGeneralBC h = new ReportsGeneralBC(); if (MIL.Session.Current == null || !MIL.Session.Current.isLoggedOn()) try { // Login to Microsoft Dynamics Ax. axSession = new MIL.Session(); axSession.Logon("35", "en-us", "AXDEV", null); // in purpose to run at development environment for check. } catch (Exception e) { Console.WriteLine("An error occurred in object creation or Axapta logon: {0}", e.Message); return null; } IEnumerable dt = Microsoft.Dynamics.Framework.Reports.AxQuery.ExecuteQueryStream("select * from MyTable where dataAreaid = '' and MyColumn = ''"); // ***** here I got the exception return t; }

Thanks :)

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

You return variable t, which is never written into, therefore the method always returns null. Please use a debugger for debugging; it will tell you whether you got null because of this bug or because of an exception.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Sorry, I didn't copy+paste all the relevant code, and it is obvious that the exception mentioned is for the line in asterisks (Microsoft.Dynamics.Framework.Reports.AxQuery.ExecuteQueryStream( ...)

Here is relevant code again:

using MIL = Microsoft.Dynamics.AX.ManagedInterop; ... public static MIL.Session axSession = null; [DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")] public static DataTable getData(string env, string lang, string companyName, string transportationId) { DataTable t = new DataTable(); ReportsGeneralBC h = new ReportsGeneralBC(); if (MIL.Session.Current == null || !MIL.Session.Current.isLoggedOn()) try { // Login to Microsoft Dynamics Ax. axSession = new MIL.Session(); axSession.Logon("35", "en-us", "AXDEV", null); // in purpose to run at development environment for check. } catch (Exception e) { Console.WriteLine("An error occurred in object creation or Axapta logon: {0}", e.Message); return null; } IEnumerable dt = Microsoft.Dynamics.Framework.Reports.AxQuery.ExecuteQueryStream("select * from MyTable where dataAreaid = '' and MyColumn = ''"); // ***** here I got the exception IEnumerator e = dt.GetEnumerator(); string s; s = ""; if (e != null) { if (e.MoveNext()) s = e.Current.GetType().ToString(); } t.Columns.Add("test", typeof(string)); r = t.NewRow(); r["test"] = s; t.Rows.Add(r); return t; }

The relevant line is the line in asterisks (Microsoft.Dynamics.Framework.Reports.AxQuery.ExecuteQueryStream ...) , and besides I couldn't find a way doing debug (it is code that run from AX, and then run VS, which use AX business connector to go on.

Thanks :)

Was this reply helpful? Yes No
Ivan (Vanya) Kashperuk on at

Like (0)

Report

Should you maybe use == for value comparison in the query?

That is the AX way

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have tried that either.

Shall I create new query (it shall have to be query object) ?

select * from MyQuery where MyQuery.MyColumn == ''

What is the syntax? (and, if mentioned - how can I debug my code?)

Thanks :)

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

Do you have a query called MyTable? And the message says that "the valid format is 'SELECT [fields] FROM [AX query name]" - I don't see any WHERE clause there, so I assume you can't use it. You should add ranges to the query itself.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

OK. got it.

but, can I do : select from a table (of some other object).

Anyway, how can I adding range value to ax query in C#?

Thanks :)

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

If you don't want to execute a query, you shouldn't call AxQuery.ExecuteQueryStream(). It should be obvious. It seems that you're looking for something like AxaptaRecordWrapper.ExecuteStmt().

Nevertheless I still suspect that you're doing a wrong thing from the beginning and it doesn't look like you're ready to deal with such low-level API.

Regarding "how can I adding range value to ax query in C#", you should realize that it's a query is defined in AX, so that where it needs to be changed.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
OK.

I succeeded AxQuery.ExecuteQueryStream() - That's return datarow.

It's another syntax that selecting from query.

I need to add

var rangeValues = new Dictionary<string, object> { {"MyTable.1.MyColum", "MyValue"} };

and dt = Microsoft.Dynamics.Framework.Reports.AxQuery.ExecuteQueryStream(stmnt, rangeValues); etc.. as in my code.

Thanks :)

Now, how can I get the list of all of the columns of a datarow (framework 3.5)

Thanks :)

Was this reply helpful? Yes No