emeadaxsupport: Calling the Query Service

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...y-service.aspx
==============

In AX 2012 some system services were added to perform generic functions like retrieving security or AOT node information. To me the most interesting system service is the Query service. This service allows you to perform a query against whatever tables you want from a service call. On the call you can specify a field list, range, sort, etc. The classes replicate the Query classes used on forms and queries inside of AX. Most notably, the query service has paging built into it. If you have a data intensive query you need to perform via a service call this is probably the right option for you. Service calls tend to timeout on the client when you retrieve a lot of data and don't have the ability to do paging. With that plug for the query service here are some examples I wrote that demonstrates how to use it:

This one has ranges and a sort and builds the whole query programmatically:

QueryServiceClient client = new QueryServiceClient();

DataSet dataSet;

//Set up paging so that 1000 records are retrieved at a time

Paging paging = new ValueBasedPaging() { RecordLimit = 1000 };

QueryMetadata query;

QueryDataSourceMetadata customerDataSource;

QueryDataRangeMetadata range, range2;

QueryDataOrderByMetadata sort;

QueryDataFieldMetadata accountNum, bankAccount, commissionGroup, cashDisc, custGroup;






query = new QueryMetadata();

// Set the properties of the query.

query.QueryType = QueryType.Join;


query.DataSources = new QueryDataSourceMetadata[1];



// Set the properties of the Customers data source.


customerDataSource = new QueryDataSourceMetadata();


customerDataSource.Name = "Customers";


customerDataSource.Enabled = true;


customerDataSource.Table = "CustTable";



//Add the data source to the query.


query.DataSources[0] = customerDataSource;



// Setting DynamicFieldList property to false so I can specify only a few fields


customerDataSource.DynamicFieldList = false;


customerDataSource.Fields = new QueryDataFieldMetadata[5];

//Add my fields


accountNum = new QueryDataFieldMetadata();


accountNum.FieldName = "AccountNum";


accountNum.SelectionField = SelectionField.Database;


customerDataSource.Fields[0] = accountNum;




bankAccount = new QueryDataFieldMetadata();


bankAccount.FieldName = "BankAccount";


bankAccount.SelectionField = SelectionField.Database;


customerDataSource.Fields[1] = bankAccount;




commissionGroup = new QueryDataFieldMetadata();


commissionGroup.FieldName = "CommissionGroup";


commissionGroup.SelectionField = SelectionField.Database;


customerDataSource.Fields[2] = commissionGroup;




cashDisc = new QueryDataFieldMetadata();


cashDisc.FieldName = "CashDisc";


cashDisc.SelectionField = SelectionField.Database;

customerDataSource.Fields[3] = cashDisc;




custGroup = new QueryDataFieldMetadata();


custGroup.FieldName = "CustGroup";


custGroup.SelectionField = SelectionField.Database;


customerDataSource.Fields[4] = custGroup;



//Put a range restriction on the query, in this case they will act as an or

//so the CustGroup == 20 || CustGroup == 30


range = new QueryDataRangeMetadata();


range.TableName = "CustTable";


range.FieldName = "CustGroup";


range.Value = "20";


range.Enabled = true;




range2 = new QueryDataRangeMetadata();


range2.TableName = "CustTable";


range2.FieldName = "CustGroup";

range2.Value = "30";


range2.Enabled = true;




customerDataSource.Ranges = new QueryDataRangeMetadata[2];


customerDataSource.Ranges[0] = range;


customerDataSource.Ranges[1] = range2;



//Add a sort


sort = new QueryDataOrderByMetadata();


sort.DataSource = "Customers";


sort.FieldName = "AccountNum";


sort.SortOrder = SortOrder.Ascending;


query.OrderByFields = new QueryOrderByMetadata[1];


query.OrderByFields[0] = sort;





// Execute the query

int i = 0;

do

{


dataSet = client.ExecuteQuery(query, ref paging);


Console.WriteLine("Query service call: " + i.ToString());


Console.WriteLine("Number of Records in CustTable: " + dataSet.Tables[0].Rows.Count.ToString());


DataRow firstReturnedRow = dataSet.Tables[0].Rows[0];


i++;



}

while (((ValueBasedPaging)paging).Bookmark != null);



Console.ReadLine();



This one executes a query modelled in the AOT:


QueryServiceClient client = new
QueryServiceClient();

Paging paging = new ValueBasedPaging() { RecordLimit = 10 };

DataSet dataset;



int i = 0;

do

{


dataset = client.ExecuteStaticQuery("CustTable", ref paging);


DataRow row = dataset.Tables[0].Rows[0];


i++;

}

while (((ValueBasedPaging)paging).Bookmark != null);



For more information on the query service and the other system services check out msdn:

http://msdn.microsoft.com/en-us/library/gg879657.aspx






Источник: http://blogs.msdn.com/b/axsupport/ar...y-service.aspx