borrowreturn.cs

visualc#+sql图书出租管理系统

			this.panel1.TabIndex = 11;
			// 
			// BorrowReturn
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.BackColor = System.Drawing.Color.LightGray;
			this.ClientSize = new System.Drawing.Size(488, 325);
			this.Controls.Add(this.panel1);
			this.Controls.Add(this.lblInfomation);
			this.Controls.Add(this.textInformation);
			this.Controls.Add(this.dataGrid1);
			this.MaximizeBox = false;
			this.MinimizeBox = false;
			this.Name = "BorrowReturn";
			this.Text = "客户租还书管理";
			this.Load += new System.EventHandler(this.Form1_Load);
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.objDataSetBorrowReturn)).EndInit();
			this.panel1.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

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

		private void btnExit_Click(object sender, System.EventArgs e)
		{
			
			this.Close();
		}

		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.textInformation.Text=this.textReaderID.Text+"租借图书"+this.textBookID.Text+"成功";
					this.UpdateDataSet();						
					this.dataGrid1.Refresh();
				}						
			
		}
		private bool BorrowBook(string BookID)
		{
			
			if(BorrowBookNumber(this.textBookID.Text)>0)
			{
				SqlCommand borrowbook=new SqlCommand();
				borrowbook.Connection=this.sqlConnection1;
				borrowbook.CommandType=CommandType.StoredProcedure;
				borrowbook.CommandText="dbo.StoredProcedureBorrowBook";
				SqlParameter parinput=borrowbook.Parameters.Add("@BookID",SqlDbType.Char);
				parinput.Direction=ParameterDirection.Input;
				parinput.Value=BookID;
				try
				{
					this.sqlConnection1.Open();			
					borrowbook.ExecuteNonQuery();
					this.sqlConnection1.Close();
					return true;
				}
				catch(System.Exception e)
				{
					this.ErrorHandle(e);
					this.sqlConnection1.Close();
					return false;

				}
			}
			else
				return false;

		}
		private int BorrowBookNumber(string BookID)
		{
			SqlCommand borrowbook=new SqlCommand();
			borrowbook.Connection=this.sqlConnection1;
			borrowbook.CommandType=CommandType.StoredProcedure;
			borrowbook.CommandText="dbo.StoredProcedureBookNumber";
			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
			{
				this.sqlConnection1.Open();			
				borrowbook.ExecuteNonQuery();
				this.sqlConnection1.Close();
				return Convert.ToInt32(paroutput.Value);
				
			}
			catch(System.Exception e)
			{
				this.ErrorHandle(e);				
				this.sqlConnection1.Close();
				return 0;

			}


		}
		private int ReaderBorrowedNumber(string ReaderID)
		{
			SqlCommand borrowbook=new SqlCommand();
			borrowbook.Connection=this.sqlConnection1;
			borrowbook.CommandType=CommandType.StoredProcedure;
			borrowbook.CommandText="dbo.StoredProcedureReaderBorrowedNumber";
			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
			{
				this.sqlConnection1.Open();			
				borrowbook.ExecuteNonQuery();
				this.sqlConnection1.Close();
				return Convert.ToInt32(paroutput.Value);
				
			}
			catch(System.Exception e)
			{
				this.ErrorHandle(e);				
				this.sqlConnection1.Close();
				return 8;

			}


		}

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

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

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

			}
		}


		private void dataGrid1_Navigate(object sender, System.Windows.Forms.NavigateEventArgs ne)
		{
			
		}

		private void dataGrid1_CurrentCellChanged(object sender, System.EventArgs e)
		{
			this.textBookID.Text=this.dataGrid1.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.textInformation.Text=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();	
				dataGrid1.Refresh();
				this.textInformation.Text=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;			
			this.sqlDataAdapter1.SelectCommand.CommandType=CommandType.Text;
			this.sqlDataAdapter1.SelectCommand.CommandText=source;
			this.sqlDataAdapter1.SelectCommand.Connection=this.sqlConnection1;		
			this.objDataSetBorrowReturn.Clear();
			this.sqlDataAdapter1.Fill(this.objDataSetBorrowReturn,"BorrowBook");
			dataGrid1.DataSource=this.objDataSetBorrowReturn;			
			this.dataGrid1.DataMember="BorrowBook";		
			dataGrid1.Refresh();
			this.textInformation.Text="查询客户"+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
			{
				this.sqlConnection1.Open();
				this.sqlDataAdapter1.Update(Changerows);
			}
			catch(System.Exception E)
			{
				this.ErrorHandle(E);
			}
			finally
			{
				this.sqlConnection1.Close();

			}	
		}

		private void Form1_Load(object sender, System.EventArgs e)
		{
					
			try
			{
				this.sqlConnection1.Open();				
				this.sqlDataAdapter1.Fill(this.objDataSetBorrowReturn,"BorrowBook");
				this.textInformation.Text="数据集初始成功";
			}
			catch(System.Exception ex)
			{
				this.ErrorHandle(ex);
				this.textInformation.Text="数据集初始失败";

			}
			this.sqlConnection1.Close();
			
		}

		
	}
}