代码之家 › 专栏 › 技术社区 › Haminteu

将SQL递归到TreeView C#ASP。净额

sql-server asp.net c#

Haminteu · 技术社区 · 8 年前

我有以下存储过程:

ALTER PROCEDURE [dbo].[sp_AssetRegisterTreeGet]
    (@AssetID INT)
AS
BEGIN
    SET NOCOUNT ON;

    WITH results AS
    (
        SELECT
            AssetID, ParentAssetID, AssetNumber 
        FROM
            T_AssetRegister 
        WHERE 
            AssetID = @AssetID
        UNION ALL
        SELECT
            t.AssetID, t.ParentAssetID, t.AssetNumber 
        FROM
            T_AssetRegister t
        INNER JOIN
            results r ON r.ParentAssetID = t.AssetID
    )
    SELECT * 
    FROM results 
    ORDER BY AssetID
    OPTION (maxrecursion 0);
END

结果如下所示:

AssetID   ParentAssetID     AssetNumber
----------------------------------------------
1         NULL              root
2         1                 Group
3         2                 Group - A

如何使用上面的存储过程将其加载到 <asp:TreeView> 在ASP中。NET使用C#?

1 回复 | 直到 8 年前

Haminteu 8 年前

我得到了答案:

DataTable dtTree = new DataTable();
public void CollTree()
    {
        using (SqlConnection conn = new SqlConnection((ConfigurationManager.ConnectionStrings["sconn"].ConnectionString)))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "AssetRegisterTreeGet";
            cmd.Parameters.Add("@AssetID", SqlDbType.Int).Value = Request.QueryString["Id"].ToString();
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dtTree);
            da.Dispose();
            cmd.Dispose();
            conn.Dispose();
        }
        AddNodes(-2147483648, TreeView1.Nodes);
    }

    void AddNodes(int id, TreeNodeCollection tn)
    {
        foreach (DataRow dr in dtTree.Select("ParentAssetID = " + id))
        {
            TreeNode sub = new TreeNode(dr["AssetNumber"].ToString(), dr["AssetID"].ToString());
            tn.Add(sub);
            AddNodes(Convert.ToInt32(sub.Value), sub.ChildNodes);
        }
    }

哪里 -2147483648 是根id。
非常感谢。