在SQL Server中巧妙存储树形结构数据及ASP.Net javaScript无刷新树形GridView的实现
一.Asp.net提供的树形控件能展示树形层次，但点击
查看当前结点的子结点时造成aspx页面代码执行，重新读取数据库，重新刷新页面，这里javaScript无刷新指的是树形结构数据一次加载完成，点击展开和
折叠子结点时通过javaScript完成，无页面代码执行，无数据获取操作，无页面刷新
二. Asp.net提供的 GridView控件只能展示二维的表格信息，如下图所示
这里树形GridView指的是除了显示多列数据外，还可显示多行数据间树形层次关系
如下面的两幅图中GridView有三列，其中分类名称列是树形结构数据
三.在存储树形结构数据时，一般按下面的方式建表
按上面的方式建表，如使用Asp.net的树形控件加载数据时，就得写递归函数，递归加载父结点的子结点
有一种巧妙的方式在上面所建的表中再增加一列，如下图所示
新增加的列叫parentPath,记录了从根结点到子结点所经过的所有结点ID,例如从根结点五华区到云南大学子结点所经过的结点为
五华区—一二一大街—云南大学，所以parentPath为,5,7,8
新增加的列parentPath目的是方便读取树形结构的数据，只需一条简单的SQL语句就可将树形结构信息提取出来
SELECT * FROM 含parentPath列的表
order by parentPath
通过一句order by parentPath简单高效提取了树形结构信
四.下面是笔者开发的电子商务类网站项目中的代码
下面是建表的SQL语句
CREATE TABLE [dbo].[sms_locationClass](
[id] [int] IDENTITY(1,1) NOT NULL,
[className] [nvarchar](100) NOT NULL,
[parentId] [int] NULL,
[parentPath] [nvarchar](500) NULL,
[depth] [int] NULL,
[orderPath] [nvarchar](500) NULL,
[orderNum] [int] NULL,
[parentPathName] [nvarchar](1000) NULL,
CONSTRAINT [PK_sms_locationClass] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
下图是数据库表结构和数据
下面是获取数据的存储过程
ALTER PROCEDURE [dbo].[sms_getlocationClasslist]
AS
CREATE TABLE #temptable
(
rownumber int IDENTITY(1,1),
id int NOT NULL,
className nvarchar(100) NOT NULL,
parentId int,
parentPath nvarchar(500),
depth int,
orderPath nvarchar(500),
orderNum int,
parentPathName nvarchar(1000)
)
INSERT INTO #temptable(id, className, parentId,
parentPath,depth,orderPath,orderNum,parentPathName)
SELECT * FROM sms_locationClass
order by sms_locationClass.orderPath
Select id,depth,parentId,className,orderNum,cast(id as nvarchar(20))+'_'+cast(depth as nvarchar(20)) as jsparameter,rownumber From #temptable
Drop Table #temptable
该存储过程执行结果如下图所示
前台页面代码
<script language="javascript" type ="text/javascript">
function hideOrShowTr(obj,currid_depth,beginrow)
{
var strarray=new Array();
strarray[0]=currid_depth.split("_")[0];
strarray[1]=currid_depth.split("_")[1];
var beginDepth=parseInt(strarray[1]) ;
var hideTr;
if(obj.style.backgroundImage.indexOf('tree_close.gif')!=-1)
hideTr=true;
else
hideTr=false;
var rowcount=document.getElementById('dg1').rows.length;
for(var i=(beginrow+1);i<rowcount;i++)
{
var showelemid=document.getElementById('dg1').rows[i].id;
var strarray1=new Array();
strarray1[0]=showelemid.split("_")[0];
strarray1[1]=showelemid.split("_")[1];
var endDepth=parseInt(strarray1[1]) ;
if(beginDepth<endDepth)
{
if(hideTr)
{
document.getElementById('dg1').rows[i].style.display="none";
}
else
{
if(endDepth==beginDepth+1)
{
document.getElementById('dg1').rows[i].style.display="";
var btnHideShow=document.getElementById("hideshowbtn"+strarray1[0]);
if(btnHideShow!=null)
{
btnHideShow.style.backgroundImage="url(../images/tree_open.gif)";
}
}
}
}
else
{
break;
}
}
if(hideTr)
{
obj.style.backgroundImage="url(../images/tree_open.gif)";
}
else
{
obj.style.backgroundImage="url(../images/tree_close.gif)";
}
}
</script>
<asp:GridView id="dg1" width="100%" AutoGenerateColumns="False" EnableViewState="False" runat="server" OnRowCreated="GridView_RowCreated" BorderWidth ="0" RowStyle-BackColor ="#F6F6F6" CellPadding ="0" CellSpacing ="0" Font-Names="Verdana, Helvetica, sans-serif" Font-Size="12px" HeaderStyle-Height ="25px" HeaderStyle-CssClass ="topbg" HeaderStyle-HorizontalAlign ="center">
<Columns >
<asp:TemplateField HeaderText ="分类名称" HeaderStyle-CssClass ="B" >
<ItemTemplate >
<table cellpadding="0" cellspacing="0" border="0" width="100%">
<tr>
<td style ="height :21px;">
<%# strclass(Convert.ToInt32(DataBinder.Eval(Container.DataItem, "id")))%>
</td>
</tr>
</table>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText ="排 序" HeaderStyle-CssClass ="B" ItemStyle-Width ="75px">
<ItemTemplate >
<div style ="height :19px; width :75px; text-align :center ; padding-top :2px;">
<a href="?action=upOrder&id=<%# DataBinder.Eval(Container.DataItem, "id")%>">
上移
</a>
|
<a href="?action=downOrder&id=<%# DataBinder.Eval(Container.DataItem, "id")%>">
下移
</a>
</div>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText ="编 辑" HeaderStyle-CssClass ="B" ItemStyle-Width ="120px">
<ItemTemplate >
<div style ="height :19px; width :120px; text-align :center ;padding-top :2px;">
<a href="?action=edit&id=<%# DataBinder.Eval(Container.DataItem,"id")%>" ><img src="../images/edit.gif" style="border:0" alt="编辑" /></a>
|
<a href="?action=del&id=<%# DataBinder.Eval(Container.DataItem, "id")%>" onclick="return isdel()"><img src="../images/no.gif" style="border:0" alt="删除" /></a>
</div>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView >
后台CS代码
绑定数据到GridView
dt_allRecord = SqlHelper.ExecuteDataset(siteInfo.Instant.connectionString
, CommandType.StoredProcedure, "sms_getlocationClasslist").Tables[0];
dv_allRecord = new DataView(dt_allRecord);
this.dg1.DataSource = dt_allRecord;
this.dg1.DataBind();
GridView绑定列调用的函数
<%# strclass(Convert.ToInt32(DataBinder.Eval(Container.DataItem, "id")))%>
public string strclass(int classid)
{
if (classid == 0)
{
dv_allRecord.RowFilter = "";
}
else
{
dv_allRecord.RowFilter = "id=" + classid.ToString();
}
string classname = "";
System.Data.DataView dv_temp = new DataView(dt_allRecord);
dv_temp.RowFilter = "parentId=" + classid;
int childCount = dv_temp.Count;
classname += "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
classname += "<tr>";
for (int j = 0; j < dv_allRecord.Count; j++)
{
DataRowView dr = dv_allRecord[j];
int depth;
depth = Convert.ToInt32(dr["depth"]);
if (depth > 0)
{
for (int i = 1; i <= depth; i++)
{
#region .....
classname += "<td style =\"width :33px; height :21px; background-color :#F6F6F6;\"></td>";
if (i == depth)
{
if (childCount > 0)
{
classname += "<td style =\"width :21px; height :21px; background-image :url(../images/tree_close.gif);\" onclick=\"hideOrShowTr(this,'" + dr["jsparameter"].ToString() + "'," + dr["rownumber"].ToString() + ")\" id=\"hideshowbtn" + classid + "\"></td>";
}
else
{
classname += "<td style =\"width :21px; height :21px; background-image :url(../images/treeleaf.gif);\"></td>";
}
}
#endregion
}
}
else
{
if (childCount > 0)
{
classname += "<td style =\"width :21px; height :21px; background-image :url(../images/tree_close.gif);\" onclick=\"hideOrShowTr(this,'" + dr["jsparameter"].ToString() + "'," + dr["rownumber"].ToString() + ")\" id=\"hideshowbtn" + classid + "\"></td>";
}
}
classname += "<td align =\"left\">";
if (Convert.ToInt32(dr["parentId"]) == 0)
{
classname += "<b>";
}
classname += dr["className"].ToString();
if (childCount > 0)
classname += "(子类数:" + childCount + ")";
classname += "</td>";
}
classname += "</tr>";
classname += "</table>";
return classname;
}
