goonborrowdb.cs

图书馆的书目查询

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

namespace Library_BS
{
	/// <summary>
	/// GoonBorrowDB 的摘要说明。
	/// </summary>
	public class GoonBorrowDB
	{
		public GoonBorrowDB()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		public int GetDateFlag(string readerID)//得到超期且未还数目
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("GetDateFlag", 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 parameterFlag = new SqlParameter("@flag", SqlDbType.Int, 4);
			parameterFlag.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterFlag);

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

			int number = (int)(parameterFlag.Value);
		
			return number;
		}

		public int 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.Int, 4);
			parameterSumFine.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterSumFine);

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

			int sum =(int)(parameterSumFine.Value);
		
			return sum;
		}
		public int GetGoonBorrowTimeByBook(int bookID)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("GetGoonBorrowTimeByBook", 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 parameterTimes = new SqlParameter("@time", SqlDbType.Int, 4);
			parameterTimes.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterTimes);

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

			int times = (int)(parameterTimes.Value);
		
			return times;
		}
		public string GetReaderIdByBook(int bookID)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("GetReaderIdByBook", 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 parameterReaderID = new SqlParameter("@readerID", SqlDbType.VarChar, 20);
			parameterReaderID.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterReaderID);

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

			string readerId = (parameterReaderID.Value).ToString();
		
			return readerId;
		}
		    
		public int GetMaxGoonBorrowDay(string readerID)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("GetMaxGoonBorrowDay", 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 parameterMaxNum = new SqlParameter("@DayNum", SqlDbType.Int, 4);
			parameterMaxNum.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterMaxNum);

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

			int Daynumber = (int)(parameterMaxNum.Value);
		
			return Daynumber;
		}
		    
		public int UpdateGoonBorrow(int 图书编号, string 状态, System.DateTime 应还时间, int 续借次数) 
		{
			string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "UPDATE [图书借阅] SET [应还时间]=@应还时间, [续借次数]=@续借次数 WHERE (([图书借阅].[图书编号] = @图书编号) AND (" +
				"[图书借阅].[状态] = @状态))";
			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.Int32;
			dbCommand.Parameters.Add(dbParam_图书编号);
			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.DateTime;
			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;
		}

	}
}