Kendo Diagram Datasource - json

Question

目标:使用kendo ui图组件绘制一个部门以项目工作流工作流的结构如下:每个部门可以有多个策略，每个策略可以有多个计划，每个计划可以有多个项目。

我有一个WCF服务，当它被调用时会返回一个json字符串。服务调用一个返回XML的存储过程，然后(使用json.net)在vb.net中将XML转换为json。

服务代码：

Public Function GetStakeholderRelationshipMatrix(StakeholderId As Integer) As String
    Try
        conn.Open()

        Dim da = New SqlDataAdapter()
        sCommand = New SqlCommand("uspGetRelationshipMatrix", conn)
        sCommand.CommandType = CommandType.StoredProcedure
        sCommand.Parameters.AddWithValue("@StakeholderId", StakeholderId)
        xmlDoc.LoadXml(sCommand.ExecuteScalar())
        Return JsonConvert.SerializeXmlNode(xmlDoc)
    Catch ex As Exception
        Return -1
    End Try
End Function

XML数据

<Departments>
  <Department Id="7" Name="Information Technology Department">
    <Strategies Id="21" Name="Increase Revenue">
      <Programs Id="45" Name="Program1">
        <Projects Id="4" Name="test3" />
      </Programs>
      <Programs Id="49" Name="Program4">
        <Projects Id="2" Name="Test1" />
        <Projects Id="3" Name="Test2" />
      </Programs>
    </Strategies>
  </Department>
</Departments>

​

Json转换后的数据：

{"d":"{\"Departments\":{\"Department\":{\"@Id\":\"7\",\"@Name\":\"Information Technology Department\",\"Strategies\":{\"@Id\":\"21\",\"@Name\":\"Increase Revenue\",\"Programs\":[{\"@Id\":\"45\",\"@Name\":\"Program1\",\"Projects\":{\"@Id\":\"4\",\"@Name\":\"test3\"}},{\"@Id\":\"49\",\"@Name\":\"Program4\",\"Projects\":[{\"@Id\":\"2\",\"@Name\":\"Test1\"},{\"@Id\":\"3\",\"@Name\":\"Test2\"}]}]}}}}"}

​

我无法将数据(json)绑定到图表组件以成功显示工作流。我需要帮助将json字符串绑定到图表组件。

剑道代码：

<div id="visual"></div>                   
var dsRelationshipMatrix = new kendo.data.DataSource({
                        transport: {
                            read: function (options) {
                                $.ajax({
                                    url: "wcf service return json string",
                                    contentType: "application/json; charset=utf-8",
                                    dataType: "json",
                                    type: "GET",
                                    success: function (result) {
                                        var obj = $.parseJSON(result.d);
                                        if (obj == null) {
                                            options.success([]);
                                        } else {
                                            options.success(obj);
                                        }
                                    },
                                    error: function (result) {
                                        options.error(result)
                                    }
                                });
                            }

                        },
                        schema: {
                            data: function (data) {
                                return data
                            }

                        }
                    }); //closes data source
                    function createDiagram() {
                        $("#visual").kendoDiagram({
                            dataSource: new kendo.data.HierarchicalDataSource({
                                data: dsRelationshipMatrix,
                                schema: {
                                    model: {
                                        children: "Strategies"
                                    }
                                }
                            }),
                            layout: {
                                type: "layered"
                            },
                            shapeDefaults: {
                                visual: visualTemplate
                            },
                            connectionDefaults: {
                                stroke: {
                                    color: "#979797",
                                    width: 2
                                }
                            }
                        });

                        var diagram = $("#visual").getKendoDiagram();
                        diagram.bringIntoView(diagram.shapes);
                    }

​

Answer 1

在经历了一段时间的痛苦之后，我得到了它的工作，并想与每个人分享这些信息。这可能不是完美的方法，但它是实现它的方法之一。关键是SQL数据的结构(提取为XML)。

在SQL中，我最终执行了一个递归循环，提取数据并将其格式化为XML。因此，根的根节点包含一个item (Department -只能有一个部门)，并且每个分层类别都有一个items部分，其中item部分作为items的子项。结构的措辞相当困难，所以……

SQL：

SELECT 
    --dStakeholder.StakeholderId AS '@Id', 
    dStakeholder.FunctionalDepartment as 'item/info' ,
    '#1696d3' as 'item/colorScheme',
    (SELECT 
        --dStrategy.StrategyId AS '@Id', 
        dStrategy.StrategyDescription 'item/info',
        '#1696d3' as 'item/colorScheme' ,
        (Select
            --dPrograms.ProgramId as '@Id',
            dPrograms.ProgramDescription 'item/info' ,
            '#1696d3' as 'item/colorScheme' ,
            (Select
                --dHeaders.ProjectId as '@Id',
                dHeaders.ProjectName  'item/info',
                '#1696d3' as 'item/colorScheme'
                    from
                    dbo.ProjectHeaders as dHeaders
                        inner join (select distinct projectid, programid, departmentid from dbo.ProjectHeaders) as pp
                            on dHeaders.ProjectId = pp.ProjectId
                        where pp.ProgramId = dPrograms.ProgramId and pp.departmentid = dStakeholder.Stakeholderid
                            FOR XML PATH(''), TYPE
            ) as 'item/items'
            from
            dbo.ProjectPrograms as dPrograms
            inner join (select distinct programid, strategyid , departmentid from dbo.ProjectHeaders) as sp
                on dPrograms.ProgramId = sp.ProgramId
            where sp.StrategyId = dStrategy.StrategyId and sp.departmentid = dstakeholder.stakeholderid
            FOR XML PATH(''), TYPE
        ) as 'item/items'
     FROM 
        dbo.ProjectStrategies as dStrategy
        inner join (select distinct strategyid , departmentid from dbo.ProjectHeaders) as bss
            on dStrategy.StrategyId = bss.StrategyId
     WHERE 
         dStakeholder.StakeholderId = bss.DepartmentId 
     FOR XML PATH(''), TYPE
    ) as 'item/items'
FROM 
    dbo.Stakeholders as dStakeholder
FOR XML PATH('root'), type  

来自sql的我的数据以这种方式出现在XML中：

​

<root>
  <item>
    <info>ERISA Compliance Services Department</info>
    <colorScheme>#1696d3</colorScheme>
    <items>
      <item>
        <info>New Commission</info>
        <colorScheme>#1696d3</colorScheme>
        <items>
          <item>
            <info>Program3</info>
            <colorScheme>#1696d3</colorScheme>
            <items>
              <item>
                <info>test3</info>
                <colorScheme>#1696d3</colorScheme>
              </item>
            </items>
          </item>
        </items>
      </item>
      <item>
        <info>SAGA Development</info>
        <colorScheme>#1696d3</colorScheme>
        <items>
          <item>
            <info>Program1</info>
            <colorScheme>#1696d3</colorScheme>
            <items>
              <item>
                <info>Test1</info>
                <colorScheme>#1696d3</colorScheme>
              </item>
            </items>
          </item>
          <item>
            <info>Program4</info>
            <colorScheme>#1696d3</colorScheme>
            <items>
              <item>
                <info>Test2</info>
                <colorScheme>#1696d3</colorScheme>
              </item>
            </items>
          </item>
        </items>
      </item>
    </items>
  </item>
</root>

​

现在我浏览一下我的xml并获取重要的信息。使用VB.net编写代码。有一个item的类，它只是列出了我需要的属性。

            conn.Open()
        Dim dt As New DataTable
        Dim da As New SqlDataAdapter("uspGetRelationshipMatrix " + Convert.ToString(StakeholderId), conn)
        da.Fill(dt)
        Dim sb = New StringBuilder()
        If dt.Rows.Count <> 0 Then
            For Each row As DataRow In dt.Rows
                sb.Append(row.Item("Data"))
            Next
        End If
        conn.Close()


        xmlDoc.LoadXml(sb.ToString)

        Dim itemList As New List(Of Item)
        Dim RootNode As XmlNode
        RootNode = xmlDoc.SelectSingleNode("/root")

        For Each DeptNode As XmlNode In RootNode.ChildNodes
            Dim deptItem As New Item
            'check to ensure that is not nothing
            Dim info As XmlNode = DeptNode.SelectSingleNode("info")
            Dim cs As XmlNode = DeptNode.SelectSingleNode("colorScheme")
            deptItem.info = info.InnerText
            deptItem.colorScheme = cs.InnerText
            'Begin items loop for child nodes
            Dim stratItems As XmlNode = DeptNode.SelectSingleNode("items")
            For Each stratItemNode As XmlNode In stratItems.ChildNodes
                Dim stratItem As New Item
                Dim stratinfo As XmlNode = stratItemNode.SelectSingleNode("info")
                Dim stratcs As XmlNode = stratItemNode.SelectSingleNode("colorScheme")
                stratItem.info = stratinfo.InnerText
                stratItem.colorScheme = stratcs.InnerText
                'place start loop
                Dim progItems As XmlNode = stratItemNode.SelectSingleNode("items")
                For Each progItemNode As XmlNode In progItems.ChildNodes
                    Dim progItem As New Item
                    Dim proginfo As XmlNode = progItemNode.SelectSingleNode("info")
                    Dim progcs As XmlNode = progItemNode.SelectSingleNode("colorScheme")
                    progItem.info = proginfo.InnerText
                    progItem.colorScheme = progcs.InnerText
                    'place start loop
                    Dim projItems As XmlNode = progItemNode.SelectSingleNode("items")
                    For Each projItemNode As XmlNode In projItems.ChildNodes
                        Dim projItem As New Item
                        Dim projinfo As XmlNode = projItemNode.SelectSingleNode("info")
                        Dim projcs As XmlNode = projItemNode.SelectSingleNode("colorScheme")
                        projItem.info = projinfo.InnerText
                        projItem.colorScheme = projcs.InnerText
                        'place start loop
                        'place end loop
                        progItem.items.Add(projItem)
                    Next
                    'place end loop
                    stratItem.items.Add(progItem)
                Next
                'place end loop
                deptItem.items.Add(stratItem)
            Next
            'end items loop
            itemList.Add(deptItem)
        Next

然后，我在vb.net中使用newtonsoft json，以便将信息表示为json字符串。Json字符串：

[  

{ERISA "info":"ERISA合规服务部“，"colorScheme":"#1696d3"，"items":[{”info“：”新委员会“，"colorScheme":"#1696d3"，"items":[{"info":"Program3"，"colorScheme":"#1696d3"，"items":[{"info":"test3"，"colorScheme":"#1696d3"，"items":[

                    ]
                 }
              ]
           }
        ]
     },
     {  
        "info":"SAGA Development",
        "colorScheme":"#1696d3",
        "items":[  
           {  
              "info":"Program1",
              "colorScheme":"#1696d3",
              "items":[  
                 {  
                    "info":"Test1",
                    "colorScheme":"#1696d3",
                    "items":[  

                    ]
                 }
              ]
           },
           {  
              "info":"Program4",
              "colorScheme":"#1696d3",
              "items":[  
                 {  
                    "info":"Test2",
                    "colorScheme":"#1696d3",
                    "items":[  

                    ]
                 }
              ]
           }
        ]
     }
  ]

}]

在此结构中设置了json的格式后，现在我只需将json插入到kendo图中，现在您就可以获得流的可视分层图像。