实验目的
通过实验，使学生掌握数据库系统设计和开发的一般方法，能够设计并实现简单的数据库系统。
实验要求
熟悉实验室实验环境，掌握实验预备知识，了解实验中故障排除的基本方法。实验中根据实验要求完成相应的任务，并独立完成实验报告。
实验内容
设计的数据库系统可从以下题目中选择，但不限定于以下题目，可自由选择。
需提供系统简要的需求分析，给出大体的功能介绍；完成数据库的概念结构设计，建议用PowerDesigner画出系统的逻辑结构图或物理结构图（也可以使用其他工具）；任选自己熟悉的数据库管理系统和编程语言完成系统主要功能模块的开发。
系统题目参考：
1、学生成绩管理系统
2、网上书城系统
3、进销存系统
4、酒店客房管理系统
5、图书管理系统
选题【学生成绩管理系统】
【运行环境】windows10-VS2019-C#-windows窗体(.NET Framework 4.7.2)
【数据库】Oracle-SQL Developer(高版本JDK)
一、系统功能概述
【学生】
查询学生信息、查询个人信息、查询课程信息。
【教师】
查询教师信息、查询个人信息、学生成绩评比。
【管理员】
更新：更新学生信息、更新教师信息、更新课程信息。
添加/删除：添加学生、删除学生、添加教师、删除教师、添加课程、删除课程。
统计：统计学生信息、统计教师信息、统计课程信息、统计学生成绩信息。
二、逻辑图及部分说明
E-R图
（图是软件工程实验的建议大家自己更改一下，不要照搬）
注：在course处增加cpno(先修课程)，图中未增加，自行添加
模块功能图
3.表内容：(红字为主码)
【student】sno,ssex,sage,sname,pwd;
【course】cno,cname,ccredit,cpno,tno;
【sc】sno,cno,grade,tno;
【teacher】tno,tname,tposition,tsalary,pwd;
SQL语句
/*级联删除*/
drop table teacher cascade constraints;
drop table student cascade constraints;
drop table course cascade constraints;
drop table sc cascade constraints;
/*删除同名表*/
DECLARE
tmp INTEGER DEFAULT 0;
BEGIN
SELECT
COUNT(*)
INTO tmp
FROM
user_tables
WHERE
table_name = 'SC';
IF ( tmp > 0 ) THEN
EXECUTE IMMEDIATE 'drop table SC';
END IF;
SELECT
COUNT(*)
INTO tmp
FROM
user_tables
WHERE
table_name = 'STUDENT';
IF ( tmp > 0 ) THEN
EXECUTE IMMEDIATE 'drop table STUDENT';
END IF;
SELECT
COUNT(*)
INTO tmp
FROM
user_tables
WHERE
table_name = 'COURSE';
IF ( tmp > 0 ) THEN
EXECUTE IMMEDIATE 'drop table COURSE';
END IF;
SELECT
COUNT(*)
INTO tmp
FROM
user_tables
WHERE
table_name = 'TEACHER';
IF ( tmp > 0 ) THEN
EXECUTE IMMEDIATE 'drop table TEACHER';
END IF;
END;
【建表】
/*学生信息*/
create table student
(
sno varchar2(10) primary key,
sname varchar2(20) not null unique,
ssex char(3) check(ssex in('男','女')),
sage smallint not null,
pwd varchar(20) not null
);
/*教师信息*/
create table teacher
(
tno varchar(7) primary key,
tname varchar(20) not null unique,
tposition varchar(20) not null,
tsalary smallint not null,
pwd varchar(20) not null
);
/*课程信息*/
create table course
(
cno varchar(4) primary key,
cpno varchar(10),
cname varchar(40) not null unique,
ccredit smallint not null,
tno varchar(7) not null,
foreign key (tno) references teacher(tno)
);
/*成绩信息*/
create table sc
(
sno varchar(10) not null,
cno varchar(4) not null,
grade smallint not null,
tno varchar(7) not null,
primary key(sno,cno),
foreign key (sno) references student(sno),
foreign key (cno) references course(cno),
foreign key (tno) references teacher(tno)
);
【建立视图】
/*创建视图*/
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 studentinfo
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 teacherinfo1 as select tposition 职位,count(tno) 在任人数,avg(tsalary) 平均工资 from teacher group by tposition;
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;
【插入数据】
/*学生数据*/
insert into student(sname,ssex,sno, sage, pwd) values('李勇','男','1906300161',20,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('刘晨','女','1906300162',19,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('王敏','女','1906300163',18,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('张立','男','1906300164',19,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('卢俊旭','男','1906300165',19,'123456');
/*教师数据*/
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t001','张三','教授',20000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t002','李四','副教授',15000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t003','王五','教授',35000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t004','王八蛋','副教授',38000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t005','大铁棒','副教授',42000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t006','小铁棒','教授',60000,'123456');
/*课程数据*/
insert into course(cno,cname,ccredit,tno,cpno) values('6','数据结构',2,'t001','2');
insert into course(cno,cname,ccredit,tno,cpno) values('2','高等数学',4,'t002',null);
insert into course(cno,cname,ccredit,tno,cpno) values('7','汇编语言',4,'t001',null);
insert into course(cno,cname,ccredit,tno,cpno) values('5','软件工程',2,'t003','7');
insert into course(cno,cname,ccredit,tno,cpno) values('1','java',3,'t004','5');
insert into course(cno,cname,ccredit,tno,cpno) values('3','数据库',3,'t005','1');
insert into course(cno,cname,ccredit,tno,cpno) values('4','操作系统',3,'t006','1');
/*SC数据*/
insert into sc values('1906300161','6',20,'t001');
insert into sc values('1906300162','2',40,'t002');
insert into sc values('1906300163','5',60,'t003');
insert into sc values('1906300164','1',80,'t004');
insert into sc values('1906300165','3',90,'t005');
数据尽量自己更改，用自己的学号或者学号基础上改几个数字。
C#窗体具体下载和使用
【下载】
如果没有该选项，则在VS installer中勾选如下：
右边的默认就行，不需要勾选太多。
【安装重要数据库连接扩展】
如果【引用】得到扩展中有Oracle.ManagedDataAccess或者其他类似此类的扩展可忽略如下步骤：
最后记得在【引用】的【添加引用】下【扩展】界面勾选上。
【数据库的连接语句】
在App.config中添加如下：
<connectionStrings>
<add name ="strCon" connectionString="data source=127.0.0.1:1521/orcl;user id =system;password=Ljx"/>
</connectionStrings>【说明】data source的那几个数字是端口号，‘/’后边是SID,user id是用户名（不是连接名），password是对应密码。
建立连接时，添加：
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);【添加窗体和类的方式】
右键项目，点击添加
代码及截图
【登录界面】
FrmLogin.cs
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
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 FrmLogin : Form
{
public FrmLogin()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql=""; OracleParameter[] para;
if (radioButton2.Checked==true)//学生登录
{
sql = "select * from student where sno=:sno and pwd=:pwd";
para = new OracleParameter[]
{new OracleParameter(":sno",OracleDbType.Varchar2,10),
new OracleParameter(":pwd",OracleDbType.Char,20)
};
para[0].Value = tbId.Text;
para[1].Value = tbPwd.Text;
}
else //教师登录
{
sql = "select * from teacher where tno=:tno and pwd=:pwd";
para = new OracleParameter[]
{new OracleParameter(":tno",OracleDbType.Char,7),
new OracleParameter(":pwd",OracleDbType.Char,20)
};
para[0].Value = tbId.Text;
para[1].Value = tbPwd.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.Read()) //HasRows
{
MessageBox.Show("登录成功");
this.DialogResult = DialogResult.OK;
this.Close();
}
else
MessageBox.Show("学号/工号或密码错误");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
}
}
【主界面】
MainForm.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 MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
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 MainForm_Load(object sender, EventArgs e)
{
FrmLogin frm = new FrmLogin();
if (frm.ShowDialog(this) == DialogResult.OK)
{ //登录成功
}
else
{//登录失败
Application.Exit();
}
}
private void 查询成绩ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmSCQuery frm = new FrmSCQuery();
frm.MdiParent = this;
frm.Show();
}
private void 添加成绩ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmSCInsert frm = new FrmSCInsert();
frm.MdiParent = this;
frm.Show();
}
}
}
【学生信息板块】
【学生查询】
FrmStuQuery.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;
using Oracle.ManagedDataAccess.Client;
using System.Configuration;
namespace DbApp
{
public partial class FrmStuQuery : Form
{
public FrmStuQuery()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = string.Format("select sno,sname,sage,ssex,Pwd from student where sname like '{0}%'",this.textBox1.Text);
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);
try
{
con.Open();//打开链接
OracleCommand cmd = new OracleCommand(sql,con);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
this.dataGridView1.DataSource = bs;
}
else
this.dataGridView1.DataSource = null;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void label1_Click(object sender, EventArgs e)
{
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void button2_Click(object sender, EventArgs e)
{
if(this.dataGridView1.SelectedRows.Count==0)
{
MessageBox.Show("请选中后再删除");
return;
}
if (MessageBox.Show("是否删除该数据", "confirm message", MessageBoxButtons.OKCancel) == DialogResult.Cancel) return;
DataGridViewRow row = this.dataGridView1.SelectedRows[0];
string sql = string.Format("delete from student where sno='{0}'", row.Cells[0].Value.ToString());
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);
try
{
con.Open();//打开链接
OracleCommand cmd = new OracleCommand(sql, con);
if(cmd.ExecuteNonQuery()==1)
{
MessageBox.Show("删除成功");
this.dataGridView1.Rows.Remove(row);
}
else
{
MessageBox.Show("没有找到学生");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void dataGridView1_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
{
if (this.dataGridView1.SelectedRows.Count == 0) return;
DataGridViewRow row = this.dataGridView1.SelectedRows[0];
FrmStuUpdate frm = new FrmStuUpdate();
frm.tbSno.Text = row.Cells[0].Value.ToString();
frm.tbSname.Text = row.Cells[1].Value.ToString();
frm.tbSage.Text = row.Cells[2].Value.ToString();
frm.tbSsex.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)
{
}
}
}
【添加学生】
FrmStuInsert.cs
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
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 FrmStuInsert : Form
{
public FrmStuInsert()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = string.Format("insert into student(sno,sname,sage,ssex,Pwd)values('{0}','{1}',{2},'{3}','{4}')",
tbSno.Text,tbSname.Text,tbSage.Text,tbSsex.Text,tbPwd.Text);
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);
try
{
con.Open();//打开链接
OracleCommand cmd = new OracleCommand(sql, con);
if (cmd.ExecuteNonQuery() == 1)
{
MessageBox.Show("添加成功");
}
else
{
MessageBox.Show("已经存在学生");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
}
}
【修改学生】
FrmStuUpdate.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;
using System.Configuration;
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}',sage={1},ssex='{2}',Pwd='{3}'where sno='{4}'",
this.tbSname.Text, this.tbSage.Text, this.tbSsex.Text, this.tbPwd.Text, this.tbSno.Text);
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);
try
{
con.Open();//打开链接
OracleCommand cmd = new OracleCommand(sql, con);
if (cmd.ExecuteNonQuery() == 1)
{
MessageBox.Show("更新成功");
}
else
{
MessageBox.Show("没有找到学生");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
}
}
【课程信息板块】
建立课程信息类Course
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using Oracle.ManagedDataAccess.Client;
using System.Windows.Forms;
namespace DbApp
{
public class Course
{
public string Cno { get; set; }
public string Cname { get; set; }
public int Ccredit { get; set; }
public string Tno { get; set; }
public string Cpno { get; set; }
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";
OracleParameter[] para = new OracleParameter[]
{ new OracleParameter(":cname",OracleDbType.Varchar2,40)};
para[0].Value = cname + "%";
//创建连接，打开连接，创建命令对象，执行命令，关闭连接
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);
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);
}
}
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,cpno,ccredit,tno) values(:cno,:cname,:cpno,:ccredit,:tno)";
OracleParameter[] para = new OracleParameter[]
{ new OracleParameter(":cno",OracleDbType.Char,4),
new OracleParameter(":cname",OracleDbType.Char,40),
new OracleParameter(":cpno",OracleDbType.Char,10),
new OracleParameter(":ccredit",OracleDbType.Int16),
new OracleParameter(":tno",OracleDbType.Char,7)};
para[0].Value = c.Cno;
para[1].Value = c.Cname;
para[2].Value = c.Cpno;
para[3].Value = c.Ccredit;
para[4].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 UpdateCourse(Course c)
{
int result = 0;
string sql = "update course set cname=:cname,cpno=:cpno,ccredit=:ccredit,tno=:tno where cno=:cno";
OracleParameter[] para = new OracleParameter[]
{new OracleParameter(":cname",OracleDbType.Char,40),
new OracleParameter(":cpno",OracleDbType.Char,10),
new OracleParameter(":ccredit",OracleDbType.Int16),
new OracleParameter(":tno",OracleDbType.Char,7),
new OracleParameter(":cno",OracleDbType.Char,4)};
para[0].Value = c.Cname;
para[1].Value = c.Cpno;
para[2].Value = c.Ccredit;
para[3].Value = c.Tno;
para[4].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;
}
}
}
建立教师信息类Teacher
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
class Teacher
{
public string Tno { get; set; }
public string Tname { get; set; }
public string Tposition { get; set; }
public string Tsalary { get; set; }
public string Pwd { get; set; }
public static List<Teacher>SelectTeacher(string tname)
{
List<Teacher> list = new List<Teacher>();
string sql = "select tno,tname,tposition,tsalarty,pwd from teacher where tname like :tname";
OracleParameter[] para = new OracleParameter[]
{new OracleParameter(":tname",OracleDbType.Varchar2,7)};
para[0].Value = tname + "%";
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())
{
Teacher t = new Teacher();
t.Tno = odr.GetString(0);
t.Tname = odr.GetString(1);
t.Tposition = odr.GetString(2);
t.Pwd = odr.GetString(2);
list.Add(t);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally { con.Close(); }
return list;
}
}
}
建立成绩信息类SC
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DbApp
{
class SC
{
public string Sno { get; set; }
public string Cno { get; set; }
public int Grade { get; set; }
public string Tno { get; set; }
public static List<SC> SelectSC(String sno)
{
List<SC> list = new List<SC>();
string sql = "select sno,cno,grade,tno from sc where sno like :sno";
OracleParameter[] para = new OracleParameter[]
{ new OracleParameter(":sno",OracleDbType.Varchar2,10)};
para[0].Value = sno + "%";
//创建连接，打开连接，创建命令对象，执行命令，关闭连接
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())
{
SC sc = new SC();
sc.Sno = odr.GetString(0);
sc.Cno = odr.GetString(1);
sc.Grade = odr.GetInt16(2);
sc.Tno = odr.GetString(3);
list.Add(sc);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally { con.Close(); }
return list;
}
public static int DeleteSC(string sno)
{
int result = 0;
string sql = "delete from sc where sno=:sno";
OracleParameter[] para = new OracleParameter[] { new OracleParameter(":sno", OracleDbType.Varchar2, 10) };
para[0].Value = 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 UpdateSC(SC sc)
{
int result = 0;
string sql = "update sc set sno=:sno,cno=:cno,grade=:grade,tno=:tno where sno=:sno";
OracleParameter[] para = new OracleParameter[]
{ new OracleParameter(":sno",OracleDbType.Char,10),
new OracleParameter(":cno",OracleDbType.Char,4),
new OracleParameter(":grade",OracleDbType.Int16),
new OracleParameter(":tno",OracleDbType.Char,7)};
para[0].Value = sc.Sno;
para[1].Value = sc.Cno;
para[2].Value = sc.Grade;
para[3].Value = sc.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 InsertSC(SC sc)
{
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.Char,10),
new OracleParameter(":cno",OracleDbType.Char,4),
new OracleParameter(":grade",OracleDbType.Int16),
new OracleParameter(":tno",OracleDbType.Char,7)};
para[0].Value = sc.Sno;
para[1].Value = sc.Cno;
para[2].Value = sc.Grade;
para[3].Value = sc.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;
}
}
}
【插入课程】
FrmCourseInsert.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 FrmCourseInsert : Form
{
public FrmCourseInsert()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Course c = new Course();
c.Cno = tbCno.Text;
c.Cname = tbCname.Text;
c.Cpno = tbCpno.Text;
c.Ccredit = Convert.ToInt16(tbCcredit.Text);
c.Tno = tbTno.Text;
if (Course.InsertCourse(c)==1)
MessageBox.Show("插入成功");
}
}
}
【查询课程】
FrmCourseQuery.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 FrmCourseQuery : Form
{
public FrmCourseQuery()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
this.dataGridView1.DataSource=Course.SelectCourse(this.textBox1.Text);
}
private void panel1_Paint(object sender, PaintEventArgs e)
{
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void dataGridView1_CellClick(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 cno = row.Cells[0].Value.ToString();
if (Course.DeleteCourse(cno) == 1)
{
MessageBox.Show("删除成功");
//this.dataGridView1.Rows.Remove(row);
}
else MessageBox.Show("找不到数据");
}
else if(e.ColumnIndex==6)
{//点击修改按钮
Course c = (Course)row.DataBoundItem;
FrmCourseUpdate frm = new FrmCourseUpdate();
frm.tbCno.Text = c.Cno;
frm.tbCname.Text = c.Cname;
frm.tbCcredit.Text = c.Ccredit.ToString();
frm.tbCpno.Text = c.Cpno;
frm.tbTno.Text = c.Tno;
frm.ShowDialog();
}
}
}
}
【修改课程】
FrmCourseUpdate.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 FrmCourseUpdate : Form
{
public FrmCourseUpdate()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Course c = new Course();
c.Cno = tbCno.Text;//保存不变
c.Cname = tbCname.Text;
c.Cpno = tbCpno.Text;
c.Ccredit = Convert.ToInt16(tbCcredit.Text);
c.Tno = tbTno.Text;
if (Course.UpdateCourse(c) == 1)
MessageBox.Show("更新成功");
else
MessageBox.Show("可能没有找到记录");
}
}
}
【成绩信息板块】
【查询成绩】
FrmSCQuery.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 FrmSCQuery : Form
{
public FrmSCQuery()
{
InitializeComponent();
}
private void dataGridView1_CellClick(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 sno = row.Cells[0].Value.ToString();
if (SC.DeleteSC(sno) == 1)
{
MessageBox.Show("删除成功");
//this.dataGridView1.Rows.Remove(row);
}
else MessageBox.Show("找不到数据");
}
else if (e.ColumnIndex == 5)
{//点击修改按钮
SC sc = (SC)row.DataBoundItem;
FrmSCUpdate frm = new FrmSCUpdate();
frm.tbsno.Text = sc.Sno;
frm.tbcno.Text = sc.Cno;
frm.tbgrade.Text = sc.Grade.ToString();
frm.tbtno.Text = sc.Tno;
frm.ShowDialog();
}
}
private void button1_Click(object sender, EventArgs e)
{
this.dataGridView1.DataSource = SC.SelectSC(this.textBox1.Text);
}
}
}
【插入成绩】
FrmSCInsert.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 FrmSCInsert : Form
{
public FrmSCInsert()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SC sc = new SC();
sc.Sno = tbsno.Text;
sc.Cno = tbcno.Text;
sc.Grade = Convert.ToInt16(tbgrade.Text);
sc.Tno = tbtno.Text;
if (SC.InsertSC(sc) == 1)MessageBox.Show("插入成功");
}
}
}
【修改成绩】
FrmSCUpdate.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 FrmSCUpdate : Form
{
public FrmSCUpdate()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SC sc = new SC();
sc.Sno = tbsno.Text;//保持不变
sc.Cno = tbcno.Text;//保持不变
sc.Grade = Convert.ToInt16(tbgrade.Text);
sc.Tno = tbtno.Text;
if (SC.UpdateSC(sc) == 1)
MessageBox.Show("更新成功");
else
MessageBox.Show("可能没有找到记录");
}
}
}
【信息统计板块】
【信息统计】
FrmStatistics.cs
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
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 FrmStatistics : Form
{
public FrmStatistics()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "select *from studentinfo";
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);
try
{
con.Open();//打开链接
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
this.dataGridView1.DataSource = bs;
}
else
this.dataGridView1.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
string sql = "select *from courseinfo";
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);
try
{
con.Open();//打开链接
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
this.dataGridView1.DataSource = bs;
}
else
this.dataGridView1.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void button3_Click(object sender, EventArgs e)
{
string sql = "select *from teacherinfo1 ";
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);
try
{
con.Open();//打开链接
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
this.dataGridView1.DataSource = bs;
}
else
this.dataGridView1.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void button4_Click(object sender, EventArgs e)
{
string sql = "select *from teacherinfo2 ";
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);
try
{
con.Open();//打开链接
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
this.dataGridView1.DataSource = bs;
}
else
this.dataGridView1.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
}
}
课程指导视频链接：
链接：https://pan.baidu.com/s/1qwRGTLPJ3vs0F5X7L81YMA
提取码：2021
这里提供一个我在学校写的实验代码源文件下载地址：（建议桃饱下载，随便搜一个CSDN下载，大概5毛左右）
645
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
