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();   -->定义结束中括号"]"

jsonWriter.WriteValue(Value); -->定义Value

代码示例

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();
        
    }

public void writeBodyObject(Common _common, NSRPlanetPressDocumentType _planetPressType)
    {
        NSRPlanetPressMappingTable  planetPressMappingTable;
        NSRPlanetPressMappingField  planetPressMappingField;
        DictTable                   tableMetadata;
        int                         fieldCount;
        ;

        tableMetadata           = new DictTable(_common.TableId);
        planetPressMappingTable = NSRPlanetPressMappingTable::find(tableMetadata.name(), _planetPressType);

        while select planetPressMappingField
            order by StartPosition
            where planetPressMappingField.MappingTable == planetPressMappingTable.RecId
               && planetPressMappingField.Included == NoYes::Yes
        {
            DictField field     = tableMetadata.fieldObject(planetPressMappingField.RefFieldId);
            str fieldName       = field.name();
            var fieldId         = fieldName2Id(_common.TableId, fieldName);
            jsonWriter.WritePropertyName(fieldName);
            jsonWriter.WriteValue(this.serializeField(_common, field));
        }
        /*
        fieldCount              = tableMetadata.fieldCnt();
        for (int fieldNo = 1; fieldNo <= fieldCount; fieldNo++)
        {
            if (
            DictField field     = tableMetadata.fieldObject(tableMetadata.fieldCnt2Id(fieldNo));
            str fieldName       = field.name();
            var fieldId         = fieldName2Id(_common.TableId, fieldName);
            jsonWriter.WritePropertyName(fieldName);
            jsonWriter.WriteValue(this.serializeField(_common, field));
        }
        */
    }

输出效果

{
  "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
      }
    ]
  }
}