上学期的大作业,以此为记。
基于C#+Oracle的模拟图书馆管理系统
目录
需要实现的功能
1.学生登录/注册
2.后台(admin)增删改查,包括修改学生信息和图书信息,可以查看所有书库库存以及学生信息表。
3.学生登录后的信息查看,自己的借书情况 以及到期日期。
数据库:Oracle11.2.0
前端:C#
开发平台:visual studio 2017 ,PL/SQL Developer
需要开启的服务
OracleServiceORCL
TNSListener
OracleDBConsoleORCL
数据库设计
图书馆分为三个校区,每个校区各有一张图书信息表。一共三个数据库。
名称 | 类型 | 可为空 | 注释 |
---|---|---|---|
bid | varchar | no | 图书ID |
bname | varchar | no | 图书名 |
bnum | int | no | 图书库存数量 |
学生表
名称 | 类型 | 可为空 | 注释 |
---|---|---|---|
sno | varchar | no | 学号 |
sname | varchar | yes | 姓名 |
spassword | varchar | no | 密码 |
region | varchar | no | 所属校区 |
借书表
名称 | 类型 | 可为空 | 注释 |
---|---|---|---|
sno | varchar | no | 学号 |
bno | varchar | no | 书号 |
bregion | varchar | no | 图书所属校区 |
gtime | date | no | 借书时间 |
btime | date | no | 归还时间 |
数据库sql文件,由于我的资源中不包含数据库需要自行创建oracle数据库然后把表导进去。
dzy数据库
用户名 admin1 密码qwe123789A
包含借书表和学生表
sql文件,导入到PLSQL即可
表结构(必须先导入表 在导入数据)
--------------------------------------------
-- Export file for user ADMIN1 --
-- Created by dell on 2020/2/20, 15:44:57 --
--------------------------------------------
set define off
spool student.log
prompt
prompt Creating table BORROW
prompt =====================
prompt
create table BORROW
(
sno VARCHAR2(20) not null,
bno VARCHAR2(20) not null,
bregion VARCHAR2(20) not null,
gtime DATE not null,
btime DATE not null
)
;
prompt
prompt Creating table STUDENT
prompt ======================
prompt
create table STUDENT
(
sno VARCHAR2(20) not null,
sname VARCHAR2(10),
spassword VARCHAR2(20) not null,
region VARCHAR2(20) not null
)
;
comment on column STUDENT.sno
is '学号';
comment on column STUDENT.sname
is '姓名';
comment on column STUDENT.spassword
is '密码';
comment on column STUDENT.region
is '所属校区';
alter table STUDENT
add constraint UNIQ primary key (SNO);
spool off
数据
prompt PL/SQL Developer import file
prompt Created on 2020年2月20日 by dell
set feedback off
set define off
prompt Disabling triggers for BORROW...
alter table BORROW disable all triggers;
prompt Disabling triggers for STUDENT...
alter table STUDENT disable all triggers;
prompt Deleting STUDENT...
delete from STUDENT;
commit;
prompt Deleting BORROW...
delete from BORROW;
commit;
prompt Loading BORROW...
insert into BORROW (sno, bno, bregion, gtime, btime)
values ('1704010604', 'w000004', '西区', to_date('13-12-2019', 'dd-mm-yyyy'), to_date('20-12-2019', 'dd-mm-yyyy'));
insert into BORROW (sno, bno, bregion, gtime, btime)
values ('1704010604', 'e000002', '东区', to_date('23-12-2019', 'dd-mm-yyyy'), to_date('30-12-2019', 'dd-mm-yyyy'));
insert into BORROW (sno, bno, bregion, gtime, btime)
values ('1704010604', 'w000001', '西区', to_date('14-12-2019', 'dd-mm-yyyy'), to_date('21-12-2019', 'dd-mm-yyyy'));
commit;
prompt 3 records loaded
prompt Loading STUDENT...
insert into STUDENT (sno, sname, spassword, region)
values ('1704010405', '曹昂', '966330', '西区');
insert into STUDENT (sno, sname, spassword, region)
values ('1704011230', '测试三', '456023', '南区');
insert into STUDENT (sno, sname, spassword, region)
values ('1703010205', '测试四', '123055', '南区');
insert into STUDENT (sno, sname, spassword, region)
values ('1604010618', '测试二', '332213', '东区');
insert into STUDENT (sno, sname, spassword, region)
values ('1804010443', '测试南', '885522', '南区');
insert into STUDENT (sno, sname, spassword, region)
values ('1704010604', '萧炎', '230017', '西区');
insert into STUDENT (sno, sname, spassword, region)
values ('1704010301', '约翰', '987654', '西区');
insert into STUDENT (sno, sname, spassword, region)
values ('1704010302', 'JR史密斯', '643359', '南区');
insert into STUDENT (sno, sname, spassword, region)
values ('1701010111', '测试', '445566', '西区');
insert into STUDENT (sno, sname, spassword, region)
values ('12306', '管理员', '517406', '图书馆管理员');
insert into STUDENT (sno, sname, spassword, region)
values ('1704010808', '海瑟薇', '123665', '西区');
insert into STUDENT (sno, sname, spassword, region)
values ('1803020506', '安东尼', '456123', '东区');
commit;
prompt 12 records loaded
prompt Enabling triggers for BORROW...
alter table BORROW enable all triggers;
prompt Enabling triggers for STUDENT...
alter table STUDENT enable all triggers;
set feedback on
set define on
prompt Done.
图书库
数据库名 libwest,libeast,libsouth
分别对应用户west1,east1,south1代表三个区,密码同上。
表结构代码(bookwest bookeast booksouth)
--------------------------------------------
-- Export file for user WEST1 --
-- Created by dell on 2020/2/20, 16:04:32 --
--------------------------------------------
set define off
spool book.log
prompt
prompt Creating table BOOKWEST
prompt =======================
prompt
create table BOOKWEST
(
bid VARCHAR2(20) not null,
bname VARCHAR2(20) not null,
bnum NUMBER not null
)
;
comment on column BOOKWEST.bid
is '图书ID';
comment on column BOOKWEST.bname
is '图书名称';
comment on column BOOKWEST.bnum
is '图书数目';
alter table BOOKWEST
add constraint ID primary key (BID);
spool off
数据
prompt PL/SQL Developer import file
prompt Created on 2020年2月20日 by dell
set feedback off
set define off
prompt Disabling triggers for BOOKWEST...
alter table BOOKWEST disable all triggers;
prompt Deleting BOOKWEST...
delete from BOOKWEST;
commit;
prompt Loading BOOKWEST...
insert into BOOKWEST (bid, bname, bnum)
values ('w000001', '三国演义', 9);
insert into BOOKWEST (bid, bname, bnum)
values ('w000002', '二战全史', 6);
insert into BOOKWEST (bid, bname, bnum)
values ('w000003', '水浒传', 9);
insert into BOOKWEST (bid, bname, bnum)
values ('w000004', '操作系统', 15);
commit;
prompt 4 records loaded
prompt Enabling triggers for BOOKWEST...
alter table BOOKWEST enable all triggers;
set feedback on
set define on
prompt Done.
导入方法:先将代码保存在文本文档,然后改拓展名为.sql 在PLSQL中选择tools-导入,先导入表结构,再导入数据。
前端设计
采用VS2017的C#作为前端开发语言。引用了Oracle的DLL作为库。
启动页面
登录连接数据库的核心代码
private void LoginButton_Click(object sender, EventArgs e)
{
if(textBoxSnoInput.Text==""||textBoxPasswordInput.Text==""||region.Text==""||region.Text=="请选择校区")
{
MessageBox.Show("用户名或密码或校区信息不能为空!", "提示",MessageBoxButtons.OKCancel,MessageBoxIcon.Error);
}
else
{
string username = textBoxSnoInput.Text.ToString();
string password = textBoxPasswordInput.Text.ToString();
string region1 = region.Text.ToString();
string strConnection = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA = (SERVICE_NAME = dzy))); Persist Security Info = True; User ID = admin1; Password = qwe123789A; ";//以上为一行,显示为三行
OracleConnection conn = new OracleConnection(strConnection);
conn.Open();
OracleCommand LoginCommand = conn.CreateCommand();
LoginCommand.CommandText = "select * from student where sno = '" + username + "' and spassword = '" + password + "'"; ;
OracleDataReader reader = LoginCommand.ExecuteReader();
try
{
while (reader.Read())
{
name = reader["sno"].ToString();
pwd = reader["spassword"].ToString();
reg = reader["region"].ToString();
}
if (username.Trim() == name.Trim() && password.Trim() == pwd.Trim() && region1.Trim() == reg.Trim())
{
MessageBox.Show("欢迎登录图书管理系统! ", "SUCCESS");
this.Hide();
if(region1 == "图书馆管理员")
{
AdminLogon adminLogon = new AdminLogon();
adminLogon.Show();
}
else
{
StudentLogon studentLogon = new StudentLogon();
studentLogon.Show();
}
//Show sw = new Show();//跳转窗体
//sw.Show();
}
else
{
MessageBox.Show("您的用户名或密码不正确! ", "ERROR");
}
}
catch
{
MessageBox.Show("数据库无法连接!");
}
finally
{
conn.Close();//关闭连接
}
}
}
学生端界面
使用了datagridview来显示数据库的数据,涉及到在子窗口调用datagridview数据的问题参见
C#.net 子窗口获取父窗口的数据(datagridview)
学生端借书界面
管理员界面(管理员账号 12306 密码517406 唯一)
管理图书信息 右键即可修改
详细代码可以下载我的资源查看。这里使用了VS自带的打包程序setup,解压得到安装包 里面包含着VS工程,一路next安装即可,桌面的快捷方式就是程序。.sln文件在VS里打开即可看到项目源码。
资源链接
打包相关操作参考了xzqsr2011的博文