一、实验目的
通过实验，使学生掌握数据库系统设计和开发的一般方法，能够设计并实现简单的数据库系统。
二、实验原理
1、学生选课关系数据库需求描述
假设有“教师”、“学生”、“课程”三个实体，教师的基本信息包括：工号、姓名、职称、工资；课程的基本信息包括：课程号、课程名、学分数；学生的基本信息包括：学号、姓名、性别、年龄。系统必须满足以下要求：
(1) 一门课程只能有一个教师任课，一个教师可以上多门课程；
(2) 一个学生可以选修多门课程，一门课程可以由多个学生来选修，记录不同学生选修不同课程的成绩；
(3) 设置一个管理员，用于维护（添加、删除和修改等基本任务）学生基本信息、教师基本信息和教师所授课程等工作，此外，管理员添加学生时，为其设置初始密码；当学生选修了某门课程，课程成绩由管理员录入；
(4) 学生可以利用学号和密码登录系统，登陆系统后，可以进行选课、修改密码和个人基本信息、查询自己的选课及总学分等操作；
(5) 能够统计不同职称的教师的数量、不同职称的教师的平均工资，可以统计每门课程的平均成绩、最高分、最低分，统计每个学生选修课程的总学分；
2、概念模型（E-R图）
3、关系模式
主码用下滑线划出
学生(学号,姓名,性别,年龄,所在系,密码)
课程(课程号,课程名,学分数)
教师(工号,职称,姓名,工资,密码)
管理员(账号,姓名,密码)
选修(学号,课程号,成绩)
任课(工号,课程号)
符号化
Student(Sno,Sname,Ssex,Sage,Sdept,Spwd)
Course(Cno,Cname,Ccredit)
Administrator(Ano,Aname,Apwd)
Teacher(Tno,Tpost,Tname,Tsalary,Tpwd)
SC(Sno,Cno,Sscore) //Sno---外码，Cno---外码
TC(Tno,Cno) //Tno---外码，Cno---外码
4、表结构
（1）学生表
| 属性名 | 类型 | 长度 | 说明 | 含义 |
| Sno | varchar2 | 20 | 主码（非空） | 学号 |
| Sname | varchar2 | 20 | 非空，可不唯一 | 姓名 |
| Ssex | varchar2 | 3 | ‘男’或‘女’ | 性别 |
| Sage | integer | 默认 | 年龄 | |
| Sdept | varchar2 | 30 | 非空 | 所在系 |
| Spwd | varchar2 | 20 | 非空 | 密码 |
（2）课程表
| 属性名 | 类型 | 长度 | 说明 | 含义 |
| Cno | varchar2 | 20 | 主码（非空） | 课程号 |
| Cname | varchar2 | 40 | 非空，且唯一 | 课程名 |
| Ccredit | varchar2 | 4 | 非空 | 学分数 |
（3）教师表
| 属性名 | 类型 | 长度 | 说明 | 含义 |
| Tno | varchar2 | 20 | 主码（非空） | 工号 |
| Tpost | varchar2 | 20 | 非空 | 职称 |
| Tname | varchar2 | 20 | 非空，可不唯一 | 姓名 |
| Tsalary | integer | 默认 | 非空 | 工资 |
| Tpwd | varchar2 | 20 | 非空 | 密码 |
（4）管理员表
| 属性名 | 类型 | 长度 | 说明 | 含义 |
| Ano | varchar2 | 20 | 主码（非空） | 账号 |
| Aname | varchar2 | 40 | 非空，可不唯一 | 姓名 |
| Apwd | varchar2 | 4 | 非空 | 密码 |
（5）学生选修表
| 属性名 | 类型 | 长度 | 说明 | 含义 |
| Sno | varchar2 | 20 | 外码，主属性（非空） | 学号 |
| Cno | varchar2 | 20 | 外码，主属性（非空） | 课程号 |
| Sscore | varchar2 | 6 | 可空 | 成绩 |
（6）教师任课表
| 属性名 | 类型 | 长度 | 说明 | 含义 |
| Tno | varchar2 | 20 | 外码，主属性（非空） | 工号 |
| Cno | varchar2 | 20 | 外码，主属性（非空） | 课程号 |
三、使用仪器、材料
Oracle 11g（xe），windows11，Visual Studio 2022；
四、实验步骤
1、设计并画出E-R图，要求标注连通词（即联系类型）；
2、将E-R图转化为关系模型，并指出各关系的主码和外码；
3、在Oracle完成数据库的逻辑设计；
4、在vs2022实现窗口应用设计；
五、实验过程原始记录
1、在Oracle完成数据库的逻辑设计
（1）以SYSTEM身份连接到orcl数据库，创建新用户CMD并授权：
create user CMD identified by cmd;
grant resource, connect, DBA to CMD;（2）以用户CMD的身份建立连接，并在此连接下执行后面的操作；
（3）在CMD连接中：建立Student、Course、Teacher、SC、TC表格；
//创建学生表
CREATE TABLE Student
( Sno VARCHAR2(20) PRIMARY KEY,
Sname VARCHAR2(20) NOT NULL,
Ssex VARCHAR2(3),
Sage INTEGER,
Sdept VARCHAR2(30)NOT NULL,
Spwd VARCHAR2(20)NOT NULL);
//创建课程表
CREATE TABLE Course
( Cno VARCHAR2(20) PRIMARY KEY,
Cname VARCHAR2(40) NOT NULL UNIQUE,
Ccredit VARCHAR2(4)NOT NULL);
//创建教师表
CREATE TABLE Teacher
( Tno VARCHAR2(20) PRIMARY KEY,
Tpost VARCHAR2(20)NOT NULL,
Tname VARCHAR2(20) NOT NULL,
Tsalary INTEGERNOT NULL,
Tpwd VARCHAR2(20)NOT NULL);
//创建管理员表
CREATE TABLE Administrator
( Ano VARCHAR2(20) PRIMARY KEY,
Aname VARCHAR2(20) NOT NULL,
Apwd VARCHAR2(20)NOT NULL);
//创建学生课程选修表
CREATE TABLE SC
( Sno VARCHAR2(20),
Cno VARCHAR2(20),
Sscore VARCHAR2(6),
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno) ON DELETE CASCADE,
FOREIGN KEY(Cno) REFERENCES Course(Cno) ON DELETE CASCADE);
//创建教师任课表
CREATE TABLE TC
( Tno VARCHAR2(20),
Cno VARCHAR2(20),
PRIMARY KEY(Tno,Cno),
FOREIGN KEY(Tno) REFERENCES Teacher(Tno) ON DELETE CASCADE,
FOREIGN KEY(Cno) REFERENCES Course(Cno) ON DELETE CASCADE) ;（4）在Student、Course、Teacher、SC、TC表格输入初始数据；
（其中所有用户的初始密码为：123）
insert into student values('32206300007','罗一隆','男',20,'网络工程',123);
insert into student values('32306200021','叶凌灵','女',17,'软件工程',123);
insert into student values('32206100026','陈尘','女',19,'计算机科学与技术',123);
insert into student values('32103400033','杨辉','男',20,'工艺美术',123);
insert into student values('32206500019','林镇','男',19,'网络空间安全',123);
insert into student values('32403200057','曾曾颖','女',18,'服装与服饰设计',123);
insert into student values('32107800102','黎逸安','男',21,'土木工程',123);
insert into student values('32204900084','梁良','男',19,'机器人工程',123);
insert into student values('32303200022','李萨','女',19,'应用心理',123);
insert into student values('32205800014','张平平','男',20,'化学测量与技术',123);
insert into course values('210610008','计算机体系结构与操作系统','4');
insert into course values('181500711','线性代数','2');
insert into course values('180600013','数据库原理','3');
insert into course values('181500712','概率论与数理统计','3');
insert into course values('180600011','计算机网络','3');
insert into course values('180600007','数据结构','3');
insert into course values('181500701','高等数学','4');
insert into course values('181800801','通用英语','2');
insert into course values('00131701','大学体育','1');
insert into course values('180600006','网络空间安全导论','2');
insert into course values('180600021','信息安全原理','3');
insert into course values('180600030','网络对抗技术','4');
insert into course values('180600025','算法设计与分析','2');
insert into course values('130500901','现代刑侦科学','2');
insert into course values('190600013','结构力学','3');
insert into course values('190600011','工程力学','4');
insert into course values('190600004','土木工程材料','3');
insert into course values('160500012','服装立体结构','3');
insert into course values('160500016','配饰造型设计','2.5');
insert into course values('160600002','色彩结构','4');
insert into course values('160600005','中西服装史','2');
insert into course values('160600003','美学','2');
insert into course values('140500001','基础化学','2');
insert into course values('140500014','化学测量原理与仪器','3');
insert into course values('140500018','自动控制原理','4');
insert into course values('140500066','综合化学实验','3');
insert into course values('170800019','儿童发展心理学','2');
insert into course values('170800008','解刨心理学','3');
insert into course values('170800010','群体心理学','3');
insert into teacher values('22105100006','教授','燕凌洁',880000,123);
insert into teacher values('22105100015','副教授','郭枫严',820000,123);
insert into teacher values('22105100010','讲师','陈铭',700000,123);
insert into teacher values('22055100013','教授','赵蓉蓉',880000,123);
insert into teacher values('22075100016','教授','张章',860000,123);
insert into teacher values('22055100024','讲师','杨廉',680000,123);
insert into teacher values('22105100002','副教授','王汉',860000,123);
insert into teacher values('22064100024','副教授','陈丹丹',840000,123);
insert into teacher values('22078100012','教授','林立',680000,123);
insert into teacher values('22109100004','讲师','熊二',620000,123);
insert into teacher values('22109100003','讲师','熊大',630000,123);
insert into teacher values('22102100008','教授','梅梅',657000,123);
insert into teacher values('22102100011','副教授','袁力鑫',632000,123);
insert into administrator values('1007','关丽媛',123);
insert into sc values('32206100026','210610008',null);
insert into sc values('32206100026','181500711',null);
insert into sc values('32206100026','180600013',null);
insert into sc values('32206100026','181500712',null);
insert into sc values('32206100026','180600011',null);
insert into sc values('32206100026','180600007',null);
insert into sc values('32206100026','180600025',null);
insert into sc values('32206100026','130500901',null);
insert into sc values('32306200021','181500701',null);
insert into sc values('32306200021','181800801',null);
insert into sc values('32306200021','00131701',null);
insert into sc values('32306200021','130500901',null);
insert into sc values('32206300007','181500712',null);
insert into sc values('32206300007','180600011',null);
insert into sc values('32206300007','180600007',null);
insert into sc values('32206300007','00131701',null);
insert into sc values('32206300007','130500901',null);
insert into sc values('32103400033','160600003',null);
insert into sc values('32103400033','160600002',null);
insert into sc values('32103400033','00131701',null);
insert into sc values('32103400033','130500901',null);
insert into sc values('32206500019','181500712',null);
insert into sc values('32206500019','180600011',null);
insert into sc values('32206500019','180600006',null);
insert into sc values('32206500019','180600030',null);
insert into sc values('32206500019','180600025',null);
insert into sc values('32206500019','130500901',null);
insert into sc values('32403200057','160600002',null);
insert into sc values('32403200057','160600005',null);
insert into sc values('32403200057','160500012',null);
insert into sc values('32403200057','160500016',null);
insert into sc values('32403200057','130500901',null);
insert into sc values('32107800102','190600013',null);
insert into sc values('32107800102','190600011',null);
insert into sc values('32107800102','190600004',null);
insert into sc values('32107800102','130500901',null);
insert into sc values('32204900084','181500711',null);
insert into sc values('32204900084','180600007',null);
insert into sc values('32204900084','181500712',null);
insert into sc values('32204900084','130500901',null);
insert into sc values('32303200022','170800019',null);
insert into sc values('32303200022','170800008',null);
insert into sc values('32303200022','170800010',null);
insert into sc values('32303200022','130500901',null);
insert into sc values('32205800014','140500066',null);
insert into sc values('32205800014','140500018',null);
insert into sc values('32205800014','140500014',null);
insert into sc values('32205800014','140500001',null);
insert into sc values('32205800014','130500901',null);
insert into tc values('22105100006','210610008');
insert into tc values('22105100006','180600007');
insert into tc values('22105100015','180600013');
insert into tc values('22105100010','180600011');
insert into tc values('22055100024','181500711');
insert into tc values('22055100024','181500712');
insert into tc values('22055100013','181500701');
insert into tc values('22064100024','181800801');
insert into tc values('22064100024','170800019');
insert into tc values('22064100024','170800008');
insert into tc values('22064100024','170800010');
insert into tc values('22078100012','00131701');
insert into tc values('22105100002','180600006');
insert into tc values('22105100002','180600030');
insert into tc values('22105100002','180600021');
insert into tc values('22105100002','180600025');
insert into tc values('22075100016','130500901');
insert into tc values('22075100016','140500001');
insert into tc values('22075100016','140500014');
insert into tc values('22075100016','140500066');
insert into tc values('22109100004','190600013');
insert into tc values('22109100004','190600011');
insert into tc values('22109100003','190600004');
insert into tc values('22109100003','140500018');
insert into tc values('22102100008','160500012');
insert into tc values('22102100008','160500016');
insert into tc values('22102100008','160600005');
insert into tc values('22102100011','160600002');
insert into tc values('22102100011','160600003');student
course
teacher
administrator
sc
tc
（5）建立统计视图（会动态变化数据）
//创建学生总成绩、总学分视图Sscoreinfo
create OR REPLACE view Sscoreinfo as
select student.sno,student.sdept,student.sname,
COALESCE((SELECT SUM(Sscore) FROM sc WHERE sno = student.sno), 0) AS 总成绩,
COALESCE((select sum(Ccredit) from course
where cno in(select cno from sc where sno=student.sno and Sscore>=60)),0)AS 总学分
from student
ORDER BY student.sno ASC;
//创建课程统计视图courseinfo
create OR REPLACE view courseinfo as
select course.cno,course.cname,teacher.tno,teacher.tname,course.ccredit,
(select count(sno) from sc where cno=course.cno) 选修人数,
(select AVG(sscore) from sc where cno=course.cno) 平均分,
(select MAX(sscore) from sc where cno=course.cno) 最高分,
(select MIN(sscore) from sc where cno=course.cno) 最低分
from course,teacher,tc
where teacher.tno=tc.tno and course.cno=tc.cno
ORDER BY course.cno ASC;
//创建学生各选修课程情况统计视图Sselectinfo
create OR REPLACE view Sselectinfo as
select student.sno,sname,course.cno,cname,Sscore
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno
ORDER BY student.sno ASC;
//创建教师信息（职称人数、平均工资）统计视图TeacherStats
//distinct确保每一行是唯一的tpost
//CAST函数将结果转换为DECIMAL类型，保留小数点后两位，总位数15
CREATE OR REPLACE VIEW TeacherStats AS
SELECT
DISTINCT teacher.tpost,
(SELECT COUNT(x.tno)
FROM teacher x
WHERE x.tpost = teacher.tpost) AS 教师总数,
(SELECT CAST(AVG(x.tsalary) AS DECIMAL(15,2))
FROM teacher x
WHERE x.tpost = teacher.tpost) AS 平均工资
FROM teacher;
//创建用户信息统计视图userinfo
CREATE OR REPLACE VIEW userinfo AS
SELECT SNO AS USERID, SNAME AS USERNAME,
SPWD AS PASSWORD, '学生' AS IDENTITY
FROM student
UNION ALL
SELECT TNO AS USERID, TNAME AS USERNAME,
TPWD AS PASSWORD, '教师' AS IDENTITY
FROM teacher
UNION ALL
SELECT ANO AS USERID, ANAME AS USERNAME,
APWD AS PASSWORD, '系统管理员' AS IDENTITY
FROM administrator;学生总成绩、总学分视图Sscoreinfo
课程统计视图courseinfo
学生各选修课程情况统计视图Sselectinfo
教师信息（职称人数、平均工资）统计视图TeacherStats
用户信息统计视图userinfo
2、在vs2022实现窗口应用设计
（1）在vs2022进行项目创建：
打开visual studio，选择Windows窗体应用类创建项目：
（2）添加扩展引用Oracle.ManagedDataAccess来使用Oracle数据库
使用c#自带的Oracle.DataAccess实现基础的Oracle数据库连接。
·在新建好的项目中，右键项目→添加→新建项：
·选择类→命名→添加：
·右击引用→添加引用：
·从vs2022自带的Oracle配置选择连接：
·右键解决方案→管理解决方案的NutGet程序包：
·搜索选择Oracle.ManagedDataAccess下载安装：
·在刚创建的OracleHelper.cs中编写以下代码：
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
namespace EMISApp
{
class OracleHelper
{
// Data Source，数据库连接字符串，用于连接到Oracle数据库。注意区分正式与测试环境。
private static string connStr =
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd"; // 这个要写在最后
#region 执行SQL语句,返回受影响行数
public static int ExecuteNonQuery(string sql, params OracleParameter[] parameters)
{
// 创建Oracle数据库连接对象
using (OracleConnection conn = new OracleConnection(connStr))
{
// 打开数据库连接
conn.Open();
// 创建Oracle命令对象
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql; // 设置要执行的SQL语句
cmd.Parameters.AddRange(parameters); // 添加SQL语句中的参数
int i = cmd.ExecuteNonQuery(); // 执行SQL语句并返回受影响的行数
conn.Close(); // 关闭数据库连接
return i; // 返回受影响的行数
}
}
}
#endregion
#region 执行SQL语句,返回DataTable;只用来执行查询结果比较少的情况
public static DataTable ExecuteDataTable(string sql, params OracleParameter[] parameters)
{
using (OracleConnection conn = new OracleConnection(connStr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
OracleDataAdapter adapter = new OracleDataAdapter(cmd); // 创建Oracle数据适配器
DataTable datatable = new DataTable(); // 创建DataTable对象存储查询结果
adapter.Fill(datatable); // 使用数据适配器填充DataTable
conn.Close();
return datatable; // 返回包含查询结果的DataTable
}
}
}
#endregion
}
}（3）登录窗口配置
·项目本身就有一个主窗口（可改名）：
·修改窗体标题：教务信息管理系统登录
·选择“视图”，打开“工具箱”，添加输入提示内容，拖动“label”到窗口设计区：
·拖动“TextBox”，添加用户输入栏，并为其重新命名：
·拖动“ComboBox”，用于登录身份选择设置，并为其重新命名：
·拖动“Button”作进入后续操作的触发器，并为其重新命名：
·加入登录窗口背景图片，选择Stretch模式，初步登录界面：
（4）对“取消”按键编码
·在属性中选操作“click”双击进入代码区：
·编码如下：
//对取消按键的处理
private void btnCancel_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("确定取消登录吗？", "提示消息",
MessageBoxButtons.YesNo);
if(result ==DialogResult.Yes)
{
this.Close();
}
}（5）对登录选项处理
①身份选项的设置：
·点击CBO的“Items”，输入各个身份：
·并规定此栏只从集合里选：
·双击登录主窗口进入加载窗口时的操作编程，将以下代码复制到函数：
cboLoginType.SelectedIndex = 0;
②登录选项的设置和功能实现：
·双击登录按键，进入登录代码编辑：
·编写增加以下代码，同时相应地增加管理员、学生、教师登录后跳转的新窗体：
注意：窗体建好写代码时才能用
·点击工具→连接到数据库
·选择Oracle Database连接
·连接好的数据库：之后可以查看到数据库信息
·以下是登录窗口的代码：
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.DataAccess.Client;
namespace EMISApp
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
//点击取消，关闭窗口
private void btnCancel_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("确定取消登录吗？", "提示消息", MessageBoxButtons.YesNo);
if(result ==DialogResult.Yes)
{
this.Close();
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
//1.非空验证：登录信息填完整正则返回真
if(!IsEmpty())
{
if (IsLogin())
{
//2.窗体跳转：新建一个窗体
MessageBox.Show("欢迎回来！");
if(cboLoginType.Text.Equals("系统管理员"))
{
AdminForm admin = new AdminForm();
admin.Show();//打开窗体
}
else if(cboLoginType.Text.Equals("学生"))
{
StuForm stu = new StuForm();
stu.Show();
}
else
{
TeacherForm tchr=new TeacherForm();
tchr.Show();
}
this.Hide();//隐藏窗体
}
else
{
MessageBox.Show("用户不存在！");
}
}
}
//登录信息验证
public bool IsLogin()
{
//1.获取控件中输入的用户信息
String num = txtUsernum.Text.Trim();
String pwd = txtPwd.Text.Trim();
//2.访问数据库
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=LAPTOP-OMCON2HD)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//准备查询语句
String sql = "select * from USERINFO where USERID='" + num + "' and PASSWORD ='" + pwd + "'";
//创建执行对象
OracleCommand command= new OracleCommand(sql, conn);
//执行sql语句，返回结果
long i = 0;
if(command.ExecuteScalar()!=null)
{
i = Convert.ToInt64(command.ExecuteScalar());
}
if (i > 0)
{
// 根据角色创建视图
string role = cboLoginType.Text;
string viewSql = "";
OracleCommand cvcom = null;
switch (role)
{
case "学生":
viewSql = "CREATE OR REPLACE VIEW USERVIEW AS SELECT * FROM STUDENT WHERE sno ='" + num + "'";
break;
case "教师":
viewSql = "CREATE OR REPLACE VIEW USERVIEW AS SELECT * FROM TEACHER WHERE tno ='" + num + "'";
break;
case "系统管理员":
viewSql = "CREATE OR REPLACE VIEW USERVIEW AS SELECT * FROM ADMINISTRATOR WHERE ano ='" + num + "'";
break;
default:
// 其他角色处理
break;
}
cvcom = new OracleCommand(viewSql, conn);
//cvcom.Parameters.Add(new OracleParameter("userid", num));
cvcom.ExecuteNonQuery();
conn.Close();
return true;
}
else
{
conn.Close();
return false;
}
}
//非空验证：用户名、密码、身份
public bool IsEmpty()
{
//获取控件值，判断
if(txtUsernum.Text.Trim()==String.Empty)
{
MessageBox.Show("账号不能为空！");
return true;
}
if (txtPwd.Text.Trim() == String.Empty)
{
MessageBox.Show("密码不能为空！");
return true;
}
if (cboLoginType.Text.Trim() == "请选择")
{
MessageBox.Show("请选择登录身份");
return true;
}
return false;
}
//加载窗口时的操作
private void MainFrom_Load(object sender, EventArgs e)
{
//组合框中默认显示“请选择”
cboLoginType.SelectedIndex = 0;
}
}
}（6）管理员主窗口的设计
·视图→工具箱→拖动MenuStrip→为菜单栏命名：msAdmin并添加栏目
·添加栏目菜单并命名各选项：学生和教师的窗口类似
*主窗体的设置为容器：（多文件规定在此窗口中，看个人喜好）
（7）其他窗口的一些设计操作
·显示数据视图
选择DataGridView：
右键编辑列：
添加要显示的列：可以与数据库的列名称一样（方便）：
绑定与数据相应的列：
（8）管理员界面功能的代码实现（主要部分）
①学生账号管理功能集
·添加、查询、修改、删除学生信息
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.DataAccess.Client;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Button;
namespace EMISApp
{
public partial class ShowStuForm : Form
{
public ShowStuForm()
{
InitializeComponent();
}
//加载操作
private void ShowStuForm_Load(object sender, EventArgs e)
{
ShowStu();
}
//加载学生信息
public void ShowStu()
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql_showStu = "select SNO,SDEPT,SNAME,SSEX,SAGE,SPWD from STUDENT ORDER BY SNO ASC";
//查询数据库后，将数据绑定到dgv控件：使用数据集DataSet和适配器DataAdapter
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(sql_showStu, conn);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "Student");
//数据源绑定到dgv控件
dgvShowStu.DataSource = ds.Tables["Student"];
conn.Close();
}
//点击查询按钮
private void btnSearch_Click(object sender, EventArgs e)
{
//获取控件信息
String stuName = txtSname.Text.Trim();
if(txtSname.Text.Trim() != String.Empty)
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql_showStu = "select * from STUDENT where SNAME = :stuName ORDER BY SNO ASC";
//使用参数化查询，避免SQL注入问题
OracleCommand cmd = new OracleCommand(sql_showStu, conn);
cmd.Parameters.Add(new OracleParameter(":stuName", stuName));
// 查询数据库，将结果填充到数据集
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "Stu");
//数据源绑定到dgv控件
dgvShowStu.DataSource = ds.Tables["Stu"];
conn.Close();
}
else
{
ShowStu();
}
}
//点击添加按钮的操作：实现添加记录
private void btnAdd_Click(object sender, EventArgs e)
{
if (!IsEmpty())
{
//获取所有控件值
String stuNo = txtGetSno.Text.Trim();
String stuPwd = txtGetSpwd.Text.Trim();
String stuName = txtGetSname.Text.Trim();
String stuSex = txtGetSsex.Text.Trim();
String stuAge = txtGetSage.Text.Trim();
String stuDept = txtGetSdept.Text.Trim();
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//准备查询语句
String sql = "select * from USERINFO where USERID='" + stuNo + "' ";
//创建执行对象
OracleCommand comm = new OracleCommand(sql, conn);
//执行sql语句，返回结果
long i = 0;
if (comm.ExecuteScalar() != null)
{
i = Convert.ToInt64(comm.ExecuteScalar());
}
//执行sql语句，返回结果
if (i>0)
{
MessageBox.Show("学生账号已存在！");
}
else
{
//执行添加操作
String sql_addStu = "insert into student values('" + stuNo + "','" + stuName + "','" + stuSex + "'," + stuAge + ",'" + stuDept + "'," + stuPwd + ")";
//创建执行对象
OracleCommand command = new OracleCommand(sql_addStu, conn);
int j = command.ExecuteNonQuery();
if (j > 0)
{
MessageBox.Show("学生账户注册成功！");
ShowStu();
}
else
{
MessageBox.Show("学生账户注册失败！");
}
Clear();
}
conn.Close();
}
}
//判断是否有不能为空的填的是空值
public bool IsEmpty()
{
//获取控件值，判断
if (txtGetSno.Text.Trim() == String.Empty)
{
MessageBox.Show("学号不能为空！");
return true;
}
if (txtGetSpwd.Text.Trim() == String.Empty)
{
MessageBox.Show("密码不能为空！");
return true;
}
if (txtGetSname.Text.Trim() == String.Empty)
{
MessageBox.Show("姓名不能为空！");
return true;
}
if (txtGetSdept.Text.Trim() == String.Empty)
{
MessageBox.Show("系别不能为空！");
return true;
}
if (txtGetSsex.Text.Trim() == String.Empty)
{
MessageBox.Show("性别不能为空！");
return true;
}
if (txtGetSage.Text.Trim() == String.Empty)
{
MessageBox.Show("年龄不能为空！");
return true;
}
return false;
}
//清空分组框的控件值
public void Clear()
{
foreach (Control ctrl in groupBox1.Controls)
{
if (ctrl is TextBox)
{
ctrl.Text = String.Empty;
}
}
}
//点击修改按钮
private void btnEdit_Click(object sender, EventArgs e)
{
if (!IsEmpty())
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
String sql = "select * from USERINFO where USERID='" + txtGetSno.Text + "' ";
//创建执行对象
OracleCommand comm = new OracleCommand(sql, conn);
//执行sql语句，返回结果
long i = 0;
if (comm.ExecuteScalar() != null)
{
i = Convert.ToInt64(comm.ExecuteScalar());
}
//判断结果
if (i > 0)
{
String sql_editStu = "update STUDENT set SNAME='" + txtGetSname.Text + "',SDEPT='" + txtGetSdept.Text + "',SSEX='" + txtGetSsex.Text + "',SAGE='" + txtGetSage.Text + "',SPWD='" + txtGetSpwd.Text + "' where SNO='" + txtGetSno.Text + "'";
OracleCommand command = new OracleCommand(sql_editStu, conn);
int j = command.ExecuteNonQuery();
if (j > 0)
{
MessageBox.Show("修改成功！");
ShowStu();
}
else
{
MessageBox.Show("修改失败！");
}
Clear();
}
else
{
MessageBox.Show("学生账号不存在！");
}
conn.Close();
}
}
//点击单元行
private void dgv_CellClick(object sender, DataGridViewCellEventArgs e)
{
int i = dgvShowStu.CurrentRow.Index;
txtGetSno.Text = dgvShowStu.Rows[i].Cells["stuNo"].Value.ToString();
txtGetSdept.Text = dgvShowStu.Rows[i].Cells["stuDept"].Value.ToString();
txtGetSname.Text = dgvShowStu.Rows[i].Cells["stuName"].Value.ToString();
txtGetSsex.Text = dgvShowStu.Rows[i].Cells["stuSex"].Value.ToString();
txtGetSage.Text = dgvShowStu.Rows[i].Cells["stuAge"].Value.ToString();
txtGetSpwd.Text = dgvShowStu.Rows[i].Cells["stuPwd"].Value.ToString();
}
//点击删除按键
private void btnDelete_Click(object sender, EventArgs e)
{
if (txtGetSno.Text.Trim() == String.Empty)
{
MessageBox.Show("学号不能为空！");
}
else
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
String sql = "select * from USERINFO where USERID='" + txtGetSno.Text + "' ";
//创建执行对象
OracleCommand comm = new OracleCommand(sql, conn);
//执行sql语句，返回结果
long i = 0;
if (comm.ExecuteScalar() != null)
{
i = Convert.ToInt64(comm.ExecuteScalar());
}
if (i > 0)
{
String sql_editStu = "Delete from STUDENT where SNO='" + txtGetSno.Text + "'";
DialogResult result= MessageBox.Show("确定删除该学生所有信息吗（包括选课和成绩）？", "提示信息",MessageBoxButtons.YesNo);
if(result == DialogResult.Yes)
{
OracleCommand command = new OracleCommand(sql_editStu, conn);
int j = command.ExecuteNonQuery();
if (j > 0)
{
MessageBox.Show("删除成功！");
ShowStu();
}
else
{
MessageBox.Show("删除失败！");
}
Clear();
}
}
else
{
MessageBox.Show("学生账号不存在！");
}
conn.Close();
}
}
}
}②教师账号管理功能集
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.DataAccess.Client;
namespace EMISApp
{
public partial class ShowTchrForm : Form
{
public ShowTchrForm()
{
InitializeComponent();
}
private void ShowTchrForm_Load(object sender, EventArgs e)
{
ShowTchr();
}
public void ShowTchr()
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql = "select * from TEACHER ORDER BY TNO ASC";
//查询数据库后，将数据绑定到dgv控件：使用数据集DataSet和适配器DataAdapter
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(sql, conn);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "Tchr");
//数据源绑定到dgv控件
dgvShowTchr.DataSource = ds.Tables["Tchr"];
conn.Close();
}
private void btnSearch_Click(object sender, EventArgs e)
{
//获取控件信息
String tchrName = txtTname.Text.Trim();
if (txtTname.Text.Trim() != String.Empty)
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql = "select * from TEACHER where TNAME = :tchrName";
//使用参数化查询，避免SQL注入问题
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter(":tchrName", tchrName));
// 查询数据库，将结果填充到数据集
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "tchr");
//数据源绑定到dgv控件
dgvShowTchr.DataSource = ds.Tables["tchr"];
conn.Close();
}
else
{
ShowTchr();
}
}
private void btnSum_Click(object sender, EventArgs e)
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql = "select * from TEACHERSTATS ";
//查询数据库后，将数据绑定到dgv控件：使用数据集DataSet和适配器DataAdapter
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(sql, conn);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "TS");
//数据源绑定到dgv控件
dgvShowTchr.DataSource = ds.Tables["TS"];
conn.Close();
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (!IsEmpty())
{
//获取所有控件值
String tchrNo = txtGetTno.Text.Trim();
String tchrPwd = txtGetTpwd.Text.Trim();
String tchrName = txtGetTname.Text.Trim();
String tchrPost = txtGetTpost.Text.Trim();
String tchrSalary = txtGetTsalary.Text.Trim();
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//准备查询语句
String sql = "select * from USERINFO where USERID='" + tchrNo + "' ";
//创建执行对象
OracleCommand comm = new OracleCommand(sql, conn);
//执行sql语句，返回结果
long i = 0;
if (comm.ExecuteScalar() != null)
{
i = Convert.ToInt64(comm.ExecuteScalar());
}
//执行sql语句，返回结果
if (i > 0)
{
MessageBox.Show("教师账号已存在！");
}
else
{
//执行添加操作
String sql_addTchr = "insert into teacher values('" + tchrNo + "','" + tchrPost + "','" + tchrName + "'," + tchrSalary + "," + tchrPwd + ")";
//创建执行对象
OracleCommand command = new OracleCommand(sql_addTchr, conn);
int j = command.ExecuteNonQuery();
if (j > 0)
{
MessageBox.Show("教师账户注册成功！");
ShowTchr();
}
else
{
MessageBox.Show("教师账户注册失败！");
}
Clear();
}
conn.Close();
}
}
private void btnEdit_Click(object sender, EventArgs e)
{
if (!IsEmpty())
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
String sql = "select * from USERINFO where USERID='" + txtGetTno.Text + "' ";
//创建执行对象
OracleCommand comm = new OracleCommand(sql, conn);
//执行sql语句，返回结果
long i = 0;
if (comm.ExecuteScalar() != null)
{
i = Convert.ToInt64(comm.ExecuteScalar());
}
//判断结果
if (i > 0)
{
String sql_editTchr = "update teacher set TNAME='" + txtGetTname.Text + "',TPOST='" + txtGetTpost.Text + "',TSALARY='" + txtGetTsalary.Text + "',TPWD='" + txtGetTpwd.Text + "' where TNO='" + txtGetTno.Text + "'";
OracleCommand command = new OracleCommand(sql_editTchr, conn);
int j = command.ExecuteNonQuery();
if (j > 0)
{
MessageBox.Show("修改成功！");
ShowTchr();
}
else
{
MessageBox.Show("修改失败！");
}
Clear();
}
else
{
MessageBox.Show("教师账号不存在！");
}
conn.Close();
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (txtGetTno.Text.Trim() == String.Empty)
{
MessageBox.Show("工号不能为空！");
}
else
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
String sql = "select * from USERINFO where USERID='" + txtGetTno.Text + "' ";
//创建执行对象
OracleCommand comm = new OracleCommand(sql, conn);
//执行sql语句，返回结果
long i = 0;
if (comm.ExecuteScalar() != null)
{
i = Convert.ToInt64(comm.ExecuteScalar());
}
if (i > 0)
{
String sql_deleteTchr = "Delete from teacher where TNO='" + txtGetTno.Text + "'";
DialogResult result = MessageBox.Show("确定删除该教师所有信息吗（包括任课信息）？", "提示信息", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
OracleCommand command = new OracleCommand(sql_deleteTchr, conn);
int j = command.ExecuteNonQuery();
if (j > 0)
{
MessageBox.Show("删除成功！");
ShowTchr();
}
else
{
MessageBox.Show("删除失败！");
}
Clear();
}
}
else
{
MessageBox.Show("教师账号不存在！");
}
conn.Close();
}
}
//判断控件值
public bool IsEmpty()
{
//获取控件值，判断
if (txtGetTno.Text.Trim() == String.Empty)
{
MessageBox.Show("工号不能为空！");
return true;
}
if (txtGetTpwd.Text.Trim() == String.Empty)
{
MessageBox.Show("密码不能为空！");
return true;
}
if (txtGetTname.Text.Trim() == String.Empty)
{
MessageBox.Show("姓名不能为空！");
return true;
}
if (txtGetTpost.Text.Trim() == String.Empty)
{
MessageBox.Show("职称不能为空！");
return true;
}
if (txtGetTsalary.Text.Trim() == String.Empty)
{
MessageBox.Show("工资不能为空！");
return true;
}
return false;
}
public void Clear()
{
foreach (Control ctrl in groupBox1.Controls)
{
if (ctrl is TextBox)
{
ctrl.Text = String.Empty;
}
}
}
//点击单元行
private void dgv_CellClick(object sender, DataGridViewCellEventArgs e)
{
int i = dgvShowTchr.CurrentRow.Index;
txtGetTno.Text = dgvShowTchr.Rows[i].Cells[0].Value.ToString();
txtGetTpost.Text = dgvShowTchr.Rows[i].Cells[1].Value.ToString();
txtGetTname.Text = dgvShowTchr.Rows[i].Cells[2].Value.ToString();
txtGetTsalary.Text = dgvShowTchr.Rows[i].Cells[3].Value.ToString();
txtGetTpwd.Text = dgvShowTchr.Rows[i].Cells[4].Value.ToString();
}
}
}③综合信息查询功能集
·学生课程相关信息
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.DataAccess.Client;
namespace EMISApp
{
public partial class SCForm : Form
{
public SCForm()
{
InitializeComponent();
}
private void SCForm_Load(object sender, EventArgs e)
{
ShowSC();
}
//加载学生信息
public void ShowSC()
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql = "select * from SSELECTINFO ORDER BY SNO ASC";
//查询数据库后，将数据绑定到dgv控件：使用数据集DataSet和适配器DataAdapter
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(sql, conn);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "StuScore");
//数据源绑定到dgv控件
dgvSC.DataSource = ds.Tables["StuScore"];
conn.Close();
}
//点击查询按钮
private void btnSSearch_Click(object sender, EventArgs e)
{
//获取控件信息
String stuName = txtSname.Text.Trim();
String cName = txtCname.Text.Trim();
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
if ((txtSname.Text.Trim() != String.Empty) && (txtCname.Text.Trim() == String.Empty))
{
//执行添加操作
string sql = "select * from SSELECTINFO where SNAME = :stuName ORDER BY SNO ASC";
//使用参数化查询，避免SQL注入问题
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter(":stuName", stuName));
// 查询数据库，将结果填充到数据集
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "Stu");
//数据源绑定到dgv控件
dgvSC.DataSource = ds.Tables["Stu"];
conn.Close();
}
else if ((txtSname.Text.Trim() == String.Empty) && (txtCname.Text.Trim() != String.Empty))
{
//执行添加操作
string sql = "select * from SSELECTINFO where CNAME = :cName ORDER BY CNO ASC";
//使用参数化查询，避免SQL注入问题
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter(":cName", cName));
//查询数据库，将结果填充到数据集
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "Crs");
//数据源绑定到dgv控件
dgvSC.DataSource = ds.Tables["Crs"];
conn.Close();
}
else if ((txtCname.Text.Trim() != String.Empty) && (txtSname.Text.Trim() != String.Empty))
{
//执行添加操作
string sql = "select * from SSELECTINFO where CNAME = :cName AND SNAME = :stuName ORDER BY CNO ASC";
//使用参数化查询，避免SQL注入问题
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter(":cName", cName));
cmd.Parameters.Add(new OracleParameter(":stuName", stuName));
//查询数据库，将结果填充到数据集
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "SC");
//数据源绑定到dgv控件
dgvSC.DataSource = ds.Tables["SC"];
conn.Close();
}
else
{
ShowSC();
}
}
private void dgv_CellClick(object sender, DataGridViewCellEventArgs e)
{
int i = dgvSC.CurrentRow.Index;
txtGetSno.Text = dgvSC.Rows[i].Cells[0].Value.ToString();
txtGetCno.Text = dgvSC.Rows[i].Cells[2].Value.ToString();
txtGetScore.Text = dgvSC.Rows[i].Cells[4].Value.ToString();
}
public bool IsEmpty()
{
//获取控件值，判断
if (txtGetSno.Text.Trim() == String.Empty)
{
MessageBox.Show("学号不能为空！");
return true;
}
if (txtGetCno.Text.Trim() == String.Empty)
{
MessageBox.Show("课程号不能为空！");
return true;
}
if (txtGetScore.Text.Trim() == String.Empty)
{
MessageBox.Show("成绩不能为空！");
return true;
}
return false;
}
public void Clear()
{
foreach (Control ctrl in groupBox1.Controls)
{
if (ctrl is TextBox)
{
ctrl.Text = String.Empty;
}
}
}
private void btnRecord_Click(object sender, EventArgs e)
{
if (!IsEmpty())
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
String sql = "select * from USERINFO where USERID='" + txtGetSno.Text + "' ";
//创建执行对象
OracleCommand comm_sno = new OracleCommand(sql, conn);
//执行sql语句，返回结果
long i = 0;
if (comm_sno.ExecuteScalar() != null)
{
i = Convert.ToInt64(comm_sno.ExecuteScalar());
}
//判断结果
if (i > 0)
{
String sql_cno = "select * from course where cno='" + txtGetCno.Text + "' ";
//创建执行对象
OracleCommand comm_cno = new OracleCommand(sql_cno, conn);
//执行sql语句，返回结果
long k = 0;
if (comm_cno.ExecuteScalar() != null)
{
k = Convert.ToInt64(comm_cno.ExecuteScalar());
}
if (i > 0)
{
String sql_RecScore = "update SC set SSCORE ='"+txtGetScore.Text+"' where SNO='" + txtGetSno.Text + "' and CNO='"+txtGetCno.Text+"'";
OracleCommand command = new OracleCommand(sql_RecScore, conn);
int j = command.ExecuteNonQuery();
if (j > 0)
{
MessageBox.Show("录入成功！");
ShowSC();
}
else
{
MessageBox.Show("录入失败！");
}
Clear();
}
else
{
MessageBox.Show("该课程不存在！");
}
}
else
{
MessageBox.Show("学生账号不存在！");
}
conn.Close();
}
}
}
}·教师任课相关信息
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.DataAccess.Client;
namespace EMISApp
{
public partial class TCForm : Form
{
public TCForm()
{
InitializeComponent();
}
private void TCForm_Load(object sender, EventArgs e)
{
ShowTC();
}
//点击查询按钮
private void btnSreach_Click(object sender, EventArgs e)
{
//获取控件信息
String tchrTame = txtTname.Text.Trim();
String cName = txtCname.Text.Trim();
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
if ((txtTname.Text.Trim() != String.Empty) && (txtCname.Text.Trim() == String.Empty))
{
//执行添加操作
string sql = "select * from COURSEINFO where TNAME = :tchrTame ORDER BY CNO ASC";
//使用参数化查询，避免SQL注入问题
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter(":tchrTame", tchrTame));
// 查询数据库，将结果填充到数据集
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "Tchr");
//数据源绑定到dgv控件
dgvTC.DataSource = ds.Tables["Tchr"];
conn.Close();
}
else if ((txtTname.Text.Trim() == String.Empty) && (txtCname.Text.Trim() != String.Empty))
{
//执行添加操作
string sql = "select * from COURSEINFO where CNAME = :cName ORDER BY CNO ASC";
//使用参数化查询，避免SQL注入问题
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter(":cName", cName));
//查询数据库，将结果填充到数据集
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "Crs");
//数据源绑定到dgv控件
dgvTC.DataSource = ds.Tables["Crs"];
conn.Close();
}
else if ((txtCname.Text.Trim() != String.Empty) && (txtTname.Text.Trim() != String.Empty))
{
//执行添加操作
string sql = "select * from COURSEINFO where CNAME = :cName AND TNAME = :tchrTame ORDER BY CNO ASC";
//使用参数化查询，避免SQL注入问题
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter(":cName", cName));
cmd.Parameters.Add(new OracleParameter(":tchrTame", tchrTame));
//查询数据库，将结果填充到数据集
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "TC");
//数据源绑定到dgv控件
dgvTC.DataSource = ds.Tables["TC"];
conn.Close();
}
else
{
ShowTC();
}
}
public void ShowTC()
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql = "select * from COURSEINFO ORDER BY CNO ASC";
//查询数据库后，将数据绑定到dgv控件：使用数据集DataSet和适配器DataAdapter
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(sql, conn);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "TC");
//数据源绑定到dgv控件
dgvTC.DataSource = ds.Tables["TC"];
conn.Close();
}
private void dgv_CellClick(object sender, DataGridViewCellEventArgs e)
{
int i = dgvTC.CurrentRow.Index;
txtGetCno.Text = dgvTC.Rows[i].Cells[0].Value.ToString();
txtGetTno.Text = dgvTC.Rows[i].Cells[2].Value.ToString();
}
public bool IsEmpty()
{
//获取控件值，判断
if (txtGetTno.Text.Trim() == String.Empty)
{
MessageBox.Show("工号不能为空！");
return true;
}
if (txtGetCno.Text.Trim() == String.Empty)
{
MessageBox.Show("课程号不能为空！");
return true;
}
return false;
}
public void Clear()
{
foreach (Control ctrl in groupBox1.Controls)
{
if (ctrl is TextBox)
{
ctrl.Text = String.Empty;
}
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (!IsEmpty())
{
//获取所有控件值
String tchrNo = txtGetTno.Text.Trim();
String cNo = txtGetCno.Text.Trim();
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//准备查询语句
String sql = "select * from teacher where tno='" + tchrNo + "' ";
//创建执行对象
OracleCommand comm = new OracleCommand(sql, conn);
//执行sql语句，返回结果
long i = 0;
if (comm.ExecuteScalar() != null)
{
i = Convert.ToInt64(comm.ExecuteScalar());
}
//判断结果
if (i > 0)
{
String sql_cno = "select * from course where cno='" + cNo + "' ";
//创建执行对象
OracleCommand comm_cno = new OracleCommand(sql_cno, conn);
//执行sql语句，返回结果
long k = 0;
if (comm_cno.ExecuteScalar() != null)
{
k = Convert.ToInt64(comm_cno.ExecuteScalar());
}
if (i > 0)
{
String sql_RecScore = "insert into TC values('" + tchrNo + "','" + cNo + "')";
OracleCommand command = new OracleCommand(sql_RecScore, conn);
int j = command.ExecuteNonQuery();
if (j > 0)
{
MessageBox.Show("添加成功！");
ShowTC();
}
else
{
MessageBox.Show("添加失败！");
}
Clear();
}
else
{
MessageBox.Show("该课程不存在！");
}
}
else
{
MessageBox.Show("教师工号不存在！");
}
conn.Close();
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (!IsEmpty())
{
//获取所有控件值
String tchrNo = txtGetTno.Text.Trim();
String cNo = txtGetCno.Text.Trim();
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//准备查询语句
String sql = "select * from teacher where tno='" + tchrNo + "' ";
//创建执行对象
OracleCommand comm = new OracleCommand(sql, conn);
//执行sql语句，返回结果
long i = 0;
if (comm.ExecuteScalar() != null)
{
i = Convert.ToInt64(comm.ExecuteScalar());
}
//判断结果
if (i > 0)
{
String sql_cno = "select * from course where cno='" + cNo + "' ";
//创建执行对象
OracleCommand comm_cno = new OracleCommand(sql_cno, conn);
//执行sql语句，返回结果
long k = 0;
if (comm_cno.ExecuteScalar() != null)
{
k = Convert.ToInt64(comm_cno.ExecuteScalar());
}
if (i > 0)
{
String sql_deleteTC = "Delete from tc where TNO='" + tchrNo + "' and CNO='" + cNo + "'";
DialogResult result = MessageBox.Show("确定删除该教师的任课信息？", "提示信息", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
OracleCommand command = new OracleCommand(sql_deleteTC, conn);
int j = command.ExecuteNonQuery();
if (j > 0)
{
MessageBox.Show("删除成功！");
ShowTC();
}
else
{
MessageBox.Show("删除失败！");
}
Clear();
}
}
else
{
MessageBox.Show("该课程不存在！");
}
}
else
{
MessageBox.Show("教师工号不存在！");
}
conn.Close();
}
}
}
}④管理员主窗口
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.DataAccess.Client;
namespace EMISApp
{
public partial class AdminForm : Form
{
public AdminForm()
{
InitializeComponent();
this.FormClosing += AdminForm_FormClosing;
}
//点击跳转添加学生账户窗体
private void tsmiAddStu_Click(object sender, EventArgs e)
{
//AddStuForm addStu = null;
//if (Application.OpenForms["AddStuForm"]!=null)
//{
// addStu = (AddStuForm)Application.OpenForms["AddStuForm"];
//}
//else
//{
// addStu = new AddStuForm();
// //设置打开的窗体为主窗体，把当前窗体作父窗体
// //addStu.MdiParent = this;
// addStu.Show();
//}
ShowStuForm showStu = null;
if (Application.OpenForms["ShowStuForm"] != null)
{
showStu = (ShowStuForm)Application.OpenForms["ShowStuForm"];
}
else
{
showStu = new ShowStuForm();
showStu.Show();
}
}
//点击跳转修改学生信息窗体
private void tsmiEditStu_Click(object sender, EventArgs e)
{
ShowStuForm showStu = null;
if (Application.OpenForms["ShowStuForm"] != null)
{
showStu = (ShowStuForm)Application.OpenForms["ShowStuForm"];
}
else
{
showStu = new ShowStuForm();
showStu.Show();
}
}
//点击跳转查询学生列表窗体
private void tsmiQueryStu_Click(object sender, EventArgs e)
{
ShowStuForm showStu = null;
if (Application.OpenForms["ShowStuForm"] != null)
{
showStu = (ShowStuForm)Application.OpenForms["ShowStuForm"];
}
else
{
showStu = new ShowStuForm();
showStu.Show();
}
}
//点击跳转学生选课信息窗体
private void tsmiShowSC_Click(object sender, EventArgs e)
{
SCForm sc = null;
if (Application.OpenForms["SCForm"] != null)
{
sc = (SCForm)Application.OpenForms["SCForm"];
}
else
{
sc = new SCForm();
sc.Show();
}
}
private void tsmiShowTC_Click(object sender, EventArgs e)
{
TCForm tc = null;
if (Application.OpenForms["TCForm"] != null)
{
tc = (TCForm)Application.OpenForms["TCForm"];
}
else
{
tc = new TCForm();
tc.Show();
}
}
private void tsmiDeleteStu_Click(object sender, EventArgs e)
{
ShowStuForm showStu = null;
if (Application.OpenForms["ShowStuForm"] != null)
{
showStu = (ShowStuForm)Application.OpenForms["ShowStuForm"];
}
else
{
showStu = new ShowStuForm();
showStu.Show();
}
}
private void tsmiRecordScore_Click(object sender, EventArgs e)
{
SCForm sc = null;
if (Application.OpenForms["SCForm"] != null)
{
sc = (SCForm)Application.OpenForms["SCForm"];
}
else
{
sc = new SCForm();
sc.Show();
}
}
private void tsmiQuerytchr_Click(object sender, EventArgs e)
{
ShowTchrForm st = null;
if (Application.OpenForms["ShowTchrForm"] != null)
{
st = (ShowTchrForm)Application.OpenForms["ShowTchrForm"];
}
else
{
st = new ShowTchrForm();
st.Show();
}
}
private void AdminForm_FormClosing(object sender, FormClosingEventArgs e)
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=LAPTOP-OMCON2HD)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//准备查询语句
String sql = "DROP VIEW USERVIEW";
//创建执行对象
OracleCommand command = new OracleCommand(sql, conn);
// 执行删除视图的命令
command.ExecuteNonQuery();
conn.Close();
MessageBox.Show("已退出");
}
private void tsmiAddTchr_Click(object sender, EventArgs e)
{
ShowTchrForm st = null;
if (Application.OpenForms["ShowTchrForm"] != null)
{
st = (ShowTchrForm)Application.OpenForms["ShowTchrForm"];
}
else
{
st = new ShowTchrForm();
st.Show();
}
}
private void tsmiEditTchr_Click(object sender, EventArgs e)
{
ShowTchrForm st = null;
if (Application.OpenForms["ShowTchrForm"] != null)
{
st = (ShowTchrForm)Application.OpenForms["ShowTchrForm"];
}
else
{
st = new ShowTchrForm();
st.Show();
}
}
private void tsmiDeleteTchr_Click(object sender, EventArgs e)
{
ShowTchrForm st = null;
if (Application.OpenForms["ShowTchrForm"] != null)
{
st = (ShowTchrForm)Application.OpenForms["ShowTchrForm"];
}
else
{
st = new ShowTchrForm();
st.Show();
}
}
private void tsmiQuitAF_Click(object sender, EventArgs e)
{
}
}
}（8）学生界面功能的代码实现（主要部分，教师与学生类似）
①账户管理功能集
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.DataAccess.Client;
namespace EMISApp
{
public partial class StuSelfForm : Form
{
public StuSelfForm()
{
InitializeComponent();
}
private void StuSelfForm_Load(object sender, EventArgs e)
{
ShowS();
}
public void ShowS()
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql = "select * from student where sno = (SELECT sno FROM userview )";
//查询数据库后，将数据绑定到dgv控件：使用数据集DataSet和适配器DataAdapter
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(sql, conn);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "ss");
//数据源绑定到dgv控件
dgvStuSelf.DataSource = ds.Tables["ss"];
conn.Close();
}
//判断是否有不能为空的填的是空值
public bool IsEmpty()
{
//获取控件值，判断
if (txtSno.Text.Trim() == String.Empty)
{
MessageBox.Show("学号不能为空！");
return true;
}
if (txtSpwd.Text.Trim() == String.Empty)
{
MessageBox.Show("密码不能为空！");
return true;
}
if (txtSname.Text.Trim() == String.Empty)
{
MessageBox.Show("姓名不能为空！");
return true;
}
if (txtSdept.Text.Trim() == String.Empty)
{
MessageBox.Show("系别不能为空！");
return true;
}
if (txtSsex.Text.Trim() == String.Empty)
{
MessageBox.Show("性别不能为空！");
return true;
}
if (txtSage.Text.Trim() == String.Empty)
{
MessageBox.Show("年龄不能为空！");
return true;
}
return false;
}
//清空分组框的控件值
public void Clear()
{
foreach (Control ctrl in groupBox1.Controls)
{
if (ctrl is TextBox)
{
ctrl.Text = String.Empty;
}
}
}
private void dgv_CellClick(object sender, DataGridViewCellEventArgs e)
{
int i = dgvStuSelf.CurrentRow.Index;
txtSno.Text = dgvStuSelf.Rows[i].Cells[0].Value.ToString();
txtSname.Text = dgvStuSelf.Rows[i].Cells[1].Value.ToString();
txtSdept.Text = dgvStuSelf.Rows[i].Cells[4].Value.ToString();
txtSsex.Text = dgvStuSelf.Rows[i].Cells[2].Value.ToString();
txtSage.Text = dgvStuSelf.Rows[i].Cells[3].Value.ToString();
txtSpwd.Text = dgvStuSelf.Rows[i].Cells[5].Value.ToString();
}
private void btnSEdit_Click(object sender, EventArgs e)
{
if (!IsEmpty())
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
String sql_editStu = "update STUDENT set SNAME='" + txtSname.Text + "',SDEPT='" + txtSdept.Text + "',SSEX='" + txtSsex.Text + "',SAGE='" + txtSage.Text + "',SPWD='" + txtSpwd.Text + "' where SNO=(SELECT sno FROM userview )";
OracleCommand command = new OracleCommand(sql_editStu, conn);
int j = command.ExecuteNonQuery();
if (j > 0)
{
MessageBox.Show("修改成功！");
ShowS();
}
else
{
MessageBox.Show("修改失败！");
}
Clear();
conn.Close();
}
}
}
}②学生选课信息查询、自主选课功能
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.DataAccess.Client;
namespace EMISApp
{
public partial class StuSelectForm : Form
{
public StuSelectForm()
{
InitializeComponent();
}
private void StuSelectForm_Load(object sender, EventArgs e)
{
ShowSC();
}
public void ShowSC()
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql = "select * from SSELECTINFO where sno = (SELECT sno FROM userview )";
//查询数据库后，将数据绑定到dgv控件：使用数据集DataSet和适配器DataAdapter
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(sql, conn);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "StuScore");
//数据源绑定到dgv控件
dgvSelect.DataSource = ds.Tables["StuScore"];
conn.Close();
}
private void btnSum_Click(object sender, EventArgs e)
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql = "select * from SSCOREINFO where sno = (SELECT sno FROM userview) ";
//查询数据库后，将数据绑定到dgv控件：使用数据集DataSet和适配器DataAdapter
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(sql, conn);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "s");
//数据源绑定到dgv控件
dgvSelect.DataSource = ds.Tables["s"];
conn.Close();
}
private void btnSearch_Click(object sender, EventArgs e)
{
//获取控件信息
String cName = txtCname.Text.Trim();
if (txtCname.Text.Trim() != String.Empty)
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql = "select * from SSELECTINFO where CNAME = :cName and sno =(SELECT sno FROM userview)";
//使用参数化查询，避免SQL注入问题
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter(":cName", cName));
// 查询数据库，将结果填充到数据集
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "s");
//数据源绑定到dgv控件
dgvSelect.DataSource = ds.Tables["s"];
conn.Close();
}
else
{
ShowSC();
}
}
private void btnShowCrs_Click(object sender, EventArgs e)
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//执行添加操作
string sql = "select * from course";
//查询数据库后，将数据绑定到dgv控件：使用数据集DataSet和适配器DataAdapter
DataSet ds = new DataSet();
OracleDataAdapter dataAdapter = new OracleDataAdapter(sql, conn);
//将获取的数据填充到数据集ds
dataAdapter.Fill(ds, "crs");
//数据源绑定到dgv控件
dgvSelect.DataSource = ds.Tables["crs"];
conn.Close();
}
public void Clear()
{
foreach (Control ctrl in groupBox1.Controls)
{
if (ctrl is TextBox)
{
ctrl.Text = String.Empty;
}
}
}
//点击单元行
private void dgv_CellClick(object sender, DataGridViewCellEventArgs e)
{
int i = dgvSelect.CurrentRow.Index;
txtGetCno.Text = dgvSelect.Rows[i].Cells[0].Value.ToString();
}
private void btnSelect_Click(object sender, EventArgs e)
{
if (txtGetCno.Text.Trim() == String.Empty)
{
MessageBox.Show("请选择课程！");
}
else
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
String sql_IsExit = "select * from COURSE where CNO='" + txtGetCno.Text + "' ";
//创建执行对象
OracleCommand comm = new OracleCommand(sql_IsExit, conn);
//执行sql语句，返回结果
long i = 0;
if (comm.ExecuteScalar() != null)
{
i = Convert.ToInt64(comm.ExecuteScalar());
}
if (i > 0)
{
//执行添加操作
String sql_IsSelect = "select * from SC where CNO='" + txtGetCno.Text + "'and sno = (SELECT sno FROM userview)";
//创建执行对象
OracleCommand command = new OracleCommand(sql_IsSelect, conn);
long j = 0;
if (command.ExecuteScalar() != null)
{
j = Convert.ToInt64(command.ExecuteScalar());
}
if (j > 0)
{
MessageBox.Show("该课程已选！");
}
else
{
//获取该学生学号
String sql_getSno = "SELECT sno FROM userview";
OracleCommand cmd = new OracleCommand(sql_getSno, conn);
OracleDataReader reader = cmd.ExecuteReader();
String sNo = null;
// 处理结果集
if (reader.Read())
{
sNo = reader["sno"].ToString();
// 在这里可以将sNo用于后续的逻辑处理
}
else
{
Console.WriteLine("error!!");
}
// 关闭DataReader
reader.Close();
String sql_select = "insert into sc values(:sno, :cno, '')";
//创建执行对象
OracleCommand com = new OracleCommand(sql_select, conn);
com.Parameters.Add(new OracleParameter(":sno", sNo));
com.Parameters.Add(new OracleParameter(":cno", txtGetCno.Text));
int k = com.ExecuteNonQuery();
if (k > 0)
{
MessageBox.Show("选课成功!");
ShowSC();
}
else
{
MessageBox.Show("选课失败！");
}
Clear();
}
}
else
{
MessageBox.Show("暂无此课程！");
}
conn.Close();
}
}
}
}③学生主窗口
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.DataAccess.Client;
namespace EMISApp
{
public partial class StuForm : Form
{
public StuForm()
{
InitializeComponent();
this.FormClosing += AdminForm_FormClosing;
}
private void tsmiStuChsCrs_Click(object sender, EventArgs e)
{
StuSelectForm ss = null;
if (Application.OpenForms["StuSelectForm"] != null)
{
ss = (StuSelectForm)Application.OpenForms["StuSelectForm"];
}
else
{
ss = new StuSelectForm();
ss.Show();
}
}
private void tsmiShowSscore_Click(object sender, EventArgs e)
{
StuSelectForm ss = null;
if (Application.OpenForms["StuSelectForm"] != null)
{
ss = (StuSelectForm)Application.OpenForms["StuSelectForm"];
}
else
{
ss = new StuSelectForm();
ss.Show();
}
}
private void AdminForm_FormClosing(object sender, FormClosingEventArgs e)
{
//数据库连接字符串
String connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=LAPTOP-OMCON2HD)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));"
+ "User Id=CMD;Password=cmd";
//创建连接对象
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//准备查询语句
String sql = "DROP VIEW USERVIEW";
//创建执行对象
OracleCommand command = new OracleCommand(sql, conn);
// 执行删除视图的命令
command.ExecuteNonQuery();
conn.Close();
MessageBox.Show("已退出");
}
private void tsmiSC_Click(object sender, EventArgs e)
{
StuSelectForm ss = null;
if (Application.OpenForms["StuSelectForm"] != null)
{
ss = (StuSelectForm)Application.OpenForms["StuSelectForm"];
}
else
{
ss = new StuSelectForm();
ss.Show();
}
}
private void tsmiStuBaseinfo_Click(object sender, EventArgs e)
{
StuSelfForm ss = null;
if (Application.OpenForms["StuSelfForm"] != null)
{
ss = (StuSelfForm)Application.OpenForms["StuSelfForm"];
}
else
{
ss = new StuSelfForm();
ss.Show();
}
}
private void tsmiEditSpwd_Click(object sender, EventArgs e)
{
StuSelfForm ss = null;
if (Application.OpenForms["StuSelfForm"] != null)
{
ss = (StuSelfForm)Application.OpenForms["StuSelfForm"];
}
else
{
ss = new StuSelfForm();
ss.Show();
}
}
}
}六、实验项目效果展示：
（1）Oracle数据库的项目应用：教务信息管理系统EMIS
①登录窗口
·登录验证：一项都不能为空
·登录验证：是否存在用户
·登录成功后：跳转窗体（以下为范例）
②管理员主窗口
·学生账号管理功能集
默认情况（无输入查询信息时）：展示所有信息且随时更新
·添加学生
按确定后可以及时更新列表、在列表查询到：
若账户已存在：
若其中有一个空为空：（避免麻烦仅展示密码为空时）
按名查询：
如果输入差错：结果为空
·修改学生账户
结果：
如果输错信息：
·删除学生账户：仅填学号即可（根据学号删除，设置点击自动录入空格更方便）
结果：再次查询无
·教师账号管理功能集
·查询教师基本信息
无输入（默认）：展示所有教师基本信息
统计按键：统计不同职称的教师的数量、不同职称的教师的平均工资
指定查看教师：
·添加、删除、修改教师与学生类似（仅展示成功画面，有代码可验证）
·综合查询功能集
·学生选课信息查询
无输入（默认）：可以查看所有学生的选课信息及其相应的成绩
单人或单课程查询：
组合查询：
·录入学生成绩：（无需手动输入学号和课程号）
按确认后：
检错：
·教师任课信息查询
无输入时（默认）：展示所有相关信息
单输入教师或课程：
容错：返回空
组合查询：
·添加教师任课信息
·删除教师任课消息
验错情况：存在性和空值（工号和课程号都是，仅展示工号）
③学生主窗口
登录此账号：临时创建一个关于这个账号的个人信息视图，时候退出会自行删除。
选择登录该账户：只展示此人信息，且之后无需输入学号姓名等个人信息验证查询。
·查看和修改个人信息、密码
查看个人基本信息：
修改信息：学号不可改
·学生查询已选课程、自主选课功能
·查询已选课程
默认（无输入时）查询各科的：
指定查询：
统计总学分、总学分：
·自主选课
显示课表：方便查询要选的课程
选课：
新选的课还没有成绩：
出错检验：
七、实验过程中遇到的问题
1、在调用窗体变量时，出现报错，原因是权限不足。要在每一个相对应的.Designer.cs文件中
将private改成public。
2、要获取输入控件的值，要设置参数形式，设置完之后要记得添加，不然就是无效的设置。
3、调试时出现以下错误：
·进程占用解决：打开Windows任务管理器，结束EMISApp.exe
·未能加载文件或程序集“Oracle.DataAccess”或它的某一个 依赖项
原因：所生成项目的处理器架构“MSIL”与引用“Oracle.DataAccess, Version=4.112.2.0,
Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=AMD64”的处理器架
构“AMD64”不匹配。这种不匹配可能会导致运行时失败。请考虑通过配置管理器更改您的项
目的目标处理器架构，以使您的项目与引用间的处理器架构保持一致，或者为引用关联一个与
您的项目的目标处理器架构相符的处理器架构。
解决：
①保证.dll路径存在（不行就换）
②更改项目的目标处理器架构：右键单击项目→属性→生成→平台目标→x64
确保项目生成的是与 Oracle.DataAccess 相匹配的64位代码
·转换异常
原因：由于试图将执行 command.ExecuteScalar() 返回的结果直接转换为 int 类型，但实际上
ExecuteScalar() 返回的是 object 类型（查询结果集中的第一行第一列的值），需要根据情况进
行正确的类型转换。
解决方式：改句期望返回的是一个计数（比如记录数），可以将结果转换为 int 或使用
Convert.ToInt32()。
4、要在窗体处显示平均值时，报错：指定的转换无效。原因是Oracle中的值为double类型，
c#中的值为float类型。在生成平均值的时候，时用Round()函数即可解决问题。
5、若要删除有外键的子表，需先删除其约束才可以删表，具体实例如下：
ALTER TABLE tc DROP CONSTRAINT SYS_C007175;
drop TABLE tc;
6、在c#如果想要插入空，不要用“null”（会插入字符），用“”；在综合查询中，可能会出现其
一子查询为空，整个查询就会空的问题，注意。
例如以下错误：插入了字符串，之后相关视图会数据消失。
八、实验体会
1、数据库设计最重要的一点是深入具体了解需求细节，画好概念模型和整理好逻辑结构，按照程序一步步来实现，若考虑不周到，将给之后的实现带来巨大的改动麻烦。
2、学会灵活使用视图，会为最终的系统设计带来极大便利。
3、在编写c#代码时，最难处理的是数据库信息和编程信息的对接，此时会出现很多报错，需要耐心理解。
4、实现用户界面时要考虑用户使用体验，化繁为简，功能聚合，窗口不宜过多。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
