ajax+asp.net2.0 sql2005三级联下拉框-阿里云开发者社区

开发者社区> geovindu> 正文

ajax+asp.net2.0 sql2005三级联下拉框

简介: 一sql2005建立数数据库:create database Schoolgouse Schoolgocreate table SchoolInfo(schoolId int IDENTITY(1,1)PRIMARY KEY,schoolName nvarchar(50))gocreate table DepartInfo(departId int IDENTITY(1,1)PRIMARY K
+关注继续查看
一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>
        &nbsp; &nbsp;
        <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>

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
9999 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
11584 0
ASP.NET MVC之下拉框绑定四种方式(十)
前言 上两节我们讲了文件上传的问题,关于这个上传的问题还未结束,我也在花时间做做分割大文件处理以及显示进度的问题,到时完成的话再发表,为了不耽误学习MVC其他内容的计划,我们今天开始好好讲讲关于MVC中下拉框中绑定枚举的几种方式。
1066 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13798 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
11879 0
下拉框控件dhtmlXCombo在ASP.NET中的使用详解
原文:http://blog.csdn.net/asengine11/article/details/6455267    在ASP.NET项目中经常会用到下拉框控件,但是ASP.NET提供的DropDownList控件不能修改样式,经常会与网站整体风格格格不入。
953 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
4469 0
+关注
geovindu
读者是,读之者,者之读.一沙一世界! to be is to do举世皆清我独浊,众人皆醒我独醉.俺是农民工,程序员.
151
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载