代码改变世界

树形表的读取

2012-09-25 17:13  露珠的微笑  阅读(332)  评论(0)    收藏  举报

写下来，怕以后做项目遇到同样的问题，又忘记要怎么做！初学者，加上自己是一个笨女孩。。

嘻嘻，选择程序员，喜欢这个行业，每天都可以学到新东西感觉很开心，只要进步就感觉开心！

添加和删除做的不够好。。传到数据库再传到页面。。。

可以用JQUERY进行页面添加删除。。。

 

显示的见面如下，可无限增加任意修改树形结构的内容

树的内容在数据库表中的存储如下：

表中的部分内容：

         

                       

父子关系是用strid来识别的

思路：先读取第一级的叶子，即所有的父亲，然后挨个父亲按二叉树的先序递归进行遍历

读取树形存储过程:

View Code 
-- =============================================
-- Author:        Qiuhua,huang
-- Create date: 17/19/2012
-- Description:    ge serveyCateName for "industry" select
-- =============================================
--pro_getServeyCate 'null'
ALTER PROCEDURE [dbo].[pro_getServeyCate]
@striID varchar(200)='null'
AS
BEGIN
declare @strid1 varchar(200)

if @striID='null'
    begin
    select * from SurveyCate_1 where StrID like '____'
    end
    
IF @striID!='null'
    begin
        set @strid1=@striID+'____'
        select * from SurveyCate_1 where StrID like ''+@strid1+''ORDER BY StrID
    end
END

 添加节点存储过程

View Code 
USE [survey]
GO
/****** Object:  StoredProcedure [dbo].[pro_InsertSurveryCate_1]    Script Date: 11/27/2012 17:26:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Qiuhua,huang
-- Create date: 17/09/2012
-- Description:    insert into SurveyCate_1
-- =============================================
--pro_InsertSurveryCate_1 '','test20120924000'
ALTER PROCEDURE [dbo].[pro_InsertSurveryCate_1]
@strid varchar(200),
@name varchar(50)

as
BEGIN
 declare @tbname varchar(50)
 declare @sql1 VARCHAR(3000)
 declare @strid1 varchar(200)    
 declare @strID2 varchar(200)
 declare @strID3 varchar(200)
 declare @sql2 varchar(3000)
 declare @sql3 varchar(3000)
 declare @intMax int
 declare @ChildStrID varchar(200)



set @sql1='update SurveyCate_1 set IsLeaf = 0 where StrID='''+@strid+'''  '
set @strID3=@strid+'____'
select top 1 @strID2=StrID from SurveyCate_1 where StrID like ''+ @strID3+''  order by StrID desc


IF @strID2 IS Null
    begin    
     set @intMax=1
     set @strid1=@strid+left('0000',4-len(Convert(varchar,@intMax)))+Convert(varchar,@intMax)
     set @sql2='insert into SurveyCate_1(StrID,Name,IsLeaf) 
                           values('''+
                                  @strid1+''','''+
                                  @name+''''+
                                  ',1)'
    end
    
else
    begin
        set @intMax=convert(int,right(@strID2,4))+1
        set @strid1=@strid+left('0000',4-len(Convert(varchar,@intMax)))+Convert(varchar,@intMax)
        set @sql2='insert into SurveyCate_1(StrID,Name,IsLeaf) 
                           values('''+
                                  @strid1+''','''+
                                  @name+''''+
                                  ',1)'
    end


                 
print @sql1
print @sql2
print @sql3
exec(@sql1+@sql2+@sql3)                                       
END

删除节点存储过程

View Code 
USE [survey]
GO
/****** Object:  StoredProcedure [dbo].[pro_DeleteSurveyCate_1]    Script Date: 11/27/2012 17:41:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Qiuhua,huang
-- Create date: 18/08/2012
-- Description:    delete SurveyCate_1/SurveyCate_1
-- =============================================
--pro_DeleteSurveyCate_1 '0013'
ALTER PROCEDURE [dbo].[pro_DeleteSurveyCate_1]
@strID VARCHAR(200)


AS
BEGIN
    declare @tbname varchar(50)
    DECLARE @strid1 varchar(200)
    declare @sql1 varchar(3000)
    declare @sql2 varchar(3000)
    declare @sql3 varchar(3000)
    declare @sql4 varchar(3000)
    
    set @sql1='delete from SurveyCate_1 where StrID like '''+@strID +'%'''
    select top 1 @strid1=strID from SurveyCate_1 where StrID like left(@StrID,len(@StrID)-4)+'____'''
    IF  @strid1='' and len(@strID)>4 
        BEGIN
         set @sql2 ='update SurveyCate_1 set IsLeaf = 1 where StrID = '''+left(@StrID,len(@StrID)-4)+''''
        END
    
    
    IF len(@strID)=4
        BEGIN
            set @sql3='delete from SurveyCate_2 where StrID like '''+@strID +'%'''
            select top 1 @strid1=strID from SurveyCate_2 where StrID like left(@StrID,len(@StrID)-4)+'____'''
            IF  @strid1='' and len(@strID)>4 
            set @sql4 ='update SurveyCate_2 set IsLeaf = 1 where StrID = '''+left(@StrID,len(@StrID)-4)+''''
    
        END
    
    
    
            
    
    EXEC(@sql1+@sql2+@sql3+@sql4)
        
    
END

c#后台代码：

View Code 
// ============================================================
// Created By:Qiuhua,Huang, Survey_20120925, 25/09/2012
// ============================================================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;

using Survey.publicCS;
using BLL;
using DAL;


namespace Survey
{
    public partial class Survey_photo :BasePage
    {
        //EN:Template stored path
        //CN: 模板存放路径
        private string modelFile = "Survey_photo.htm";

        //EN:Define an application environment parameter object
        //CN:定义一个应用环境参数对象
        private Triwow.Common.Engineer.ApplicationParam ap = null;

        //EN:Define a template manager
        //CN:定义一个模板管理器
        Ader.TemplateEngine.TemplateManager tm = null;

        private int level = 0;
        public StringBuilder Menu = new StringBuilder("<div><br /><table><tbody><tr><td><a href='#' onclick='getCate(\"\",\"Root\")'>Root</a></td></tr>");



        protected void Page_Load(object sender, EventArgs e)
        {
            ap = new Triwow.Common.Engineer.ApplicationParam(typeof(Survey.Survey_photo));
            ap.TitleName = Triwow.Common.Config.ApplicationConfiguration.GetSetting("WebPageTitle", "Survey_photo");
            ap.CssFiles = "";//多个可用分号分隔
            ap.JSFiles = "Js/jquery-1.4.2.js;JsForPage/Survey_photo.js";//多个可用分号分隔

            //EN:Reads the template through the template engine
            //CN:通过模板引擎读取模板
            tm = Ader.TemplateEngine.TemplateManager.FromFile(Server.MapPath(ap.TemplatePath + modelFile));
            tm.SetValue("appParam", ap);

            Response.Write(tm.Process());
        }


        //get surveycate2 for treeview
//strid为“null”时读所有父结点
        [AjaxPro.AjaxMethod]
        public String Create_SurveyCate2(string stridd)
        {

            object[] obj = { stridd };
            BLL_SurveyCate2 surveycate2 = new BLL_SurveyCate2();
            List<SurveyCate_2> parents = surveycate2.GetSurveyCate2(BasePage.getConn(), obj);
            if (parents.Count > 0)
            {
                level++;//结点所在级别
                StringBuilder nbps = new StringBuilder();
                for (int i = 0; i <= level; i++)
                {
                    nbps.Append("&nbsp;&nbsp;&nbsp;&nbsp;");
                }
                foreach (SurveyCate_2 dr in parents)
                {

                    string child = "<tr><td>" + nbps.ToString() +
                                    "<a href='#' onclick='getCate(\"" + dr.StrID + "\"" + ",\"" + dr.Name + "\");'>"
                                    + dr.Name + "</a></tr></td>";


                    Menu.Append(child);
                    string s = dr.StrID;
                    Create_SurveyCate2(dr.StrID);

                }
            }
            else
            {
                return "";

            }


            level--;
            Menu.Append("<tbody><table></div></ul>");
            return Menu.ToString();

        }


        [AjaxPro.AjaxMethod]
        public string Add_Question(string strid, string name)
        {
            try
            {
                BLL_SurveyCate2 surveycate2 = new BLL_SurveyCate2();
                object[] obj = { strid, name };
                surveycate2.AddSurveyQuestion(BasePage.getConn(), obj);
                return Create_SurveyCate2("null");

            }
            catch (Exception e)
            {
                return "0";
            }
        }


        [AjaxPro.AjaxMethod]
        public string Edit_SurveyQuestion(string strid, string name)
        {
            try
            {
                BLL_SurveyCate2 surveycate2 = new BLL_SurveyCate2();
                object[] obj = { strid, name };
                surveycate2.EditSurvey(BasePage.getConn(), obj);
                return Create_SurveyCate2("null");
            }
            catch (Exception e)
            {
                return "0";
            }
        }


        [AjaxPro.AjaxMethod]
        public string Delete_Survey(string strid)
        {
            try
            {
                BLL_SurveyCate2 surveycate2 = new BLL_SurveyCate2();
                object[] obj = { strid };
                surveycate2.DelteSurvey(BasePage.getConn(), obj);
                return Create_SurveyCate2("null");
            }
            catch (Exception e)
            {
                return "0";
            }
        }



        [AjaxPro.AjaxMethod]
        public string showSurveyCate()
        {


            return Create_SurveyCate2("null");
        }




    }
}

JS文件Survey_photo.js：

View Code 
$(document).ready(function() {
    var h = Survey.Survey_photo.showSurveyCate().value;
    $("#surveycate2").html(h);
});

function getCate(strID, name) {
    $("#edittext").val(name);
    $(".name").text(name);

    $("#strid").val(strID);
    

}


function addSurvey2() {
    var strid = $("#strid").val();
    var name = $("#addtext").val();
    var h = Survey.Survey_photo.Add_Question(strid, name).value;
    if (h != "0") {
        alert("successfully!");
        $("#surveycate2").html(h);
        $("#addtext").val(" ");
        $("#edittext").val(" ");
        $(".name").text("Root ");
    }
    if (h == "0") {
        alert("error!");
    }
}


function editFun() {
    var strid = $("#strid").val();
    var name = $("#edittext").val();
    var h = Survey.Survey_photo.Edit_SurveyQuestion(strid, name).value;
    if (h != "0" && name != "Root") {
        alert("successfully!");
        $("#surveycate2").html(h);
        $("#addtext").val(" ");
        $("#edittext").val(" ");
        $(".name").text("Root ");
    }
    if (h == "0") {
        alert("error!");
    }

    if (name == "Root") {
        alert("can't edit Root");
    }
}


function delFun() {
    var name=$(".name").text();
    var strid = $("#strid").val();
    var h = Survey.Survey_photo.Delete_Survey(strid).value;
    if (h != "0" && name!="Root") {
        alert("successfully!");
        $("#surveycate2").html(h);
        $("#addtext").val(" ");
        $("#edittext").val(" ");
        $(".name").text("Root");
    }
    if (h == "0") {
        alert("error!");
    }
    if (name == "Root") {
        alert("can't delete Root");
    }
}

静态模板Survey_photo.htm：

View Code 
<!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>
    <title>$appParam.TitleName$</title>
            $appParam.CssFiles$ 
            $appParam.AjaxProClientCode$
            $appParam.JSFiles$
</head>
<body>
<table>
<tr><td colspan="2">Category:<span class="name"></span></td></tr>
<tr><td>Add phto Category:</td><td><input type="hidden" id="strid" value=""/><input type="text" id="addtext" name="text" /><input type="button" id="addBtn" value="ADD" onclick="addSurvey2()" />Add Phto Category's suborder</td></tr>
<tr><td>Edit phto Category:</td><td><input type="text" id="edittext" name="text" /><input type="button" id="editBtn"  value="Edit" onclick="editFun()"/></td></tr>
<tr><td>Del Phto Category:</td><td><span class="name"></span><input type="button" id="delBtn" value="Delete" onclick="delFun()"/>Edit Phto Category</td></tr>
</table>

<div id="surveycate2"></div>
</body>
</html>