数据库数据库实验报告
开课学院及实验室:计算机科学与工程实验室
学院 计算机科学与网络工程
实验课程名称 数据库原理实验
实验项目名称 数据库系统设计综合实验
(1)实验目的
通过实验,使学生掌握数据库系统设计和开发的一般方法,能够设计并实现简单的数据库系统。
(2)实验要求
熟悉实验室实验环境,掌握实验预备知识,了解实验中故障排除的基本方法。实验中根据实验要求完成相应的任务,并独立完成实验报告。
(3)实验环境
Oracle 11g,windows 10;
(4)实验内容和步骤
假设有“教师”、“学生”、“课程”三个实体,教师的基本信息包括:工号、姓名、职称、工资,课程的基本信息包括:课程号、课程名、学分数,学生的基本信息包括:学号、姓名、性别、年龄。实体之间存在以下联系:
(1)一门课程至多有一个教师讲授,一个教师可以上多门课程;
(2)一个学生可以选修多门课程,一门课程可以由多个学生来选修,记录不同学生选修不同课程的成绩。
请根据以下步骤,完成数据库设计和应用系统的开发。
1.设计并画出E-R图,要求标注连通词(即联系类型);
共有三个实体:教师、学生、课程。课程增加了一个先修课程属性,教师和学生增加了密码信息。可得出如下的E-R模型图,加粗的属性为各关系的码。
2.将E-R图转化为关系模型,并指出各关系的主码和外码;
转换得到的关系模式如下:
student(sno,sname,ssex,sage,pwd),sno为student表的主码
course(cno,cname,cpno,ccredit,tno),cno为course表的主码,cpno和tno为外码,分别参照course(cno),teacher(tno)
sc(sno,cno,grade),(sno,cno)为sc表的主码,sno和cno为外码,分别参照student(sno),course(cno)
teacher(tno,tname,tsalary,trank,pwd),tno为teacher表的主码
3.选择一个关系数据库管理系统(例如MySql、SQL Server、Oracle等),设计数据库的物理结构;
在此次实验中,我使用的是oracle来设计该系统的数据库。
(1)首先创建一个用户c##dblesson,并为授予该用户一些权限。
create user c##dblesson identified by d123;
grant connect,resource,unlimited tablespace to c##dblesson;
grant create view to c##dblesson;
(2)数据库中的表格
a.学生表
学生表记录了学生的基本信息,主码为学号,密码可以由学生自己更改,或者由管理员更改,在系统登录界面学生可以通过用户名(即学号)和密码进行登录。
初始创建的学生表如下
b.教师表
教师表记录教师的基本信息,主码为工号。
c.课程表
课程表记录课程的课程号、课程名、课程学分以及任课教师等。主码为课程号。课程可以由管理员进行修改、添加等。
d.选修表
选修表记录学生所选课程的成绩,该成绩可以由管理员进行录入。学生选修的课程也会添加在这个表格中。
(3)视图
创建了四个表格后,为了方便系统统计信息,创建了以下视图
a.Studentinfo视图
这个视图用来统计每个学生选修课程的总学分以及已经完成的(即60分以上)选修课程的总学分。
b.Courseinfo视图
这个视图是每个教师所教的每门课程的平均成绩、最高分、最低分的统计信息。
c.Teacherinfo视图(两个)
用于统计不同职称的教师的数量、不同职称的教师的平均工资。
4.选择一门熟悉的面向对象程序设计语言,设计并开发一个应用管理系统,系统功能必须满足以下要求。
(1)系统包含两类用户,即管理员和学生,管理员登录系统后具有如下功能:
① 维护(添加、删除和修改等基本任务)学生基本信息、教师基本信息和课程基本信息等;
② 可以为学生重置密码;
③ 当学生选修了某门课程,课程成绩由管理员录入;
④ 能够统计不同职称的教师的数量、不同职称的教师的平均工资;可以统计每个教师所教的每门课程的平均成绩、最高分、最低分;统计每个学生选修课程的总学分以及已经完成的(即60分以上)选修课程的总学分;
⑤ 修改个人基本信息(包括密码)。
(2)学生利用学号和密码登录系统后具有如下功能:
①修改个人基本信息(包括密码);
②选修课程、退选课程;
③查询选修课程的成绩;
④统计个人的全部选修课程的总学分以及已经完成的(即60分以上)选修课程的总学分。
根据上述描述,解答下列问题:
注:实验报告最低要求必须包括以下内容:
(1)E-R图;
(2)把E-R模型转换为关系模型;
(3)相关表的SQL语句,包括创建表、视图、存储过程、函数、索引等的语句。
对于完成开发的同学,实验报告应该包括主要界面的功能测试(需要截图)以及相关的实现代码。
程序的源代码放在报告最后。
程序运行截图
下面为程序运行时各个功能测试的截图
-
登录界面
在登录界面,用户选择身份进行登录,用户输入的用户名和密码需与数据库的信息相等。在这个系统中定义了教师都为管理员身份。
-
登录成功后界面如下
教师界面
学生界面
- 教师端功能
a.维护(添加、删除和修改等)学生基本信息、教师基本信息和课程基本信息等;
b.可以为学生重置密码;
c.录入学生成绩;
d.能够统计不同职称的教师的数量、不同职称的教师的平均工资;可以统计每个教师所教的每门课程的平均成绩、最高分、最低分;统计每个学生选修课程的总学分以及已经完成的(即60分以上)选修课程的总学分;
e.修改个人基本信息(包括密码)。
教师端的菜单如下:
查询学生信息
- 学生端功能
学生登录之后可以查看学生的选课情况,统计个人的学分,选修课程、退选课程,修改个人信息,并查询自己选修课的成绩。
(5)实验总结
通过本次实验,了解概念结构设计的基本方法,掌握了逻辑结构设计的基本方法,学会了为模型设计E-R图并将E-R图转换为关系模式,理解了物理结构设计的基本方法。在实验中通过VS用C#语言编写了一个学生选课信息管理数据库系统,并设计了管理员和学生两种身份,该系统在查询,添加,修改,删除信息的时候会连接数据库,提高了数据的存储和查询的效率。通过这次实验,使对数据库的设计有了初步的认识,对SQL语句有了更深的理解。
(6)程序源代码
数据库SQL代码:
创建学生表
create table Student
(
Sno varchar(9) primary key,
Sname varchar(20) unique,
Ssex varchar(3),
Sage smallint,
pwd varchar(20) default '123456' not null
);
向学生表插入数据
insert into student(sname, ssex, sno, sage,pwd) values('李勇', '男', '201215121','20','123456');
insert into student values('201215122','刘晨','女','19','123456');
insert into student values('201215123','王敏','女','18','123456');
insert into student values('201215125','张立','男','19','123456');
insert into student values('201215100','张一','男','19','123456');
创建教师表
create table teacher
(
tno varchar(10) primary key,
tname varchar(20),
tsalary smallint ,
trank varchar(20),
pwd varchar(20) DEFAULT '123456' not null
);
向教师表插入数据
insert into teacher(tno,tname, trank, tsalary, pwd) values('1','王五','教授',20000,'123456');
insert into teacher(tno,tname, trank, tsalary, pwd) values('2','王柳','副教授',11000,'123456');
insert into teacher(tno,tname, trank, tsalary, pwd) values('3','王琦','导师',10000,'123456');
insert into teacher(tno,tname, trank, tsalary, pwd) values('4','王久','讲师',5000,'123456');
创建课程表
create table Course
(
Cno varchar(9) primary key,
Cname varchar(40),
Cpno varchar(4),
Ccredit smallint,
tno varchar(10) default '1' not null,
foreign key(tno) references teacher(tno),
foreign key(Cpno) references Course(Cno)
);
向课程表插入数据
insert into course values('6','数据处理','','2','1');
insert into course values('2','数学','','2','1');
insert into course values('7','PACSAL 语言','6','4','2');
insert into course values('5','数据结构','7','4','1');
insert into course values('1','数据库','5','4','3');
insert into course values('3','信息系统','1','4','4');
insert into course values('4','操作系统','6','3','4');
创建选修表
create table SC
(
Sno varchar(9),
Cno varchar(9),
Grade smallint,
primary key(Sno, Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
);
向选修表插入数据
insert into sc values('201215121','1','92');
insert into sc values('201215121','2','85');
insert into sc values('201215121','3','88');
insert into sc values('201215122','2','90' );
insert into sc values('201215122','3','80');
创建学生信息视图
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(ccredit) from course where cno in (select cno from sc where sno=student.sno and grade > 60)) 已完成课程总学分 ,
(select max(grade) from sc where sc.sno in (student.sno)) 最高分,
(select min(grade) from sc where sc.sno in (student.sno)) 最低分 from student;
drop view studentinfo;
创建课程信息视图
create view courseinfo as select course.cno 课程编号,course.cname 课程名称,teacher.tname 任课老师,
course.ccredit 课程学分,countnum 选修人数,avg_grade 平均分,max_grade 最高分,min_grade 最低分
from teacher,course left outer join
(select cname, count(*) countnum,avg(grade) avg_grade,max(grade) max_grade,min(grade) min_grade
from sc,course where course.cno=sc.cno group by cname)a1 on (course.cname=a1.cname)
where teacher.tno=course.tno;
创建教师信息的两个视图
create view teacherinfo1 as select trank ,count(trank)教师数量 ,avg(tsalary)平均薪水 from teacher group by trank;
create view teacherinfo2 as select tno 教师工号,tname 教师名称,(select count(student.sno) from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and course.tno=teacher.tno) 授课学生总人数
from teacher;
c#程序设计代码
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
namespace DbApp
{
internal static class Program
{
public static readonly string strCon = ConfigurationManager.ConnectionStrings["strCon"].ConnectionString;
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
FrmLogin frm = new FrmLogin();
if (frm.ShowDialog() == DialogResult.OK)
Application.Run(new MainFrm());
else
Application.Exit();
}
}
}
MainFrm.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class MainFrm : Form
{
public MainFrm()
{
InitializeComponent();
}
private void MainFrm_Load(object sender, EventArgs e)
{
if (FrmLogin.flag == 1)
{
this.Text = "学生信息管理系统,当前用户(管理员):" + FrmLogin.loginName;
学生个人信息ToolStripMenuItem.Visible = false;
选课中心ToolStripMenuItem.Visible = false;
统计个人学分ToolStripMenuItem.Visible = false;
}
else
{
this.Text = "学生信息管理系统,当前用户(学生):" + FrmLogin.loginName;
查询学生ToolStripMenuItem.Visible = false;
添加学生ToolStripMenuItem.Visible = false;
录入成绩ToolStripMenuItem.Visible = false;
课程管理ToolStripMenuItem.Visible = false;
教师管理ToolStripMenuItem.Visible = false;
教师个人中心ToolStripMenuItem.Visible = false;
统计ToolStripMenuItem.Visible = false;
}
}
private void 查询学生ToolStripMenuItem_Click(object sender, EventArgs e)
{
// 用户点击了菜单“查询学生”,打开一个“查询学生”的窗体进行学生查询
FrmStuQuery frm = new FrmStuQuery();
frm.MdiParent = this;
frm.Show();
}
private void 添加学生ToolStripMenuItem_Click(object sender, EventArgs e)
{
// 用户点击了菜单“添加学生”,打开一个“添加学生”的窗体
FrmStuInsert frm = new FrmStuInsert();
frm.MdiParent = this;
frm.Show();
}
private void 查询课程ToolStripMenuItem_Click(object sender, EventArgs e)
{
//打开一个查询课程的窗体
FrmCourseQuery frm = new FrmCourseQuery();
frm.MdiParent = this;
frm.Show();
}
private void 添加课程ToolStripMenuItem_Click(object sender, EventArgs e)
{
//打开添加课程的窗体
FrmCourseInsert frm = new FrmCourseInsert();
frm.MdiParent = this;
frm.Show();
}
private void 统计ToolStripMenuItem_Click(object sender, EventArgs e)
{
//显示统计窗体
FrmStatistics frm = new FrmStatistics();
frm.MdiParent = this;
frm.Show();
}
private void 查询教师ToolStripMenuItem_Click(object sender, EventArgs e)
{
//打开一个查询教师的窗体
FrmTeacherQuery frm = new FrmTeacherQuery();
frm.MdiParent = this;
frm.Show();
}
private void 选课ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmSelectCourse frm = new FrmSelectCourse();
frm.MdiParent = this;
frm.Show();
}
private void 退课ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmDropCourse frm = new FrmDropCourse();
frm.MdiParent = this;
frm.Show();
}
private void 添加教师ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmTeacherInsert frm = new FrmTeacherInsert();
frm.MdiParent = this;
frm.Show();
}
private void 录入成绩ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmInsertGrade frm = new FrmInsertGrade();
frm.MdiParent = this;
frm.Show();
}
private void 学生选课情况ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmStuCourse frm = new FrmStuCourse();
frm.MdiParent = this;
frm.Show();
}
private void 个人信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmPresonInfo frm = new FrmPresonInfo();
frm.MdiParent = this;
frm.Show();
}
private void 个人信息ToolStripMenuItem1_Click(object sender, EventArgs e)
{
FrmPresonInfo frm = new FrmPresonInfo();
frm.MdiParent = this;
frm.Show();
}
private void 我的选课情况ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmStuPersonCourse frm = new FrmStuPersonCourse();
frm.MdiParent = this;
frm.Show();
}
private void 授课情况ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmTeaPersonCourse frm = new FrmTeaPersonCourse();
frm.MdiParent = this;
frm.Show();
}
private void 统计个人学分ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmPersonStatistic frm = new FrmPersonStatistic();
frm.MdiParent = this;
frm.Show();
}
}
}
Course类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
namespace DbApp
{
public class Course
{
public string Cno { get; set; }
public string Cname { get; set; }
public string Cpno { get; set; }
public int Ccredit { get; set; }
public string Tno { get; set; }
//与course表相关的数据库操作实现在Course类中
//根据输入的课程名查找课程列表
public static List<Course> SelectCourse(String cname)
{
List<Course> list = new List<Course>();
string sql = "select cno,cname,cpno,ccredit,tno from course where cname like :cname";
//:cname是查询参数
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":cname",OracleDbType.Varchar2,40)
};
para[0].Value = cname + "%";
OracleConnection con = new OracleConnection(Program.strCon);
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.Cname = odr.GetString(1);
if (odr.IsDBNull(2)) c.Cpno = null;
else c.Cpno = odr.GetString(2);
c.Ccredit = odr.GetInt16(3);
c.Tno = odr.GetString(4);
list.Add(c);
}
}
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.Varchar2,4)
};
para[0].Value = cno;
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
//把修改后的课程c的信息更新到数据库
public static int UpdateCourse(Course c)
{//课程号没有变,其他属性可能变
string sql = "update course set cname = :cname, cpno = :cpno, ccredit = :ccredit,tno=:tno where cno = :cno";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":cname",OracleDbType.Varchar2,40),
new OracleParameter(":cpno",OracleDbType.Varchar2,4),
new OracleParameter(":ccredit",OracleDbType.Int16),
new OracleParameter(":tno",OracleDbType.Varchar2,20),
new OracleParameter(":cno",OracleDbType.Varchar2,4)
};
para[0].Value = c.Cname;
if (string.IsNullOrEmpty(c.Cpno))
para[1].Value = DBNull.Value;
else para[1].Value = c.Cpno;
para[2].Value = c.Ccredit;
para[3].Value = c.Tno;
para[4].Value = c.Cno;
int result = 0;//表示影响的行数
//连接数据库
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
//把课程c插入到数据库
public static int InsertCourse(Course c)
{
string sql = "insert into course (cno,cname,cpno,ccredit,tno) values(:cno,:cname,:cpno,:ccredit,:tno)";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":cname",OracleDbType.Varchar2,40),
new OracleParameter(":cpno",OracleDbType.Varchar2,4),
new OracleParameter(":ccredit",OracleDbType.Int16),
new OracleParameter(":cno",OracleDbType.Varchar2,4),
new OracleParameter(":tno",OracleDbType.Varchar2,4)
};
para[0].Value = c.Cno;
para[1].Value = c.Cname;
if (string.IsNullOrEmpty(c.Cpno))
para[2].Value = DBNull.Value;
else para[2].Value = c.Cpno;
para[3].Value = c.Ccredit;
para[4].Value = c.Tno;
int result = 0;//表示影响的行数
//连接数据库
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
}
}
Teacher类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
namespace DbApp
{
public class Teacher
{
public string Tno { get; set; }
public string Tname { get; set; }
public int Tsalary { get; set; }
public string Trank { get; set; }
public string pwd { get; set; }
public static List<Teacher> SelectTeacher(string tno)
{
List<Teacher> list = new List<Teacher>();
string sql = "select tno,tname,tsalary,trank,pwd from teacher where tno like :tno and tno!='sys'";
//:cname是查询参数
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":tno",OracleDbType.Varchar2,9)
};
para[0].Value = tno + "%";
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.AddRange(para);
OracleDataReader ord = cmd.ExecuteReader();
while (ord.Read())
{
Teacher c = new Teacher();//新生成一个Teacher的对象
c.Tno = ord.GetString(0);
c.Tname = ord.GetString(1);
c.Tsalary = ord.GetInt32(2);
c.Trank = ord.GetString(3);
c.pwd = ord.GetString(4);
list.Add(c);//将课程加入到列表中去
}
}
finally
{
con.Close();
}
return list;
}
public static List<Teacher> SelectAllTeacher()
{
List<Teacher> list = new List<Teacher>();
string sql = "select tno,tname,trank,tsalary from teacher";
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader ord = cmd.ExecuteReader();
while (ord.Read())
{
Teacher c = new Teacher();//新生成一个Teacher的对象
c.Tno = ord.GetString(0);
c.Tname = ord.GetString(1);
c.Tsalary = ord.GetInt32(2);
c.Trank = ord.GetString(3);
list.Add(c);//将课程加入到列表中去
}
}
finally
{
con.Close();
}
return list;
}
public static int DeleteTeacher(string tno)
{
int result = 0;//表示删除是否成功
string sql = "delete from teacher where tno=:tno";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":tno",OracleDbType.Varchar2,4)
};
para[0].Value = tno;
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);//执行sql命令;
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
//把修改后的信息更新到数据库
public static int UpdateTeacher(Teacher c)
{
string sql = string.Format("Update teacher set tname='{0}' ,tsalary='{1}', trank='{2}',pwd='{3} ' where tno='{4}'",
c.Tname, c.Tsalary, c.Trank, c.pwd, c.Tno);
int result = 0;//表示影响的行数
//连接数据库
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
public static int InserTeacher(Teacher c)
{
string sql = "insert into Teacher(tno,tname,trank,tsalary) values(:tno,:tname,:Trank,:tsalary)";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter("tno",OracleDbType.Varchar2,9),
new OracleParameter("tname",OracleDbType.Varchar2,20),
new OracleParameter("trank",OracleDbType.Varchar2,25),
new OracleParameter("tsalary",OracleDbType.Int16)
};
para[0].Value = c.Tno;
para[1].Value = c.Tname;
para[2].Value = c.Trank;
para[3].Value = c.Tsalary;
int result = 0;//表示影响的行数
OracleConnection con = new OracleConnection(Program.strCon);//连接数据库
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);//执行sql命令;
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
}
}
SC类
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
namespace DbApp
{
public class SC
{
public string Sno { get; set; }
public string Cno { get; set; }
public int Grade { get; set; }
public string Tno { get; set; }
public static int DeleteSc(SC c)
{
int result = 0;//表示删除是否成功
string sql = "delete from sc where cno=:cno and sno=:sno";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":cno",OracleDbType.Varchar2,4),
new OracleParameter(":sno",OracleDbType.Varchar2,9)
};
para[0].Value = c.Cno;
para[1].Value = FrmLogin.loginName;
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);//执行sql命令;
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
public static int DeleteSc2(string cno, string sno)
{
int result = 0;//表示删除是否成功
string sql = "delete from sc where cno=:cno and sno=:sno";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":cno",OracleDbType.Varchar2,4),
new OracleParameter(":sno",OracleDbType.Varchar2,9)
};
para[0].Value = cno;
para[1].Value = sno;
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);//执行sql命令;
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
public static int AddSc(SC c)//添加选课
{
int result = 0;//表示选课是否成功
string sql = "insert into sc(sno,cno,grade,tno) values(:sno,:cno,:grade,:tno)";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":sno",OracleDbType.Varchar2,9),
new OracleParameter(":cno",OracleDbType.Varchar2,4),
new OracleParameter(":grade",OracleDbType.Int16),
new OracleParameter(":tno",OracleDbType.Varchar2,9),
};
para[0].Value = FrmLogin.loginName;
para[1].Value = c.Cno;
para[2].Value = 0;
para[3].Value = c.Tno;
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);//执行sql命令;
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
public static List<SC> SelectSc(string tno)
{
List<SC> list = new List<SC>();
string sql = "select student.sno,course.cno,grade from sc,course,student where sc.sno=student.sno and sc.cno=course.cno and student.sno=:sno";
//:cname是查询参数
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter("sno",OracleDbType.Varchar2,9)
};
para[0].Value = tno;
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.AddRange(para);
OracleDataReader ord = cmd.ExecuteReader();
while (ord.Read())
{
SC c = new SC();//新生成一个Course的对象
c.Sno = ord.GetString(0);
c.Cno = ord.GetString(1);
c.Grade = ord.GetInt16(2);
c.Tno = ord.GetString(3);
list.Add(c);//将课程加入到列表中去
}
}
finally
{
con.Close();
}
return list;
}
public static int UpdateSc(SC c)
{
string sql = "update sc set grade=:grade where cno =:cno and sno=:sno ";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":grade",OracleDbType.Int16),
new OracleParameter(":cno",OracleDbType.Varchar2,4),
new OracleParameter(":sno",OracleDbType.Varchar2,9),
};
para[0].Value = c.Grade;
para[1].Value = c.Cno;
para[2].Value = c.Sno;
int result = 0;//表示影响的行数
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);//执行sql命令;
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
}
}
学生查询
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace DbApp
{
public partial class FrmStuQuery : Form
{
public FrmStuQuery()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//点击了“查询”按钮,执行selecty语句,在dataGridView1中显视查询结果
string sql = "select sno 学号,sname 姓名,ssex 性别,sage 年龄, pwd 密码 from student where sname like '" + this.textBox1.Text + "%' and sname !='sys'";
//连接到数据库执行sql命令的一般步骤
//1.创建连接对象;2.打开连接对象;3.创建命令对象;4.执行命令;5.关闭连接
OracleConnection con = new OracleConnection(Program.strCon);//1
try
{
con.Open();//2
OracleCommand cmd = new OracleCommand(sql, con);//3
OracleDataReader odr = cmd.ExecuteReader(); //4
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else
{
dataGridView1.DataSource = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//5
}
}
private void button2_Click(object sender, EventArgs e)
{
//用户点击了删除按钮,获取选中行的学号,从数据库删除该学号的学生
if (dataGridView1.SelectedRows.Count == 0) return;
if (MessageBox.Show("是否删除数据", "请确认信息", MessageBoxButtons.OKCancel) == DialogResult.Cancel)
return;
DataGridViewRow row = dataGridView1.SelectedRows[0];
string sno = row.Cells[0].Value.ToString();
string sql = string.Format("delete from student where sno='{0}'", sno);
OracleConnection con = new OracleConnection(Program.strCon);//1
try
{
con.Open();//2
OracleCommand cmd = new OracleCommand(sql, con);//3
if (cmd.ExecuteNonQuery() == 1) //4
{
MessageBox.Show("删除成功");
dataGridView1.Rows.Remove(row);
}
else
{
MessageBox.Show("没有找到对应学号的学生");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//5
}
}
private void dataGridView1_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
{
//用户双击了DataGridView的单元格内容,打开修改学生窗体,修改选中的学生信息
if (dataGridView1.SelectedRows.Count == 0) return;
FrmStuUpdate frm = new FrmStuUpdate();
//把选中的行的内容依次填写到窗体的文本框
DataGridViewRow row = dataGridView1.SelectedRows[0];
frm.tbSno.Text = row.Cells[0].Value.ToString();
frm.tbSname.Text = row.Cells[1].Value.ToString();
frm.tbSsex.Text = row.Cells[2].Value.ToString();
frm.tbSage.Text = row.Cells[3].Value.ToString();
frm.tbpwd.Text = row.Cells[4].Value.ToString();
frm.ShowDialog(this);
}
private void FrmStuQuery_Load(object sender, EventArgs e)
{
this.Text = "查询学生信息";
}
}
}
学生修改
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace DbApp
{
public partial class FrmStuUpdate : Form
{
public FrmStuUpdate()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//用户点击了“修改按钮”,把修改后的信息存储到数据库
string sql = string.Format("update student set sname = '{0}',ssex='{1}',sage='{2}',pwd='{3}' where sno='{4}'",tbSname.Text,tbSsex.Text,tbSage.Text,tbpwd.Text,tbSno.Text);
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
if (1 == cmd.ExecuteNonQuery())
{
MessageBox.Show("修改成功");
this.Close();
}
else
MessageBox.Show("没有找到对应学号的学生");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void FrmStuUpdate_Load(object sender, EventArgs e)
{
this.Text = "修改信息";
}
}
}
插入学生
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace DbApp
{
public partial class FrmStuInsert : Form
{
public FrmStuInsert()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//点击了“添加学生”按钮,把输入的学生信息存储到数据库
string sql = string.Format("insert into student(sno,sname,ssex,sage,pwd) values('{0}','{1}','{2}','{3}','{4}')", tbSno.Text, tbSname.Text, tbSsex.Text, tbSage.Text, tbpwd.Text);
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
if (1 == cmd.ExecuteNonQuery())
{
MessageBox.Show("插入成功");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void FrmStuInsert_Load(object sender, EventArgs e)
{
this.Text = "添加学生";
}
}
}
学生选课
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Linq;
using Oracle.ManagedDataAccess.Client;
namespace DbApp
{
public partial class FrmSelectCourse : Form
{
public FrmSelectCourse()
{
InitializeComponent();
List<Course> list = new List<Course>();
string sql = "select distinct course.cno 课程号 ,course.cname 课程名 ,course.cpno 先修课程,course.ccredit 学分,course.tno 任课老师 from course,sc " +
"where course.cno not in(select course.cno from course,sc where course.cno = sc.cno and sc.sno = '" + FrmLogin.loginName + "')";
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
Course c = new Course();
c.Cno = odr.IsDBNull(0) ? string.Empty : odr.GetString(0);
c.Cname = odr.IsDBNull(1) ? string.Empty : odr.GetString(1);
c.Cpno = odr.IsDBNull(2) ? string.Empty : odr.GetString(2);
c.Ccredit = odr.IsDBNull(3) ? 0 : odr.GetInt16(3);
c.Tno = odr.IsDBNull(4) ? string.Empty : odr.GetString(4);
list.Add(c);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
try
{
this.dataGridView1.DataSource = list;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count == 0) return;
DataGridViewRow row = dataGridView1.SelectedRows[0];
Course c = (Course)row.DataBoundItem;
if (MessageBox.Show("是否选修该门课程\n课程名为:" + c.Cname, "请确认信息", MessageBoxButtons.OKCancel) == DialogResult.Cancel)
return;
string sql = string.Format("insert into sc(sno, cno, grade) values('" + FrmLogin.loginName + "','{0}','')", c.Cno);
OracleConnection con = new OracleConnection(Program.strCon);//1
try
{
con.Open();//2
OracleCommand cmd = new OracleCommand(sql, con);//3
if (cmd.ExecuteNonQuery() == 1) //4
{
MessageBox.Show("选课成功");
}
else
{
MessageBox.Show("没有找到对应学号的学生");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//5
}
}
private void FrmSelectCourse_Load(object sender, EventArgs e)
{
this.Text = "选课";
}
}
}
统计个人信息
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class FrmPersonStatistic : Form
{
public FrmPersonStatistic()
{
InitializeComponent();
string sql = "select * from studentinfo where 学号 = " + FrmLogin.loginName;
OracleConnection con = new OracleConnection(Program.strCon);//1
try
{
con.Open();//2
OracleCommand cmd = new OracleCommand(sql, con);//3
OracleDataReader odr = cmd.ExecuteReader(); //4
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else
{
dataGridView1.DataSource = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//5
}
}
private void FrmPersonStatistic_Load(object sender, EventArgs e)
{
this.Text = "统计学分";
}
}
}
查看学生选课情况
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class FrmStuCourse : Form
{
public FrmStuCourse()
{
InitializeComponent();
string sql = "select student.sno 学号,sname 姓名,ssex 性别,sage 年龄, sc.cno 课程号, cname 课程名, ccredit 学分, grade 分数 from student, course, sc where student.sno = sc.sno and course.cno = sc.cno";
//连接到数据库执行sql命令的一般步骤
//1.创建连接对象;2.打开连接对象;3.创建命令对象;4.执行命令;5.关闭连接
OracleConnection con = new OracleConnection(Program.strCon);//1
try
{
con.Open();//2
OracleCommand cmd = new OracleCommand(sql, con);//3
OracleDataReader odr = cmd.ExecuteReader(); //4
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else
{
dataGridView1.DataSource = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//5
}
}
private void FrmStuCourse_Load(object sender, EventArgs e)
{
this.Text = "选课情况";
}
}
}
查看学生个人选课
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class FrmStuPersonCourse : Form
{
public FrmStuPersonCourse()
{
InitializeComponent();
string sql = "select student.sno 学号, sname 姓名, course.cno 课程号, cname 课程, ccredit 学分, grade 成绩 from student, course,sc where course.cno = sc.cno and student.sno = sc.sno and student.sno=" + FrmLogin.loginName;
OracleConnection con = new OracleConnection(Program.strCon);//1
try
{
con.Open();//2
OracleCommand cmd = new OracleCommand(sql, con);//3
OracleDataReader odr = cmd.ExecuteReader(); //4
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else
{
dataGridView1.DataSource = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//5
}
}
private void FrmStuPersonCourse_Load(object sender, EventArgs e)
{
this.Text = "选课情况";
}
}
}
查询教师信息
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class FrmTeacherQuery : Form
{
public FrmTeacherQuery()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
this.dataGridView1.DataSource = Teacher.SelectTeacher(this.textBox1.Text);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void dataGridView1_CellClick_1(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex < 0) return;//标题行的编号是-1
DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
Teacher c = (Teacher)row.DataBoundItem;//将dataGridView中的一行数据转换为Teacher对象
try
{
if (e.ColumnIndex == 5)//删除操作
{
if (MessageBox.Show("是否删除数据!", "请确认信息", MessageBoxButtons.OKCancel) == DialogResult.Cancel)
return;
if (Teacher.DeleteTeacher(c.Tno) == 1)
MessageBox.Show("删除成功!");
else
MessageBox.Show("没有找到对应教工号的教师!");
}
else if (e.ColumnIndex == 6) //修改
{//点击了e.RowIndex行的修改按钮,那么打开一个窗体修改课程c
//修改操作
FrmTeacherUpdate frm = new FrmTeacherUpdate();
frm.tbTno.Text = c.Tno;
frm.tbTname.Text = c.Tname;
frm.tbTrank.Text = c.Trank;
frm.tbTsalary.Text = c.Tsalary.ToString();
frm.tbpwd.Text = c.pwd;
frm.ShowDialog(this);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void FrmTeacherQuery_Load(object sender, EventArgs e)
{
this.Text = "查询教师信息";
}
}
}
修改教师信息
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace DbApp
{
public partial class FrmTeacherUpdate : Form
{
public FrmTeacherUpdate()
{
InitializeComponent();
}
private void button1_Click_1(object sender, EventArgs e)
{
//用户点击了“修改按钮”,把修改后的信息存储到数据库
string sql = string.Format("update teacher set tname = '{0}',trank='{1}',tsalary='{2}',pwd='{3}' where tno='{4}'", tbTname.Text, tbTrank.Text, tbTsalary.Text, tbpwd.Text, tbTno.Text);
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
if (1 == cmd.ExecuteNonQuery())
{
MessageBox.Show("修改成功");
this.Close();
}
else
MessageBox.Show("没有找到对应学号的教师");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void FrmTeacherUpdate_Load(object sender, EventArgs e)
{
this.Text = "修改信息";
}
}
}
教师查看授课情况
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class FrmTeaPersonCourse : Form
{
public FrmTeaPersonCourse()
{
InitializeComponent();
string sql = "select teacher.tno 工号, tname 姓名, course.cno 课程号, cname 课程 from teacher, course where course.tno = teacher.tno and teacher.tno=" + FrmLogin.loginName;
OracleConnection con = new OracleConnection(Program.strCon);//1
try
{
con.Open();//2
OracleCommand cmd = new OracleCommand(sql, con);//3
OracleDataReader odr = cmd.ExecuteReader(); //4
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else
{
dataGridView1.DataSource = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//5
}
}
private void FrmTeaPersonCourse_Load(object sender, EventArgs e)
{
this.Text = "授课情况";
}
}
}
添加教师
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class FrmTeacherInsert : Form
{
public FrmTeacherInsert()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = string.Format("insert into teacher(tno,tname, trank, tsalary, pwd) values('{0}','{1}','{2}',{3},'{4}')", tbTno.Text, tbTname.Text, tbTrank.Text, tbTsalary.Text, tbpwd.Text);
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
if (1 == cmd.ExecuteNonQuery())
{
MessageBox.Show("插入成功");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void FrmTeacherInsert_Load(object sender, EventArgs e)
{
this.Text = "添加教师";
}
}
}
录入成绩
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class FrmInsertGrade : Form
{
public FrmInsertGrade()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = string.Format("update sc set grade = '{0}' where sno='{1}' and cno = '{2}'", tbGrade.Text, tbSno.Text, tbCno.Text);
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
if (1 == cmd.ExecuteNonQuery())
{
MessageBox.Show("录入成功");
this.Close();
}
else
MessageBox.Show("没有找到对应学号的学生");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void FrmInsertGrade_Load(object sender, EventArgs e)
{
this.Text = "录入成绩";
}
}
}
查询课程信息
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class FrmCourseQuery : Form
{
public FrmCourseQuery()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//根据文本框输入的课程名找课程列表,然后绑定到datagridview1
try
{
this.dataGridView1.DataSource = Course.SelectCourse(this.textBox1.Text);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
//用户单击了datagridview1的单元格,参数e会记录你单击的是哪一行,哪一列
//MessageBox.Show(e.RowIndex.ToString() + "," + e.ColumnIndex.ToString());
if (e.RowIndex < 0) return;//标题行的编号是-1
DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
Course c = (Course)row.DataBoundItem;
try
{
if (e.ColumnIndex == 5) //删除
{//点击了e.RowIndex行的删除按钮,删除课程号等于c.cno的课程
if (MessageBox.Show("是否删除数据", "请确认信息", MessageBoxButtons.OKCancel) == DialogResult.Cancel)
return;
else
{
if (Course.DeleteCourse(c.Cno) == 1)
MessageBox.Show("删除成功");
else MessageBox.Show("没有找到对应课程号的课程");
}
}
else if (e.ColumnIndex == 6) //修改
{//点击了e.RowIndex行的修改按钮,那么打开一个窗体修改课程c
FrmCourseUpdate frm = new FrmCourseUpdate();
//在显示之前,把需要修改的课程信息填写到对话框的文本框
frm.tbCno.Text = c.Cno;
frm.tbCname.Text = c.Cname;
frm.tbCpno.Text = c.Cpno;
frm.tbCcredit.Text = c.Ccredit.ToString();
frm.tbTno.Text = c.Tno;
frm.ShowDialog(this);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void FrmCourseQuery_Load(object sender, EventArgs e)
{
this.Text = "查询课程";
}
}
}
修改课程信息
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class FrmCourseUpdate : Form
{
public FrmCourseUpdate()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//把修改后的信息存储到数据库
Course c = new Course();
c.Cno = this.tbCno.Text;
c.Cname = tbCname.Text;
c.Cpno = tbCpno.Text;
c.Ccredit = Convert.ToInt32(tbCcredit.Text);
c.Tno = tbTno.Text;
//调用course类的函数 ,把修改后的课程c的信息更新到数据库
try
{
if (1 == Course.UpdateCourse(c))
{
MessageBox.Show("修改成功");
this.Close();
}
else MessageBox.Show("没有找到对应课程号的课程");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void FrmCourseUpdate_Load(object sender, EventArgs e)
{
this.Text = "修改课程";
}
}
}
添加课程
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
public partial class FrmCourseInsert : Form
{
public FrmCourseInsert()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//把用户输入的课程信息存储到数据库
Course c = new Course();
c.Cno = this.tbCno.Text;
c.Cname = tbCname.Text;
c.Cpno = tbCpno.Text;
c.Ccredit = Convert.ToInt32(tbCcredit.Text);
c.Tno = tbTno.Text;
//调用Course.insertCourse(c)插入数据到数据库
try
{
Course.InsertCourse(c);
MessageBox.Show("插入成功");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void FrmCourseInsert_Load(object sender, EventArgs e)
{
this.Text = "添加课程";
}
}
}
统计各种信息
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace DbApp
{
public partial class FrmStatistics : Form
{
public FrmStatistics()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "select *from studentinfo where 学号 != 'sys'";
OracleConnection con = new OracleConnection(Program.strCon);//1
try
{
con.Open();//2
OracleCommand cmd = new OracleCommand(sql, con);//3
OracleDataReader odr = cmd.ExecuteReader(); //4
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else
{
dataGridView1.DataSource = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//5
}
}
private void button2_Click(object sender, EventArgs e)
{
string sql = "select * from courseinfo";
OracleConnection con = new OracleConnection(Program.strCon);//1
try
{
con.Open(); //2
OracleCommand cmd = new OracleCommand(sql, con); // 3
OracleDataReader odr = cmd.ExecuteReader();//4
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else
{
dataGridView1.DataSource = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//5
}
}
private void button3_Click(object sender, EventArgs e)
{
string sql = "select * from teacherinfo1 where trank!='sys' ";
//1.创建连接对象;2。打开连接对象;3.创建命令对象;4.执行命令;5.关闭连接
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();//2
OracleCommand cmd = new OracleCommand(sql, con);//3
OracleDataReader odr = cmd.ExecuteReader();//4
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else dataGridView1.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//6
}
}
private void button4_Click(object sender, EventArgs e)
{
string sql = "select *from teacherinfo2 where 教师工号!='sys'";
//1.创建连接对象;2。打开连接对象;3.创建命令对象;4.执行命令;5.关闭连接
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();//2
OracleCommand cmd = new OracleCommand(sql, con);//3
OracleDataReader odr = cmd.ExecuteReader();//4
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else dataGridView1.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();//6
}
}
private void FrmStatistics_Load(object sender, EventArgs e)
{
this.Text = "统计信息";
}
}
}