存储过程 With子查询

创建表：tb_loc 字段

列名	描述
location_id	地区编号
location_name	地区名称
parentlocation_id	上级地区编号

CREATE TABLE [tb_loc](
    [id] [int],
    [name] [varchar](16),
    [parent] [int]
)

GO

INSERT tb_loc(id,name,parent) VALUES( 1,'河北省',NULL)
INSERT tb_loc(id,name,parent) VALUES( 2,'石家庄',1)
INSERT tb_loc(id,name,parent) VALUES( 3,'保定',1)
INSERT tb_loc(id,name,parent) VALUES( 4,'山西省',NULL)
INSERT tb_loc(id,name,parent) VALUES( 5,'太原',4)
INSERT tb_loc(id,name,parent) VALUES( 6,'新华区',2)
INSERT tb_loc(id,name,parent) VALUES( 7,'北焦村',6)
INSERT tb_loc(id,name,parent) VALUES( 8,'大郭村',6)
INSERT tb_loc(id,name,parent) VALUES( 9,'河南省',NULL)
INSERT tb_loc(id,name,parent) VALUES( 10,'大郭村南',8)
INSERT tb_loc(id,name,parent) VALUES( 11,'大郭村北',8)
INSERT tb_loc(id,name,parent) VALUES( 12,'北焦村东',7)
INSERT tb_loc(id,name,parent) VALUES( 13,'北焦村西',7)
INSERT tb_loc(id,name,parent) VALUES( 14,'桥东区',3)
INSERT tb_loc(id,name,parent) VALUES( 15,'桥西区',3)
GO

创建存储过程：

CREATE PROCEDURE pr_GetLocations
AS
BEGIN
    WITH locs(id,name,parent,loclevel)
    AS
    (
        SELECT id,name,parent,0 AS loclevel FROM tb_loc
        WHERE parent IS NULL
        UNION ALL
        SELECT l.id,l.name,l.parent,loclevel+1 FROM tb_loc l
            INNER JOIN locs p ON l.parent=p.id
    )

    SELECT * FROM locs
END

新建asp.net 网站

实体类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for Location
/// </summary>
public class Location
{
public Location()
{
        Id = 0;
        Name = string.Empty;
        ParentId = 0;
        SubLocations = new LocationCollection();
}
    public int Id { get; set; }
    public string Name { get; set; }
    public int ParentId { get; set; }
    public LocationCollection SubLocations { get; set; }
}

创建LocationCollection集合类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for LocationCollection
/// </summary>
public class LocationCollection:List<Location>
{
public LocationCollection()
{
  //
  // TODO: Add constructor logic here
  //
}
}

创建DAO数据访问：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DAO
/// </summary>
public class DAO
{
public DAO()
{
  //
  // TODO: Add constructor logic here
  //
}
    /// <summary>
    /// 7编写GetLocations方法，返回所在地集合对象（请根据实际情况修改数据库连接字符串）：
    /// </summary>
    /// <returns></returns>
    public LocationCollection GetLocations()
    {
        LocationCollection locs = new LocationCollection();
        using (SqlConnection conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=test;Integrated Security=True"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "pr_GetLocations";
            cmd.Connection = conn;
            SqlDataReader reader = cmd.ExecuteReader();

            int level = 0;
            int oldlevel = 1;
            LocationCollection container = new LocationCollection();
            LocationCollection current = new LocationCollection();

            while (reader.Read())
            {
                Location loc = GetLocationFromReader(reader, out level);
                if (level == 0)
                {
                    locs.Add(loc);
                    container.Add(loc);
                }
                else
                {
                    if (oldlevel != level)
                    {
                        container.Clear();

                        foreach(Location l in current)
                        {
                            container.Add(l);//目的是为了3.1步
                        }
                        current.Clear();
                        oldlevel = level;
                    }
                    current.Add(loc);//当前级别是当前级别的所有条信息，以备下次循环调用parentid
                    foreach (Location m in container)//3.1
                    {//3.1
                        if (m.Id == loc.ParentId)//3.1
                        {
                            m.SubLocations.Add(loc);//3.1
                        }//3.1
                    }//3.1
                }
            }

        };
        return locs;
    }
    /// </summary>
    /// <param name="reader"></param>
    /// <param name="level"></param>
    /// <returns></returns>
    private Location GetLocationFromReader(SqlDataReader reader, out int level)
    {
        Location loc = new Location();
        loc.Id = Convert.ToInt32(reader["id"]);
        loc.Name = Convert.ToString(reader["name"]);

        object o = reader["parent"];
        if (o != DBNull.Value)
            loc.ParentId = Convert.ToInt32(o);

level = Convert.ToInt32(reader["loclevel"]);

        return loc;
    }
    /// <summary>
    /// 编写CreateLocation方法，该方法遍历实体集合找到与当前实体对象的父级编号匹配的实体，并将当前实体加入到父级实体的子集合中：
    /// </summary>
    /// <param name="container"></param>
    /// <param name="loc"></param>
    private void CreateLocation(LocationCollection container, Location loc)
    {
        foreach (Location location in container)
        {
            if (location.Id == loc.ParentId)
            {
                location.SubLocations.Add(loc);
                break;
            }
        }
    }

}

前台代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.BindData();
        }
    }
    //Default.aspx页面后置代码中编写BindData数据绑定方法：
    private void BindData()
    {
        DAO dao = new DAO();

LocationCollection locs = dao.GetLocations();

TreeNodeCollection nodes = CreateTreeNodes(locs);

        foreach (TreeNode node in nodes)
        {
            trvLocation.Nodes.Add(node);
        }
    }
    //BindData方法调用了CreateTreeNode方法返回节点集合，该方法中递归调用自身以得到全部所在地节点：
    private TreeNodeCollection CreateTreeNodes(LocationCollection locs)
    {
        TreeNodeCollection nodeColl = new TreeNodeCollection();

        foreach (Location loc in locs)
        {
            TreeNode node = new TreeNode(loc.Name, loc.Id.ToString());

            if (loc.SubLocations.Count > 0)
            {
                TreeNodeCollection subColl = CreateTreeNodes(loc.SubLocations);

                foreach (TreeNode subNode in subColl)
                    node.ChildNodes.Add(subNode);
            }

nodeColl.Add(node);
}

return nodeColl;
}

}

结果：

结束成功！