D365: 将多个关联的表数据转换为JSON格式

最近碰到一个需求，将D365系统中的多个关联表的数据转换成JSON格式导出然后上传到blob storage，实现方式记录一下，以便将来使用

首先在调用是引用using Newtonsoft.Json，using System.IO

引用后，我们用到两个class

System.IO.StringWriter
Newtonsoft.Json.JsonTextWriter

分别定义这两个class的变量，然后进行实例化

stringWriter = new System.IO.StringWriter();
jsonWriter = new Newtonsoft.Json.JsonTextWriter(stringWriter);

开始构建JSON，这里主要介绍构建JSON用到的方法

jsonWriter.WriteStartObject(); -->定义开始大括号"{"

jsonWriter.writeEndObject();   -->定义结束大括号"}"

jsonWriter.WritePropertyName("JSON"); -->定义属性名称，

例如 "JSON": {"OrderAccount": "M-CUA-DEALER"}

jsonWriter.WriteStartArray(); -->定义开始中括号"["

jsonWriter.WriteEndArray();   -->定义结束中括号"]"

代码示例

public void createJson_Statement()
    {
        CustTrans       custTrans;
        ;

        //CustTable
        jsonWriter.WritePropertyName(new DictTable(custTable.TableId).name());
        jsonWriter.WriteStartObject();
        this.writeBodyObject(custTable, NSRPlanetPressDocumentType::Statement);
        
        //CustTrans
        jsonWriter.WritePropertyName(new DictTable(custTrans.TableId).name());
        jsonWriter.WriteStartArray();
        while select custTrans
            where custTrans.AccountNum == custTable.AccountNum
        {
            jsonWriter.WriteStartObject();
            this.writeBodyObject(custTrans, NSRPlanetPressDocumentType::Statement);
            jsonWriter.WriteEndObject();
        }
        jsonWriter.WriteEndArray();

        //Bracket
        jsonWriter.WritePropertyName(#Bracket);
        jsonWriter.WriteStartArray();
        this.writeBracketBody();
        jsonWriter.WriteEndArray();
        jsonWriter.WriteEndObject();
        
    }

输出效果

{
  "DocumentType": "Invoice",
  "DocumentSubType": "",
  "VersionNumber": "1.5",
  "DistributionMethod": {
    "Method": "Post",
    "Value": ""
  },
  "CustTable": {
    "PaymTermId": "AR14",
    "LineDisc": "",
    "TaxWithholdGroup_TH": "",
    "PartyCountry": "",
    "AccountNum": "M-CUA-TEN118SL",
    "CustTrans": [
      {
        "PostingProfileClose": "",
        "AccountingEvent": "0",
        "AccountNum": "M-CUA-TEN118SL",
        "AmountCur": "184.06",
        "AmountMST": "184.06",
      },
      {
        "PostingProfileClose": "",
        "AccountingEvent": "0",
        "AccountNum": "M-CUA-TEN118SL",
        "AmountCur": "353.71",
        "AmountMST": "353.71",
      },
      {
        "PostingProfileClose": "",
        "AccountingEvent": "0",
        "AccountNum": "M-CUA-TEN118SL",
        "AmountCur": "728.89",
        "AmountMST": "728.89",
      }
    ],
    "Bracket": [
      {
        "Bracket": "< 0",
        "Amount": 4052.04
      },
      {
        "Bracket": "0 - 30",
        "Amount": 0.0
      },
      {
        "Bracket": "30 - 60",
        "Amount": 0.0
      },
      {
        "Bracket": "60 - 90",
        "Amount": 0.0
      },
      {
        "Bracket": "90 - 120",
        "Amount": 0.0
      },
      {
        "Bracket": "120 - 150",
        "Amount": 0.0
      },
      {
        "Bracket": "> 150",
        "Amount": 0.0
      }
    ]
  }
}