finedb.cs

图书馆的书目查询

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Configuration;


namespace Library_BS
{
	/// <summary>
	/// FineDB 的摘要说明。
	/// </summary>
	public class FineDB
	{
		public FineDB()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		public System.DateTime GetRuturnDate(int bookId)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("GetRuturnDate", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterbookID = new SqlParameter("@bookID", SqlDbType.Int, 4);
			parameterbookID.Value = bookId;
			myCommand.Parameters.Add(parameterbookID);

			SqlParameter parameterReturndate = new SqlParameter("@Returndate", SqlDbType.SmallDateTime);
			parameterReturndate.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterReturndate);

			// Open the connection and execute the Command
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			System.DateTime date = Convert.ToDateTime(parameterReturndate.Value);
		
			return date;
		}
		public void CalcFine(int bookId)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("CalcFine", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterbookID = new SqlParameter("@bookID", SqlDbType.Int, 4);
			parameterbookID.Value = bookId;
			myCommand.Parameters.Add(parameterbookID);

			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

		}
		public float GetFine(int bookId)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("GetFine", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterbookID = new SqlParameter("@bookID", SqlDbType.Int, 4);
			parameterbookID.Value = bookId;
			myCommand.Parameters.Add(parameterbookID);

			SqlParameter parameterFine = new SqlParameter("@Fine", SqlDbType.Float , 8);
			parameterFine.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterFine);

			// Open the connection and execute the Command
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			float fine =(float)(parameterFine.Value);
		
			return fine;

		}
		
		public float GetSumFine(string readerID)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("SumFine", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterReaderID = new SqlParameter("@readerId", SqlDbType.VarChar, 20);
			parameterReaderID.Value = readerID;
			myCommand.Parameters.Add(parameterReaderID);

			SqlParameter parameterSumFine = new SqlParameter("@sum", SqlDbType.Float , 8);
			parameterSumFine.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterSumFine);

			// Open the connection and execute the Command
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			float sum =(float)(parameterSumFine.Value);
		
			return sum;
		}
		    
		public System.Data.DataSet SelectCurrentReader(string 读者编号) 
		{
			string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
			System.Data.SqlClient.SqlConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);

			string queryString = "SELECT [图书编号], [读者编号], [应罚金额], [是否交款] FROM [图书罚款] WHERE [读者编号] ='"+ 读者编号+"'";
			System.Data.SqlClient.SqlCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter();
			dataAdapter.SelectCommand = dbCommand;
			System.Data.DataSet dataSet = new System.Data.DataSet();
			dataAdapter.Fill(dataSet,"OneFine");
        
			return dataSet;
		}// Insert page code here
		    
		public System.Data.DataSet SelectAllReaders() 
		{
			string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
			System.Data.SqlClient.SqlConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "SELECT [图书罚款].[图书编号], [图书罚款].[读者编号], [图书罚款].[应罚金额], [图书罚款].[是否交款] FROM [图书罚款]";
			System.Data.SqlClient.SqlCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter();
			dataAdapter.SelectCommand = dbCommand;
			System.Data.DataSet dataSet = new System.Data.DataSet();
			dataAdapter.Fill(dataSet,"AllFine");
        
			return dataSet;
		}
		    
		public int UpdateFineState(string 读者编号, int 是否交款) 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library_Dww\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "UPDATE [图书罚款] SET [是否交款]=@是否交款 WHERE ([图书罚款].[读者编号] = @读者编号)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_读者编号 = new System.Data.SqlClient.SqlParameter();
			dbParam_读者编号.ParameterName = "@读者编号";
			dbParam_读者编号.Value = 读者编号;
			dbParam_读者编号.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_读者编号);
			System.Data.IDataParameter dbParam_是否交款 = new System.Data.SqlClient.SqlParameter();
			dbParam_是否交款.ParameterName = "@是否交款";
			dbParam_是否交款.Value = 是否交款;
			dbParam_是否交款.DbType = System.Data.DbType.Int32;
			dbCommand.Parameters.Add(dbParam_是否交款);
        
			int rowsAffected = 0;
			dbConnection.Open();
			try 
			{
				rowsAffected = dbCommand.ExecuteNonQuery();
			}
			finally 
			{
				dbConnection.Close();
			}
        
			return rowsAffected;
		}

	}
}