• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

在SQL Server中巧妙存储树形结构数据及ASP.Net javaScript无刷新树形GridView的实现 ...

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

      .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 /></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 = ";

        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;
}

 

 

 

 


 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
Web.ConfigTransformationASP.NET4.0新特性发布时间:2022-07-10
下一篇:
收集的资料共享出来(五)--Asp.Net 权限解决办法发布时间:2022-07-10
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap