[AX]AX2012 AIF(九):系统服务之Query service
通过Query service可以查询AX的数据,又分静态查询、用户自定义查询和动态查询三种方式,下面以实例来演示如何使用这几种查询。和使用其他系统服务一样首先要在.net工程中添加查询服务的引用,WSDL地址在http://<servername>:8101/DynamicsAx/Services/QueryService。
- 静态查询:它使用已经创建在AOT中的Query,通过QueryServiceClient.ExecuteStaticQuery()方法运行这个Query,返回以数据集表示的结果:
using System; using System.Data; using System.Windows.Forms; using QueryServiceStaticTest.QueryServiceReference; namespace QueryServiceStaticTest { public partial class Form1 : Form { public Form1() { InitializeComponent(); this.Load += new EventHandler(Form1_Load); } private void Form1_Load(object sender, EventArgs e) { QueryServiceClient client = new QueryServiceClient(); DataSet dataSet; Paging paging = null; // Create the binding source objects. BindingSource custBindingSource = new BindingSource(); BindingSource custTransBindingSource = new BindingSource(); BindingSource custTransOpenBindingSource = new BindingSource(); // Set the grid datasources to binding source objects. dataGridView1.DataSource = custBindingSource; dataGridView2.DataSource = custTransBindingSource; dataGridView3.DataSource = custTransOpenBindingSource; // Call the CustTable query using the query service. dataSet = client.ExecuteStaticQuery("CustTable", ref paging); // Set the binding source data sources and data members. // CustTable_CustTrans and CustTrans_CustTransOpen are table relations // in the dataset. custBindingSource.DataSource = dataSet; custBindingSource.DataMember = "CustTable"; custTransBindingSource.DataSource = custBindingSource; custTransBindingSource.DataMember = "CustTable_CustTrans"; custTransOpenBindingSource.DataSource = custTransBindingSource; custTransOpenBindingSource.DataMember = "CustTrans_CustTransOpen"; } } }
在上面的例子中我们使用的是CustTable Query对象,它有三个Datasource,顶级的CustTable、CustTable下的CustTrans以及CustTrans下的CustTransOpen,返回数据集中包含分别对应三个数据源的数据表,注意数据表的命名方式,是以父数据源的名称通过下划线连接当前数据源的名称得到的。
需要说明的是在AX Query的Datasource中如果使用了带表继承的基表,在返回的数据集中会同时包含基表和衍生表的数据,这些数据表通过DataRelation对象联系,在基表的数据行对象上调用DataRow.GetChildRows()方法并指定相应的DataRelation参数可以获取相应的衍生表的数据,详细的例子可以参见http://msdn.microsoft.com/EN-US/library/gg843785.aspx。
- 用户自定义查询:在.net中我们可以使用代码创建一个自定义的QueryMetadata对象,再通过QueryMetadata.ExecuteQuery()方法运行这个查询获得数据,这是一个样例:
QueryServiceClient client = new QueryServiceClient(); DataSet dataSet; Paging paging = null; QueryMetadata query; QueryDataSourceMetadata customerDataSource; query = new QueryMetadata(); // Set the properties of the query. query.QueryType = QueryType.Join; query.AllowCrossCompany = true; query.DataSources = new QueryDataSourceMetadata[1]; // Set the properties of the Customers data source. customerDataSource = new QueryDataSourceMetadata(); customerDataSource.Name = "Customers"; customerDataSource.Enabled = true; customerDataSource.FetchMode = FetchMode.OneToOne; customerDataSource.Table = "CustTable"; // Setting DynamicFieldList property to true returns all fields. customerDataSource.DynamicFieldList = true; //Add the data source to the query. query.DataSources[0] = customerDataSource; // Execute the query you just created. dataSet = client.ExecuteQuery(query, ref paging);
上面的例子中使用代码创建了一个QueryMetadata对象,添加CustTable表作为数据源Customers,随后调用QueryServiceClient.ExecuteQuery()方法运行这个QueryMetadata对象获取数据。
在上面我们提到继承表作为查询数据源会通过DataRelation返回衍生表的数据到各自单独的DataTable中,实际上我们可以通过指定queryMetadata.ReturnFlatDataSet属性为true,将基表衍生表的数据返回到一个数据表中:
using TestQueryService.ServiceReference1; using TestQueryService.ServiceReference2; DataSet dataSet; Paging paging = null; QueryServiceClient client = new QueryServiceClient(); AxMetadataServiceClient metadataClient = new AxMetadataServiceClient(); // Get the query from the AOT using the metadata service. var queryMetadata = metadataClient.GetQueryMetadataByName(new[] { "TestPartiesInWA" })[0]; // Set the query property. queryMetadata.ReturnFlatDataSet = true; // This code is here to convert the queryMetadata object due to namespace conflicts w/mdsr and qsr - remove after M3 using (MemoryStream memoryStream = new MemoryStream()) { var queryMetadataQS = new ServiceReference1.QueryMetadata(); DataContractSerializer mdsSerializer = new DataContractSerializer(typeof(ServiceReference2.QueryMetadata)); DataContractSerializer qsSerializer = new DataContractSerializer(typeof(ServiceReference1.QueryMetadata)); mdsSerializer.WriteObject(memoryStream, queryMetadata); memoryStream.Seek(0, SeekOrigin.Begin); queryMetadataQS = (ServiceReference1.QueryMetadata)qsSerializer.ReadObject(memoryStream); dataSet = client.ExecuteQuery(queryMetadataQS, ref paging); } // Display the flattened dataset in the grid. dataGridView1.DataSource = dataSet.Tables["TestPartiesInWA"];
这个例子中我们使用了MetaData service(工程中中引用的命名空间TestQueryService.ServiceReference2)获取AOT中定义的查询“TestPartiesInWA”,有关的Query查询元数据保存到一个TestQueryService.ServiceReference2.QueryMetadata类型的变量,但是这个变量不能强制转化为Query service的QueryMetadata类型(引入到工程中的命名空间TestQueryService.ServiceReference1),两者在不同的命名空间,所以上面的代码中先把Metadata service的QueryMetadata序列化到一个内存流,再反序列化到Query service的QueryMetadata,给人的感觉奇奇怪怪。
既然说到使用Metadata service得到AOT中的Query metadata对象后在Query service中使用,再来看看MSDN上的另外一个例子(http://msdn.microsoft.com/EN-US/library/gg844682.aspx),这个例子中使用了带有效时间的表,样例表和表数据用这里的X++代码添加(http://msdn.microsoft.com/EN-US/library/gg861773.aspx),在C#中我们需要指定查询有效时间的起始和终止日期,完整代码如下:
using System; using System.Data; using System.IO; using System.Runtime.Serialization; using TestQueryServiceDateEffect.AxMetadataService; using TestQueryServiceDateEffect.QueryServiceReference; using ValidTimeStateQueryType = TestQueryServiceDateEffect.AxMetadataService.ValidTimeStateQueryType; namespace TestQueryServiceDateEffect { class Program { static void Main() { var startDate = new DateTime(2007, 7, 01); var endDate = new DateTime(2007, 7, 31); Paging paging = null; var client = new QueryServiceClient(); var metadataClient = new AxMetadataServiceClient(); // Get the query from the AOT using the metadata service. var queryMetadata = metadataClient.GetQueryMetadataByName(new[] { "TestEmployees" })[0]; // Set the date range on the query. queryMetadata.ValidTimeStateQueryType =ValidTimeStateQueryType.Range; queryMetadata.ValidTimeStateValidFromDateTime = startDate.ToUniversalTime(); queryMetadata.ValidTimeStateValidToDateTime = endDate.ToUniversalTime(); // Call the query service with the query retrieved // from the metadata service. var dataSet = client.ExecuteQuery(MetadataServiceQueryMetadata2QueryServiceQueryMetadata(queryMetadata), ref paging); var testEmployee = dataSet.Tables["TestEmployee"]; // Loop through the TestEmployee table and display the results. if (testEmployee == null || testEmployee.Rows.Count <= 0) return; foreach (DataRow row in testEmployee.Rows) { Console.WriteLine(row["EmployeeId"] + "\t" + row["FirstName"] + "\t" + row["LastName"] + "\t" + row["ValidFrom"] + "\t" + row["ValidTo"]); } Console.ReadLine(); } static QueryServiceReference.QueryMetadata MetadataServiceQueryMetadata2QueryServiceQueryMetadata(AxMetadataService.QueryMetadata metadata) { using (var memoryStream = new MemoryStream()) { var mdsSerializer = new DataContractSerializer(typeof(AxMetadataService.QueryMetadata)); var qsSerializer = new DataContractSerializer(typeof(QueryServiceReference.QueryMetadata)); mdsSerializer.WriteObject(memoryStream, metadata); memoryStream.Seek(0, SeekOrigin.Begin); var queryMetadataQs = (QueryServiceReference.QueryMetadata)qsSerializer.ReadObject(memoryStream); return queryMetadataQs; } } } }
命名空间TestQueryServiceDateEffect.AxMetadataService是我们引入的元数据服务,TestQueryServiceDateEffect.QueryServiceReference则是引入的查询服务,和上面的例子一样,需要在两个命名空间对Query metadata实例进行转换,这里创建了函数MetadataServiceQueryMetadata2QueryServiceQueryMetadata()来做这个动作,另外还需要注意需要使用DateTime.ToUniversalTime()转换为UTC时间传入。
- 动态查询:使用动态查询首先需要在X++中从AifQueryBuilder类扩展自己的查询类,然后在c#中使用QueryServiceClient.ExecuteDynamicQuery()调用X++的查询类,还是来看具体的例子。
[DataContractAttribute] class CustTransQueryBuilderArgs extends AifQueryBuilderArgs { CustAccount custAccount; } [DataMemberAttribute] public CustAccount parmCustAccount(CustAccount _custAccount = custAccount) { custAccount = _custAccount; return custAccount; }
首先在X++中定义了一个继承自AifQueryBuilderArgs的类CustTransQueryBuilderArgs,它带一个参数方法来指定后续查询要使用的客户编号。
[DataMemberAttribute] public CustAccount parmCustAccount(CustAccount _custAccount = custAccount) { custAccount = _custAccount; return custAccount; } [DataMemberAttribute] public CustAccount parmCustAccount(CustAccount _custAccount = custAccount) { custAccount = _custAccount; return custAccount; } [SysEntryPointAttribute()] public void initialize() { QueryBuildDataSource qbds; ; query = new Query(); query.addDataSource(tableNum(CustTrans)); qbds = query.dataSourceTable(tableNum(CustTrans)); qbds.addRange(fieldNum(CustTrans,AccountNum)).value(args.parmCustAccount()); queryRun = new QueryRun(query); }
在X++中又定义了一个继承自AifQueryBuilder的类CustTransQueryBuilder,重载方法setArgs()处理传入的查询参数类对象,重载方法initialize()初始化Query和QueryRun对象。在转到c#之前,需要正确编译X++代码并做一次“Incremental CIL”编译,否则在后面引用服务后会找不到参数类CustTransQueryBuilderArgs。和前面一样,在c#工程中添加查询服务的引用,C#程序如下:
using System; using System.Data; using TestQueryServiceDynamic.QueryServiceReference; namespace TestQueryServiceDynamic { class Program { static void Main(string[] args) { var client = new QueryServiceClient(); Paging paging = null; var custTransArgs = new CustTransQueryBuilderArgs() { parmCustAccount = "3013" }; var dataSet = client.ExecuteDynamicQuery("CustTransQueryBuilder", custTransArgs, ref paging); var custTrans = dataSet.Tables[0]; // Loop through the TestEmployee table and display the results. if (custTrans == null || custTrans.Rows.Count <= 0) return; foreach (DataRow row in custTrans.Rows) { Console.WriteLine(row["TransDate"] + "\t" + row["AccountNum"] + "\t" + row["AmountMST"] ); } Console.ReadLine(); } } }
不管是哪种查询服务,只有通过验证的用户可以使用查询服务,查询服务使用windows继承验证,所能访问的数据则是依据用户的角色,如果用户没用相应datasource的访问权限,查询服务返回失败错误,如果只是没有表中的某个字段,相应字段将返回空值(DbNull),但是如果这些不能访问的字段出现在数据源连接(Join)、Order by、Group by或者Datasource range中,整个查询服务都将返回失败错误。