borrowreturn.cs

Csharp SQL图书馆管理系统 Csharp SQL图书馆管理系统源码

			// BorrowReturn
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.BackColor = System.Drawing.Color.LightBlue;
			this.ClientSize = new System.Drawing.Size(488, 325);
			this.Controls.Add(this.groupBox1);
			this.Controls.Add(this.lblBookID);
			this.Controls.Add(this.lblReaderID);
			this.Controls.Add(this.textBookID);
			this.Controls.Add(this.textReaderID);
			this.Controls.Add(this.dg);
			this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;
			this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
			this.MaximizeBox = false;
			this.MinimizeBox = false;
			this.Name = "BorrowReturn";
			this.StartPosition = System.Windows.Forms.FormStartPosition.CenterParent;
			this.Text = "借还书窗体";
			this.Load += new System.EventHandler(this.Form1_Load);
			((System.ComponentModel.ISupportInitialize)(this.dg)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.objDataSetBorrowReturn)).EndInit();
			this.groupBox1.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// 应用程序的主入口点。
		/// </summary>
		[STAThread]
		
		public void ErrorHandle(System.Exception E)
		{
			MessageBox.Show(E.ToString());
		}	

		private void btnBorrow_Click(object sender, System.EventArgs e)
		{			
			DataRow row=this.objDataSetBorrowReturn.Tables["BorrowBook"].NewRow();
			row["ReaderID"]=this.textReaderID.Text;
			row["BookID"]=this.textBookID.Text;
			row["BorrowDate"]=System.DateTime.Today;
			row["ReturnDate"]=DateTime.Today.AddMonths(1);
			this.objDataSetBorrowReturn.Tables["BorrowBook"].Rows.Add(row);
			if(BorrowBook(this.textBookID.Text)&&BorrowReader(this.textReaderID.Text))
			{
				this.UpdateDataSet();						
				dg.Refresh();
				MessageBox.Show(this.textReaderID.Text+"借阅图书"+this.textBookID.Text+"成功");
			}						
		}
		private bool BorrowBook(string BookID)
		{
			if(BorrowBookNumber(this.textBookID.Text)>0)
			{
				SqlCommand borrowbook=new SqlCommand();
				borrowbook.Connection=conn;
				borrowbook.CommandType=CommandType.StoredProcedure;
				borrowbook.CommandText="dbo.SP_BorrowBook";
				SqlParameter parinput=borrowbook.Parameters.Add("@BookID",SqlDbType.Char);
				parinput.Direction=ParameterDirection.Input;
				parinput.Value=BookID;
				try
				{
					conn.Open();			
					borrowbook.ExecuteNonQuery();
					conn.Close();
					return true;
				}
				catch(System.Exception e)
				{
					this.ErrorHandle(e);
					conn.Close();
					return false;
				}
			}
			else
				return false;
		}
		private int BorrowBookNumber(string BookID)
		{
			SqlCommand borrowbook=new SqlCommand();
			borrowbook.Connection=conn;
			borrowbook.CommandType=CommandType.StoredProcedure;
			borrowbook.CommandText="dbo.SP_BookNumber";
			SqlParameter parinput=borrowbook.Parameters.Add("@BookID",SqlDbType.Char);
			parinput.Direction=ParameterDirection.Input;
			parinput.Value=BookID;
			SqlParameter paroutput=borrowbook.Parameters.Add("@BookNumber",SqlDbType.Int);
			paroutput.Direction=ParameterDirection.Output;
			try
			{
				conn.Open();			
				borrowbook.ExecuteNonQuery();
				conn.Close();
				return Convert.ToInt16(paroutput.Value);
			}
			catch(System.Exception e)
			{
				this.ErrorHandle(e);				
				conn.Close();
				return 0;
			}
		}
		private int ReaderBorrowedNumber(string ReaderID)
		{
			SqlCommand borrowbook=new SqlCommand();
			borrowbook.Connection=conn;
			borrowbook.CommandType=CommandType.StoredProcedure;
			borrowbook.CommandText="dbo.SP_ReaderBorrowedNumber";
			SqlParameter parinput=borrowbook.Parameters.Add("@ReaderID",SqlDbType.Char);
			parinput.Direction=ParameterDirection.Input;
			parinput.Value=ReaderID;
			SqlParameter paroutput=borrowbook.Parameters.Add("@BorrowedNumber",SqlDbType.Int);
			paroutput.Direction=ParameterDirection.Output;
			try
			{
				conn.Open();			
				borrowbook.ExecuteNonQuery();
				conn.Close();
				return Convert.ToInt16(paroutput.Value);
			}
			catch(System.Exception e)
			{
				this.ErrorHandle(e);				
				conn.Close();
				return 8;
			}
		}

		private bool BorrowReader(string ReaderID)
		{
			if(ReaderBorrowedNumber(ReaderID)<8)
			{
				SqlCommand borrowbook=new SqlCommand();
				borrowbook.Connection=conn;
				borrowbook.CommandType=CommandType.StoredProcedure;
				borrowbook.CommandText="dbo.SP_BorrowReader";
				SqlParameter parinput=borrowbook.Parameters.Add("@ReaderID",SqlDbType.Char);
				parinput.Direction=ParameterDirection.Input;
				parinput.Value=ReaderID;
				try
				{
					conn.Open();			
					borrowbook.ExecuteNonQuery();
					conn.Close();
					return true;
				}
				catch(System.Exception e)
				{
					this.ErrorHandle(e);					
					conn.Close();
					return false;
				}
			}
			else
				return false;
		}
		private bool ReturnBook(string BookID)
		{
			SqlCommand returnbook=new SqlCommand();
			returnbook.Connection=conn;
			returnbook.CommandType=CommandType.StoredProcedure;
			returnbook.CommandText="dbo.SP_ReturnBook";
			SqlParameter parinput=returnbook.Parameters.Add("@BookID",SqlDbType.Char);
			parinput.Direction=ParameterDirection.Input;
			parinput.Value=BookID;
			try
			{
				conn.Open();			
				returnbook.ExecuteNonQuery();
				conn.Close();
				return true;
			}
			catch(System.Exception e)
			{
				this.ErrorHandle(e);				
				conn.Close();
				return false;

			}
		}
		private bool ReturnReader(string ReaderID)
		{
			SqlCommand returnbook=new SqlCommand();
			returnbook.Connection=conn;
			returnbook.CommandType=CommandType.StoredProcedure;
			returnbook.CommandText="dbo.SP_ReturnReader";
			SqlParameter parinput=returnbook.Parameters.Add("@ReaderID",SqlDbType.Char);
			parinput.Direction=ParameterDirection.Input;
			parinput.Value=ReaderID;
			try
			{
				conn.Open();			
				returnbook.ExecuteNonQuery();
				conn.Close();
				return true;
			}
			catch(System.Exception e)
			{
				this.ErrorHandle(e);				
				conn.Close();
				return false;
			}
		}


		
		private void dg_CurrentCellChanged(object sender, System.EventArgs e)
		{
			this.textBookID.Text=dg.CurrentCell.ToString();
			this.textBookID.Refresh();
		}

		private void btnReturn_Click(object sender, System.EventArgs e)
		{
			
			DataRow[] rows=this.GetSpecialRecord(this.textReaderID.Text,this.textBookID.Text);
			
			foreach(DataRow drow in rows)
			{
				int dday=System.DateTime.Today.DayOfYear-((System.DateTime)drow["ReturnDate"]).DayOfYear;
				if(dday>0)
					if(MessageBox.Show(this.textReaderID.Text+"读者你的"+this.textBookID.Text+"图书已经过期"+Convert.ToString(dday)
						+"天,罚	款"+Convert.ToString(dday/10)+"元RMB","过期",MessageBoxButtons.OKCancel,MessageBoxIcon.Warning)==DialogResult.Cancel)
					{
						return;
					}

				
				drow.Delete();
			}			

			if(ReturnReader(this.textReaderID.Text)&&ReturnBook(this.textBookID.Text))
			{
				UpdateDataSet();	
				dg.Refresh();
				MessageBox.Show(this.textReaderID.Text+"归还图书"+this.textBookID.Text+"成功");
			}			

			
		}
		
		public DataRow[] GetSpecialRecord(string ReaderID,string BookID)
		{					
			
			try
			{
				DataRow[] rows =this.objDataSetBorrowReturn.Tables["BorrowBook"].Select("ReaderID= '"+ ReaderID+"'"+"and BookID='"+BookID+"'");				
				return rows;
			}
			catch(Exception ex)
			{
				Console.WriteLine("GetSpecialExpert failed in ExpertAccess!"+ex.StackTrace.ToString());
				return null;
			}
		}

		private void btnLoad_Click(object sender, System.EventArgs e)
		{
			string source="select ReaderID,BookID,BorrowDate,ReturnDate from BorrowBook where ReaderID="+this.textReaderID.Text;			
			da.SelectCommand.CommandType=CommandType.Text;
			da.SelectCommand.CommandText=source;
			da.SelectCommand.Connection=conn;		
			this.objDataSetBorrowReturn.Clear();
			da.Fill(this.objDataSetBorrowReturn,"BorrowBook");
			dg.DataSource=this.objDataSetBorrowReturn;			
			dg.DataMember="BorrowBook";		
			dg.Refresh();
			MessageBox.Show("查询读者"+this.textReaderID.Text+"记录"+"成功");
		}
		public void UpdateDataSet()
		{			
			DataSetBorrowReturn objDataSetTemp=new DataSetBorrowReturn();
			objDataSetTemp=(DataSetBorrowReturn)(this.objDataSetBorrowReturn.GetChanges());
			try
			{
				this.UpdateDataSource(objDataSetTemp);
				this.objDataSetBorrowReturn.Merge(objDataSetTemp);
				this.objDataSetBorrowReturn.AcceptChanges();
			}
			catch(System.Exception E)
			{
				this.ErrorHandle(E);
			}
		}
		
		public void UpdateDataSource(DataSetBorrowReturn  Changerows)
		{
			try
			{
				conn.Open();
				da.Update(Changerows);
			}
			catch(System.Exception E)
			{
				this.ErrorHandle(E);
			}
			finally
			{
				conn.Close();

			}	
		}

		private void Form1_Load(object sender, System.EventArgs e)
		{
			try
			{
				conn.Open();				
				da.Fill(this.objDataSetBorrowReturn,"BorrowBook");
			}
			catch(System.Exception ex)
			{
				this.ErrorHandle(ex);
				MessageBox.Show("数据集初始失败");
			}
			conn.Close();
		}
	}
}