--
建立数据库
create database department;
-- 使用数据库
use department
-- 建立部门表
create table TDepartment
(
depID int primary key ,
depName varchar ( 30 ) not null
)
insert into TDepartment values ( 1 , ' 信息化部 ' );
insert into TDepartment values ( 2 , ' 市场部 ' );
insert into TDepartment values ( 3 , ' 综合行政部 ' );
-- 建立员工表
create table emp
(
empID int primary key ,
empName varchar ( 30 ) not null ,
depID int foreign key references TDepartment(depID)
)
insert into emp values ( 1 , ' baggio ' , 1 );
insert into emp values ( 2 , ' 张君 ' , 2 );
insert into emp values ( 3 , ' 刘冶 ' , 2 );
insert into emp values ( 4 , ' 王伟 ' , 3 );
insert into emp values ( 5 , ' 李中和 ' , 3 );
insert into emp values ( 6 , ' 张清芳 ' , 3 );
insert into emp values ( 7 , ' 陈嘉桦 ' , 1 );
insert into emp values ( 8 , ' 黎明 ' , 1 );
insert into emp values ( 9 , ' 张学友 ' , 1 );
create database department;
-- 使用数据库
use department
-- 建立部门表
create table TDepartment
(
depID int primary key ,
depName varchar ( 30 ) not null
)
insert into TDepartment values ( 1 , ' 信息化部 ' );
insert into TDepartment values ( 2 , ' 市场部 ' );
insert into TDepartment values ( 3 , ' 综合行政部 ' );
-- 建立员工表
create table emp
(
empID int primary key ,
empName varchar ( 30 ) not null ,
depID int foreign key references TDepartment(depID)
)
insert into emp values ( 1 , ' baggio ' , 1 );
insert into emp values ( 2 , ' 张君 ' , 2 );
insert into emp values ( 3 , ' 刘冶 ' , 2 );
insert into emp values ( 4 , ' 王伟 ' , 3 );
insert into emp values ( 5 , ' 李中和 ' , 3 );
insert into emp values ( 6 , ' 张清芳 ' , 3 );
insert into emp values ( 7 , ' 陈嘉桦 ' , 1 );
insert into emp values ( 8 , ' 黎明 ' , 1 );
insert into emp values ( 9 , ' 张学友 ' , 1 );
2.前台设计.一个dropdownList,一个ListBox,dropdownList中查出来的部门ListBox中给出相应人员
3. 程序后台设计(建立数据库DB类略,参照上一课)
1
using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Web;
5 using System.Web.Security;
6 using System.Web.UI;
7 using System.Web.UI.WebControls;
8 using System.Web.UI.WebControls.WebParts;
9 using System.Web.UI.HtmlControls;
10 using System.Data.SqlClient;
11
12 public partial class _ Default : System.Web.UI.Page
13 {
14 protected void Page_Load(object sender, EventArgs e)
15 {
16
17 if (!this.IsPostBack)
18 {
19 // DropDownList中在显示部门
20 SqlConnection conn = DB.getConnection();
21 conn. Open ();
22 SqlCommand cmd = new SqlCommand(" select * from TDepartment",conn);
23 SqlDataReader sdr = cmd.ExecuteReader();
24 DropDownListDepart.DataSource = sdr;
25 DropDownListDepart.DataTextField = "depName";
26 DropDownListDepart.DataValueField = "depID";
27 DropDownListDepart.DataBind();
28 sdr. Close ();
29 // 在ListBox中显示员工
30 SqlCommand cmdEmp = new SqlCommand(" select * from emp where depID = " + DropDownListDepart.SelectedValue, conn);
31 SqlDataReader sdrEmp = cmdEmp.ExecuteReader();
32 while (sdrEmp. Read ()) {
33 // 使用Items.Add来加载一个集合类item数据
34 ListBoxEmp.Items. Add (new ListItem(sdrEmp.GetString( 1 ),sdrEmp.GetInt32( 0 ).ToString()));
35 }
36 sdrEmp. Close ();
37 conn. Close ();
38 }
39 }
40 /**/ /**将DropDownListDepart的 AutoPostBack="True"后,
41 * 产生的SelectedIndexChanged事件
42 **/
43 protected void DropDownListDepart_SelectedIndexChanged(object sender, EventArgs e)
44 {
45 // 既然是使用Items.Add来加载一个集合类item数据,就应该在重新加载时清除
46 ListBoxEmp.Items.Clear();
47 // 显示员工
48 SqlConnection conn = DB.getConnection();
49 conn. Open ();
50 SqlCommand cmdEmp = new SqlCommand(" select * from emp where depID = " + DropDownListDepart.SelectedValue, conn);
51 SqlDataReader sdrEmp = cmdEmp.ExecuteReader();
52 while (sdrEmp. Read ())
53 {
54 ListBoxEmp.Items. Add (new ListItem(sdrEmp.GetString( 1 ), sdrEmp.GetInt32( 0 ).ToString()));
55 }
56 sdrEmp. Close ();
57 conn. Close ();
58 }
59 }
60
2 using System.Data;
3 using System.Configuration;
4 using System.Web;
5 using System.Web.Security;
6 using System.Web.UI;
7 using System.Web.UI.WebControls;
8 using System.Web.UI.WebControls.WebParts;
9 using System.Web.UI.HtmlControls;
10 using System.Data.SqlClient;
11
12 public partial class _ Default : System.Web.UI.Page
13 {
14 protected void Page_Load(object sender, EventArgs e)
15 {
16
17 if (!this.IsPostBack)
18 {
19 // DropDownList中在显示部门
20 SqlConnection conn = DB.getConnection();
21 conn. Open ();
22 SqlCommand cmd = new SqlCommand(" select * from TDepartment",conn);
23 SqlDataReader sdr = cmd.ExecuteReader();
24 DropDownListDepart.DataSource = sdr;
25 DropDownListDepart.DataTextField = "depName";
26 DropDownListDepart.DataValueField = "depID";
27 DropDownListDepart.DataBind();
28 sdr. Close ();
29 // 在ListBox中显示员工
30 SqlCommand cmdEmp = new SqlCommand(" select * from emp where depID = " + DropDownListDepart.SelectedValue, conn);
31 SqlDataReader sdrEmp = cmdEmp.ExecuteReader();
32 while (sdrEmp. Read ()) {
33 // 使用Items.Add来加载一个集合类item数据
34 ListBoxEmp.Items. Add (new ListItem(sdrEmp.GetString( 1 ),sdrEmp.GetInt32( 0 ).ToString()));
35 }
36 sdrEmp. Close ();
37 conn. Close ();
38 }
39 }
40 /**/ /**将DropDownListDepart的 AutoPostBack="True"后,
41 * 产生的SelectedIndexChanged事件
42 **/
43 protected void DropDownListDepart_SelectedIndexChanged(object sender, EventArgs e)
44 {
45 // 既然是使用Items.Add来加载一个集合类item数据,就应该在重新加载时清除
46 ListBoxEmp.Items.Clear();
47 // 显示员工
48 SqlConnection conn = DB.getConnection();
49 conn. Open ();
50 SqlCommand cmdEmp = new SqlCommand(" select * from emp where depID = " + DropDownListDepart.SelectedValue, conn);
51 SqlDataReader sdrEmp = cmdEmp.ExecuteReader();
52 while (sdrEmp. Read ())
53 {
54 ListBoxEmp.Items. Add (new ListItem(sdrEmp.GetString( 1 ), sdrEmp.GetInt32( 0 ).ToString()));
55 }
56 sdrEmp. Close ();
57 conn. Close ();
58 }
59 }
60