ajax +asp.net2.0 sqlserver2005 三级联

1 create database School
  2 go
  3
  4 use School
  5 go
  6
  7 create table SchoolInfo
  8 (
  9 schoolId int IDENTITY(1,1)PRIMARY KEY,
10 schoolName nvarchar(50)
11 )
12 go
13
14 create table DepartInfo
15 (
16 departId int IDENTITY(1,1)PRIMARY KEY,
17 schoolId int,
18 departName nvarchar(50)
19 )
20 go
21
22 create table personinfo
23 (
24 personId int IDENTITY(1,1) PRIMARY KEY,
25 departId int,
26 personName nvarchar(50)
27 )
28 go
29
30 --外键1  //先把表建完之后，再建外键
31 ALTER TABLE DepartInfo ADD CONSTRAINT SchoolInfo_DepartInfo_rf FOREIGN KEY
32 (
33     schoolId
34 )
35 REFERENCES SchoolInfo(schoolId)
36 GO
37
38 ALTER TABLE personinfo ADD CONSTRAINT DepartInfo_personinfo_rf FOREIGN KEY
39 (
40     departId
41 )
42 REFERENCES DepartInfo(departId)
43 GO
44
45 insert into SchoolInfo(schoolName)
46 VALUES('涂家小学')
47
48 insert into SchoolInfo(schoolName)
49 VALUES('江口小学')
50
51 insert into SchoolInfo(schoolName)
52 VALUES('下炮小学')
53
54 select * from SchoolInfo
55
56 insert into DepartInfo(SchoolId,departName)
57 values(1,'教务处')
58 insert into DepartInfo(SchoolId,departName)
59 values(1,'校办公室')
60 insert into DepartInfo(SchoolId,departName)
61 values(1,'学生处')
62 insert into DepartInfo(SchoolId,departName)
63 values(2,'党务处')
64 insert into DepartInfo(SchoolId,departName)
65 values(2,'校委办公室')
66 insert into DepartInfo(SchoolId,departName)
67 values(2,'团支处')
68 insert into DepartInfo(SchoolId,departName)
69 values(3,'电教处')
70 insert into DepartInfo(SchoolId,departName)
71 values(3,'校办公室')
72 insert into DepartInfo(SchoolId,departName)
73 values(3,'外办处')
74 GO
75
76 select * from DepartInfo
77
78 insert into personinfo(departId,personName)
79 values(1,'涂聚文')
80 insert into personinfo(departId,personName)
81 values(1,'赵金红')
82 insert into personinfo(departId,personName)
83 values(1,'涂斯博')
84 insert into personinfo(departId,personName)
85 values(2,'涂思懿')
86 insert into personinfo(departId,personName)
87 values(2,'涂年生')
88 insert into personinfo(departId,personName)
89 values(2,'赵刚')
90 insert into personinfo(departId,personName)
91 values(3,'赵思博')
92 insert into personinfo(departId,personName)
93 values(3,'高杰')
94 insert into personinfo(departId,personName)
95 values(3,'何处')
96 insert into personinfo(departId,personName)
97 values(4,'刘杰')
98 insert into personinfo(departId,personName)
99 values(4,'胡新胜')
100 insert into personinfo(departId,personName)
101 values(4,'黄瑞斌')
102 insert into personinfo(departId,personName)
103 values(5,'黄岗')
104 insert into personinfo(departId,personName)
105 values(5,'钟艳红')
106 insert into personinfo(departId,personName)
107 values(5,'毛湖南')
108 insert into personinfo(departId,personName)
109 values(6,'涂惟')
110 insert into personinfo(departId,personName)
111 values(6,'毛婷')
112 insert into personinfo(departId,personName)
113 values(6,'曾海波')
114 insert into personinfo(departId,personName)
115 values(7,'李鹏')
116 insert into personinfo(departId,personName)
117 values(7,'赵三')
118 insert into personinfo(departId,personName)
119 values(7,'李四')
120 insert into personinfo(departId,personName)
121 values(8,'王五')
122 insert into personinfo(departId,personName)
123 values(8,'毛六')
124 insert into personinfo(departId,personName)
125 values(8,'陈七')
126 insert into personinfo(departId,personName)
127 values(9,'张八')
128 insert into personinfo(departId,personName)
129 values(9,'艾九')
130 insert into personinfo(departId,personName)
131 values(9,'刘十')
132
133
134 select * from personinfo
135
136 select personid,personname from personinfo where departid=4

1。建立数据库
2。CascadingDropDown_DB.aspx代码

1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="CascadingDropDown_DB.aspx.cs" Inherits="CascadingDropDown_DB" EnableEventValidation="false" %>
2
3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
5 <html xmlns="http://www.w3.org/1999/xhtml" >
6 <head runat="server">
7     <title>无标题页</title>
8 </head>
9 <body>
10     <form id="form1" runat="server">
11     <div>
12         <asp:ScriptManager ID="ScriptManager1" runat="server">
13         </asp:ScriptManager>
14
15     </div>
16         <ajaxToolkit:CascadingDropDown ID="CascadingDropDown1" runat="server"
17         TargetControlID="ddlSchool"
18         Category="schoolname"
19          ServicePath="PersonDBService.asmx"
20          ServiceMethod="GetSchool"
21           PromptText="请选择学校"
22           LoadingText="正在加载学校" >
23         </ajaxToolkit:CascadingDropDown>
24         <ajaxToolkit:CascadingDropDown ID="CascadingDropDown2" runat="server" TargetControlID="ddlDepart"
25          Category="departname"
26          ServicePath="PersonDBService.asmx"
27          ServiceMethod="GetDepartsForSchool"
28           PromptText="请选择部门"
29           LoadingText="正在加载部门"
30          ParentControlID="ddlSchool" >
31         </ajaxToolkit:CascadingDropDown>
32         <ajaxToolkit:CascadingDropDown ID="CascadingDropDown3" runat="server" TargetControlID="ddlPerson"
33          Category="personname"
34          ServicePath="PersonDBService.asmx"
35          ServiceMethod="GetPersonsForDepart"
36           PromptText="请选择人员"
37           LoadingText="正在加载人员"
38           ParentControlID="ddlDepart">
39         </ajaxToolkit:CascadingDropDown>
40            
41         <table style=" 572px; height: 168px;">
42             <tr>
43                 <td style=" 200px">
44                 </td>
45                 <td>
46                    <strong>选择数据库中的人员</strong> </td>
47             </tr>
48             <tr>
49                 <td style=" 200px">
50                     1.选择学校：</td>
51                 <td>
52         <asp:DropDownList ID="ddlSchool" runat="server" Width="234px">
53         </asp:DropDownList></td>
54             </tr>
55             <tr>
56                 <td style=" 200px">
57                     2.选择部门：</td>
58                 <td>
59         <asp:DropDownList ID="ddlDepart" runat="server" Width="232px">
60         </asp:DropDownList></td>
61             </tr>
62             <tr>
63                 <td style=" 200px">
64                     3.选择人员：</td>
65                 <td>
66         <asp:DropDownList ID="ddlPerson" runat="server" Width="230px" OnSelectedIndexChanged="ddlPerson_SelectedIndexChanged" AutoPostBack="True">
67         </asp:DropDownList></td>
68             </tr>
69             <tr>
70                 <td colspan="2">
71                   <asp:UpdatePanel ID="UpdatePanel1" runat="server">
72                         <ContentTemplate>
73                     <asp:Label ID="Label1" runat="server" Width="341px"></asp:Label>
74                         </ContentTemplate>
75                      <Triggers>
76                           <asp:AsyncPostBackTrigger ControlID="ddlPerson" EventName="SelectedIndexChanged" />
77                      </Triggers>
78                     </asp:UpdatePanel>
79                 </td>
80             </tr>
81         </table>
82     </form>
83 </body>
84 </html>

三。CascadingDropDown_DB.aspx.cs 代码：

1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Collections;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11
12 public partial class CascadingDropDown_DB : System.Web.UI.Page
13 {
14     protected void Page_Load(object sender, EventArgs e)
15     {
16
17     }
18     protected void ddlPerson_SelectedIndexChanged(object sender, EventArgs e)
19     {
20         //选择的内容-注意此处不是使用的selectvalue
21         string person = ddlSchool.SelectedItem.Text + "学校" + ddlDepart.SelectedItem.Text + "部门的" + ddlPerson.SelectedItem.Text;
22         //动态显示
23         Label1.Text = "您选择的是-" + person;
24     }
25 }

四PersonDBService.asmx 服务代码：

1 <%@ WebService Language="C#" CodeBehind="~/App_Code/PersonDBService.cs" Class="PersonDBService" %>

五PersonDBService.cs 代码：

  1 using System;
  2 using System.Web;
  3 using System.Collections;
  4 using System.Web.Services;
  5 using System.Web.Services.Protocols;
  6
  7 using AjaxControlToolkit;
  8 using System.Data;
  9 using System.Data.SqlClient;
10 using System.Collections.Specialized;
11 using System.Collections.Generic;
12 using System.Configuration;
13
14 /// <summary>
15 /// PersonDBService 的摘要说明
16 /// </summary>
17 [WebService(Namespace = "http://tempuri.org/")]
18 [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
19 [System.Web.Script.Services.ScriptService]
20 public class PersonDBService : System.Web.Services.WebService {
21
22     public PersonDBService () {
23
24         //如果使用设计的组件，请取消注释以下行
25         //InitializeComponent();
26     }
27     /// <summary>
28     /// 获取学校信息
29     /// </summary>
30     /// <param name="knownCategoryValues">父级条件</param>
31     /// <param name="category">目录</param>
32     /// <returns>返回数据</returns>
33     [WebMethod]
34     public CascadingDropDownNameValue[] GetSchool( string knownCategoryValues, string category)
35     {
36         List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>();
37         //返回学校表信息
38         DataTable schools = getSchoolTable();
39         //遍历表中的行
40         foreach (DataRow dr in schools.Rows)
41         {
42             string school = (string)dr["schoolname"];
43             int schoolId = (int)dr["schoolid"];
44             //表数据添加到级联菜单中
45             values.Add(new CascadingDropDownNameValue(
46               school, schoolId.ToString()));
47         }
48         return values.ToArray();
49     }
50     //获取部门信息
51     [WebMethod]
52     public CascadingDropDownNameValue[] GetDepartsForSchool(string knownCategoryValues,string category)
53     {
54         //找到指定条件的信息集合
55         StringDictionary kv = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
56         int schoolId;
57         //找出指定值的ID。
58         if (!kv.ContainsKey("schoolname") ||
59             !Int32.TryParse(kv["schoolname"], out schoolId))
60         {
61             return null;
62         }
63         //根据学校id返回部门表
64         DataTable departs = getDepartTable(schoolId);
65         List<CascadingDropDownNameValue> values =  new List<CascadingDropDownNameValue>();
66         //遍历部门表
67         foreach (DataRow dr in departs.Rows)
68         {
69             //添加部门信息到级联菜单
70             values.Add(new CascadingDropDownNameValue(
71               (string)dr["departname"], dr["departid"].ToString()));
72         }
73         return values.ToArray();
74     }
75     //获取人员信息
76     [WebMethod]
77     public CascadingDropDownNameValue[] GetPersonsForDepart( string knownCategoryValues, string category)
78     {
79         //找到指定条件的集合
80         StringDictionary kv = CascadingDropDown.ParseKnownCategoryValuesString( knownCategoryValues);
81         int departId;
82         //获取指定的ID
83         if (!kv.ContainsKey("departname") ||
84             !Int32.TryParse(kv["departname"], out departId))
85         {
86             return null;
87         }
88         //根据部门ID返回人员表
89         DataTable persons = getPersonTable(departId);
90         List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>();
91         //遍历人员表
92         foreach (DataRow dr in persons.Rows)
93         {
94             //添加人员信息到级联菜单
95             values.Add(new CascadingDropDownNameValue(
96               (string)dr["personname"], dr["personid"].ToString()));
97         }
98         return values.ToArray();
99     }
100     /// <summary>
101     /// 获取学校表数据
102     /// </summary>
103     public DataTable getSchoolTable()
104     {
105         //创建数据库连接
106         SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolConnectionString"].ToString());
107         SqlDataAdapter da = new SqlDataAdapter("select schoolid,schoolname  from schoolinfo", conn);
108         DataSet ds = new DataSet();
109         //填充数据内容
110         da.Fill(ds);
111         return ds.Tables[0];
112     }
113     /// <summary>
114     /// 获取指定学校ID的部门信息
115     /// </summary>
116     public DataTable getDepartTable(int schoolid)
117     {
118         //创建数据库连接
119         SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolConnectionString"].ToString());
120         SqlDataAdapter da = new SqlDataAdapter("select departid,departname from departinfo where schoolid=" + schoolid.ToString(), conn);
121         DataSet ds = new DataSet();
122         //填充数据内容
123         da.Fill(ds);
124         return ds.Tables[0];
125     }
126     /// <summary>
127     /// 获取指定部门ID的人员信息
128     /// </summary>
129     public DataTable getPersonTable(int departid)
130     {
131         //创建数据库连接
132         SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolConnectionString"].ToString());
133         SqlDataAdapter da = new SqlDataAdapter("select personid,personname from personinfo where departid="+ departid.ToString(), conn);
134         DataSet ds = new DataSet();
135         //填充数据内容
136         da.Fill(ds);
137         return ds.Tables[0];
138     }
139 }

七：Web.config 数据库连接代码：
    <connectionStrings>
        <add name="SchoolConnectionString" connectionString="Data Source=GEOVI-5E9530747\SQLEXPRESS;Initial Catalog=School;Integrated Security=True"
   providerName="System.Data.SqlClient" />
    </connectionStrings>