libcl.cs

数据库文件先要导入

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace 图书管理系统
{
	/// <summary>
	/// LibClass 的摘要说明。
	/// </summary>
	public class LibClass
	{
		public LibClass()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		public SqlConnection getConnection()
		{
			SqlConnection conn=new SqlConnection();
			conn.ConnectionString="Integrated Security=SSPI;data source=(local); initial catalog=libdb;";
			return conn;
		}
		public bool login(string loginname,string password)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="SELECT MName,MPassword FROM manager WHERE (MName='"+loginname+"' and MPassword='"+password+"')";
			comm.Connection=conn;
			SqlDataReader rd=comm.ExecuteReader();
			try
			{
				if(rd.HasRows)
				{
					return true;
				}
				else
				{
					MessageBox.Show("输入的登陆名或密码不正确！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
					return false;
				}
			}
			finally
			{
				rd.Close();
				conn.Close();
			}
		
		}
		public void addreader(string rname,string rsex,string pw,string email,string bz)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="INSERT INTO readers(Rname,Rsex,RPassword,E_mail,BZ) VALUES('"+rname+"','"+rsex+"','"+pw+"','"+email+"','"+bz+"')";
			comm.Connection=conn;
			try
			{
				comm.ExecuteNonQuery();
				MessageBox.Show("添加成功！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			catch
			{
				MessageBox.Show("添加失败！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			finally
			{
				conn.Close();
			}
		}
		public string[] selectreader(string a,string b)
		{
			string[] str=new string[5];
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="SELECT RID,Rname,Rsex,E_mail,BZ FROM dbo.readers WHERE("+a+"='"+b+"')";
			comm.Connection=conn;
			SqlDataReader rd=comm.ExecuteReader();
			try
			{
				if(rd.Read())
				{
					for(int i=0;i<5;i++)
					{
						str[i]=rd.GetValue(i).ToString();;
					}
				}
				else
				{
					MessageBox.Show("未找到该读者！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
				}
			}
			finally
			{
				conn.Close();
				rd.Close();
			}
			return str;
		}

		public void updatereader(string rid,string rname,string rsex,string email,string bz)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="UPDATE readers SET Rname='"+rname+"',Rsex='"+rsex+"',E_mail='"+email+"',bz='"+bz+"' WHERE RID='"+rid+"'";
			comm.Connection=conn;
			try
			{				
                comm.ExecuteNonQuery();
				MessageBox.Show("修改成功！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
     		}
			catch
			{
				MessageBox.Show("修改失败！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			finally
			{
				conn.Close();
			}
		}

		public void deletereader(string rid)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="DELETE readers WHERE readers.RID='"+rid+"'";
			comm.Connection=conn;
			try
			{				
				comm.ExecuteNonQuery();
				MessageBox.Show("删除成功！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			catch
			{
				MessageBox.Show("删除失败！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			finally
			{
				conn.Close();
			}
		}

		public SqlDataAdapter setds1(string mark,string mak)
		{
			SqlConnection conn =getConnection();
			string s="SELECT r.RID as 读者编号,r.Rname as 读者姓名,r.Rsex as 读者性别,r.E_mail,r.Rcount as 借书数量,bi.ISBN,b.BName as 书籍名称,bi.BDate as 借书日期,bi.BLDate as 还书日期 from readers r inner join binfo bi on r.RID=bi.RID inner join books b on bi.ISBN=b.ISBN where("+mark+"='"+mak+"')";
			SqlDataAdapter da=new SqlDataAdapter(s,conn);
			return da;
		}
		public SqlDataAdapter setds2(string mark,string mark2,string mak,string mak2)
		{
			SqlConnection conn =getConnection();
			string s="SELECT r.RID as 读者编号,r.Rname as 读者姓名,r.Rsex as 读者性别,r.E_mail,r.Rcount as 借书数量,bi.ISBN,b.BName as 书籍名称,bi.BDate as 借书日期,bi.BLDate as 还书日期 from readers r inner join binfo bi on r.RID=bi.RID inner join books b on bi.ISBN=b.ISBN where("+mark+"='"+mak+"' and "+mark2+"='"+mak2+"')";
			SqlDataAdapter da=new SqlDataAdapter(s,conn);
			return da;
		}

		public void addbook(string isbn,string bname,string type,string author,string publisher,string count,string price,string bz)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="INSERT INTO books(ISBN,BName,type,BCount,Author,Publisher,Price,BZ) VALUES('"+isbn+"','"+bname+"','"+type+"','"+count+"','"+author+"','"+publisher+"','"+price+"','"+bz+"')";
			comm.Connection=conn;
			try
			{
				comm.ExecuteNonQuery();
				MessageBox.Show("添加成功！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			catch
			{
				MessageBox.Show("添加失败！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			finally
			{
				conn.Close();
			}
		}

		public string[] selectbook(string a,string b)
		{
			string[] str=new string[8];
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="SELECT ISBN,BName,type,BCount,Price,Author,Publisher,BZ FROM dbo.books WHERE("+a+"='"+b+"')";
			comm.Connection=conn;
			SqlDataReader rd=comm.ExecuteReader();
			try
			{
				if(rd.Read())
				{
					for(int i=0;i<8;i++)
					{
						str[i]=rd.GetValue(i).ToString();;
					}
				}
				else
				{
					MessageBox.Show("未找到此书！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
				}
			}
			finally
			{
				conn.Close();
				rd.Close();
			}
			return str;
		}

		public void updatebook(string isbn,string bname,string type,string bcount,string price,string author,string publisher,string bz)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="UPDATE books SET ISBN='"+isbn+"',BName='"+bname+"',Type='"+type+"',Bcount='"+bcount+"',Price='"+price+"',Author='"+author+"',Publisher='"+publisher+"',bz='"+bz+"' WHERE ISBN='"+isbn+"'";
			comm.Connection=conn;
			try
			{				
				comm.ExecuteNonQuery();
				MessageBox.Show("修改成功！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			catch
			{
				MessageBox.Show("修改失败！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			finally
			{
				conn.Close();
			}
		}

		public void deletebook(string isbn)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="DELETE books WHERE books.ISBN='"+isbn+"'";
			comm.Connection=conn;
			try
			{				
				comm.ExecuteNonQuery();
				MessageBox.Show("删除成功！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			catch
			{
				MessageBox.Show("删除失败！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			finally
			{
				conn.Close();
			}
		}

		public SqlDataAdapter setdsbook(string mark,string mak)
		{
			SqlConnection conn =getConnection();
			string s="SELECT ISBN,BName as 书籍名称,type as 类别,BCount as 数量,Price as 价格,Author as 作者,Publisher as 出版社,BZ as 备注 FROM dbo.books WHERE("+mark+"='"+mak+"')";
			SqlDataAdapter da=new SqlDataAdapter(s,conn);
			return da;
		}

		public string Bdate(string rid,string isbn)
		{
			string d="";
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="SELECT BDate FROM binfo where(RID='"+rid+"' and ISBN='"+isbn+"')";
			comm.Connection=conn;
			SqlDataReader rd=comm.ExecuteReader();
			try
			{
				if(rd.Read())
				{
					d=rd["BDate"].ToString();
				}
			}
			finally
			{
				rd.Close();
				conn.Close();
			}
			return d;
		}

		public void borrowbook(string isbn,string rid,DateTime bdate,DateTime bldate,string bz)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
            SqlCommand com =new SqlCommand();
			comm.CommandText="INSERT INTO binfo(ISBN,RID,BDate,Bldate,BZ) VALUES('"+isbn+"','"+rid+"','"+bdate+"','"+bldate+"','"+bz+"')";
			com.CommandText="UPDATE readers SET RCount=(Rcount+1) WHERE RID='"+rid+"'";
			comm.Connection=conn;
			com.Connection=conn;
			try
			{
				comm.ExecuteNonQuery();
				com.ExecuteNonQuery();
				MessageBox.Show("借书成功！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			catch
			{
				MessageBox.Show("借书失败！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			finally
			{
				conn.Close();
			}
		}
		
		public string Rcount(string rid)
		{
			string c="";
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="SELECT RCount FROM readers where(RID='"+rid+"')";
			comm.Connection=conn;
			SqlDataReader rd=comm.ExecuteReader();
			try
			{
				if(rd.Read())
				{
					c=rd["RCount"].ToString();
				}
			}
			finally
			{
				rd.Close();
				conn.Close();
			}
			return c;
		}

		public void returnbook(string isbn,string rid,DateTime rdate,DateTime rldate,string bz)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			SqlCommand com =new SqlCommand();
            SqlCommand co =new SqlCommand();
			co.CommandText="DELETE binfo WHERE(ISBN='"+isbn+"' and RID='"+rid+"')";
			comm.CommandText="INSERT INTO rinfo(ISBN,RID,RDate,Rldate,BZ) VALUES('"+isbn+"','"+rid+"','"+rdate+"','"+rldate+"','"+bz+"')";
			com.CommandText="UPDATE readers SET RCount=(Rcount-1) WHERE RID='"+rid+"'";
			comm.Connection=conn;
			com.Connection=conn;
			co.Connection=conn;
			try
			{
				co.ExecuteNonQuery();
				comm.ExecuteNonQuery();
				com.ExecuteNonQuery();
				MessageBox.Show("还书成功！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			catch
			{
				MessageBox.Show("还书失败！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			finally
			{
				conn.Close();
			}
		}

		public string Rdate(string rid,string isbn)
		{
			string d="";
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="SELECT BlDate FROM binfo where(RID='"+rid+"' and ISBN='"+isbn+"')";
			comm.Connection=conn;
			SqlDataReader rd=comm.ExecuteReader();
			try
			{
				if(rd.Read())
				{
					d=rd["BlDate"].ToString();
				}
			}
			finally
			{
				rd.Close();
				conn.Close();
			}
			return d;
		}

		public bool selectn(string n)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="SELECT MName FROM manager where(MName='"+n+"')";
			comm.Connection=conn;
			SqlDataReader rd=comm.ExecuteReader();
			try
			{
				if(rd.HasRows)
				{
					return true;
				}
				else
				{
					MessageBox.Show("输入的原登陆名不正确！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
					return false;
				}
			}
			finally
			{
				rd.Close();
				conn.Close();
			}
		}

		public bool selectp(string p)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="SELECT MPassword FROM manager where(MPassword='"+p+"')";
			comm.Connection=conn;
			SqlDataReader rd=comm.ExecuteReader();
			try
			{
				if(rd.HasRows)
				{
					return true;
				}
				else
				{
					MessageBox.Show("输入的原密码不正确！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
					return false;
				}
			}
			finally
			{
				rd.Close();
				conn.Close();
			}
		}

		public void updaten(string n,string n1)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="UPDATE manager SET MName='"+n+"' WHERE(MName='"+n1+"')";
			comm.Connection=conn;
			try
			{				
				comm.ExecuteNonQuery();
				MessageBox.Show("修改成功！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			catch
			{
				MessageBox.Show("修改失败！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			finally
			{
				conn.Close();
			}
		}

		public void updatepw(string p,string p1)
		{
			SqlConnection conn =getConnection();
			conn.Open();
			SqlCommand comm =new SqlCommand();
			comm.CommandText="UPDATE manager SET MPassword='"+p+"' WHERE(MPassword='"+p1+"')";
			comm.Connection=conn;
			try
			{				
				comm.ExecuteNonQuery();
				MessageBox.Show("修改成功！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			catch
			{
				MessageBox.Show("修改失败！","信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
			}
			finally
			{
				conn.Close();
			}
		}
	}
}