使用C#将SQL转TreeView JSON及存储过程转JSON适配Kendo TreeView问题

Hey there! Let's tackle this problem step by step—getting your stored procedure results into the exact nested JSON structure the AngularJS Kendo TreeView needs.

First, let's make sure we're on the same page: you need output that follows this pattern, where top-level nodes have a text field and an items array of child nodes:

{
  "data": [
    { 
      "text": "Furniture", 
      "items": [ 
        { "text": "Tables & Chairs" }, 
        { "text": "Sofas" }, 
        { "text": "Occasional Furniture" } 
      ] 
    },
    { 
      "text": "Decor", 
      "items": [ 
        { "text": "Bed Linen" }, 
        { "text": "Curtains & Blinds" }, 
        { "text": "Carpets" } 
      ] 
    }
  ]
}

Step 1: Confirm your stored procedure returns hierarchical data #

First, ensure your stored procedure outputs a flat dataset with parent-child relationships. For example, it should return columns like:

If your stored procedure doesn't return this structure, adjust it to include a parent ID field so we can map child nodes to their parents.

Option 1: Generate nested JSON directly in SQL (SQL Server 2016+) #

If you're using SQL Server, you can modify your stored procedure to output the exact JSON structure using recursive CTEs and FOR JSON PATH:

WITH CategoryHierarchy AS (
    -- Get top-level nodes (no parent)
    SELECT 
        CategoryID, 
        CategoryName AS [text], 
        ParentID
    FROM YourCategoriesTable
    WHERE ParentID IS NULL

    UNION ALL

    -- Recursively get child nodes
    SELECT 
        c.CategoryID, 
        c.CategoryName AS [text], 
        c.ParentID
    FROM YourCategoriesTable c
    INNER JOIN CategoryHierarchy ch ON c.ParentID = ch.CategoryID
)
SELECT 
    [text],
    -- Nest child nodes
    (SELECT [text] FROM CategoryHierarchy ch WHERE ch.ParentID = c.CategoryID FOR JSON PATH) AS items
FROM CategoryHierarchy c
WHERE ParentID IS NULL
FOR JSON PATH, ROOT('data')

This query will spit out the exact JSON format you need—no extra processing required on the backend or frontend.

Option 2: Process the data in your backend (e.g., C#/.NET) #

If you prefer to handle the transformation in code, fetch the flat dataset from your stored procedure, then use recursion to build the tree:

// Define a class to match your stored procedure's output
public class CategoryNode
{
    public int CategoryID { get; set; }
    public int? ParentID { get; set; }
    public string text { get; set; }
    public List<CategoryNode> items { get; set; } = new List<CategoryNode>();
}

// Build the nested tree structure
public List<CategoryNode> BuildTree(List<CategoryNode> flatNodes)
{
    var tree = new List<CategoryNode>();
    var nodeLookup = flatNodes.ToDictionary(n => n.CategoryID);

    foreach (var node in flatNodes)
    {
        if (node.ParentID == null)
        {
            tree.Add(node);
        }
        else if (nodeLookup.TryGetValue(node.ParentID.Value, out var parent))
        {
            parent.items.Add(node);
        }
    }

    // Clean up unnecessary fields (Kendo only needs text/items)
    CleanTreeNodes(tree);
    return tree;
}

private void CleanTreeNodes(List<CategoryNode> nodes)
{
    foreach (var node in nodes)
    {
        node.CategoryID = 0;
        node.ParentID = null;
        CleanTreeNodes(node.items);
    }
}

Serialize the resulting tree list to JSON, and you'll have the structure Kendo expects.

Option 3: Transform the data in AngularJS frontend #

If you want to handle the transformation client-side, fetch the flat data from your API, then use a recursive JavaScript function to build the tree:

// Assume $scope.flatCategories is the flat dataset from your API
$scope.buildTree = function(nodes, parentId = null) {
    return nodes
        .filter(node => node.ParentID === parentId)
        .map(node => ({
            text: node.CategoryName,
            items: $scope.buildTree(nodes, node.CategoryID)
        }));
};

// Generate the final tree data
$scope.treeData = {
    data: $scope.buildTree($scope.flatCategories)
};

Then bind it directly to your Kendo TreeView:

<div kendo-tree-view k-data-source="treeData.data"></div>

Quick Notes #

• Make sure special characters like & are properly escaped (e.g., &)—Kendo TreeView will handle HTML entities automatically, but double-check if you're using custom templates.
• All three options work for multi-level hierarchies (not just 2 levels), so don't worry about deep nested categories.

内容的提问来源于stack exchange，提问作者dscythe