Oracle数据库图书管理系统的课程设计

在Oracle数据库最初的sys用户XE数据库下执行；

--删除表空间

drop tablespace tablespace_book including contents

cascade constraints;



--创建永久表空间

create tablespace tablespace_book

datafile 'f:\oracle\book' size 50m

autoextend on next 50m maxsize 100m;    --自动增长表空间从50M到100M；



--创建用户

create user book identified by book

default tablespace tablespace_book;



--用户授权

grant all privileges to book;

 

在创建的book用户下执行，我连接的数据库名与用户名相同；

--创建管理员角色

create role manager identified by manager;



--创建读者角色

create role reader identified by reader;



--创建读者表

create table reader(

rno number(10) not null primary key,

rname varchar2(20),

rid number(18) unique)tablespace tablespace_book

storage(initial 256k);



--创建图书表

create table book(

bno number(10) not null primary key,

bname varchar2(20) not null,

bprice varchar(4),

author varchar2(30) not null,

bnum int not null)tablespace tablespace_book

storage(initial 256k);



--创建借阅表

create table rb(

readno number(10) not null primary key,

rno number(10) not null,

rname varchar2(20),

bno number(18) not null,

bname varchar2(20),

regdate date default sysdate,

back char(2))tablespace tablespace_book

storage(initial 256k);



--创建管理员表

create table manager(

mno number(10) not null primary key,

mname varchar2(20)not null,

regdate date default sysdate

)tablespace tablespace_book

storage(initial 256k);



--为借阅表创建外键约束

alter table rb add constraint fk_rb foreign key

(rno) references

reader(rno);



--向读者表中插入数据

insert into reader(rno,rname,rid)

values(101,'叶凡一',123456789);

insert into reader(rno,rname,rid)

values(102,'叶凡二',223456789);

insert into reader(rno,rname,rid)

values(103,'叶凡三',323456789);

insert into reader(rno,rname,rid)

values(104,'叶凡四',423456789);

insert into reader(rno,rname,rid)

values(105,'叶凡五',523456789);

insert into reader(rno,rname,rid)

values(106,'叶凡六',623456789);

insert into reader(rno,rname,rid)

values(107,'叶凡七',723456789);

insert into reader(rno,rname,rid)

values(108,'叶凡八',823456789);

insert into reader(rno,rname,rid)

values(109,'叶凡九',923456789);

insert into reader(rno,rname,rid)

values(110,'叶凡十',023456789);



--向图书表中插入10条数据

insert into book(bno,bname,bprice,author,bnum)

values(1010,'遮天1',50,'辰东',1);

insert into book(bno,bname,bprice,author,bnum)

values(1011,'遮天2',50,'辰东',3);

insert into book(bno,bname,bprice,author,bnum)

values(1012,'遮天3',50,'辰东',5);

insert into book(bno,bname,bprice,author,bnum)

values(1013,'遮天4',50,'辰东',7);

insert into book(bno,bname,bprice,author,bnum)

values(1014,'遮天5',50,'辰东',8);

insert into book(bno,bname,bprice,author,bnum)

values(1015,'遮天6',50,'辰东',5);

insert into book(bno,bname,bprice,author,bnum)

values(1016,'遮天7',50,'辰东',4);

insert into book(bno,bname,bprice,author,bnum)

values(1017,'遮天8',50,'辰东',2);

insert into book(bno,bname,bprice,author,bnum)

values(1018,'遮天9',50,'辰东',10);

insert into book(bno,bname,bprice,author,bnum)

values(1019,'遮天10',50,'晨曦',10);



--向借阅表中插入数据

insert into rb(readno,rno,rname,bno,bname,BACK)

values(1,103,'叶凡三',1010,'遮天1','N');

insert into rb(readno,rno,rname,bno,bname,BACK)

values(2,103,'叶凡三',1011,'遮天2','N');

insert into rb(readno,rno,rname,bno,bname,BACK)

values(3,103,'叶凡三',1012,'遮天3','N');

insert into rb(readno,rno,rname,bno,bname,BACK)

values(4,104,'叶凡四',1013,'遮天4','N');

insert into rb(readno,rno,rname,bno,bname,BACK)

values(5,105,'叶凡五',1014,'遮天5','N');

insert into rb(readno,rno,rname,bno,bname,BACK)

values(6,105,'叶凡五',1015,'遮天6','N');

insert into rb(readno,rno,rname,bno,bname,BACK)

values(7,107,'叶凡七',1016,'遮天7','N');

insert into rb(readno,rno,rname,bno,bname,BACK)

values(8,109,'叶凡九',1017,'遮天8','N');

insert into rb(readno,rno,rname,bno,bname,BACK)

values(9,101,'叶凡一',1018,'遮天9','N');

insert into rb(readno,rno,rname,bno,bname,BACK)

values(10,101,'叶凡一',1019,'遮天10','N');



--向管理员表中插入数据

insert into manager(mno,mname) values(1,'图图');

insert into manager(mno,mname) values(2,'花花');



--创建主键索引

create unique index manager_index on manager (mno desc) 

tablespace tablespace_book 

storage 

( 

  buffer_pool default 

);



create unique index book_index on book (bno desc) 

tablespace tablespace_book 

storage 

( 

  buffer_pool default 

);



create unique index read_index on rb (readno desc) 

tablespace tablespace_book 

storage 

( 

  buffer_pool default 

);



create unique index reader_index on reader (rno desc) 

tablespace tablespace_book 

storage 

( 

  buffer_pool default 

);



--创建视图

create or replace view book_view_union as

select bno,bname,bprice,bnum from book;



create or replace view reader_view_union as

select rno,rname,rid from reader;



create or replace view rb_view_union as

select readno,rno,rname,bno,bname,regdate,back from rb;



--创建存储过程实现表中数据的修改

--还书数据的修改管理

CREATE OR REPLACE PROCEDURE RBack(B_ID VARCHAR2)

IS

BEGIN

UPDATE RB SET BACK = 'Y' WHERE readno = B_ID;

COMMIT;

END;

--执行

BEGIN

rback('1');

END;



--创建函数统计各种图书的库存量

CREATE OR REPLACE FUNCTION GETSCOUNT(au IN VARCHAR2) RETURN NUMBER

  AS

  BOOKNUM NUMBER;

  BEGIN

  SELECT bnum INTO BOOKNUM FROM BOOK WHERE author = au;

  RETURN(BOOKNUM);

  END;

  

--调用函数

DECLARE

COUNTNUM NUMBER:=0;

BEGIN

COUNTNUM:=GETSCOUNT('晨曦');

SYS.DBMS_OUTPUT.PUT('图书馆有“晨曦”所写书籍总数为: ');

SYS.DBMS_OUTPUT.PUT(COUNTNUM);

SYS.DBMS_OUTPUT.PUT_LINE('本');

END;



--触发器

CREATE OR REPLACE TRIGGER tri_rb

AFTER INSERT OR UPDATE ON RB

FOR EACH ROW

DECLARE

BEGIN

SYS.DBMS_OUTPUT.PUT_LINE('借阅者：'||:NEW.RNAME);

SYS.DBMS_OUTPUT.PUT_LINE('书籍编号：'||:NEW.BNO);

SYS.DBMS_OUTPUT.PUT_LINE('书籍名称：'||:NEW.BNAME);

SYS.DBMS_OUTPUT.PUT_LINE('借出日期：'||:NEW.REGDATE);

SYS.DBMS_OUTPUT.PUT_LINE('是否归还：'||:NEW.BACK);

SYS.DBMS_OUTPUT.PUT_LINE('触发器已被执行');

END;



--普通读者用户只能进行查询操作授权

grant select on book to reader;



--授权管理员角色可进行各种操作

grant connect,resource to manager;

grant create any sequence to manager;  

grant create any table to manager;  

grant delete any table to manager;  

grant insert any table to manager;  

grant select any table to manager;  

grant unlimited tablespace to manager;  

grant execute any procedure to manager;  

grant update any table to manager;  

grant create any view to manager;

【实例简介】
mfc  图书管理系统  数据库   SQLServer   源码   直接可以用的   用VC6.0开发的
【实例截图】
【核心代码】
图书管理系统
└── 06
├── Database
│   ├── BookManage_Data.MDF
│   └── BookManage_Log.LDF
├── readme.doc
└── 源程序
├── bbb.aps
├── bbb.clw
├── bbb.cpp
├── bbbDlg.cpp
├── bbbDlg.cpp.bak
├── bbbDlg.h
├── bbb.dsp
├── bbb.dsw
├── bbb.h
├── bbb.ncb
├── bbb.opt
├── bbb.plg
├── bbb.rc
├── bbb.rc.bak
├── CustomGrid.cpp
├── CustomGrid.h
├── D.cpp
├── Debug
│   ├── bbb.bsc
│   ├── bbbDlg.obj
│   ├── bbbDlg.sbr
│   ├── bbb.exe
│   ├── bbb.ilk
│   ├── bbb.obj
│   ├── bbb.pch
│   ├── bbb.pdb
│   ├── bbb.res
│   ├── bbb.sbr
│   ├── CustomGrid.obj
│   ├── DlgBaseInfoM.obj
│   ├── DlgBaseInfoM.sbr
│   ├── DlgBookAdjust2.obj
│   ├── DlgBookAdjust2.sbr
│   ├── DlgBookAdjustPrice2.obj
│   ├── DlgBookAdjustPrice2.sbr
│   ├── DlgBookInfo1.obj
│   ├── DlgBookInfo1.sbr
│   ├── DlgBookInput2.obj
│   ├── DlgBookInput2.sbr
│   ├── DlgBookKinds1.obj
│   ├── DlgBookKinds1.sbr
│   ├── DlgBookPrice2.obj
│   ├── DlgBookPrice2.sbr
│   ├── DlgBookSale2.obj
│   ├── DlgBookSale2.sbr
│   ├── DlgConfigM.obj
│   ├── DlgConfigM.sbr
│   ├── DlgDeskInfo1.obj
│   ├── DlgDeskInfo1.sbr
│   ├── DlgInputBack2.obj
│   ├── DlgInputBack2.sbr
│   ├── DlgInputBackQuery3.obj
│   ├── DlgInputBackQuery3.sbr
│   ├── DlgInputQuery31.obj
│   ├── DlgInputQuery3.obj
│   ├── DlgInputQuery3.sbr
│   ├── DlgLogin.obj
│   ├── DlgLogin.sbr
│   ├── DlgOperator1.obj
│   ├── DlgOperator1.sbr
│   ├── DlgProviderInfo1.obj
│   ├── DlgProviderInfo1.sbr
│   ├── DlgQueryM.obj
│   ├── DlgQueryM.sbr
│   ├── DlgSaleBack2.obj
│   ├── DlgSaleBack2.sbr
│   ├── DlgSaleBackQuery3.obj
│   ├── DlgSaleBackQuery3.sbr
│   ├── DlgSaleQuery3.obj
│   ├── DlgSaleQuery3.sbr
│   ├── DlgStoreInfo1.obj
│   ├── DlgStoreInfo1.sbr
│   ├── DlgStoreInfoM.obj
│   ├── DlgStoreInfoM.sbr
│   ├── KeyEdit.obj
│   ├── Login.ini
│   ├── msado15.tlh
│   ├── msado15.tli
│   ├── MyCoolMenu.obj
│   ├── MyCoolMenu.sbr
│   ├── PrintDoc.obj
│   ├── PrintFram.obj
│   ├── PrintView.obj
│   ├── Report.obj
│   ├── StdAfx.obj
│   ├── StdAfx.sbr
│   ├── TabSheet.obj
│   ├── TabSheet.sbr
│   ├── vc60.idb
│   └── vc60.pdb
├── DlgBookAdjust2.cpp
├── DlgBookAdjust2.h
├── DlgBookAdjustPrice2.cpp
├── DlgBookAdjustPrice2.h
├── DlgBookInfo1.cpp
├── DlgBookInfo1.h
├── DlgBookInput2.cpp
├── DlgBookInput2.h
├── DlgBookKinds1.cpp
├── DlgBookKinds1.h
├── DlgBookPrice2.cpp
├── DlgBookPrice2.h
├── DlgBookSale2.cpp
├── DlgBookSale2.h
├── DlgConfigM.cpp
├── DlgConfigM.h
├── DlgDeskInfo1.cpp
├── DlgDeskInfo1.h
├── DlgInputBack2.cpp
├── DlgInputBack2.h
├── DlgInputBackQuery3.cpp
├── DlgInputBackQuery3.h
├── DlgInputQuery3.cpp
├── DlgInputQuery3.h
├── DlgLogin.cpp
├── DlgLogin.h
├── DlgOperator1.cpp
├── DlgOperator1.h
├── DlgProviderInfo1.cpp
├── DlgProviderInfo1.h
├── DlgSaleBack2.cpp
├── DlgSaleBack2.h
├── DlgSaleBackQuery3.cpp
├── DlgSaleBackQuery3.h
├── DlgSaleQuery3.cpp
├── DlgSaleQuery3.h
├── DlgStoreInfo1.cpp
├── DlgStoreInfo1.h
├── KeyEdit.cpp
├── KeyEdit.h
├── msado15.tlh
├── msado15.tli
├── mssccprj.scc
├── MyCoolMenu.cpp
├── MyCoolMenu.h
├── PrintDoc.cpp
├── PrintDoc.h
├── PrintFram.cpp
├── PrintFram.h
├── PrintView.cpp
├── PrintView.h
├── pub.h
├── ReadMe.txt
├── Release
│   ├── bbb.bsc
│   ├── bbbDlg.obj
│   ├── bbbDlg.sbr
│   ├── bbb.exe
│   ├── bbb.obj
│   ├── bbb.pch
│   ├── bbb.res
│   ├── bbb.sbr
│   ├── CustomGrid.obj
│   ├── CustomGrid.sbr
│   ├── DlgBaseInfoM.obj
│   ├── DlgBaseInfoM.sbr
│   ├── DlgBookAdjust2.obj
│   ├── DlgBookAdjust2.sbr
│   ├── DlgBookAdjustPrice2.obj
│   ├── DlgBookAdjustPrice2.sbr
│   ├── DlgBookInfo1.obj
│   ├── DlgBookInfo1.sbr
│   ├── DlgBookInput2.obj
│   ├── DlgBookInput2.sbr
│   ├── DlgBookKinds1.obj
│   ├── DlgBookKinds1.sbr
│   ├── DlgBookPrice2.obj
│   ├── DlgBookPrice2.sbr
│   ├── DlgBookSale2.obj
│   ├── DlgBookSale2.sbr
│   ├── DlgConfigM.obj
│   ├── DlgConfigM.sbr
│   ├── DlgDeskInfo1.obj
│   ├── DlgDeskInfo1.sbr
│   ├── DlgInputBack2.obj
│   ├── DlgInputBack2.sbr
│   ├── DlgInputBackQuery3.obj
│   ├── DlgInputBackQuery3.sbr
│   ├── DlgInputQuery3.obj
│   ├── DlgInputQuery3.sbr
│   ├── DlgLogin.obj
│   ├── DlgLogin.sbr
│   ├── DlgOperator1.obj
│   ├── DlgOperator1.sbr
│   ├── DlgProviderInfo1.obj
│   ├── DlgProviderInfo1.sbr
│   ├── DlgQueryM.obj
│   ├── DlgQueryM.sbr
│   ├── DlgSaleBack2.obj
│   ├── DlgSaleBack2.sbr
│   ├── DlgSaleBackQuery3.obj
│   ├── DlgSaleBackQuery3.sbr
│   ├── DlgSaleQuery3.obj
│   ├── DlgSaleQuery3.sbr
│   ├── DlgStoreInfo1.obj
│   ├── DlgStoreInfo1.sbr
│   ├── DlgStoreInfoM.obj
│   ├── DlgStoreInfoM.sbr
│   ├── KeyEdit.obj
│   ├── KeyEdit.sbr
│   ├── Login.ini
│   ├── msado15.tlh
│   ├── msado15.tli
│   ├── MyCoolMenu.obj
│   ├── MyCoolMenu.sbr
│   ├── PrintDoc.obj
│   ├── PrintDoc.sbr
│   ├── PrintFram.obj
│   ├── PrintFram.sbr
│   ├── PrintView.obj
│   ├── PrintView.sbr
│   ├── Report.obj
│   ├── Report.sbr
│   ├── StdAfx.obj
│   ├── StdAfx.sbr
│   ├── TabSheet.obj
│   ├── TabSheet.sbr
│   └── vc60.idb
├── Report.cpp
├── Report.h
├── res
│   ├── 1-1-1.bmp
│   ├── 1-1-2.bmp
│   ├── 1-1-3.bmp
│   ├── 1-1-4.bmp
│   ├── 1-4.bmp
│   ├── 1.bmp
│   ├── 2.bmp
│   ├── 3.bmp
│   ├── 4.bmp
│   ├── a1.bmp
│   ├── aaa.bmp
│   ├── aa.bmp
│   ├── a.bmp
│   ├── ADD.ICO
│   ├── b1.bmp
│   ├── BaseInfo.ico
│   ├── bbb.ico
│   ├── bb.bmp
│   ├── bbb.rc2
│   ├── b.bmp
│   ├── b.bmp 副本 拷贝.bmp
│   ├── bj.bmp
│   ├── c1.bmp
│   ├── clipboard02.ico
│   ├── d1.bmp
│   ├── DEL.ICO
│   ├── DE图书管理系统主界面2.bmp
│   ├── end.ico
│   ├── FindInfo.ico
│   ├── first.ico
│   ├── folder users.ico
│   ├── icon14.ico
│   ├── icon2.ico
│   ├── InputStore.ico
│   ├── Left6_32x32x256.ico
│   ├── mainfram.bmp
│   ├── MessageMode.ico
│   ├── MODFY.ico
│   ├── MsgNoRead.ico
│   ├── QUERY.ICO
│   ├── Recycle.ico
│   ├── Recycle Red.ico
│   ├── Right6_32x32x256.ico
│   ├── Shut Down.ico
│   ├── Thumbs.db
│   ├── toolbar1.bmp
│   ├── toolbar2.bmp
│   ├── 图片2.bmp
│   ├── 按钮-1 拷贝.bmp
│   ├── 按钮-2 拷贝.bmp
│   ├── 按钮-3 拷贝.bmp
│   ├── 按钮-4 拷贝.bmp
│   └── 资产设备管理系统主界面.bmp
├── resource.h
├── resource.hm
├── SkinPlusPlus.h
├── StdAfx.cpp
├── StdAfx.h
├── TabSheet.cpp
├── TabSheet.h
└── vssver.scc
6 directories, 282 files

一，设计目的 

   通过模拟图书管理系统，实现以下功能 

   学生账号的注册 

   学生对馆藏图书状况的查询 

   学生借书，还书状态的查询 

   学生借书以及归还图书 

   学生欠款的查询 

二，框架分析 

数据库主要包括： 

学生表：学生学号，学生姓名，学生性别，学生类型，学生密码 

图书表：图书编号，图书名，图书出版社，图书作者，图书状态（1代表可以借，0代表不可以，）图书类型，图书出版日期 

借书表：学生号，图书号，图书名，借出日期 

还书表：学生号，图书号，图书名，归还日期 

欠费金额表：学生号，图书号，图书名，欠费金额（超过30天每天收0.3元） 

三，实验环境 

VS2013，SQLSERVER2014 

四，体系结构 

在客户端与数据库之间增加了一个“中间层”，分为表示层，业务逻辑层，数据访问层 

表示层：为客户提供对应程序的访问 

业务逻辑层：以类库的形式为表示层服务 

数据访问层：实现整个系统的数据库连接，数据库存取操作，以组件类库的形式为业务逻辑层提供服务 

五，代码实现 

数据库的建立：`



create  database MyLibrary 
 on  
 (name=Mylibrary_data1, 
  filename='d:\Mylibrary_data1.mdf',
  size=10,
   maxsize=50, filegrowth=4)
log on 
 (name=Mylibrary_log1,  
 filename='d:\Mylibrary_log1.ldf',
   size=10MB,
   maxsize=20MB, 
   filegrowth=2MB) 
use MyLibrary;
create table reader(
readerid varchar(20) primary key,
readername varchar(20) not null,
readersex varchar(20) not null,
readertype varchar(20) not null,
readerpd varchar(20) not null
)
create table book(
bookid varchar(20) primary key,
booktype varchar(20) not null,
bookauthor varchar(20) not null,
bookname varchar(20) not null,
isbrrowed varchar(20) not null,
booklibrary varchar(20) not null,
bookpubdate datetime
)
create table brrow_data(
readerid varchar(20) primary key,
bookid varchar(20) ,
bookname varchar(20),
brrowdate datetime
foreign key (readerid) references reader(readerid),
foreign key (bookid) references book(bookid)
)
create table return_record(
readerid varchar (20) primary key,
bookid varchar(20),
bookname varchar(20),
returndate datetime,
foreign key (readerid) references reader(readerid),
foreign key (bookid) references book(bookid)
)
create table readerfee(
readerid varchar(20) primary key,
bookid varchar(20),
bookname varchar(20),
readerpay varchar(20),
foreign key (readerid) references reader(readerid),
foreign key (bookid) references book(bookid)
)
--已有图书插如表中
insert into book (bookid,bookname,booktype,bookauthor,booklibrary,bookpubdate,isbrrowed) values ('3140921000','缥缈之旅','修真','小猪','仙王出版社','2005-09-01','否');
insert into book (bookid,bookname,booktype,bookauthor,booklibrary,bookpubdate,isbrrowed) values ('3140921001','唐朝好男人','穿越','多一半','新星出版社','2005-07-01','否');
insert into book (bookid,bookname,booktype,bookauthor,booklibrary,bookpubdate,isbrrowed) values ('3140921002','鬼吹灯','恐怖','天下霸唱','安徽文艺出版社','2005-09-01','否');
insert into book (bookid,bookname,booktype,bookauthor,booklibrary,bookpubdate,isbrrowed) values ('3140921003','和空姐同居的日子','都市','海王','中国海关出版社','2005-08-01','否');
insert into book (bookid,bookname,booktype,bookauthor,booklibrary,bookpubdate,isbrrowed) values ('3140921004','卡图','科幻','方向','广西人民出版社','2005-06-01','否');
insert into book (bookid,bookname,booktype,bookauthor,booklibrary,bookpubdate,isbrrowed) values ('3140921005','蜀山剑侠传','仙侠','还珠楼主','陕西人民出版社','2005-04-01','否');
insert into book (bookid,bookname,booktype,bookauthor,booklibrary,bookpubdate,isbrrowed) values ('3140921006','何以笙箫默','言情','顾漫','朝华出版社','2005-05-01','否');
insert into book (bookid,bookname,booktype,bookauthor,booklibrary,bookpubdate,isbrrowed) values ('3140921007','步步惊心','穿越','童话','民族出版社','2005-12-01','否');
--将读者插如表中
insert into reader(readerid,readername,readersex,readertype,readerpd) values ('21007000','李磊','男','学生','000000');
insert into reader(readerid,readername,readersex,readertype,readerpd) values ('21007001','棠张僧','男','学生','000000');
insert into reader(readerid,readername,readersex,readertype,readerpd) values ('21007002','韩梅梅','女','学生','000000');
insert into reader(readerid,readername,readersex,readertype,readerpd) values ('21007003','路西','女','学生','000000');
insert into reader(readerid,readername,readersex,readertype,readerpd) values ('21007004','王强','男','学生','000000');
insert into reader(readerid,readername,readersex,readertype,readerpd) values ('21007005','马正标','男','管理','000000');
insert into reader(readerid,readername,readersex,readertype,readerpd) values ('21007006','卢海鹏','男','管理','000000');
insert into reader(readerid,readername,readersex,readertype,readerpd) values ('21007007','李艳玲','女','管理','000000');
--查询功能
select *from reader;
select *from book;
select *from brrow_data;
select *from return_record;
select *from readerfee;
--删除表
drop table reader;
drop table book;
drop table brrow_data;
drop table return_record;
drop table readerfee;

登录界面的编辑 



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.Data.SqlClient;

namespace Library
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void panel1_Paint(object sender, PaintEventArgs e)
        {

        }
        //登录按钮
        private void button1_Click(object sender, EventArgs e)
        {
            Form2 form2 = new Form2();
            this.Hide();
            form2.Show();
            mycon.Close();
            //sql = string.Format("select count(*) from reader where readerid='{0}'",textBox1.Text);
            //SqlCommand com = new SqlCommand(sql,mycon);
            //int count = (int)com.ExecuteScalar();
            //if (count==1)
            //{
            //    sql = string.Format("select count(*) from reader where readerpd='{0}'",textBox2.Text);
            //    com = new SqlCommand(sql, mycon);
            //    count = (int)com.ExecuteScalar();
            //    if (count>=1)
            //    {
            //        if (comboBox1.Text=="学生")
            //        {
            //            Form2 form2 = new Form2();
            //            this.Hide();
            //            form2.Show();
                          form2.readerid = textBox1.Text;
            //            mycon.Close();
            //        }
            //        else if (comboBox1.Text=="管理员")
            //        {
            //            Form4 form4 = new Form4();
            //            this.Hide();
            //            form4.Show();
            //            mycon.Close();
            //        }
            //        else
            //        {
            //            MessageBox.Show("请选择您的身份！");
            //        }
            //    }
            //    else
            //    {
            //        MessageBox.Show("密码错误，请重新输入！");
            //    }
            //}
            //else{
            //    MessageBox.Show("账号不存在，请重新输入！");
            //}
        }
        string sql, con;
        SqlConnection mycon;
        //加载事件
        private void Form1_Load(object sender, EventArgs e)
        {
            con = "Server=.;Database=MyLibrary;Trusted_Connection=SSPI";
            mycon = new SqlConnection(con);
            mycon.Open();
        }
        //注册
        private void button2_Click(object sender, EventArgs e)
        {
            Form3 form3 = new Form3();
            this.Hide();
            form3.Show();
        }
    }
}


注册界面的编辑 



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.Data.SqlClient;

namespace Library
{
    public partial class Form3 : Form
    {
        public Form3()
        {
            InitializeComponent();
        }

        private void label3_Click(object sender, EventArgs e)
        {

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
        //提交申请
        private void button1_Click(object sender, EventArgs e)
        {
            sql = string.Format("select count(*) from reader where readerpd='{0}'", textBox2.Text);
            SqlCommand com = new SqlCommand(sql, mycon);
            int count = (int)com.ExecuteScalar();
            if (count==1)
            {
                MessageBox.Show("抱歉，该账号已存在！");
            }
            else 
            {
                if (textBox1.Text==""||textBox2.Text==""||textBox4.Text==""||comboBox1.Text==""||textBox3.Text=="")
                {
                    MessageBox.Show("请将信息填写完整！");
                }
                else
                {
                    Form1 form1 = new Form1();
                    form1.Show();
                    this.Hide();
                }
            }
        }
        string sql, con;
        SqlConnection mycon;

        private void Form3_Load(object sender, EventArgs e)
        {
            con = "Server=.;Database=MyLibrary;Trusted_Connection=SSPI";
            mycon = new SqlConnection(con);
            mycon.Open();
        }
    }
}


学生管理界面的编辑 



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.Data.SqlClient;

namespace Library
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        public string readerid;
        string sql, con;
        SqlConnection mycon;
        SqlCommand com;
        //加载事件
        private void Form2_Load(object sender, EventArgs e)
        {
            label1.Hide(); textBox1.Hide(); button4.Hide();
            dataGridView1.Hide();
            con = "Server=.;Database=MyLibrary;Trusted_Connection=SSPI";
            mycon = new SqlConnection(con);
            mycon.Open();
        }
        //个人信息
        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.Show(); label1.Hide(); textBox1.Hide(); button4.Hide();
            sql = string.Format("select * from reader where readerid='{0}'", readerid);
            SqlDataAdapter mydata = new SqlDataAdapter(sql, con);
            DataSet myds = new DataSet();
            mydata.Fill(myds, "reader");
            dataGridView1.DataSource = myds.Tables["reader"];
        }
        //图书信息
        private void button2_Click(object sender, EventArgs e)
        {
            dataGridView1.Show(); label1.Hide(); textBox1.Hide(); button4.Hide();
            sql = "select * from book";
            SqlDataAdapter mydata = new SqlDataAdapter(sql, con);
            DataSet myds = new DataSet();
            mydata.Fill(myds, "book");
            dataGridView1.DataSource = myds.Tables["book"];
        }
        //缴费系统
        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                sql = string.Format("insert into readerfee(readerid,bookid,bookname,readerpay)select readerid ,bookid , bookname,0.03*(datediff(day,convert(smalldatetime,brrowdate),getdate())-30) from brrow_data where datediff(day,convert(smalldatetime,brrowdate),getdate())>=0;");
                com = new SqlCommand();
                com.Connection = mycon;
                com.CommandType = CommandType.Text;
                com.CommandText = sql;
                SqlDataReader dr = com.ExecuteReader();
                dr.Close();
            }
            catch { }
            sql = string.Format("select * from readerfee where readerid='{0}'", readerid);
            SqlDataAdapter mydata = new SqlDataAdapter(sql, con);
            DataSet myds = new DataSet();
            mydata.Fill(myds, "readerfee");
            dataGridView1.DataSource = myds.Tables["readerfee"];
        }
        //借还记录
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            dataGridView1.Show(); label1.Hide(); textBox1.Hide(); button4.Hide();
            if (comboBox1.Text == "借书记录")
            {
                sql = string.Format("select * from brrow_data where readerid='{0}'", readerid);
                SqlDataAdapter mydata = new SqlDataAdapter(sql, con);
                DataSet myds = new DataSet();
                mydata.Fill(myds, "reader");
                dataGridView1.DataSource = myds.Tables["reader"];
            }
            else
            {
                sql = string.Format("select * from return_record where readerid='{0}'", readerid);
                SqlDataAdapter mydata = new SqlDataAdapter(sql, con);
                DataSet myds = new DataSet();
                mydata.Fill(myds, "reader");
                dataGridView1.DataSource = myds.Tables["reader"];
            }
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            dataGridView1.Hide();
            if (comboBox2.Text == "我要借书")
            {
                button4.Show();
                button4.Text = "借阅本书";
                label1.Text = "搜索书名";
                label1.Show();
                textBox1.Show();
            }
            else if (comboBox2.Text == "我要还书")
            {
                button4.Show();
                button4.Text = "归还本书";
                label1.Text = "所借书号";
                label1.Show();
                textBox1.Show();
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (button4.Text == "借阅本书")
            {
                sql = string.Format("select count(*) from book where bookname='{0}'", textBox1.Text);
                com = new SqlCommand(sql, mycon);
                int count = (int)com.ExecuteScalar();
                if (count >= 1)
                {
                    sql = string.Format("select bookid from book where bookname='{0}'", textBox1.Text);
                    com = new SqlCommand(sql, mycon);
                    string bookid = (string)com.ExecuteScalar();
                    sql = string.Format("insert into brrow_data (readerid,bookid,bookname,brrowdate) values('{0}','{1}','{2}','{3}')", readerid, bookid, textBox1.Text, DateTime.Now);
                    com = new SqlCommand();
                    com.Connection = mycon;
                    com.CommandType = CommandType.Text;
                    com.CommandText = sql;
                    SqlDataReader dr = com.ExecuteReader();
                    dr.Close();
                    MessageBox.Show("借书成功！");
                }
                else
                    MessageBox.Show("抱歉，不存在本书！");
            }
            else if (button4.Text == "归还本书")
            {
                sql = string.Format("select count(*) from brrow_data where bookid='{0}'", textBox1.Text);
                com = new SqlCommand(sql, mycon);
                int count = (int)com.ExecuteScalar();
                if (count >= 1)
                {
                    sql = string.Format("select bookname from brrow_data where bookid='{0}'", textBox1.Text);
                    com = new SqlCommand(sql, mycon);
                    string bookname = (string)com.ExecuteScalar();
                    sql = string.Format("insert into return_record(readerid,bookid,bookname,returndate) values ('{0}','{1}','{2}','{3}')", readerid, textBox1.Text, bookname, DateTime.Now);
                    com = new SqlCommand();
                    com.Connection = mycon;
                    com.CommandType = CommandType.Text;
                    com.CommandText = sql;
                    SqlDataReader dr = com.ExecuteReader();
                    dr.Close();
                    sql = string.Format("delete  from brrow_data where bookid='{0}'", textBox1.Text);
                    com = new SqlCommand();
                    com.Connection = mycon;
                    com.CommandType = CommandType.Text;
                    com.CommandText = sql;
                    SqlDataReader dr1 = com.ExecuteReader();
                    dr1.Close();
                    MessageBox.Show(bookname + "还书成功！");
                }
                else
                {
                    MessageBox.Show("抱歉！您没有借这本书！");
                }
            }
        }
    }
}


六，心得总结 

通过对一系列功能的实现，了解到了如何如何建立数据库并进行增删改查等基本操作，如何实现数据库与winform应用程序之间的连接，如何通过C#语言来操控数据库。更重要的是意识到了三层架构的重要性，将会加强学习这一方面。

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.Scanner;
/*

需求：

图书管理系统

（1）实现图书的增删改查功能，并保存信息到数据库。

（2）退出系统，下次登录时，能够查看之前的图书信息。
分析：

（0）建库，建表

（1）创建图书实体类Book

（2）实现增删改查，通过SQL实现
*/

public class BookManager {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
    Scanner sc = new Scanner(System.in);
    while (true) {
        System.out.println("****************************");
        System.out.println("******  图书管理系统  ******");
        System.out.println("******  1.添加图书  ******");
        System.out.println("******  2.修改图书  ******");
        System.out.println("******  3.删除图书  ******");
        System.out.println("******  4.查询图书  ******");
        System.out.println("******  0.退出系统  ******");
        System.out.println("****************************");
        System.out.println("请输入操作序号：");
        int choose = sc.nextInt();
        switch (choose) {
            case 1:
                addBook();
                break;
            default:
                System.out.println("您的输入有误！");
                break;
        }
    }
}

//实现添加图书功能
private static void addBook() throws SQLException, ClassNotFoundException {
    Scanner sc = new Scanner(System.in);
    System.out.println("....添加图书....");//伪代码
    System.out.println("请输入图书的编号：");
    int bookId = sc.nextInt();
    System.out.println("请输入图书的名称：");
    String bookName = sc.next();
    System.out.println("请输入图书的价格：");
    float bookPrice = sc.nextFloat();
    System.out.println("请输入图书的作者：");
    String bookAuthor = sc.next();
    System.out.println("请输入图书的出版社：");
    String bookPublisher = sc.next();
    //构建Book对象
    Book book = new Book(bookId, bookName, bookPrice, bookAuthor, bookPublisher);
    //加载驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获取数据库连接
    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "root";
    Connection conn = DriverManager.getConnection(url, user, password);
    //获取预编执行语句对象
    String sql = "insert into work values(?,?,?,?,?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    //设置SQL参数
    pstmt.setInt(1, book.getBookid());
    pstmt.setString(2, book.getBookName());
    pstmt.setFloat(3, book.getBookPrice());
    pstmt.setString(4, book.getBookAuthor());
    pstmt.setString(5, book.getBookPublisher());
    //执行SQL
    int row = pstmt.executeUpdate();
    pstmt.close();
    conn.close();
    //提示
    System.out.println("图书添加成功！");
}

}