数据库系统设计综合实验

实验目的

通过实验，使学生掌握数据库系统设计和开发的一般方法，能够设计并实现简单的数据库系统。
实验要求
熟悉实验室实验环境，掌握实验预备知识，了解实验中故障排除的基本方法。实验中根据实验要求完成相应的任务，并独立完成实验报告。

实验内容

设计的数据库系统可从以下题目中选择，但不限定于以下题目，可自由选择。
需提供系统简要的需求分析，给出大体的功能介绍；完成数据库的概念结构设计，建议用PowerDesigner画出系统的逻辑结构图或物理结构图（也可以使用其他工具）；任选自己熟悉的数据库管理系统和编程语言完成系统主要功能模块的开发。
系统题目参考：
1、学生成绩管理系统

一：E-R图的建立

我选择的题目是学生成绩管理系统。用c#语言来实现对窗口的创建、与数据库的连接，将程序实现。
首先需要梳理一个学生、课程、教师的e-r图

二：创建数据库，建立表格以及所涉及到的视图：

因为该程序需要实现的功能有：
学生：查成绩、选课、退课、改密码、查询学生信息
教师：查询教师信息、查询学生信息，改密码、给学生登成绩、查询自己教授的课程、
退掉被安排的课程
管理员：增加/删除/查询 学生、增加/删除/查询 老师、增加/删除/查看课程、修改学生/老师信息、给老师排课、解散课程、给学生选/退课程。
因为管理员是一个全局的成员，所以这里不单独为管理员建立一个信息表

在MySql、SQL Server、Oracle中选择一个数据库管理系统，并设计数据库的物理结构；
创建用户bb密码：bct88788502

creat user bb identified by bct88788502 Grant connect ,resource,unlimited tablespace to bb; Grant create view to bb; 

（1）student表

学号是主键，其他不为空。姓名可以重复`

create table student (   sno varchar(10) primary key,     sname varchar(10) not null unique,     ssex varchar(10) check(ssex in ('男','女')),     sage varchar(10) not null,     sprof varchar(20) not null,     pwd varchar(20) not null ); 

（2）teacher表

工号为主键，其他不为空

create table teacher (  tno varchar(10) primary key,    tname varchar(10) not null,    tsex varchar(10) check(tsex in ('男','女')),    pwd varchar(10) not null ); 

（3）course课程表

课程号为主键，其他不为空。

(   cno varchar(10) primary key,     cname varchar(10) not null,     ccredit varchar(10) not null,     tno varchar(10) not null,     tname varchar(10) not null ); 

（4）选修表

create table sc (  sname varchar(10) not null,    sno varchar(10) not null,    cname varchar(10) not null,    cno varchar(10) not null,    grade number(10) not null ); 

（5）课程库表

create table courselibrary (   课程号 varchar(10) primary key,     课程 varchar(10) not null,     学分 varchar(10) not null ); 

所以为实现以上功能还需要建立两个视图

（6）scnifo

这个视图用来给老师提供等成绩的服务

（1）create view scnifo as select sc.sno 学号,sc.sname 姓名,sc.cname 课程, (select tname from course where course.cname=sc.cname) 老师,(select tno from course where course.cname=sc.cname) 工号, sc.grade 成绩,(select ccredit from course where course.cname=sc.cname) 学分 from sc; 

（7）Studentnifo

这个视图用来显示学生的成绩，为学生查成绩提供服务

（2）create view studentinfo as select student.sno 学号,student.sname 姓名, (select sum(ccredit) from course where cno in (select cno from sc where sno=student.sno)) 总学分 , (select sum(grade) from sc where sno=student.sno)总分 from student; 

向数据库中插入数据：

insert into student(sno,sname,ssex,sage,sprof,pwd)values('1','小王','男','20','计算机科学与技术','123456'); insert into student(sno,sname,ssex,sage,sprof,pwd)values('2','小白','男','20','网络工程','123456'); insert into student(sno,sname,ssex,sage,sprof,pwd)values('3','小刘','男','20','计算机科学与技术','123456'); insert into student(sno,sname,ssex,sage,sprof,pwd)values('4','小黎','男','19','计算机科学与技术','123456'); insert into student(sno,sname,ssex,sage,sprof,pwd)values('5','小舒','男','20','软件工程','123456'); insert into student(sno,sname,ssex,sage,sprof,pwd)values('6','大哥','男','20','修仙艺术','123456'); 

insert into teacher(tno,tname,tsex,pwd)values('001','李老师','男','123456'); insert into teacher(tno,tname,tsex,pwd)values('002','王老师','女','123456'); insert into teacher(tno,tname,tsex,pwd)values('003','张老师','男','123456'); insert into teacher(tno,tname,tsex,pwd)values('004','林老师','女','123456'); 

insert into course(cno,cname,ccredit,tno,tname) values('1','数据结构','5','001','李老师'); insert into course(cno,cname,ccredit,tno,tname) values('2','操作系统','4','001','李老师'); insert into course(cno,cname,ccredit,tno,tname) values('3','计算机导论','2','002','王老师'); insert into course(cno,cname,ccredit,tno,tname) values('4','汇编语言','2','003','张老师'); insert into course(cno,cname,ccredit,tno,tname) values('5','数据库','3','004','林老师'); 

insert into courselibrary(课程号，课程，学分) values('1','数据结构','5'); insert into courselibrary(课程号，课程，学分) values('2','操作系统','4'); insert into courselibrary(课程号，课程，学分) values('3','计算机导论','2'); insert into courselibrary(课程号，课程，学分) values('4','汇编语言','2'); insert into courselibrary(课程号，课程，学分) values('5','数据库','3'); insert into courselibrary(课程号，课程，学分) values('6','心理催眠','3'); insert into courselibrary(课程号，课程，学分) values('7','大学体育','4'); 

insert into sc(sname,sno,cname,cno,grade)values('小白','2','数据结构','1','100'); insert into sc(sname,sno,cname,cno,grade)values('小白','2','操作系统','2','90'); insert into sc(sname,sno,cname,cno,grade)values('小白','2','数据库','5','97'); insert into sc(sname,sno,cname,cno,grade)values('小王','1','操作系统','2','95'); insert into sc(sname,sno,cname,cno,grade)values('小王','1','计算机导论','3','96'); insert into sc(sname,sno,cname,cno,grade)values('小刘','3','计算机导论','3','97'); insert into sc(sname,sno,cname,cno,grade)values('小刘','3','数据库','5','95'); insert into sc(sname,sno,cname,cno,grade)values('小舒','4','计算机导论','3','96'); insert into sc(sname,sno,cname,cno,grade)values('小舒','4','汇编语言','4','98'); insert into sc(sname,sno,cname,cno,grade)values('小黎','5','计算机导论','3','96'); insert into sc(sname,sno,cname,cno,grade)values('大哥','6','操作系统','2','89'); 

三：使用C#连接数据库必知：

首先需要建立一个C#项目：

然后添加一个连接数据库拓展引用：
OracleManagedDataAccess.dll 下载安装方式如下：

右键点击引用，选择管理nuget程序包，搜索oracle.ManagetDataAccess 程序包直接安装即可
连接数据库时，需要添加上oracle.ManageDataAccess 头文件。
具体语句：

OracleConnection con=new OracleConnection con.ConnetionString=”DataSource=(DESCRTPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=xxxx))(CONNECT_DATA=(SERVICE_NAME=ORACLE)));persist Security Info=True;User ID=bb;Password=bct88788502;” 

第二种简单方便的方法：

在app.config中添加如下代码

OracleConnection con =new OracleConnection(ConfigurationManager.ConnectionStrings[“strCon”].ConnectionString); 

四：C#代码部分：

（1）学生类部分代码（student）

public class 学生类     { public string sno { get; set; } public string sname { get; set; } public string ssex { get; set; } public string sage { get; set; } public string sprof { get; set; } public string pwd { get; set; } public static List<学生类> Selectstudent(string sno)//显示学生信息 { string sql = "select sno,sname,ssex,sage，sprof,pwd from student where sno like :sno";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":sno", OracleDbType.Varchar2, 10) };             para[0].Value = sno + "%"; //MessageBox.Show(tname+"%");             List<学生类> list = new List<学生类>(); //创建链接，打开连接，创建命令对象，执行命令，关闭连接 OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para); OracleDataReader odr = cmd.ExecuteReader(); while (odr.Read()) {                      学生类 s = new 学生类(); //c.Cno = odr.GetString(0); //ord["cno'].                     s.sno = odr.GetString(0);                     s.sname = odr.GetString(1);                     s.ssex = odr.GetString(2);                     s.sage = odr.GetString(3);                     s.sprof = odr.GetString(4); if(user.use=="administrator"||stu.stu1==s.sno) {                         s.pwd = odr.GetString(5); } else                         s.pwd = "******";                     list.Add(s); } } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return list; } public static int changescode(学生类 s)//学生修改学生密码 { int result = 0; string sql = "update student set pwd=:pwd where sno=:sno ";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":pwd",OracleDbType.Varchar2,20), new OracleParameter(":sno",OracleDbType.Varchar2,10) };              para[0].Value = s.pwd;             para[1].Value = s.sno; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch(Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int changestudent(学生类 s)//管理员修改学生信息 { int result = 0; string sql = "update student set sname=:sname,ssex=:ssex,sage =:sage,sprof =:sprof,pwd =:pwd where sno =:sno";              OracleParameter[] para = new OracleParameter[] { new OracleParameter(":sname",OracleDbType.Varchar2,10), new OracleParameter(":ssex",OracleDbType.Varchar2,10), new OracleParameter(":sage",OracleDbType.Varchar2,10), new OracleParameter(":sprof",OracleDbType.Varchar2,10), new OracleParameter(":pwd",OracleDbType.Varchar2,10), new OracleParameter(":sno",OracleDbType.Varchar2,10), };             para[0].Value = s.sname;             para[1].Value = s.ssex;             para[2].Value = s.sage;             para[3].Value = s.sprof;             para[4].Value = s.pwd;             para[5].Value = s.sno; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql,con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch(Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int deletesutdent(学生类 s)//根据学号删除学生 { int result = 0; string sql= "delete from student where sno=:sno";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":sno",OracleDbType.Varchar2,10), };             para[0].Value = s.sno; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch(Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int addstudent(学生类 s)//添加学生 { int result = 0; string sql = "insert into student(sno,sname,ssex,sage,sprof,pwd)values(:sno,:sname,:ssex,:sage,:sprof,:pwd)";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":sno",OracleDbType.Varchar2,10), new OracleParameter(":sname",OracleDbType.Varchar2,10), new OracleParameter(":ssex",OracleDbType.Varchar2,10), new OracleParameter(":sage",OracleDbType.Varchar2,10), new OracleParameter(":sprof",OracleDbType.Varchar2,10), new OracleParameter(":pwd",OracleDbType.Varchar2,10) };             para[0].Value = s.sno;             para[1].Value = s.sname;             para[2].Value = s.ssex;             para[3].Value = s.sage;             para[4].Value = s.sprof;             para[5].Value = s.pwd; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch(Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } } 

（2）课程类（course）

public class 课程类     { public string cno { get; set; } public string cname { get; set; } public string ccredit { get; set; } public string tno { get; set; } public string tname { get; set; } public static List<课程类> showcourse(string cno)//显示所有课程 { string sql = "select cno,cname,ccredit,tno ,tname from course where cno like:cno ";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":cno", OracleDbType.Varchar2, 10) };             para[0].Value = cno + "%";             List<课程类> list = new List<课程类>(); OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para); OracleDataReader odr = cmd.ExecuteReader(); while (odr.Read()) {                     课程类 c = new 课程类();                     c.cno = odr.GetString(0);                     c.cname = odr.GetString(1);                     c.ccredit = odr.GetString(2);                     c.tno = odr.GetString(3);                     c.tname = odr.GetString(4);                     list.Add(c); } } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return list; } public static List<课程类> tselectcourse(string tno)//查询教师教授的课程 { string sql = "select cno ,cname,ccredit from course where tno=:tno ";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":tno", OracleDbType.Varchar2, 10) };             para[0].Value = tno;             List<课程类> list = new List<课程类>(); OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para); OracleDataReader odr = cmd.ExecuteReader(); while (odr.Read()) {                     课程类 c = new 课程类();                     c.cno = odr.GetString(0);                     c.cname = odr.GetString(1);                     c.ccredit = odr.GetString(2);                     list.Add(c); } } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return list; } public static int deleteteacher(课程类 c)//根据工号删除老师教授的课程 { int result = 0; string sql = "delete from course where tno=:tno";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":tno",OracleDbType.Varchar2,10) };             para[0].Value = c.tno; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int deleteteacher2(课程类 c)//根据课程号删除老师教授的课程 { int result = 0; string sql = "delete from course where cno=:cno";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":cno",OracleDbType.Varchar2,10) };             para[0].Value = c.cno; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int deleteteacher3(课程类 c)//根据课程号、工号删除解散指定老师老师教授得单门课程 { int result = 0; string sql = "delete from course where cno=:cno and tno=:tno";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":cno",OracleDbType.Varchar2,10), new OracleParameter(":tno",OracleDbType.Varchar2,10) };             para[0].Value = c.cno;             para[1].Value = c.tno; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int addcourse(课程类 c)//给老师排课 { int result = 0; string sql = "insert into course (cno,cname,ccredit,tno,tname)values(:cno,:cname,:ccredit,:tno,:tname)";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":cno",OracleDbType.Varchar2,10), new OracleParameter(":cname",OracleDbType.Varchar2,10), new OracleParameter(":ccredit",OracleDbType.Varchar2,10), new OracleParameter(":tno",OracleDbType.Varchar2,10), new OracleParameter(":tname",OracleDbType.Varchar2,10) };             para[0].Value = c.cno;             para[1].Value = c.cname;             para[2].Value = c.ccredit;             para[3].Value = c.tno;             para[4].Value = c.tname; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } } 

（3）教师类（teacher)

public class 教师类     { public string tno { get; set; } public string tname { get; set; } public string tsex { get; set; } public string pwd { get; set; } public static List<教师类>selectteacher(string tno) { string sql = "select tno ,tname ,tsex,pwd from teacher where tno like:tno";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":tno",OracleDbType.Varchar2,10) };             para[0].Value = tno+'%';             List < 教师类 >list= new List<教师类>(); OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para); OracleDataReader odr = cmd.ExecuteReader(); while(odr.Read()) {                     教师类 t = new 教师类();                     t.tno = odr.GetString(0);                     t.tname = odr.GetString(1); if (odr.IsDBNull(2)) t.tsex = null; else t.tsex = odr.GetString(2);                     t.pwd = odr.GetString(3);//管理员查看                                           list.Add(t); } } catch(Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return list; } public static int changetcode(教师类 t)//教师修改密码 { int result = 0; string sql = "update teacher set pwd=:pwd where tno=:tno";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":pwd",OracleDbType.Varchar2,10), new OracleParameter(":sno",OracleDbType.Varchar2,10) };             para[0].Value = t.pwd;             para[1].Value = t.tno; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch(Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int changeteacher(教师类 t)//改变教师信息 { int result = 0; string sql = "update teacher set tno=:tno,tname=:tname,tsex=:tsex,pwd=:pwd where tno=:tno";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":tno",OracleDbType.Varchar2,10), new OracleParameter(":tname",OracleDbType.Varchar2,10), new OracleParameter(":tsex",OracleDbType.Varchar2,10), new OracleParameter(":pwd",OracleDbType.Varchar2,10) };             para[0].Value = t.tno;             para[1].Value = t.tname;             para[2].Value = t.tsex;             para[3].Value = t.pwd; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int deleteteacher(教师类 t)//删除教师 { int result = 0; string sql = "delete from teacher where tno=:tno ";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":tno",OracleDbType.Varchar2,10) };             para[0].Value = t.tno; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open();//创建连接 OracleCommand cmd = new OracleCommand(sql, con);//连接数据库                 cmd.Parameters.AddRange(para);//连接参数                 result = cmd.ExecuteNonQuery();//返回执行结果 } catch(Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int addteacher(教师类 t)//添加教师 { int result = 0; string sql = "insert into teacher (tno,tname,tsex,pwd)values(:tno,:tname,:tsex,:pwd)";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":tnno",OracleDbType.Varchar2,10), new OracleParameter(":tname",OracleDbType.Varchar2,10), new OracleParameter(":tsex",OracleDbType.Varchar2,10), new OracleParameter(":pwd",OracleDbType.Varchar2,10) };             para[0].Value = t.tno;             para[1].Value = t.tname;             para[2].Value = t.tsex;             para[3].Value = t.pwd; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } } 

（4）sc类

 public class cs类     { public string sname { get; set; } public string sno { get; set; } public string cname { get; set; } public string cno { get; set; } public int grade { get; set; } public static List<cs类> selectcs(string sno)//学生端查看已经选了的课程； { string sql = "select sname,sno,cname,cno,grade from sc where sno like:sno";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":sno", OracleDbType.Varchar2, 10) }; if (user.use == "administrator")//管理员可以看所有人的。 { para[0].Value = sno + "%"; } if(user.use=="student")//学生只能查看自己的 {                 para[0].Value = sno ; }             List<cs类> list = new List<cs类>(); OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para); OracleDataReader odr = cmd.ExecuteReader(); while (odr.Read()) {                     cs类 c = new cs类();                     c.sname = odr.GetString(0);                     c.sno = odr.GetString(1);                     c.cname = odr.GetString(2);                     c.cno = odr.GetString(3); if (odr.IsDBNull(4)) c.grade = 0; else c.grade = odr.GetInt32(4);                     list.Add(c); } } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return list; } public static int insertsc(cs类 cs)//学生选课、学校排课 { int result = 0; string sn, sna; int count; if (user.use == "student") { string sql1 = "select sname from student where sno=:snoo";//获取选课学生的名字                 OracleParameter[] para1 = new OracleParameter[] { new OracleParameter(":snoo",OracleDbType.Varchar2,10) };                 para1[0].Value = stu.stu1; OracleConnection con1 = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);                  con1.Open(); OracleCommand cmd1 = new OracleCommand(sql1, con1);                 cmd1.Parameters.AddRange(para1); OracleDataReader odr = cmd1.ExecuteReader();                 odr.Read();                 sn = odr.GetString(0); //选课 string sql = "insert into sc(sname ,sno,cname,cno,grade)values(:sname,:sno,:cname,:cno,:grade)";                 OracleParameter[] para = new OracleParameter[] { new OracleParameter(":sname",OracleDbType.Varchar2,10), new OracleParameter(":sno",OracleDbType.Varchar2,10), new OracleParameter(":cname",OracleDbType.Varchar2,10), new OracleParameter(":cno",OracleDbType.Varchar2,10), new OracleParameter(":grade",OracleDbType.Int32,10)};                 para[0].Value = sn;                 para[1].Value = cs.sno;                 para[2].Value = cs.cname;                 para[3].Value = cs.cno;                 para[4].Value = -1;//-1代表刚选课，老师还没有登成绩 OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                     con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                     cmd.Parameters.AddRange(para);                     result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                     MessageBox.Show(ex.Message); } finally {                     con.Close(); } } if(user.use=="administrator")//管理员操作时 { string sql1 = "select sno,sname from (select rownum a,sno, sname from student where sprof=:sprof)where a=:a";//获取选课学生的专业                     OracleParameter[] para1 = new OracleParameter[] { new OracleParameter(":sprof",OracleDbType.Varchar2,10), new OracleParameter(":a",OracleDbType.Int32,10) };                     para1[0].Value = stu.ssprof;                 para1[1].Value = course.j; OracleConnection con1 = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); if (con1.State == ConnectionState.Closed) { con1.Open(); } OracleCommand cmd1 = new OracleCommand(sql1, con1);                     cmd1.Parameters.AddRange(para1); OracleDataReader odr = cmd1.ExecuteReader();                     odr.Read();                     sna = odr.GetString(0);                     sn = odr.GetString(1); if (con1.State != ConnectionState.Closed) { con1.Close(); } string sql = "insert into sc(sname ,sno,cname,cno,grade)values(:sname,:sno,:cname,:cno,:grade)";                     OracleParameter[] para = new OracleParameter[] { new OracleParameter(":sname",OracleDbType.Varchar2,10), new OracleParameter(":sno",OracleDbType.Varchar2,10), new OracleParameter(":cname",OracleDbType.Varchar2,10), new OracleParameter(":cno",OracleDbType.Varchar2,10), new OracleParameter(":grade",OracleDbType.Int32,10)};                     para[0].Value = sna;                     para[1].Value = sn;                     para[2].Value = cs.cname;                     para[3].Value = cs.cno;                     para[4].Value = -1;//-1代表刚选课，老师还没有登成绩 OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                         con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                         cmd.Parameters.AddRange(para);                         result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                         MessageBox.Show(ex.Message); } finally {                         con.Close(); } } return result; } public static int deletesc(string cno)//学生退课/根据课程号解散该课程。（管理员进行解散课程时候连带） { int result = 0; string sql = "delete from sc where  cno=:cno";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":cno", OracleDbType.Varchar2, 10) };             para[0].Value = cno; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int changegrade(string sno, string cname, int grade)//教师登入学生成绩 { int result = 0; string sql = "update sc set grade=:grade where sno=:sno and cname=:cname ";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":grade",OracleDbType.Int32,10), new OracleParameter(":sno",OracleDbType.Varchar2,10), new OracleParameter(":cname",OracleDbType.Varchar2,10) };             para[0].Value = grade;             para[1].Value = sno;             para[2].Value = cname; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int deletstudent(cs类 cs)//根据学号删除学生选课（删除学生时使用） { int result = 0; string sql = "delete from sc where sno=:sno ";              OracleParameter[] para = new OracleParameter[] { new OracleParameter(":sno",OracleDbType.Varchar2,10), };             para[0].Value = cs.sno; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } } 

（5）课程表类：（Courselibrary）

public class courselibrary     { public string 课程号 { get; set; } public string 课程 { get; set; } public string 学分 { get; set; } public static List<courselibrary> Selectcourse(string cno)//显示课程 { string sql = "select *from courselibrary where 课程号 like:课程号";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":课程号",OracleDbType.Varchar2,10) };             para[0].Value = cno + "%";             List<courselibrary> list = new List<courselibrary>(); OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para); OracleDataReader odr= cmd.ExecuteReader(); while(odr.Read()) {                     courselibrary c = new courselibrary();                     c.课程号 = odr.GetString(0);                     c.课程 = odr.GetString(1);                     c.学分 = odr.GetString(2);                     list.Add(c); } } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return list; } public static int Addcourse(courselibrary c)//添加课程; { int result = 0; string sql = "insert into courselibrary (课程号,课程,学分)values(:cno,:cname,:ccredit)";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":cno",OracleDbType.Varchar2,10), new OracleParameter(":cname",OracleDbType.Varchar2,10), new OracleParameter(":ccredit",OracleDbType.Varchar2,10) };             para[0].Value = c.课程号;             para[1].Value = c.课程;             para[2].Value = c.学分; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } public static int Deletecourse(courselibrary c)//删除课程从课程库中 { int result = 0; string sql = "delete from courselibrary where 课程号 like:课程号";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":课程号",OracleDbType.Varchar2,10) };             para[0].Value = c.课程号; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery(); } catch (Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return result; } } 

（6scnifo视图（教师查看学生成绩，登分等）

class 教师查自己学生     { public string 学号 { get; set; } public string 姓名{ get; set; } public string 课程 { get; set; } public string 老师 { get; set; } public string 工号 { get; set; } public int 成绩 { get; set; } public string 学分 { get; set; } public static List<教师查自己学生> selectstudent(string tno) { string sql = "select * from scnifo where 工号 like:工号";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":工号",OracleDbType.Varchar2,10) };             para[0].Value = tno+"%";             List<教师查自己学生> list = new List<教师查自己学生>(); OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para); OracleDataReader odr = cmd.ExecuteReader(); while (odr.Read()) {                     教师查自己学生 t = new 教师查自己学生();                     t.学号 = odr.GetString(0);                     t.姓名 = odr.GetString(1);                     t.课程 = odr.GetString(2);                     t.老师 = odr.GetString(3);                     t.工号 = odr.GetString(4);                     t.成绩 = odr.GetInt32(5);                     t.学分 = odr.GetString(6);                     list.Add(t); } } catch(Exception ex) {                 MessageBox.Show(ex.Message); } finally {                 con.Close(); } return list; } } 

(8)studentnifo(学生查看成绩视图）

class studentinfo     { public string 学号 { get; set; } public string 姓名 { get; set; } public int  总学分 { get; set; } public int  总分 { get; set; } public static List<studentinfo>Selectdegree (string sno) { string sql = "select * from studentinfo where 学号 like:学号";             OracleParameter[] para = new OracleParameter[] { new OracleParameter(":学号", OracleDbType.Varchar2, 10) };             para[0].Value = sno+"%";             List<studentinfo> list = new List<studentinfo>(); OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                 con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para); OracleDataReader odr = cmd.ExecuteReader(); while (odr.Read()) {                     studentinfo s = new studentinfo();                     s.学号 = odr["学号"].ToString();                     s.姓名 = odr.GetString(1); if (odr.IsDBNull(2)) s.总学分 = 0; else s.总学分 = odr.GetInt32(2); if (odr.IsDBNull(3)) s.总分 = 0; else s.总分 = odr.GetInt32(3);                     list.Add(s); } } catch(Exception ex) {                 MessageBox.Show(ex. Message); } finally {                 con.Close(); } return list; } } 

(9)全局变量（创建时候自带的program.cs)

public class stu     { public static string[] str = new string[1000];//全局数组，用于标记被学生选了的课程 public static string stu1;//全局变量，判断登录的是哪个学生。 public static string ssprof;//全局变量，获取学生专业; } public class course     { public static string[] str = new string[1000];//全局数组，用于判断课程库是否可以选定课程删除课程（没有分配的课程可以删除，被分配了的课程不可以删除） public static string tno2;//全局变量，判断是哪个教师 public static int j = 0;//统一添加课程时使用的变量 } public static class user     { public static string use { get; set; }//全局变量，记录登录权限的变量 } 

public class stu     { public static string[] str = new string[1000];//全局数组，用于标记被学生选了的课程 public static string stu1;//全局变量，判断登录的是哪个学生。 public static string ssprof;//全局变量，获取学生专业; } public class course     { public static string[] str = new string[1000];//全局数组，用于判断课程库是否可以选定课程删除课程（没有分配的课程可以删除，被分配了的课程不可以删除） public static string tno2;//全局变量，判断是哪个教师 public static int j = 0;//统一添加课程时使用的变量 } public static class user     { public static string use { get; set; }//全局变量，记录登录权限的变量 } 

五：C#窗体设计以及编辑部分：

(1)利用工具箱中的lable、textbox、button、radiobutton控件设计一个登录界面

右下角的属性栏可以更改控件的按钮以及设置控件的相应功能；
Form1;cs

public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { if (radioButton1.Checked) {                 user.use = "student"; var from = new 菜单栏(); //from.Show(); //this.Hide(); string sql = "select *from student where sno=:sno and pwd=:pwd";                 OracleParameter[] para = new OracleParameter[] { new OracleParameter(":sno", OracleDbType.Varchar2, 10), new OracleParameter(":pwd",OracleDbType.Varchar2,10)};                 para[0].Value = this.textBox1.Text;                 para[1].Value = this.textBox2.Text; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                     con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                     cmd.Parameters.AddRange(para); OracleDataReader odr = cmd.ExecuteReader();//阅读器指向它 if (odr.HasRows) {                         stu.stu1 = this.textBox1.Text;                         MessageBox.Show("登录成功！", "欢迎学生" + textBox1.Text); this.DialogResult = DialogResult.OK; from.Show(); this.Hide(); } else                         MessageBox.Show("用户名或密码错误"); } catch (Exception ex) {                     MessageBox.Show(ex.Message); } finally {                     con.Close(); } } else if (radioButton2.Checked) {                 user.use = "teacher"; var from = new 菜单栏(); //from.Show(); //this.Hide(); string sql = "select *from teacher where tno=:tno and pwd=:pwd";                 OracleParameter[] para = new OracleParameter[] { new OracleParameter(":tno", OracleDbType.Varchar2, 10), new OracleParameter(":pwd",OracleDbType.Varchar2,10)};                 para[0].Value = this.textBox1.Text;                 para[1].Value = this.textBox2.Text; OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString); try {                     con.Open(); OracleCommand cmd = new OracleCommand(sql, con);                     cmd.Parameters.AddRange(para); OracleDataReader odr = cmd.ExecuteReader();//阅读器指向它 if (odr.HasRows) {                         course.tno2 = this.textBox1.Text;                         MessageBox.Show("登录成功！", "欢迎教师" + textBox1.Text); this.DialogResult = DialogResult.OK; from.Show(); this.Hide(); } else MessageBox.Show("用户名或密码错误"); } catch (Exception ex) {                     MessageBox.Show(ex.Message); } finally {                     con.Close(); } } else if (radioButton3.Checked && this.textBox1.Text == "1" && this.textBox2.Text == "1") { //管理员可以添加、删除学生、增加/删除老师、给老师分配课程、 //修改管理员密码                  user.use = "administrator"; var from = new 菜单栏();                 MessageBox.Show("欢迎您，管理员" + textBox1.Text); from.Show(); this.Hide(); } else {                 MessageBox.Show("请输入正确的用户名或者密码，或者选择正确的相应人员"); } } private void Form1_Load(object sender, EventArgs e) { /*int i;             for(i=0;i<1000;i++)             {                 stu.str[i] = null;                 course.str[i] = null;             }             stu.stu1 = null;             stu.ssprof = null;*/ this.Left = 800; this.Top = 350; } private void button2_Click(object sender, EventArgs e) {             stu.stu1 = null;//清空学生登陆记录 this.Close(); } 

(2)创建一个菜单栏（menustrip控件）

菜单栏.cs

public partial class 菜单栏 : Form     { public 菜单栏() { InitializeComponent(); } private void 学生查询ToolStripMenuItem_Click(object sender, EventArgs e) { this.IsMdiContainer = true;                 学生查询 frm = new 学生查询();                 frm.MdiParent = this;                 frm.Show(); } private void 菜单栏_Load(object sender, EventArgs e) { this.Left = 600; this.Top = 200; } private void 查看已选课程ToolStripMenuItem_Click(object sender, EventArgs e) { this.IsMdiContainer = true;                 学生查看已选课程 frm = new 学生查看已选课程();                 frm.MdiParent = this;                 frm.Show(); } private void 选退课系统ToolStripMenuItem_Click(object sender, EventArgs e) { this.IsMdiContainer = true;                 学生选退课系统 frm = new 学生选退课系统();                 frm.MdiParent = this;                 frm.Show(); } private void 修改密码ToolStripMenuItem_Click(object sender, EventArgs e) { if (user.use == "student") { this.IsMdiContainer = true;                 学生修改密码 frm = new 学生修改密码();                 frm.MdiParent = this;                 frm.Show(); } else if (user.use == "teacher") { this.IsMdiContainer = true;                 教师修改密码 frm = new 教师修改密码();                 frm.MdiParent = this;                 frm.Show(); } else MessageBox.Show("权限不足"); } private void 老师查询老师ToolStripMenuItem_Click(object sender, EventArgs e) { ; } private void 教师查询ToolStripMenuItem_Click(object sender, EventArgs e) { this.IsMdiContainer = true;                 教师查询 frm = new 教师查询();                 frm.MdiParent = this;                 frm.Show(); } private void 登入成绩ToolStripMenuItem_Click(object sender, EventArgs e) { this.IsMdiContainer = true;                 教师查学生成绩 frm = new 教师查学生成绩();                 frm.MdiParent = this;                 frm.Show(); } private void 添加删除学生ToolStripMenuItem_Click(object sender, EventArgs e) { this.IsMdiContainer = true;                 添加删除学生 frm = new 添加删除学生();                 frm.MdiParent = this;                 frm.Show(); } private void 退出ToolStripMenuItem_Click(object sender, EventArgs e) { this.IsMdiContainer = true; Form1 frm = new Form1();             stu.stu1 = null;//清空学生登录记录             course.tno2 = null;//清空教师登录记录 this.Hide();             frm.Show(); } private void 添加删除老师ToolStripMenuItem_Click(object sender, EventArgs e) { this.IsMdiContainer = true;                 添加删除教师 frm = new 添加删除教师();                 frm.MdiParent = this;                 frm.Show(); } private void 排课ToolStripMenuItem_Click(object sender, EventArgs e) { this.IsMdiContainer = true;                 排课 frm = new 排课();                 frm.MdiParent = this;                 frm.Show(); } private void 添加删除课程ToolStripMenuItem_Click(object sender, EventArgs e) { if (user.use == "administrator") { this.IsMdiContainer = true;                 添加删除课程 frm = new 添加删除课程();                 frm.MdiParent = this;                 frm.Show(); } else                 MessageBox.Show("无权访问"); } private void 教师管理ToolStripMenuItem_Click(object sender, EventArgs e) { if (user.use == "student") {                 MessageBox.Show("无权访问"); this.教师管理ToolStripMenuItem.HideDropDown(); } } private void 学生管理ToolStripMenuItem_Click(object sender, EventArgs e) { if (user.use == "teacher") {                 MessageBox.Show("无权访问"); this.教师管理ToolStripMenuItem.HideDropDown(); } } private void 课程管理ToolStripMenuItem_Click(object sender, EventArgs e) { if (user.use == "teacher") {                 MessageBox.Show("无权访问"); this.教师管理ToolStripMenuItem.HideDropDown(); } } private void 管理员选项ToolStripMenuItem_Click(object sender, EventArgs e) { if (user.use == "student"||user.use=="teacher") {                 MessageBox.Show("无权访问"); this.教师管理ToolStripMenuItem.HideDropDown(); } } } 

1:学生查询.cs

 private void button1_Click(object sender, EventArgs e) { this.dataGridView1.DataSource = 学生类.Selectstudent(this.textBox2.Text); this.dataGridView2.DataSource = studentinfo.Selectdegree(this.textBox2.Text); } 

2:教师查询：

教师查询.cs

public partial class 教师查询 : Form     { public 教师查询() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { this.dataGridView1.DataSource = 教师类.selectteacher(this.textBox1.Text); } private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) { if (this.dataGridView1.Rows.Count == 0) { return; } if (e.RowIndex < 0) return; DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex]; if(e.ColumnIndex==4) { //查询老师教授的课程 string tno = row.Cells[0].Value.ToString();                 course.tno2 = tno;//标记老师工号 this.dataGridView2.DataSource = 课程类.tselectcourse(tno); } } private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e) { if (dataGridView2.Rows.Count == 0) return; if (e.RowIndex < 0) return; DataGridViewRow row = this.dataGridView2.Rows[e.RowIndex];             课程类 c = new 课程类             {                 cno = row.Cells[0].Value.ToString(),                 tno = course.tno2//标记教师工号 };             cs类 cs = new cs类             {                 cno = row.Cells[0].Value.ToString() }; int i=int.Parse(c.cno); if (user.use == "teacher")//教师端不能退出已经被学生选了的课程 { if (e.ColumnIndex == 3) { if (stu.str[i] ==null)//判断是否有学生选课 { if (课程类.deleteteacher3(c) == 1 ) {                             course.str[i] = null;//清空排课记录                             MessageBox.Show("删除课程成功！"); } else                             MessageBox.Show("删除失败！"); } else                         MessageBox.Show("删除失败，该课程已有学生选课，若想删除请联系管理员进行统一删除："); } } else if (user.use == "administrator") { if (e.ColumnIndex == 3) { if (课程类.deleteteacher3(c) == 1 && cs类.deletesc(cs.cno) != 0) {                         course.str[i] = null;//清空排课记录                         stu.str[i] = null;//删除学生选课记录                         MessageBox.Show("解散课程成功！"); } else                      MessageBox.Show("解散课程成功！"); } } } } 

登入成绩：

public 教师查学生成绩() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { this.dataGridView1.DataSource = 教师查自己学生.selectstudent(this.textBox1.Text); } private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) { if (this.dataGridView1.Rows.Count == 0) { return; } if (e.RowIndex < 0) return; DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex]; if (e.ColumnIndex == 7) { //登成绩 string sno = row.Cells[0].Value.ToString(); string grade = row.Cells[5].Value.ToString(); string cname = row.Cells[2].Value.ToString(); int i=int.Parse(grade); if(cs类.changegrade(sno,cname,i)==1) {                     MessageBox.Show("修改成功"); } else {                     MessageBox.Show("修改失败"); } } } 

3:课程管理：

选、退课系统

private void button1_Click(object sender, EventArgs e) { this.dataGridView1.DataSource = cs类.selectcs(this.textBox1.Text); } private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) { if (this.dataGridView1.Rows.Count == 0) { return; } if (e.RowIndex < 0) return; DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex]; if (e.ColumnIndex == 5) { //选课 string grade = row.Cells[4].Value.ToString(); string cno = row.Cells[3].Value.ToString(); int i=int.Parse(grade); int j=int.Parse(cno); if (user.use == "student")//学生退课时有成绩的课不能退 { if (cs类.deletesc(cno) == 1 && i < 0) {                         stu.str[j] = null;//删除学生选课记录                         MessageBox.Show("退课成功！"); } else {                         MessageBox.Show("您这门课已有考核成绩，无法进行退课。"); } } else if(user.use=="administrator")//管理员可以任意进行退课 { if (cs类.deletesc(cno) == 1 ) {                         stu.str[j] = null;//删除学生选课记录                         MessageBox.Show("退课成功！"); } } else MessageBox.Show("出现错误"); } }