家里的电脑上不能安装MS SQL,索性就用MySQL。顺路学习了一下使用MySql的Function。
希望使用的XDJM能在这里留个言。谢谢
数据库:
ysql> create table category(
-> categoryid int,
-> categoryname varchar(20),
-> FatherId int);
DELIMITER //
mysql> create function IsLeaf(cat_id int)
-> returns int
-> begin
-> declare count int;
-> select count(*) into count from category where fatherid=cat_id;
-> if count = 0 then
-> return 1
-> ;
-> end if;
-> return 0;
-> end;
-> //
mysql> insert into category values(1,'My Documen
-> ;
Query OK, 1 row affected (0.08 sec)
mysql> insert into category values(2,'ASP.NET',1
-> ;
Query OK, 1 row affected (0.02 sec)
mysql> insert into category values(3,'JAVA',1)
-> ;
Query OK, 1 row affected (0.03 sec)
mysql> insert into category values(4,'C#',1)
-> ;
Query OK, 1 row affected (0.04 sec)
mysql> insert into category values(5,'HTML',1)
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> insert into category values(6,'Oracle',1)
-> ;
Query OK, 1 row affected (0.02 sec)
mysql> insert into category values(7,'Line',1)
-> ;
Query OK, 1 row affected (0.02 sec)
Tree.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Tree.aspx.cs" Inherits="Tree" %>
<!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>Tree</title>
<meta name="CODE_LANGUAGE" content="C#" />
<meta name="vs_defaultClientScript" content="JavaScript" />
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5" />
<link type="text/css" href="css/tree.css" rel="stylesheet" />
</head>
<body>
<form id="Form1" method="post" runat="server">
<div class="TreeMenu" id="CategoryTree">
<h4>基于Ajax的动态树型菜单</h4>
</div>
<div id="docContent">
<div id="title" align="center"></div>
<div id="author" align="right"></div>
<div id="writetime" align="right"></div>
</div>
<div id="docList">
</div>
<script language="jscript" type="text/jscript">
function ExpandSubCategory(iCategoryID)
{
var li_father = document.getElementById("li_" + iCategoryID);
if (li_father.getElementsByTagName("li").length > 0) //分类已下载
{
ChangeStatus(iCategoryID);
displayDocList(iCategoryID);
return;
}
li_father.className = "Opened";
switchNote(iCategoryID, true);
Tree.GetSubCategory(iCategoryID, GetSubCategory_callback);
}
function GetSubCategory_callback(response)
{
var dt = response.value.Tables[0];
if (dt.Rows.length > 0)
{
var iCategoryID = dt.Rows[0].FatherID;
}
var li_father = document.getElementById("li_" + iCategoryID);
var ul = document.createElement("ul");
for (var i = 0;i < dt.Rows.length;i++)
{
if (dt.Rows[i].IsChild == 1) //叶子节点
{
var li = document.createElement("li");
li.className = "Child";
li.id = "li_" + dt.Rows[i].CategoryID;
var img = document.createElement("img");
img.id = dt.Rows[i].CategoryID;
img.className = "s";
img.src = "css/s.gif";
var a = document.createElement("a");
a.href = "javascript:OpenDocument('" + dt.Rows[i].CategoryID + "');";
a.innerHTML = dt.Rows[i].CategoryName;
}
else
{
var li = document.createElement("li");
li.className = "Closed";
li.id = "li_" + dt.Rows[i].CategoryID;
var img = document.createElement("img");
img.id = dt.Rows[i].CategoryID;
img.className = "s";
img.src = "css/s.gif";
img.onclick = function () {
ExpandSubCategory(this.id);
};
img.alt = "展开/折叠";
var a = document.createElement("a");
a.href = "javascript:ExpandSubCategory(" +
dt.Rows[i].CategoryID + ");";
a.innerHTML = dt.Rows[i].CategoryName;
}
li.appendChild(img);
li.appendChild(a);
ul.appendChild(li);
}
li_father.appendChild(ul);
displayDocList(iCategoryID);
switchNote(iCategoryID, false);
}
function OpenDocument(iCategoryID)
{
var div_docContent = document.getElementById("docContent");
var div_docList = document.getElementById("docList");
div_docContent.style.display = "";
div_docList.style.display = "none";
Tree.GetDocInfo(iCategoryID, GetDocInfo_callback);
}
function GetDocInfo_callback(response)
{
}
function ChangeStatus(iCategoryID)
{
var li_father = document.getElementById("li_" + iCategoryID);
if (li_father.className == "Closed")
{
li_father.className = "Opened";
}
else
{
li_father.className = "Closed";
}
}
function switchNote(iCategoryID, show)
{
var li_father = document.getElementById("li_" + iCategoryID);
if (show)
{
var ul = document.createElement("ul");
ul.id = "ul_note_" + iCategoryID;
var note = document.createElement("li");
note.className = "Child";
var img = document.createElement("img");
img.className = "s";
img.src = "css/s.gif";
var a = document.createElement("a");
a.href = "javascript:void(0);";
a.innerHTML = "请稍候...";
note.appendChild(img);
note.appendChild(a);
ul.appendChild(note);
li_father.appendChild(ul);
}
else
{
var ul = document.getElementById("ul_note_" + iCategoryID);
if (ul)
{
li_father.removeChild(ul);
}
}
}
function displayDocList(iCategoryID)
{
var div_docContent = document.getElementById("docContent");
var div_docList = document.getElementById("docList");
div_docContent.style.display = "none";
div_docList.style.display = "";
div_docList.style.padding = 20;
while (div_docList.childNodes.length > 0)
{
div_docList.removeChild(div_docList.childNodes[0]);
}
var dt = Tree.GetDocInfoInCategory(iCategoryID).value.Tables[0];
if (dt)
{
for (var i = 0;i < dt.Rows.length;i++)
{
var a = document.createElement("a");
a.href = "javascript:OpenDocument(" + dt.Rows[i].CategoryID + ");";
a.innerHTML = "<font color=#1122aa size=2>" + dt.Rows[i].CategoryName + "</font>";
var li = document.createElement("li");
li.appendChild(a);
var div = document.createElement("div");
div.appendChild(li);
div_docList.appendChild(div);
}
}
}
// 加载根节点
var tree = document.getElementById("CategoryTree");
var root = document.createElement("li");
root.id = "li_0";
tree.appendChild(root);
// 加载页面时显示第一级分类
ExpandSubCategory(0);
</script>
</form>
</body>
</html>
Tree.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using AjaxPro;
using MySql.Data.MySqlClient;
public partial class Tree : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Utility.RegisterTypeForAjax(typeof(Tree));
}
private Random rand = new Random();
[AjaxMethod()]
public DataSet GetSubCategory(int iCategoryID)
{
DataSet ds = new DataSet();
MySqlConnection conn = new MySqlConnection("server=127.0.0.1;uid=root;pwd=111111;database=test");
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = string.Format("SELECT CategoryID, CategoryName, FatherID, IsLeaf(CategoryID) as IsChild FROM Category WHERE FatherID = {0}", iCategoryID);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
try
{
da.Fill(ds);
}
catch (MySqlException)
{
}
finally
{
conn.Close();
}
System.Threading.Thread.Sleep(500 + rand.Next(1000));
Console.WriteLine(ds.Tables[0].Rows.Count.ToString());
return ds;
}
[AjaxMethod()]
public DataSet GetDocInfo(int iCategoryID)
{
DataSet ds = new DataSet();
return ds;
}
[AjaxMethod()]
public DataSet GetDocInfoInCategory(int iCategoryID)
{
DataSet ds = new DataSet();
MySqlConnection conn = new MySqlConnection("server=127.0.0.1;uid=root;pwd=111111;database=test");
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = string.Format(
"SELECT CategoryID, CategoryName, FatherID FROM Category WHERE FatherID = {0} AND IsLeaf(CategoryID) = 1", iCategoryID);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
try
{
da.Fill(ds);
}
catch (MySqlException)
{
}
finally
{
conn.Close();
}
return ds;
}
}
CSS
a
{
text-decoration:none;
}
a,a:visited
{
color:#000;
background:inherit;
}
body
{
margin:0;
padding:20px;
font:12px tahoma,宋体,sans-serif;
}
dt
{
font-size:22px;
font-weight:bold;
margin:0 0 0 15px;
}
dd
{
margin:0 0 0 15px;
}
h4
{
margin:0;
padding:0;
font-size:18px;
text-align:center;
}
p
{
margin:0;
padding:0 0 0 18px;
}
p a,p a:visited
{
color:#00f;
background:inherit;
}
.TreeMenu img.s
{
cursor:hand;
vertical-align:middle;
}
.TreeMenu ul
{
padding:0;
}
.TreeMenu li
{
list-style:none;
padding:0;
}
.Closed ul
{
display:none;
}
.Child img.s
{
background:none;
cursor:default;
}
#CategoryTree ul
{
margin:0 0 0 17px;
}
#CategoryTree img.s
{
width:34px;
height:18px;
}
#CategoryTree .Opened img.s
{
background:url(skin3/opened.gif) no-repeat 0 1px;
}
#CategoryTree .Closed img.s
{
background:url(skin3/closed.gif) no-repeat 0 1px;
}
#CategoryTree .Child img.s
{
background:url(skin3/child.gif) no-repeat 13px 2px;
}
{
float:left;
width:249px;
border:1px solid #99BEEF;
background:#D2E4FC;
color:inherit;
margin:3px;
padding:3px;
}