管理系统"/>
c#,Oracle 10g——创建一个学生课程管理系统
目录
- 一、基本要求
- 二、创建学生课程管理系统
- (1)在sqldeveloper设计数据库物理模式
- (2)在c#设计系统窗口
- 三、相应代码
一、基本要求
假设有“教师”、“学生”、“课程”三个实体,教师的基本信息包括:工号、姓名、职称、工资,课程的基本信息包括:课程号、课程名、学分数,学生的基本信息包括:学号、姓名、性别、年龄。系统必须满足以下要求:
(1)一门课程只能有一个教师任课,一个教师可以上多门课程;
(2)一个学生可以选修多门课程,一门课程可以由多个学生来选修,记录不同学生选修不同课程的成绩;
(3)设置一个管理员,用于维护(添加、删除和修改等基本任务)学生基本信息、教师基本信息和教师所授课程等工作,此外,管理员添加学生时,为其设置初始密码;当学生选修了某门课程,课程成绩由管理员录入;
(4)学生可以利用学号和密码登录系统,登陆系统后,可以进行选课、修改密码和个人基本信息、查询自己的选课及总学分等操作;
(5)能够统计不同职称的教师的数量、不同职称的教师的平均工资,可以统计每门课程的平均成绩、最高分、最低分,统计每个学生选修课程的总学分;
实现环境
vs 2017, Oracle 10g, sqldeveloper.
- 相关E-R图
- 关系模式
Student(Sno,Sname,Ssex,Sage,Sdept) Sno---主码。Course(Cno,Cname,Ccredit) Cno---主码。Teacher(Tno,Tname,Tsalary,Tpost) Tno---主码。SC(Sno,Cno) Sno,Cno---主码,Sno---外码,Cno---外码。TC(Tno,Cno) Tno,Cno---主码,Sno---外码,Cno---外码,取值唯一。
二、创建学生课程管理系统
(1)在sqldeveloper设计数据库物理模式
1.创建学生表
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,Sname CHAR(20) UNIQUE,Ssex CHAR(3), Sage SMALLINT, Sdept CHAR(20)Pwd VARCHAR2(20)
);
2.创建课程表
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,Cname CHAR(40),Ccredit SMALLINT,
);
3.创建创建学生选修课程表
CREATE TABLE SC
(Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY(Sno,Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno) on delete cascade,FOREIGN KEY (Cno) REFERENCES Course(Cno) on delete cascade
);
4.创建教师表
CREATE TABLE Teacher
(TNO VARCHAR2(20) not null,TName NVARCHAR2(20) not null,TSex VARCHAR2(3) not null,TSalary NUMBER(30,2),TPost VARCHAR2(20),constraint PK_TEACHER primary key (TNO)
);
5.创建教师授课表
create table TC(
TNO VARCHAR2(20) not null,
CNO CHAR(4) not null UNIQUE,
constraint PK_TC PRIMARY KEY (TNO,CNO),
constraint FK_TC1 FOREIGN KEY(TNO) REFERENCES teacher(tno) ON delete CASCADE ,
constraint FK_TC2 FOREIGN KEY(CNO) REFERENCES course(cno) on delete cascade
);
6.创建统计视图
-------创建学生成绩视图
create VIEW studenttinfo
as
select student.sno,student.sname,
(select sum(grade) from sc where sno=student.sno) 总分,
(select sum(ccredit) from course where cno in(select cno from sc where sno=student.sno)) 总学分
from student;-------创建课程统计视图
create view courseinfo
as
select courseo,courseame,courseredit,
(select count(sno) from sc where cno=courseo) 选修人数
from course;-------创建学生选修课程统计视图
create view studentselsction
as
select student.sno,sname,courseo,cname,grade
from student,sc,course
where student.sno=sc.sno and courseo=sco;-------创建教师教授课程具体信息统计视图
create view teacherteaching
as
select teacher.tno,teacher.tname,courseo,courseame
from teacher,course,tc
where teacher.tno=tc.tno and courseo=tco;-------创建教师统计视图
SELECT distinct teacher.tpost,(select count(x.tno) from teacher x where x.tpost=teacher.tpost) 教师总数,(select cast(avg(x.tsalary) as decimal(10,2)) from teacher x where x.tpost=teacher.tpost) 平均工资
FROM teacher
(2)在c#设计系统窗口
创建窗体应用程序后,添加扩展引用Oracle.ManagedDataAccess来使用Oracle数据库。
1.在App.config中添加数据库用户名和密码。
<configuration><connectionStrings><add name="strCon" connectionString ="data source=服务器ip/数据库名;User id=与用户名;password=密码"/>
2.定义类来储存信息以及插入、删除等函数
-----CommonData.cs
//全局变量用于存放登录后的用户名
namespace CourseSelectSystem
{class CommonData{public static string sno;public static string passwords;}
}-----Course.cs
//课程信息
namespace CourseSelectSystem
{class Course{public string Cno{get;set;}public string Cname{get;set;}public int Ccredit{get;set;}//查询课程信息public static List<Course> SelectCourse(string cname){string sql = "select cno,cname,ccredit from course where cname like :cname";OracleParameter[] para = new OracleParameter[] { new OracleParameter(":cname", OracleDbType.Char, 40) };para[0].Value = cname + "%";List<Course> list = new List<Course>();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()){Course c = new Course();c.Cno = odr.GetString(0);//c.Cno = odr["cno"].ToString();c.Cname = odr.GetString(1);c.Ccredit = odr.GetInt32(2);list.Add(c);}}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return list;}//删除课程public static int DeleteCourse(string cno){int result = 0;string sql = "delete from course where cno=:cno";OracleParameter[] para = new OracleParameter[] { new OracleParameter(":cno", OracleDbType.Char, 4) };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 InsertCourse(Course c){int result = 0;string sql = "insert into course(cno,cname,ccredit) values (:cno,:cname,:ccredit)";OracleParameter[] para = new OracleParameter[]{new OracleParameter(":cno", OracleDbType.Char, 4) ,new OracleParameter(":cname", OracleDbType.Char, 40) ,new OracleParameter(":ccredit", OracleDbType.Int32)};para[0].Value = c.Cno;para[1].Value = c.Cname;para[2].Value = c.Ccredit;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 UpdateCourse(Course c){int result = 0;string sql = "update course set cname=:cname,ccredit=:ccredit where cno=:cno";OracleParameter[] para = new OracleParameter[]{new OracleParameter(":cname", OracleDbType.Char, 40) ,new OracleParameter(":ccredit", OracleDbType.Int32),new OracleParameter(":cno", OracleDbType.Char, 4)};para[0].Value = c.Cname;para[1].Value = c.Ccredit;para[2].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 List<Course> StudentSelectCourse(string sno){string sql = "select cno,cname,ccredit from course where cno not in(select cno from sc where sno=:sno)";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":sno", OracleDbType.Char, 9)};para[0].Value = sno + "%";List<Course> list = new List<Course>();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()){Course c = new Course();c.Cno = odr.GetString(0);//c.Cno = odr["cno"].ToString();c.Cname = odr.GetString(1);c.Ccredit = odr.GetInt32(2);list.Add(c);}}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}return list;}//学生选课public static int StudentChooseCourse(string sno,string cno){int result = 0;string sql = "insert into sc(sno,cno,grade) values (:sno,:cno,:grade)";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":sno", OracleDbType.Char, 9),new OracleParameter(":cno", OracleDbType.Char, 4),new OracleParameter(":grade", OracleDbType.Int32)};para[0].Value = sno;para[1].Value = cno;para[2].Value = 0;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.设计登录窗口
//对登录按钮添加单击响应事件(管理员账号:Admin 密码:admin)
private void button1_Click(object sender, EventArgs e){string sql = "select * from student where sno=:sno and pwd=:pwd";OracleParameter[] para = new OracleParameter[] {new OracleParameter(":sno",OracleDbType.Char,9),new OracleParameter(":pwd",OracleDbType.Varchar2,20)};para[0].Value = this.tb_name.Text;//获取文本框信息引用到sql语句中para[1].Value = this.tb_passwords.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(para[0].Value.Equals("Admin")&& para[1].Value.Equals("admin")){MessageBox.Show("管理员登录成功!");this.Hide();//弹出新窗口隐藏登录窗口AdminForm admin = new AdminForm();admin.Closed+= new EventHandler(this.admin_Closed);admin.Show();}else if (odr.HasRows){MessageBox.Show("学生登录成功!");CommonData.sno = this.tb_name.Text;CommonData.passwords = this.tb_passwords.Text;// Console.WriteLine(CommonData.sno);this.Hide();StudentForm student = new StudentForm();student.Closed+= new EventHandler(this.student_Close);student.Show();}else{this.tb_name.Text = "";this.tb_passwords.Text = "";MessageBox.Show("用户名或密码错误。");}}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}}//管理员界面关闭事件private void admin_Closed(object sender, EventArgs e){Application.Exit();}//学生界面关闭事件private void student_Close(object sender, EventArgs e){Application.Exit();}
代码效果
4.(1)设计管理员登录后操作界面
(2)新建相应功能的窗口(例如建立一个添加课程的窗口):
添加响应事件:
private void button1_Click(object sender, EventArgs e){Course c = new Course();c.Cno = this.tbCno.Text;c.Cname = this.tbCname.Text;c.Ccredit = Convert.ToInt32(tbCcredit.Text);//使用定义好的course类中的InsertCourse函数if (Course.InsertCourse(c) == 1){MessageBox.Show("添加成功。");this.Close();}}
(3)对于查询功能的窗口,要在数据视图中添加相应数据源
之后在选择已经定义好的类。
(4)在菜单栏中添加相应功能,并且添加打开新窗口的响应事件,例如:
private void 查询学生ToolStripMenuItem_Click(object sender, EventArgs e){AdminStudentSearch adminStudentSearch = new AdminStudentSearch();adminStudentSearch.MdiParent = this;adminStudentSearch.Show();}
(
5.设计学生操作界面
设计方法和设计管理员操作界面相差不大,使用函数时只用把参数改成全局变量CommonData中的相应数据。
三、相应代码
下载好后需要先创建相应的数据库,之后在在App.config文件中添加数据库用户名和密码
更多推荐
c#,Oracle 10g——创建一个学生课程管理系统
发布评论