一sql2005建立数数据库：
create database School
go
use School
go
create table SchoolInfo
(
schoolId int IDENTITY(1,1)PRIMARY KEY,
schoolName nvarchar(50)
)
go
create table DepartInfo
(
departId int IDENTITY(1,1)PRIMARY KEY,
schoolId int,
departName nvarchar(50)
)
go
create table personinfo
(
personId int IDENTITY(1,1) PRIMARY KEY,
departId int,
personName nvarchar(50)
)
go
--外键1 //先把表建完之后，再建外键
ALTER TABLE DepartInfo ADD CONSTRAINT SchoolInfo_DepartInfo_rf FOREIGN KEY
(
schoolId
)
REFERENCES SchoolInfo(schoolId)
GO
ALTER TABLE personinfo ADD CONSTRAINT DepartInfo_personinfo_rf FOREIGN KEY
(
departId
)
REFERENCES DepartInfo(departId)
GO
insert into SchoolInfo(schoolName)
VALUES('涂家小学')
insert into SchoolInfo(schoolName)
VALUES('江口小学')
insert into SchoolInfo(schoolName)
VALUES('下炮小学')
select * from SchoolInfo
insert into DepartInfo(SchoolId,departName)
values(1,'教务处')
insert into DepartInfo(SchoolId,departName)
values(1,'校办公室')
insert into DepartInfo(SchoolId,departName)
values(1,'学生处')
insert into DepartInfo(SchoolId,departName)
values(2,'党务处')
insert into DepartInfo(SchoolId,departName)
values(2,'校委办公室')
insert into DepartInfo(SchoolId,departName)
values(2,'团支处')
insert into DepartInfo(SchoolId,departName)
values(3,'电教处')
insert into DepartInfo(SchoolId,departName)
values(3,'校办公室')
insert into DepartInfo(SchoolId,departName)
values(3,'外办处')
GO
select * from DepartInfo
insert into personinfo(departId,personName)
values(1,'涂聚文')
insert into personinfo(departId,personName)
values(1,'赵金红')
insert into personinfo(departId,personName)
values(1,'涂斯博')
insert into personinfo(departId,personName)
values(2,'涂思懿')
insert into personinfo(departId,personName)
values(2,'涂年生')
insert into personinfo(departId,personName)
values(2,'赵刚')
insert into personinfo(departId,personName)
values(3,'赵思博')
insert into personinfo(departId,personName)
values(3,'高杰')
insert into personinfo(departId,personName)
values(3,'何处')
insert into personinfo(departId,personName)
values(4,'刘杰')
insert into personinfo(departId,personName)
values(4,'胡新胜')
insert into personinfo(departId,personName)
values(4,'黄瑞斌')
insert into personinfo(departId,personName)
values(5,'黄岗')
insert into personinfo(departId,personName)
values(5,'钟艳红')
insert into personinfo(departId,personName)
values(5,'毛湖南')
insert into personinfo(departId,personName)
values(6,'涂惟')
insert into personinfo(departId,personName)
values(6,'毛婷')
insert into personinfo(departId,personName)
values(6,'曾海波')
insert into personinfo(departId,personName)
values(7,'*')
insert into personinfo(departId,personName)
values(7,'赵三')
insert into personinfo(departId,personName)
values(7,'李四')
insert into personinfo(departId,personName)
values(8,'王五')
insert into personinfo(departId,personName)
values(8,'毛六')
insert into personinfo(departId,personName)
values(8,'陈七')
insert into personinfo(departId,personName)
values(9,'张八')
insert into personinfo(departId,personName)
values(9,'艾九')
insert into personinfo(departId,personName)
values(9,'刘十')
select * from personinfo
select personid,personname from personinfo where departid=4
二CascadingDropDown_DB.aspx代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CascadingDropDown_DB.aspx.cs" Inherits="CascadingDropDown_DB" EnableEventValidation="false" %>
<!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 runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
</div>
<ajaxToolkit:CascadingDropDown ID="CascadingDropDown1" runat="server"
TargetControlID="ddlSchool"
Category="schoolname"
ServicePath="PersonDBService.asmx"
ServiceMethod="GetSchool"
PromptText="请选择学校"
LoadingText="正在加载学校" >
</ajaxToolkit:CascadingDropDown>
<ajaxToolkit:CascadingDropDown ID="CascadingDropDown2" runat="server" TargetControlID="ddlDepart"
Category="departname"
ServicePath="PersonDBService.asmx"
ServiceMethod="GetDepartsForSchool"
PromptText="请选择部门"
LoadingText="正在加载部门"
ParentControlID="ddlSchool" >
</ajaxToolkit:CascadingDropDown>
<ajaxToolkit:CascadingDropDown ID="CascadingDropDown3" runat="server" TargetControlID="ddlPerson"
Category="personname"
ServicePath="PersonDBService.asmx"
ServiceMethod="GetPersonsForDepart"
PromptText="请选择人员"
LoadingText="正在加载人员"
ParentControlID="ddlDepart">
</ajaxToolkit:CascadingDropDown>
<table style="width: 572px; height: 168px;">
<tr>
<td style="width: 200px">
</td>
<td>
<strong>选择数据库中的人员</strong> </td>
</tr>
<tr>
<td style="width: 200px">
1.选择学校：</td>
<td>
<asp:DropDownList ID="ddlSchool" runat="server" Width="234px">
</asp:DropDownList></td>
</tr>
<tr>
<td style="width: 200px">
2.选择部门：</td>
<td>
<asp:DropDownList ID="ddlDepart" runat="server" Width="232px">
</asp:DropDownList></td>
</tr>
<tr>
<td style="width: 200px">
3.选择人员：</td>
<td>
<asp:DropDownList ID="ddlPerson" runat="server" Width="230px" OnSelectedIndexChanged="ddlPerson_SelectedIndexChanged" AutoPostBack="True">
</asp:DropDownList></td>
</tr>
<tr>
<td colspan="2">
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:Label ID="Label1" runat="server" Width="341px"></asp:Label>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="ddlPerson" EventName="SelectedIndexChanged" />
</Triggers>
</asp:UpdatePanel>
</td>
</tr>
</table>
</form>
</body>
</html>
三CascadingDropDown_DB.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;
public partial class CascadingDropDown_DB : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ddlPerson_SelectedIndexChanged(object sender, EventArgs e)
{
//选择的内容-注意此处不是使用的selectvalue
string person = ddlSchool.SelectedItem.Text + "学校" + ddlDepart.SelectedItem.Text + "部门的" + ddlPerson.SelectedItem.Text;
//动态显示
Label1.Text = "您选择的是-" + person;
}
}
四PersonDBService.asmx 服务代码：
<%@ WebService Language="C#" CodeBehind="~/App_Code/PersonDBService.cs" Class="PersonDBService" %>
五PersonDBService.cs 代码：
using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using AjaxControlToolkit;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.Configuration;
/// <summary>
/// PersonDBService 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class PersonDBService : System.Web.Services.WebService {
public PersonDBService () {
//如果使用设计的组件，请取消注释以下行
//InitializeComponent();
}
/// <summary>
/// 获取学校信息
/// </summary>
/// <param name="knownCategoryValues">父级条件</param>
/// <param name="category">目录</param>
/// <returns>返回数据</returns>
[WebMethod]
public CascadingDropDownNameValue[] GetSchool( string knownCategoryValues, string category)
{
List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>();
//返回学校表信息
DataTable schools = getSchoolTable();
//遍历表中的行
foreach (DataRow dr in schools.Rows)
{
string school = (string)dr["schoolname"];
int schoolId = (int)dr["schoolid"];
//表数据添加到级联菜单中
values.Add(new CascadingDropDownNameValue(
school, schoolId.ToString()));
}
return values.ToArray();
}
//获取部门信息
[WebMethod]
public CascadingDropDownNameValue[] GetDepartsForSchool(string knownCategoryValues,string category)
{
//找到指定条件的信息集合
StringDictionary kv = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
int schoolId;
//找出指定值的ID。
if (!kv.ContainsKey("schoolname") ||
!Int32.TryParse(kv["schoolname"], out schoolId))
{
return null;
}
//根据学校id返回部门表
DataTable departs = getDepartTable(schoolId);
List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>();
//遍历部门表
foreach (DataRow dr in departs.Rows)
{
//添加部门信息到级联菜单
values.Add(new CascadingDropDownNameValue(
(string)dr["departname"], dr["departid"].ToString()));
}
return values.ToArray();
}
//获取人员信息
[WebMethod]
public CascadingDropDownNameValue[] GetPersonsForDepart( string knownCategoryValues, string category)
{
//找到指定条件的集合
StringDictionary kv = CascadingDropDown.ParseKnownCategoryValuesString( knownCategoryValues);
int departId;
//获取指定的ID
if (!kv.ContainsKey("departname") ||
!Int32.TryParse(kv["departname"], out departId))
{
return null;
}
//根据部门ID返回人员表
DataTable persons = getPersonTable(departId);
List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>();
//遍历人员表
foreach (DataRow dr in persons.Rows)
{
//添加人员信息到级联菜单
values.Add(new CascadingDropDownNameValue(
(string)dr["personname"], dr["personid"].ToString()));
}
return values.ToArray();
}
/// <summary>
/// 获取学校表数据
/// </summary>
public DataTable getSchoolTable()
{
//创建数据库连接
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolConnectionString"].ToString());
SqlDataAdapter da = new SqlDataAdapter("select schoolid,schoolname from schoolinfo", conn);
DataSet ds = new DataSet();
//填充数据内容
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// 获取指定学校ID的部门信息
/// </summary>
public DataTable getDepartTable(int schoolid)
{
//创建数据库连接
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolConnectionString"].ToString());
SqlDataAdapter da = new SqlDataAdapter("select departid,departname from departinfo where schoolid=" + schoolid.ToString(), conn);
DataSet ds = new DataSet();
//填充数据内容
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// 获取指定部门ID的人员信息
/// </summary>
public DataTable getPersonTable(int departid)
{
//创建数据库连接
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolConnectionString"].ToString());
SqlDataAdapter da = new SqlDataAdapter("select personid,personname from personinfo where departid="+ departid.ToString(), conn);
DataSet ds = new DataSet();
//填充数据内容
da.Fill(ds);
return ds.Tables[0];
}
}
七：Web.config 数据库连接代码：
<connectionStrings>
<add name="SchoolConnectionString" connectionString="Data Source=GEOVI-5E9530747/SQLEXPRESS;Initial Catalog=School;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>