--测试数据 深度排序 DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10)) INSERT @t SELECT '001',NULL ,'山东省' UNION ALL SELECT '002','001','烟台市' UNION ALL SELECT '004','002','招远市' UNION ALL SELECT '003','001','青岛市' UNION ALL SELECT '005',NULL ,'四会市' UNION ALL SELECT '006','005','清远市' UNION ALL SELECT '007','006','小分市'
--深度排序显示处理 --生成每个节点的编码累计(相同当单编号法的编码) DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000)) DECLARE @Level int SET @Level=0 INSERT @t_Level SELECT ID,@Level,ID FROM @t WHERE PID IS NULL WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID FROM @t a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1 END
--显示结果 SELECT a.* FROM @t a,@t_Level b WHERE a.ID=b.ID ORDER BY b.Sort /*--结果 ID PID Name ------ --------- ---------- 001 NULL 山东省 002 001 烟台市 004 002 招远市 003 001 青岛市 005 NULL 四会市 006 005 清远市 007 006 小分市 --*/--查询指定节点及其所有子节点的函数 CREATE FUNCTION f_Cid(@ID char(3)) RETURNS @t_Level TABLE(ID char(3),Level int) AS BEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @ID,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level FROM tb a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1 END RETURN END GO
--调用函数查询002及其所有子节点 SELECT a.* FROM tb a,f_Cid('002') b WHERE a.ID=b.ID /*--结果 ID PID Name ------ ------- ---------- 002 001 烟台市 004 002 招远市 --*/
写了一个DataTable数据源的
dropdownlist ID:AreaSource
public partial class _Default : System.Web.UI.Page
{
DataView dv;
/// <summary>
/// 层次分割符
/// </summary>
const string STR_TREENODE = "┆┄";
/// <summary>
/// 顶级父节点parentid
/// </summary>
const int INT_TOPID = 0; const string STR_ID = "id";
const string STR_PARENTID = "parentid";
const string STR_DISPLAYNAME = "typename"; protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
dv = new DataView(this.AreaSource);
dv.Sort = STR_PARENTID;
string schar = STR_TREENODE;
if (dv.Table.Rows.Count > 0)
{
RecursBind(INT_TOPID, ref schar);
}
}
} /// <summary>
/// 递归绑定DropDownList
/// </summary>
/// <param name="pid"></param>
/// <param name="schar"></param>
private void RecursBind(int pid,ref string schar)
{
DataRowView[] rows = dv.FindRows(pid);
if (rows.Length == 0) schar = STR_TREENODE;
foreach (DataRowView row in rows)
{
if (pid != 0)
{
schar += STR_TREENODE;
}
this.AreaList.Items.Add(new ListItem(schar + row[STR_DISPLAYNAME].ToString(), row[STR_ID].ToString()));
//Response.Write(schar + row[STR_DISPLAYNAME].ToString() + "<br>");
RecursBind(Convert.ToInt32(row[STR_ID]), ref schar);
}
} /// <summary>
/// 测试数据源
/// </summary>
private DataTable AreaSource
{
get {
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn(STR_ID, typeof(int)));
dt.Columns.Add(new DataColumn(STR_PARENTID, typeof(int)));
dt.Columns.Add(new DataColumn(STR_DISPLAYNAME, typeof(string))); dt.Rows.Add(new object[] { 1, 0, "湖北" });
dt.Rows.Add(new object[] { 2, 0, "江苏" });
dt.Rows.Add(new object[] { 3, 0, "浙江" });
dt.Rows.Add(new object[] { 4, 1, "黄冈" });
dt.Rows.Add(new object[] { 5, 4, "黄冈镇级" });
dt.Rows.Add(new object[] { 6, 5, "黄冈村" });
dt.Rows.Add(new object[] { 7, 3, "杭州" });
dt.Rows.Add(new object[] { 8, 2, "南京" });
return dt;
}
}
}
{
try
{
this.datasetRoleToModule1.Tables["AdminModule"].Clear();
this.dataEntity.FillTableAdminModule(this.datasetRoleToModule1.Tables["AdminModule"], this.cmbGroup.Value.ToString());
DataView dv = new DataView( this.datasetRoleToModule1.Tables["AdminModule"]); dv.RowFilter = " LEN(GradeCode) = 3 ";
dv.RowStateFilter = DataViewRowState.CurrentRows;
dv.Sort = "GradeCode ASC";
DataRowView rv;
for(int i=0;i < dv.Count;i++)
{
rv = dv[i];
TreeNode newNode = new TreeNode();
newNode.Tag = rv;
newNode.Text = rv["ModuleName"].ToString().Trim();
newNode.ImageIndex = 0;
newNode.SelectedImageIndex = 1;
object[] key = new object[2];
key[0] = this.RoleCode;
key[1] = rv["ModuleCode"]; DataRow foundRow;
foundRow = this.datasetRoleToModule1.Tables["AdminRoleToModule"].Rows.Find(key);
if(foundRow != null)
newNode.Checked = true;
else
newNode.Checked = false;
node.Nodes.Add(newNode);
AddSubModules(newNode);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
private void AddSubModules(TreeNode node)
{
try
{
DataView dv = new DataView( this.datasetRoleToModule1.Tables["AdminModule"]);
DataRowView rv;
rv = (DataRowView)node.Tag;
dv.RowFilter = "SUBSTRING(GradeCode, 1, LEN(GradeCode) - 3) = '" + rv["GradeCode"] + "'";
dv.RowStateFilter = DataViewRowState.CurrentRows;
dv.Sort = "GradeCode ASC";
for(int i=0;i < dv.Count;i++)
{
rv = dv[i];
TreeNode newNode = new TreeNode();
newNode.Tag = rv;
newNode.Text = rv["ModuleName"].ToString().Trim();
newNode.ImageIndex = 0;
newNode.SelectedImageIndex = 1;
object[] key = new object[2];
key[0] = this.RoleCode;
key[1] = rv["ModuleCode"]; DataRow foundRow;
foundRow = this.datasetRoleToModule1.Tables["AdminRoleToModule"].Rows.Find(key);
if(foundRow != null)
newNode.Checked = true;
else
newNode.Checked = false;
node.Nodes.Add(newNode);
AddSubModules(newNode);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
--测试数据 深度排序
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/