存储过程 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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:treeview ID="trvLocation" runat="server" Font-Size="12px" ShowLines="True"></asp:treeview>
</div>
</form>
</body>
</html>
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;
}
}
结果：
结束成功！
