编程开发 购物 网址 游戏 小说 歌词 地图 快照 股票 美女 新闻 笑话 | 汉字 软件 日历 阅读 下载 图书馆 开发 租车 短信 China
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
图片批量下载器
↓批量下载图片,美女图库↓
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
移动开发 架构设计 编程语言 互联网 开发经验 Web前端 开发总结
开发杂谈 系统运维 研发管理 数据库 云 计 算 Java开发
VC(MFC) Delphi VB C++(C语言) C++ Builder 其它开发语言 云计算 Java开发 .Net开发 IOS开发 Android开发 PHP语言 JavaScript
ASP语言 HTML(CSS) HTML5 Apache MSSQL数据库 Oracle数据库 PowerBuilder Informatica 其它数据库 硬件及嵌入式开发 Linux开发资料
  编程开发知识库 -> 开发杂谈 -> 用C#实现一个简单的图书管理系统(课程设计) -> 正文阅读
 

[开发杂谈]用C#实现一个简单的图书管理系统(课程设计)[第1页]

用C#(sharp)实现一个简单的图书管理系统(课程设计)


学校的数据库课程结束了,要求完成一个课程设计,于是我用C#实现了一个简单的图书管理系统(ADO.NET)

一、 首先把数据库脚本贴出来(数据库名为library)



USE [library]
GO
/****** Object:  Table [dbo].[books]    Script Date: 06/12/2016 11:27:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[books](
	[bNum] [nvarchar](10) NOT NULL,
	[bName] [nvarchar](60) NOT NULL,
	[bAuthor] [nvarchar](60) NOT NULL,
	[bPubCom] [nvarchar](50) NOT NULL,
	[bPubDat] [nvarchar](20) NOT NULL,
	[ISBN] [nvarchar](50) NOT NULL,
	[bPrice] [nvarchar](10) NOT NULL,
	[bTag] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_books] PRIMARY KEY CLUSTERED 
(
	[bNum] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000001', N'数据库', N'123', N'123出版社', N'2012-09', N'978-1-23465-8', N'56', N'2')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000002', N'编程珠玑', N'Jon·Bentley', N'人民邮电出版社', N'2015-01', N'978-7-115-35761-8', N'39', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000003', N'C陷阱与缺陷', N'Andrew·Koeing', N'人民邮电出版社', N'2009-09', N'978-7-115-17179-5', N'30', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000004', N'C专家编程', N'Peter Van Der Linden', N'人民邮电出版社', N'2009-09', N'978-7-115-17108-1', N'45', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000005', N'Python网络数据采集', N'Ryan Mitchell', N'人民邮电出版社', N'2016-03', N'978-7-115-41629-2', N'59', N'4')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000006', N'Flask Web开发', N'Miguel Grinberg', N'人民邮电出版社', N'2015-01', N'978-7-115-37399-1', N'59', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000007', N'C和指针', N'Kenneth A·Reek', N'人民邮电出版社', N'2009-12', N'978-7-115-17201-3', N'65', N'4')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000008', N'汇编语言', N'王爽', N'清华大学出版社', N'2015-05', N'978-7-302-33314-2', N'36', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000009', N'Python Cookbook', N'David Beazley & Brian K.Jones', N'人民邮电出版社', N'2015-05', N'978-7-115-37959-7', N'108', N'4')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000010', N'Python基础教程', N'Magnue Lie Hetland', N'人民邮电出版社', N'2015-09', N'978-7-115-35352-8', N'79', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000011', N'程序员代码面试指南', N'左程云', N'电子工业出版社', N'2015-09', N'978-7-121-27011-6', N'79', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000012', N'CLR via C#', N'Jeffrey Richter', N'清华大学出版社', N'2015-05', N'978-7-302-38097-9', N'109', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000013', N'大国崛起', N'唐晋', N'人民出版社', N'2011-03', N'7-01-006006-1', N'56', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000014', N'自控力', N'凯利·麦格尼格尔', N'文化发展出版社', N'2012-08', N'978-7-5142-0503-9', N'39.8', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000015', N'麦肯锡思维', N'Rob Koplowitz', N'企业管理出版社', N'2012-08', N'978-7-5164-1050-9', N'39.8', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000016', N'哈佛谈判心理学', N'Rrica Ariel Fox', N'中国友谊出版公司', N'2014-11', N'978-7-5057-3422-7', N'49.8', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000017', N'领导力21法则', N'John C Maxwell', N'时代出版传媒股份有限公司', N'2016-01', N'978-7-5699-0647-9', N'45', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000018', N'斯坦福极简经济学', N'Timothy Taylor', N'湖南人民出版社', N'2015-02', N'978-7-5561-0739-1', N'35', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000019', N'拖延心理学', N'Jane B Birka & Lenora M Yuen', N'中国人民大学出版社', N'2009-12', N'978-7-300-11390-6', N'39.8', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000020', N'英语魔法师之语法俱乐部', N'旋元佑', N'九州出版社', N'2001-05', N'7-80114-627-1', N'35', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000021', N'赖世雄美语音标', N'赖世雄', N'外文出版社', N'2016-05', N'978-7-119-08680-4', N'20', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000022', N'人性的弱点', N'戴尔·卡耐基', N'人民日报出版社', N'2015-05', N'978-7-5115-3089-9', N'29.8', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000023', N'重新定义公司 谷歌是如何运营的', N'Eric Schmidt & Jonethan Rosenberg & Alan Eagle', N'中信出版集团', N'2015-09', N'978-7-5086-5359-4', N'49', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000024', N'从0到1 开启商业与未来的秘密', N'Peter Thiel', N'中信出版社', N'2015-01', N'978-7-5086-4971-9', N'45', N'5')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000025', N'代码大全2', N'Steven McConnell', N'电子工业出版社', N'2015-06', N'978-7-121-02298-2', N'128', N'0')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000026', N'胡适的北大哲学课 壹古代哲学', N'胡适', N'新世界出版社', N'2014-04', N'978-7-5104-4841-6', N'30', N'3')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000027', N'胡适的北大哲学课 贰中古哲学', N'胡适', N'新世界出版社', N'2014-04', N'978-7-5104-4841-6', N'30', N'3')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000028', N'胡适的北大哲学课 叁近世哲学', N'胡适', N'新世界出版社', N'2014-04', N'978-7-5104-4841-6', N'30', N'3')
INSERT [dbo].[books] ([bNum], [bName], [bAuthor], [bPubCom], [bPubDat], [ISBN], [bPrice], [bTag]) VALUES (N'000029', N'胡适的北大哲学课 肆世界哲学', N'胡适', N'新世界出版社', N'2014-04', N'978-7-5104-4841-6', N'30', N'3')
/****** Object:  Table [dbo].[admin]    Script Date: 06/12/2016 11:27:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[admin](
	[aId] [nvarchar](20) NOT NULL,
	[aPwd] [nvarchar](20) NOT NULL,
	[aName] [nvarchar](20) NOT NULL,
	[aGender] [nvarchar](20) NOT NULL,
	[aPhoNum] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_admin] PRIMARY KEY CLUSTERED 
(
	[aId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[admin] ([aId], [aPwd], [aName], [aGender], [aPhoNum]) VALUES (N'1', N'1', N'张三', N'男', N'12345678999')
INSERT [dbo].[admin] ([aId], [aPwd], [aName], [aGender], [aPhoNum]) VALUES (N'111111', N'111111', N'李四', N'女', N'12311112222')
INSERT [dbo].[admin] ([aId], [aPwd], [aName], [aGender], [aPhoNum]) VALUES (N'2', N'2', N'张三', N'男', N'12311111111')
INSERT [dbo].[admin] ([aId], [aPwd], [aName], [aGender], [aPhoNum]) VALUES (N'222222', N'222222', N'李四', N'女', N'12322221111')
/****** Object:  Table [dbo].[borrow]    Script Date: 06/12/2016 11:27:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[borrow](
	[ID] [nvarchar](50) NOT NULL,
	[uId] [nvarchar](20) NOT NULL,
	[bName] [nvarchar](60) NOT NULL,
	[bNum] [nvarchar](10) NOT NULL,
	[bPrice] [nvarchar](10) NOT NULL,
	[borrowDate] [varchar](20) NOT NULL,
	[returnDate] [varchar](20) NOT NULL,
	[Tag] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_borrow] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[borrow] ([ID], [uId], [bName], [bNum], [bPrice], [borrowDate], [returnDate], [Tag]) VALUES (N'000001', N'2', N'C专家编程', N'000004', N'45', N'2016/06/10', N'2016/09/08', N'0')
INSERT [dbo].[borrow] ([ID], [uId], [bName], [bNum], [bPrice], [borrowDate], [returnDate], [Tag]) VALUES (N'000003', N'2', N'数据库', N'000001', N'56', N'2016/06/10', N'2016/08/09', N'0')
INSERT [dbo].[borrow] ([ID], [uId], [bName], [bNum], [bPrice], [borrowDate], [returnDate], [Tag]) VALUES (N'000005', N'2', N'数据库', N'000001', N'56', N'2016/06/10', N'2016/08/09', N'0')
INSERT [dbo].[borrow] ([ID], [uId], [bName], [bNum], [bPrice], [borrowDate], [returnDate], [Tag]) VALUES (N'000006', N'2', N'数据库', N'000001', N'56', N'2016/06/10', N'2016/08/09', N'0')
INSERT [dbo].[borrow] ([ID], [uId], [bName], [bNum], [bPrice], [borrowDate], [returnDate], [Tag]) VALUES (N'000007', N'2', N'Python网络数据采集', N'000005', N'59', N'2016/06/10', N'2016/09/08', N'0')
INSERT [dbo].[borrow] ([ID], [uId], [bName], [bNum], [bPrice], [borrowDate], [returnDate], [Tag]) VALUES (N'000008', N'2', N'C和指针', N'000007', N'65', N'2016/06/10', N'2016/09/08', N'0')
INSERT [dbo].[borrow] ([ID], [uId], [bName], [bNum], [bPrice], [borrowDate], [returnDate], [Tag]) VALUES (N'000009', N'2', N'Python Cookbook', N'000009', N'108', N'2016/06/10', N'2016/08/09', N'0')
/****** Object:  Table [dbo].[users]    Script Date: 06/12/2016 11:27:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
	[uId] [nvarchar](20) NOT NULL,
	[uPwd] [nvarchar](20) NOT NULL,
	[uName] [nvarchar](20) NOT NULL,
	[uGender] [nvarchar](20) NOT NULL,
	[uPhoNum] [nvarchar](20) NOT NULL,
	[uBan] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
	[uId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[users] ([uId], [uPwd], [uName], [uGender], [uPhoNum], [uBan]) VALUES (N'1', N'1', N'张三', N'男', N'12345678999', N'0')
INSERT [dbo].[users] ([uId], [uPwd], [uName], [uGender], [uPhoNum], [uBan]) VALUES (N'111111', N'111111', N'李四', N'女', N'11122221111', N'0')
INSERT [dbo].[users] ([uId], [uPwd], [uName], [uGender], [uPhoNum], [uBan]) VALUES (N'123456', N'123456', N'李四', N'女', N'12312341234', N'1')
INSERT [dbo].[users] ([uId], [uPwd], [uName], [uGender], [uPhoNum], [uBan]) VALUES (N'2', N'2', N'王五', N'男', N'12312313123', N'0')
INSERT [dbo].[users] ([uId], [uPwd], [uName], [uGender], [uPhoNum], [uBan]) VALUES (N'333333', N'333333', N'张三', N'男', N'12311112222', N'0')
INSERT [dbo].[users] ([uId], [uPwd], [uName], [uGender], [uPhoNum], [uBan]) VALUES (N'a123456', N'a123456', N'王五', N'女', N'12312341234', N'0')
/****** Object:  StoredProcedure [dbo].[Date_Test]    Script Date: 06/12/2016 11:27:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[Date_Test]
as  
select uId into #A from borrow where convert(varchar(20),GETDATE(),111)>= returnDate group by uId
update borrow set Tag='1' where convert(varchar(20),GETDATE(),111)>= returnDate
update users set uBan='1' where uId in(select uId from #A)
drop table #A
GO
/****** Object:  View [dbo].[booksWithbookindex]    Script Date: 06/12/2016 11:27:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[booksWithbookindex]
as
select ROW_NUMBER()over(order by bNum ) as bookindex,* from books
GO

二、 定义一个MyDictionary类 和 定义一个SqlHelper类(封装复杂的SQL操作)


namespace 图书管理系统
{
    // 因为要频繁使用Dictionary<string, string> 
    // 所以用一个自定义类MyDictionary继承Dictionary<string, string> 
    public class MyDictionary : Dictionary<string, string>
    {
    }
}

namespace 图书管理系统
{
    public static class SqlHelper
    {
        // 获取数据库连接   返回连接对象
        private static SqlConnection GetConn()
        {
            return new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["library"].ConnectionString);
        }

        // 执行sql操作   返回是否存在用户
        public static bool IsUserExists(string uId)
        {
            bool isExists = false;

            using (SqlConnection conn = GetConn())
            {
                string sql = "select count(*) from users where uId =@uId";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@uId", uId));
                conn.Open();
                int obj = Convert.ToInt32(cmd.ExecuteScalar());// 返回受影响的行数
                if (obj > 0)
                {
                    isExists = true;
                }
            }
            return isExists;
        }

        // 执行sql操作   返回是否存在书名
        public static bool IsBookExistsWithName(string bName)
        {
            bool isExists = false;

            using (SqlConnection conn = GetConn())
            {
                string sql = "select count(*) from books where bName =@bName";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@bName", bName));
                conn.Open();
                int obj = Convert.ToInt32(cmd.ExecuteScalar());// 返回受影响的行数
                if (obj > 0)
                {
                    isExists = true;
                }
            }
            return isExists;
        }
        // 执行sql操作   返回是否存在此书籍序列号
        public static bool IsBookExistsWithNum(string bNum)
        {
            bool isExists = false;

            using (SqlConnection conn = GetConn())
            {
                string sql = "select count(*) from books where bNum =@bNum";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@bNum", bNum));
                conn.Open();
                int obj = Convert.ToInt32(cmd.ExecuteScalar());// 返回受影响的行数
                if (obj > 0)
                {
                    isExists = true;
                }
            }
            return isExists;
        }

        // 执行 ...   返回受影响行数
        private static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] ps)
        {
            int rows = -1;

            using (SqlConnection conn = GetConn())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = type; // 存储过程 type为StoredProcedure
                cmd.Parameters.AddRange(ps);
                conn.Open();
                rows = cmd.ExecuteNonQuery();
            }

            return rows;
        }
        public static int ExecuteNonQuery(string sql, MyDictionary  dic)
        {
            SqlParameter[] ps = new SqlParameter[dic.Count];
            int index = 0;
            foreach (var item in dic)
            {
                ps[index++] = new SqlParameter(item.Key, item.Value);
            }
            return ExecuteNonQuery(sql, CommandType.Text, ps);
        }
        public static int ExecuteNonQuery(string sql, CommandType type, MyDictionary dic)
        {
            SqlParameter[] ps = new SqlParameter[dic.Count];
            int index = 0;
            foreach (var item in dic)
            {
                ps[index++] = new SqlParameter(item.Key, item.Value);
            }
            return ExecuteNonQuery(sql, type, ps);
        }


        // 执行sql语句   返回首行首列
        public static object ExecuteScalar(string sql)
        {
            object obj = null;
            using (SqlConnection conn = GetConn())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                obj = cmd.ExecuteScalar();
            }
            return obj;
        }
        public static object ExecuteScalar(string sql, CommandType type, MyDictionary dic)
        {
            object obj = null;
            using (SqlConnection conn = GetConn())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = type;

                //构造参数
                SqlParameter[] ps = new SqlParameter[dic.Count];
                int index = 0;
                foreach (var item in dic)
                {
                    ps[index++] = new SqlParameter(item.Key, item.Value);
                }
                cmd.Parameters.AddRange(ps);
                //执行命令
                conn.Open();
                obj = cmd.ExecuteScalar();
            }
            return obj;
        }
        public static object ExecuteScalar(string sql, MyDictionary dic)
        {
            return ExecuteScalar(sql, CommandType.Text, dic);
        }

        // 执行查询  返回结果集DataTable  获取列表
        public static DataTable GetList(string sql, MyDictionary  dic)
        {
            // 构造数据表,用于存储查询的数据
            DataTable dt = new DataTable();
            // 创建连接对象
            using (SqlConnection conn = GetConn())
            {
                // 执行命令
                SqlCommand cmd = new SqlCommand(sql, conn);
                // 构造参数
                SqlParameter[] ps = new SqlParameter[dic.Count];
                int index = 0;
                foreach (var item in dic)
                {
                    ps[index++] = new SqlParameter(item.Key, item.Value);
                }
                cmd.Parameters.AddRange(ps);
                // 执行命令
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
            }
            return dt;
        }
    }
}

三、 各个窗体及实现

1.登陆界面




namespace 图书管理系统
{
    public partial class LoginForm : Form
    {
        public LoginForm()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
             // 获取账号
             string Id = txtId.Text.Trim();
             // 获取密码
             string Pwd = txtPwd.Text.Trim();

             if (Id == "" || Pwd == "")
             {
                 MessageBox.Show("请输入账号或密码");
             }
             else 
             {
                 string str = System.Configuration.ConfigurationManager.ConnectionStrings["library"].ConnectionString;
                 // 构造sql查询语句
                 string sql;
                 if (rBtn1.Checked == true)
                 {
                     sql = "select uName from users where uId='" + Id + "' and uPwd='" + Pwd + "'";
                 }
                 else 
                 {
                     sql = "select aName from admin where aId='" + Id + "' and aPwd='" + Pwd + "'";
                 }
                 // 构造连接对象
                 using (SqlConnection conn = new SqlConnection(str))       
                 {
                     SqlCommand cmd = new SqlCommand(sql, conn);
                     // 打开数据库连接
                     conn.Open();                                          
                     // 执行查询语句,返回结果集第一行第一列
                     string name = cmd.ExecuteScalar().ToString ();       
                     
                     if (name != "")
                     {
                         // 登录窗体隐藏
                         this.Hide();
                         // 创建主窗体
                         MainForm mainForm = new MainForm();
                         // 将账号传给主窗体MainForm
                         mainForm.Id = Id;
                         // 用主窗体MainForm下的_Tag标记登陆的是用户还是管理员
                         if (rBtn1.Checked == true)                   
                         {
                             mainForm._Tag = "user";
                             mainForm.Name1 = name;

                             sql = "select uBan from users where uId='" + Id + "' and uPwd='" + Pwd + "'";
                             cmd = new SqlCommand(sql, conn);
                             string b = cmd.ExecuteScalar().ToString();
                             mainForm.B = b;
                         }
                         else
                         {
                             mainForm._Tag = "admin";
                             mainForm.Name1 = name;
                         }
                         // 显示主窗体
                         mainForm.ShowDialog();                         
                     }
                     else 
                     {
                         MessageBox.Show("账号或密码错误!","登录失败");
                     }
                 }
             }
        }
    }
}

2.主界面



namespace 图书管理系统
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private string _id;// 账号
        public string Id
        {
            get { return _id; }
            set { _id = value; }
        }

        private string _name;
        public string Name1 // 姓名
        {
            get { return _name; }
            set { _name = value; }
        }

        // 权限标记 值为admin为管理员 ,值为user则为普通用户
        private string _tag;
        public string _Tag
        {
            get { return _tag; }
            set { _tag = value; }
        }

        // 标记账户是否被锁定  1为锁定 
        private string _b;  
        public string B
        {
            get { return _b; }
            set { _b = value; }
        }

        // 在加载窗体时设定操作权限
        private void MainForm_Load(object sender, EventArgs e)
        {
            if (_Tag == "user")
            {
                tSSL2.Text = "权限级别:普通用户";
                tSSL5.Text = "   " + Name1;
                // 权限为用户时 将部分功能关闭
                新用户注册.Enabled = false;
                锁定用户.Enabled = false;
                新书入库.Enabled = false;
                借书.Enabled = false;
                还书.Enabled = false;
                书籍注销.Enabled = false;
                用户信息查询.Enabled = false;
                添加用户.Enabled = false;
                添加书籍.Enabled = false;
                借书办理.Enabled = false;
                还书办理.Enabled = false;
                if (B == "1")
                {
                    toolStripStatusLabel3.Text = " 状态:被锁定";
                    MessageBox.Show("账户被锁定,请尽快联系管理员", "警告");
                }
            }
            else
            {
                tSSL2.Text = "权限级别:管理员";
                tSSL5.Text = "   " + Name1;
            } 
        }

        #region 关于、帮助
        private void 关于软件ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MessageBox.Show("请联系管理员", "关于");
        }

        private void toolStripButton6_Click(object sender, EventArgs e)
        {
            MessageBox.Show("请联系管理员", "注意");
        }
        #endregion

        // 时钟
        private void timer1_Tick(object sender, EventArgs e)
        {
            xxx.Text = DateTime.Now.ToString();
        }

        // 退出时关闭所有窗口(login)
        private void MainForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.Exit(); 
        }

        #region 修改密码
        private void APF()
        {
            AlterPwdForm altPF = new AlterPwdForm();
            altPF.Id = Id;
            altPF._Tag = _Tag;
            altPF.ShowDialog();
        }
        private void 修改密码_Click(object sender, EventArgs e)
        {
            APF();
        }
        private void toolStripButton8_Click(object sender, EventArgs e)
        {
            APF();
        }
        #endregion

        #region 修改个人信息
        private void UIF()
        {
            UpdateIndivForm uIF = new UpdateIndivForm();
            uIF.Id = Id;
            uIF._Tag = _Tag;
            uIF.ShowDialog();
        }
        private void 修改信息_Click(object sender, EventArgs e)
        {
            UIF();
        }
        private void toolStripButton7_Click(object sender, EventArgs e)
        {
            UIF();
        }
        #endregion

        #region 添加用户
        private void NewUser()
        {
            UpdateIndivForm uIF = new UpdateIndivForm();
            uIF.Id = Id;
            uIF._Tag = _Tag;
            uIF.P = "1";
            uIF.Text = "新用户注册";
            uIF.ShowDialog();
        }
        private void 添加用户_Click(object sender, EventArgs e)
        {
            NewUser();
        }
        private void 新用户注册_Click(object sender, EventArgs e)
        {
            NewUser();
        }
        #endregion

        #region 注销用户
        private void 注销用户_Click(object sender, EventArgs e)
        {
            UpdateIndivForm deleteForm = new UpdateIndivForm();
            deleteForm.Text = "注销用户";
            deleteForm.P = "2";
            deleteForm.ShowDialog();
        }
        #endregion

        #region 添加书籍
        private static void INB()
        {
            新书入库Form iNB = new 新书入库Form();
            iNB.ShowDialog();
        }
        private void 添加书籍_Click(object sender, EventArgs e)
        {
            INB();
        }
        private void 新书入库_Click(object sender, EventArgs e)
        {
            INB();
        }
        #endregion

        #region 书籍注销
        private void 书籍注销_Click(object sender, EventArgs e)
        {
            新书入库Form iNB = new 新书入库Form();
            iNB.P = "1";
            iNB.Text = "注销书籍";
            iNB.ShowDialog();
        }
        #endregion

        #region 书籍查询
        private void 图书查询_Click(object sender, EventArgs e)
        {
            FindBookForm fbf = new FindBookForm();
            fbf.ShowDialog();
        }

        private void 查找书籍_Click(object sender, EventArgs e)
        {
            FindBookForm fbf = new FindBookForm();
            fbf.Tag1 = this._Tag;
            fbf.ShowDialog();
        }
        #endregion

        #region 借书办理
        private void 借书办理_Click(object sender, EventArgs e)
        {
            BorrowBooks bb = new BorrowBooks();
            bb.ShowDialog();
        }
        private void 借书_Click(object sender, EventArgs e)
        {
            BorrowBooks bb = new BorrowBooks();
            bb.ShowDialog();
        }        
        #endregion

        #region 还书办理
        private void 还书办理_Click(object sender, EventArgs e)
        {
            ReturnBooks rb = new ReturnBooks();
            rb.ShowDialog();
        }
        private void 还书_Click(object sender, EventArgs e)
        {
            ReturnBooks rb = new ReturnBooks();
            rb.ShowDialog();
        }        
        #endregion

        #region 借书单查询
        private void 借书单查询_Click(object sender, EventArgs e)
        {
            MessageBox.Show("功能暂未开放");
        }
        private void 查询借书记录_Click(object sender, EventArgs e)
        {
            MessageBox.Show("功能暂未开放");
        }        
        #endregion


        private void 用户信息查询_Click(object sender, EventArgs e)
        {
            MessageBox.Show("该功能暂未开放");
        }
    }
}

3.新用户注册、修改个人信息、注销用户界面在同一个窗体(图片略)


namespace 图书管理系统
{
    public partial class UpdateIndivForm : Form
    {
        public UpdateIndivForm()
        {
            InitializeComponent();
        }
        private string _id;
        public string Id
        {
            get { return _id; }
            set { _id = value; }
        }
        // 权限标记 值admin为管理员 ,值为user则为普通用户
        private string _tag;
        public string _Tag
        {
            get { return _tag; }
            set { _tag = value; }
        }
        // P标记用来决定是否隐藏panel
        private string _p;
        public string P
        {
            get { return _p; }
            set { _p = value; }
        }

        // 加载个人信息
        private void UpdateIndivForm_Load(object sender, EventArgs e)
        {
            if (P == "1")       // p == "1"  为注册新用户功能
            {
                注册用户panel.Visible = true;
                注销用户panel.Visible = false;
            }
            else if (P == "2")  // p == "2"  为注销用户功能
            {
            }
            else                //          为修改个人信息
            {
                注册用户panel.Visible = false;
                注销用户panel.Visible = false ;
                string str = System.Configuration.ConfigurationManager.ConnectionStrings["library"].ConnectionString;
                string sql;
                if (_Tag == "user")
                {
                    sql = "select * from users where uId='" + Id + "'";
                }
                else
                {
                    sql = "select * from admin where aId='" + Id + "'";
                }
                using (SqlConnection conn = new SqlConnection(str))
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    conn.Open();
                    // ExecuteReader()返回一个Datareader对象,内容是与命令匹配的所有行
                    using (SqlDataReader r = cmd.ExecuteReader())
                    {
                        r.Read();
                        账号textBox.Text = r.GetString(0);
                        姓名textBox.Text = r.GetString(2);
                        电话号码textBox.Text = r.GetString(4);
                        if (_Tag == "user")
                        {
                            if (r.GetString(5) == "1")
                            {
                                label7.Text = "被锁定,请尽快联系管理员办理解锁";
                            }
                            else
                            {
                                label7.Text = "正常";
                            }
                        }
                        else
                        {
                            label7.Text = "正常";
                        }

                        if (r.GetString(3) == "男")
                        {
                            男radioButton.Checked = true;
                        }
                        else
                        {
                            女radioButton.Checked = true;
                        }
                    }
                    cmd.Dispose();
                }
                提交button.Visible = false;
            }
        }

        #region 提交修改个人信息  
        private void 姓名textBox_TextChanged(object sender, EventArgs e)
        {
            提交button.Visible = true;
        }
        private void 账号textBox_TextChanged(object sender, EventArgs e)
        {
            if (账号textBox.Text!= Id)
            {
                提交button.Visible = true;
            }
            else
            {
                提交button.Visible = false;
            }
        }
        private void 男radioButton_CheckedChanged(object sender, EventArgs e)
        {
            提交button.Visible = true;
        }
        private void 电话号码textBox_TextChanged(object sender, EventArgs e)
        {
            提交button.Visible = true;
        }

        private void 提交button_Click(object sender, EventArgs e)
        { 
            // 判断账号是否存在
            if (账号textBox.Text != Id)
            {
                if (SqlHelper.IsUserExists(uIdTb.Text.Trim()))
                {
                    MessageBox.Show("账号已存在", "操作失败");
                    return;
                }
            }

            // 完成用户的修改
            string uName = 姓名textBox.Text.Trim();
            string uId = 账号textBox.Text.Trim();
            string uPhoNum = 电话号码textBox.Text.Trim();
            string uGender;
            if (男radioButton.Checked == true)
            {
                uGender = "男";
            }
            else
            {
                uGender = "女";
            }
            // 构造sql语句
            string sql;
            if (_Tag == "user")
            {
                sql = "update users set uId =@uId,uName=@uName,uGender=@uGender,uPhoNum=@uPhoNum where uId=@ID";
            }
            else
            {
                sql = "update admin set aId =@uId,aName=@uName,aGender=@uGender,aPhoNum=@uPhoNum where aId=@ID";
            }
            MyDictionary dic = new MyDictionary();
            dic.Add("@uId", uId);
            dic.Add("@uName", uName);
            dic.Add("@uGender", uGender);
            dic.Add("@uPhoNum", uPhoNum);
            dic.Add("@ID", Id);
            // 执行并返回
            int i = SqlHelper.ExecuteNonQuery(sql, dic);

            if (i == 1)
            {
                MessageBox.Show("提交完成");
            }
            this.Close();

        }       
        #endregion


        private void 注册完成button_Click(object sender, EventArgs e)
        {
            string uGender = "";
            if (uIdTb.Text.Trim().Length < 6)
            {
                MessageBox.Show("账号长度应该大于6");
            }
            else if (uPwdTb.Text.Trim().Length < 6)
            {
                MessageBox.Show("密码长度应该大于6");
            }
            else if (uNameTb.Text.Trim().Length < 2 || uNameTb.Text.Trim().Length > 15)
            {
                MessageBox.Show("姓名长度应该大于1并且小于等于15");
            }
            else if (注册rBtn男.Checked == false && 注册rBtn女.Checked == false)
            {
                MessageBox.Show("请选择性别");
            }
            else if (Regex.IsMatch(uPhoNumTb.Text.Trim(), @"^1\d{10}$") != true && Regex.IsMatch(uPhoNumTb.Text.Trim(), @"^(\d{3,4}-)?\d{6,8}$") != true)
            {
                MessageBox.Show("请输入合法的手机号码或电话号码");
            }
            else
            {
                // 判断判断账号是否存在
                if (SqlHelper.IsUserExists(uIdTb.Text.Trim()))
                {
                    MessageBox.Show("账号已存在","操作失败");
                    return;
                }
                
                if(注册rBtn男.Checked == true )
                {
                    uGender ="男";
                }
                else 
                {
                    uGender ="女";
                }

                // 添加新用户
                string sql = "insert into users(uId,uPwd,uName,uGender,uPhoNum,uBan) values(@id,@pwd,@name,@gender,@phonum,'0')";
                MyDictionary  dic = new MyDictionary ();
                dic.Add("@id", uIdTb.Text.Trim());
                dic.Add("@pwd", uPwdTb.Text.Trim());
                dic.Add("@name", uNameTb.Text.Trim());
                dic.Add("@gender", uGender);
                dic.Add("@phonum", uPhoNumTb.Text.Trim());
                int i = SqlHelper.ExecuteNonQuery(sql, dic);
                if (i == 1)
                {
                    MessageBox.Show("成功注册新用户");
                    this.Close();
                }
                else
                {
                    MessageBox.Show("操作失败,请联系管理员");
                }
            }
        }

        private void 注销Btn_Click(object sender, EventArgs e)
        {
            // 判断账号是否存在
            if (!SqlHelper.IsUserExists(deletTB.Text.Trim()))
            {
                MessageBox.Show("账号不存在", "操作失败");
                return;
            }
            else
            {
                DialogResult dt = MessageBox.Show("确定要注销此用户吗", "提示", MessageBoxButtons.OKCancel);
                if (dt != DialogResult.OK)
                {
                    return;
                }
                else
                {
                    string sql = "delete from users where uId=@id";
                    MyDictionary dic = new MyDictionary();
                    dic.Add("@id", deletTB.Text.Trim());
                    int i = SqlHelper.ExecuteNonQuery(sql, dic);
                    if (i == 1)
                    {
                        MessageBox.Show("注销成功");
                    }
                }
                this.Close();
            }
        }
    }
}

4.修改密码界面(图片略)

namespace 图书管理系统
{
    public partial class AlterPwdForm : Form
    {
        public AlterPwdForm()
        {
            InitializeComponent();
        }
        private string _id;
        public string Id
        {
            get { return _id; }
            set { _id = value; }
        }
        // 权限标记 值admin为管理员 ,值为user则为普通用户
        private string _tag;
        public string _Tag
        {
            get { return _tag; }
            set { _tag = value; }
        }

        private void APbtn_Click(object sender, EventArgs e)
        {
            string sql;
            if (textBox1.Text.Trim() == "" || textBox2.Text.Trim() == "" || textBox3.Text.Trim() == "")
            {
                MessageBox.Show("请填写完整", "警告");
            }
            else if (textBox2.Text.Trim().Length < 6 || textBox3.Text.Trim().Length < 6)
            {
                MessageBox.Show("新密码长度必须大于六位", "提示");
            }
            else if (textBox2.Text.Trim() != textBox3.Text.Trim())
            {
                MessageBox.Show("两次输入的新密码不一致","警告");
            }
            else
            {
                
                if (_Tag == "user")
                {
                    sql = "update users set uPwd =@uPwd where uId=@uId and uPwd=@uPwdd";

                }
                else
                {
                    sql = "update admin set aPwd = @uPwd where aId=@uId and aPwd=@uPwdd";
                }
                MyDictionary dic1 = new MyDictionary();
                dic1.Add("@uPwd", textBox2.Text.Trim());
                dic1.Add("@uId", Id.ToString());
                dic1.Add("@uPwdd", textBox1.Text.Trim());
                int n = SqlHelper.ExecuteNonQuery(sql, dic1);
                if (n > 0)
                {
                    MessageBox.Show("修改成功", "OK");
                    this.Close();
                }
                else
                {
                    MessageBox.Show("密码错误", "警告");
                }
            }
        }
    }
}

5.借书办理窗口



namespace 图书管理系统
{
    public partial class BorrowBooks : Form
    {
        public BorrowBooks()
        {
            InitializeComponent();
        }

        private void 查书btn_Click(object sender, EventArgs e)
        {
            if (查寻书名tB.Text.Trim() == "")
            {
                MessageBox.Show("书名不能为空");
            }
            else 
            {
                string sql = "select bNum,bName,bAuthor,bPubCom,bPrice,bTag from booksWithbookindex where bName like '%'+ @bName +'%'";
                MyDictionary dic = new MyDictionary();
                dic.Add("@bName", 查寻书名tB.Text.Trim());
                DataTable dt = SqlHelper.GetList(sql, dic);
                dataGridView1.DataSource = dt;
            }
        }

        private void 提交借书_Click(object sender, EventArgs e)
        {
            // 是否存在该用户
            if (uIdTb.Text.Trim() == ""|| comboBox1.Text.ToString().Trim() == "")
            {
                MessageBox.Show("请填写用户名和借阅时间!", "警告");
                return;
            }
            else if (bNumTB.Text.Trim() == "" || bNameTB.Text.Trim() == "" || bPriceTB.Text.Trim() == "")
            {
                MessageBox.Show("请选择书籍!","警告");
            }
            else if (!SqlHelper.IsUserExists(uIdTb.Text.Trim()))
            {
                MessageBox.Show("不存在该用户!", "警告");
                return;
            }
            else
            {
                // 自动获得图书序列号
                string sql = " select COUNT(*)+1 from borrow";
                string ID = SqlHelper.ExecuteScalar(sql).ToString();
                StringBuilder sb = new StringBuilder(ID);
                while (sb.Length != 6)
                {
                    sb.Insert(0, "0");
                }
                ID = sb.ToString();

                // 查询书籍库存量
                sql = "select bTag from books where bNum=@bNum";
                MyDictionary dic1 = new MyDictionary();
                dic1.Add("@bNum", bNumTB.Text.Trim());
                string n = SqlHelper.ExecuteScalar(sql, dic1).ToString();
                if (n == "0")
                {
                    MessageBox.Show("该书籍库存量为零!","警告");
                    return ;
                }

                // 插入数据到借书表(borrow)
                sql = "insert into borrow values(@ID,@uId,@bName,@bNum,@bPrice,convert(varchar(20),GETDATE() ,111),convert(varchar(20),dateadd(day," + comboBox1.Text.ToString().Substring(0, 2) + ",CONVERT(varchar(20),GETDATE() ,111)),111),@Tag)";
                MyDictionary dic = new MyDictionary();
                dic.Add("@ID", ID);
                dic.Add("@uId", uIdTb.Text.Trim());
                dic.Add("@bName", bNameTB.Text.Trim());
                dic.Add("@bNum", bNumTB.Text.Trim());
                dic.Add("@bPrice", bPriceTB.Text.Trim());
                dic.Add("@Tag", "0");// Tag = 0 标记该书未超归还期限 
                int i = SqlHelper.ExecuteNonQuery(sql, dic);
                if (i > 0)
                {
                   n = (Convert.ToInt32(n) - 1).ToString(); // 库存量-1
                    sql = "update books set bTag=@bTag where bNum=@bNum";
                    MyDictionary dic2 = new MyDictionary();
                    dic2.Add("@bTag", n);
                    dic2.Add("@bNum", bNumTB.Text.Trim());
                    SqlHelper.ExecuteScalar(sql, dic2);
                    MessageBox.Show("提交成功");
                }
            }
        }
        // 表格单元格鼠标MouseUp事件
        private void dataGridView1_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            // 开关文本框的锁定,将表格内的数据显示到文本框内
            int a = dataGridView1.CurrentRow.Index;
            bNumTB.ReadOnly = false;
            bNameTB.ReadOnly = false;
            bPriceTB.ReadOnly = false;
            bNumTB.Text = dataGridView1.Rows[a].Cells["bNum"].Value.ToString();
            bNameTB.Text = dataGridView1.Rows[a].Cells["bbName"].Value.ToString();
            bPriceTB.Text = dataGridView1.Rows[a].Cells["bPrice"].Value.ToString();
            bNumTB.ReadOnly = true;
            bNameTB.ReadOnly = true;
            bPriceTB.ReadOnly = true;
        }
    }
}

6.还书办理窗口


namespace 图书管理系统
{
    public partial class ReturnBooks : Form
    {
        public ReturnBooks()
        {
            InitializeComponent();
        }

        private void Btn1_Click(object sender, EventArgs e)
        {
            if (Btn1.Text.Trim() == "")
            {
                MessageBox.Show("账号不能为空");
            }
            else
            {
                ShowBorrowList();
            }
        }

        private void ShowBorrowList()
        {
            string sql = "select ID,uId,bName,bNum,bPrice,borrowDate,returnDate,Tag from borrow where uId =@uId";
            MyDictionary dic = new MyDictionary();
            dic.Add("@uId", uIdTB.Text.Trim());
            DataTable dt = SqlHelper.GetList(sql, dic);
            dataGridView1.DataSource = dt;
        }

        private void dataGridView1_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            // 开关文本框的锁定,将表格内的数据显示到文本框内
            int a = dataGridView1.CurrentRow.Index;
            IDDTB.ReadOnly = false;
            bNameTB.ReadOnly = false;
            bPriceTB.ReadOnly = false;
            bNumTB.ReadOnly = false;
            uuIdTB.ReadOnly = false;
            TagTB.ReadOnly = false;
            borrowDateTB.ReadOnly = false;
            returnDateTB.ReadOnly = false;
            IDDTB.Text = dataGridView1.Rows[a].Cells["ID"].Value.ToString();
            bNameTB.Text = dataGridView1.Rows[a].Cells["bName"].Value.ToString();
            bPriceTB.Text = dataGridView1.Rows[a].Cells["bPrice"].Value.ToString();
            bNumTB.Text = dataGridView1.Rows[a].Cells["bNum"].Value.ToString();
            uuIdTB.Text = dataGridView1.Rows[a].Cells["uId"].Value.ToString();
            TagTB.Text = (dataGridView1.Rows[a].Cells["Tag"].Value.ToString() == "1") ? "是" : "否";
            borrowDateTB.Text = dataGridView1.Rows[a].Cells["borrowDate"].Value.ToString();
            returnDateTB.Text = dataGridView1.Rows[a].Cells["returnDate"].Value.ToString();
            IDDTB.ReadOnly = true;
            bNameTB.ReadOnly = true;
            bPriceTB.ReadOnly = true;
            bNumTB.ReadOnly = true;
            uuIdTB.ReadOnly = true;
            TagTB.ReadOnly = true;
            borrowDateTB.ReadOnly = true;
            returnDateTB.ReadOnly = true;
        }

        #region 处理逾期未还书的用户
        // 窗口加载时处理借书表内逾期的记录 
        private void ReturnBooks_Load(object sender, EventArgs e)
        {
            // 文本框锁定
            IDDTB.ReadOnly = true;
            bNameTB.ReadOnly = true;
            bPriceTB.ReadOnly = true;
            bNumTB.ReadOnly = true;
            uuIdTB.ReadOnly = true;
            TagTB.ReadOnly = true;
            borrowDateTB.ReadOnly = true;
            returnDateTB.ReadOnly = true;

            // Date_Test存储过程
            // 将逾期未还书的标记 (Tag 置为 1)
            // 并将其用户冻结 (uBan 置为 1)
            // create proc Date_Test
            // as 
            // select uId into #A from borrow where convert(varchar(20),GETDATE(),111)>= returnDate group by uId
            // update borrow set Tag='1' where convert(varchar(20),GETDATE(),111)>= returnDate
            // update users set uBan='1' where uId in(select uId from #A)
            // drop table #A
            string sql = "Date_Test";
            MyDictionary dic = new MyDictionary();
            SqlHelper.ExecuteNonQuery(sql, CommandType.StoredProcedure, dic);
        }
        #endregion

        private void button1_Click(object sender, EventArgs e)
        {
            string sql="select uBan from users where uId=@uID";
            MyDictionary dic3 = new MyDictionary();
            dic3.Add("@uID", uuIdTB.Text.Trim());
            if (SqlHelper.ExecuteScalar(sql, dic3).ToString() == "1")
            {
                MessageBox.Show("因逾期未还书账户被锁定,请及时解锁,并缴纳罚款");
                return;
            }
            else 
            {
                // 办理借书手续
                // 删除借书记录 
                sql = "delete from borrow where ID=@ID";
                MyDictionary dic4 = new MyDictionary();
                dic4.Add("@ID", IDDTB.Text.Trim());
                SqlHelper.ExecuteNonQuery(sql, dic4);

                // 得到书籍库存量 并+1
                sql = "select bTag from books where bNum=@bNum";
                MyDictionary dic5 = new MyDictionary();
                dic5.Add("@bNum", bNumTB.Text.Trim());
                int n = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, dic5)) + 1;

                sql = "update books set bTag=@bTag where bNum=@bNum";
                MyDictionary dic6 = new MyDictionary();
                dic6.Add("@bTag", n.ToString());
                dic6.Add("@bNum", bNumTB.Text.Trim());
                SqlHelper.ExecuteNonQuery(sql, dic6);

                MessageBox.Show("还书提交完成");

                // 刷新借书单
                ShowBorrowList();

            }
        }
    }
}

7.新书入库和注销书籍窗口


这个窗口用了panel实现了:新书入库、注销书籍两个功能(图片略)
namespace 图书管理系统
{
    public partial class 新书入库Form : Form
    {
        public 新书入库Form()
        {
            InitializeComponent();
        }

        private string _p;  // p=="1"时为书籍注销操作
        public string P
        {
            get { return _p; }
            set { _p = value; }
        }

        private void 新书入库Form_Load(object sender, EventArgs e)
        {
            if (P != "1")
            {
                书籍注销panel.Visible = false;
            }
        }

        private void 新书入库Btn_Click(object sender, EventArgs e)
        {
            if (bNameTB.Text.Trim() == "" || bAuthorTB.Text.Trim() == "" || bPubComTB.Text.Trim() == "" || ISBN.Text.Trim() == "" || bTagTB.Text.Trim() == "" || bPriceTB.Text.Trim() == "" || bPubDatTB.Text.Trim() == "")
            {
                MessageBox.Show("请填写完整信息", "失败");
            }
            else
            {
                // 自动获得图书序列号
                string sql = " select COUNT(*)+1 from books";
                string bNum = SqlHelper.ExecuteScalar(sql).ToString();
                StringBuilder sb = new StringBuilder(bNum);
                while (sb.Length != 6)
                {
                    sb.Insert(0, "0");
                }
                bNum = sb.ToString();
                
                // 添加操作
                sql = "insert into books values(@bNum,@bName,@bAuthor,@bPubCom,@bPubDat,@ISBN,@bPrice,@bTag)";
                MyDictionary dic = new MyDictionary();
                dic.Add("@bNum", bNum);
                dic.Add("@bName", bNameTB.Text.Trim());
                dic.Add("@bAuthor", bAuthorTB.Text.Trim());
                dic.Add("@bPubCom", bPubComTB.Text.Trim());
                dic.Add("@bPubDat", bPubDatTB.Text.Trim());
                dic.Add("@ISBN", ISBN.Text.Trim());
                dic.Add("@bPrice", bPriceTB.Text.Trim());
                dic.Add("@bTag", bTagTB.Text.Trim());
                int i = SqlHelper.ExecuteNonQuery(sql, dic);
                if (i == 1)
                {
                    MessageBox.Show("添加成功,图书序列号为" + bNum);
                }
            }
        }

        private void 注销书籍Btn_Click(object sender, EventArgs e)
        {
            // 判断账号是否存在
            if (!SqlHelper.IsBookExistsWithNum(注销书籍TB.Text.Trim()))
            {
                MessageBox.Show("书籍不存在", "操作失败");
                return;
            }
            else
            {
                DialogResult dt = MessageBox.Show("确定要注销此书吗", "提示", MessageBoxButtons.OKCancel);
                if (dt != DialogResult.OK)
                {
                    return;
                }
                else
                {
                    string sql = "delete from books where bNum=@bNum";
                    MyDictionary dic = new MyDictionary();
                    dic.Add("@bNum", 注销书籍TB.Text.Trim());
                    int i = SqlHelper.ExecuteNonQuery(sql, dic);
                    if (i == 1)
                    {
                        MessageBox.Show("注销成功");
                    }
                }
                this.Close();
            }
        }
    }
}

8.查询书籍窗口



namespace 图书管理系统
{
    public partial class FindBookForm : Form
    {
        public FindBookForm()
        {
            InitializeComponent();
        }
        private string _tag;
        public string Tag1
        {
            get { return _tag; }
            set { _tag = value; }
        }

        // pageIndex 标记DGV应该显示第几页
        private int pageIndex;
        // pageIndex 标记DGV的总页数
        int maxIndex;

        #region 添加书籍
        private static void INB()
        {
            新书入库Form iNB = new 新书入库Form();
            iNB.ShowDialog();
        }
        private void 新书入库ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            INB();
        }
        private void 书籍入库tSBtn_Click(object sender, EventArgs e)
        {
            INB();
        }

        #endregion

        #region 注销书籍
        private void 注销书籍ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            新书入库Form iNB = new 新书入库Form();
            iNB.P = "1";
            iNB.Text = "注销书籍";
            iNB.ShowDialog();
        }
        #endregion

        #region 查看帮助
        private void 查看帮助ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MessageBox.Show("请联系管理员", "帮助");
        }
        private void 查看帮助tSBtn_Click(object sender, EventArgs e)
        {
            MessageBox.Show("请联系管理员", "帮助");
        }

        #endregion

        private void FindBookForm_Load(object sender, EventArgs e)
        {
            if (Tag1 == "user")
            {
                注销书籍ToolStripMenuItem.Enabled = false;
                修改ToolStripMenuItem.Enabled = false;
                新书入库ToolStripMenuItem.Enabled = false;
                书籍入库tSBtn.Enabled = false;
                保存修改tSBtn.Enabled = false;
                dgv_list.ReadOnly = true;
            }
            pageIndex = 1;
            GetData();
        }

        private void GetData()
        {
            // 获取页大小
            int pageSize = 14;
            // 计算最多有多少页--Math.Ceiling--向上取整
            // Convert.ToInt32(SqlHelper.ExecuteScalar("select count(*) from books"))得到数据记录总数
            maxIndex = (int)Math.Ceiling((Convert.ToInt32(SqlHelper.ExecuteScalar("select count(*) from books")) * 1.0 / pageSize));
            // 获取连接字符串
            string str = System.Configuration.ConfigurationManager.ConnectionStrings["library"].ConnectionString;
            // 得到连接
            using (SqlConnection conn = new SqlConnection(str))
            {
                // 判断是否有数据
                // Convert.ToInt32(SqlHelper.ExecuteScalar("select count(*) from books"))得到数据记录总数
                if (Convert .ToInt32 (SqlHelper.ExecuteScalar("select count(*) from books")) > 0 && pageIndex > 0)
                {
                    // 确定有数据才进行查询
                    // 创建表格,用来存储数据
                    DataTable dt = new DataTable();
                    // 构造带分页功能的 sql 语句
                    string sql = "select bNum,bName,bAuthor,bPubCom,bPubDat,ISBN,bPrice,bTag from booksWithbookindex where bookindex between @sIndex and @eIndex";
                    // 构造适配器对象
                    SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
                    // 传递参数
                    // 本页第一条数据
                    adapter.SelectCommand.Parameters.AddWithValue("@sIndex", ((pageIndex - 1) * pageSize + 1));
                    // 本页最后一条数据
                    adapter.SelectCommand.Parameters.AddWithValue("@eIndex", pageIndex * pageSize);
                    // 执行查询
                    conn.Open();
                    adapter.Fill(dt);
                    dgv_list.DataSource = dt;
                }
                else if (pageIndex <= 0)
                {
                    pageIndex = 1;
                }
                else
                {
                    MessageBox.Show("没有获取到数据","警告");
                }
            }
        }

        private void 刷新书库tSBtn_Click(object sender, EventArgs e)
        {
            if (pageIndex <= 0)
            {
                pageIndex = 1;
            }
            GetData();
        }
        private void 刷新ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (pageIndex <= 0)
            {
                pageIndex = 1;
            }
            GetData();
        }

        private void 跳转至首页tSBtn_Click(object sender, EventArgs e)
        {
            pageIndex = 1;
            GetData();
        }

        private void 跳转至上一页tSBtn_Click(object sender, EventArgs e)
        {
            pageIndex--;
            if (pageIndex <= 0)
            {
                pageIndex = 1;
            }
            GetData();
        }

        private void 跳转至下一页页tSBtn_Click(object sender, EventArgs e)
        {
            pageIndex++;
            if (pageIndex > maxIndex )
            {
                pageIndex--;
                MessageBox.Show("哎呀,到底啦 !","提示");
            }
            GetData();
        }

        private void 跳转至最后一页tSBtn_Click(object sender, EventArgs e)
        {
            pageIndex = maxIndex;
            GetData();
        }

        private void 保存修改tSBtn_Click(object sender, EventArgs e)
        {
            MessageBox.Show("功能暂未开放");
            /*
             * 
             * 
            // 若绑定数据源则用dgv_list.DataSource as DataTable;
            // 未绑定则用DgvToTable()强制转换
            // = DgvToTable(dgv_list);
            DataTable dt=dgv_list.DataSource as DataTable;
            // 完成adapter的UpdateCommand
            string str = System.Configuration.ConfigurationManager.ConnectionStrings["library"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(str))
            {

                SqlDataAdapter adapter = new SqlDataAdapter();

                // 构造UpdateCommand
                string update = "update books set bName=@bName, bAuthor=@bAuthor,bPubCom=@bPubCom,bPubDat=@bPubDat,ISBN=@ISBN,bPrice=@bPrice,bTag=@bTag where bNum=@bNum";
                SqlCommand updateCmd = new SqlCommand(update, conn);
                updateCmd.Parameters.Add("@bName", SqlDbType.NVarChar , 60, "bName");
                updateCmd.Parameters.Add("@bAuthor", SqlDbType.NVarChar, 60, "bAuthor");
                updateCmd.Parameters.Add("@bPubCom", SqlDbType.NVarChar, 50, "bPubCom");
                updateCmd.Parameters.Add("@bPubDat", SqlDbType.NVarChar, 20, "bPubDat");
                updateCmd.Parameters.Add("@ISBN", SqlDbType.NVarChar, 50, "ISBN");
                updateCmd.Parameters.Add("@bPrice", SqlDbType.NVarChar, 10, "bPrice");
                updateCmd.Parameters.Add("@bTag", SqlDbType.NVarChar, 10, "bTag");
                updateCmd.Parameters.Add("@bNum", SqlDbType.NVarChar, 10, "bNum");
                adapter.UpdateCommand = updateCmd;

                // 执行
                conn.Open();
                adapter.Update(dt);
            }

            // 刷新数据
            GetData();
            MessageBox.Show("保存成功");
            *
            *
            */
        }
        private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MessageBox.Show("功能暂未开放");
        }

        private void 按书名查找tSBtn_Click(object sender, EventArgs e)
        {
            string sql = "select bNum,bName,bAuthor,bPubCom,bPubDat,ISBN,bPrice,bTag from booksWithbookindex where bName like '%'+ @bName +'%'";
            MyDictionary dic = new MyDictionary();
            dic.Add("@bName", 书名tSTB.Text.Trim());
            DataTable dt = SqlHelper.GetList(sql, dic);
            dgv_list.DataSource = dt;
        }
    }
}

  开发杂谈 最新文章
centos6 更新glibc-2.14
流水账...12321984543
ros常用工具汇总
我想成为一个真的程序员
KITTI数据集测试
TensorFlow(安装和初使用)
集算报表实现动态显示列
高斯牛顿迭代法
【51nod】第K大区间2(二分+树状数组)
Android自定义View——圆形进度条式按钮
上一篇文章      下一篇文章      查看所有文章
加:2016-06-14 11:39:23  更:2016-06-14 11:41:04 
VC(MFC) Delphi VB C++(C语言) C++ Builder 其它开发语言 云计算 Java开发 .Net开发 IOS开发 Android开发 PHP语言 JavaScript
ASP语言 HTML(CSS) HTML5 Apache MSSQL数据库 Oracle数据库 PowerBuilder Informatica 其它数据库 硬件及嵌入式开发 Linux开发资料
360图书馆 软件开发资料 文字转语音 购物精选 软件下载 美食菜谱 新闻资讯 电影视频 小游戏 Chinese Culture 股票 租车
生肖星座 三丰软件 视频 开发 短信 中国文化 网文精选 搜图网 美图 阅读网 多播 租车 短信 看图 日历 万年历 2018年1日历
2018-1-23 23:58:06
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  编程开发知识库