using System;

namespace Library_BS
{
	/// <summary>
	/// gongneng 的摘要說明。
	/// </summary>
	public class gongneng
	{
		//修改密碼
		public int modifypd(string userBarCode, string userPassword) 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "UPDATE [Users] SET [UserPassword]=@UserPassword WHERE ([Users].[UserBarCode] = @U" +
				"serBarCode)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_userBarCode = new System.Data.SqlClient.SqlParameter();
			dbParam_userBarCode.ParameterName = "@UserBarCode";
			dbParam_userBarCode.Value = userBarCode;
			dbParam_userBarCode.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userBarCode);
			System.Data.IDataParameter dbParam_userPassword = new System.Data.SqlClient.SqlParameter();
			dbParam_userPassword.ParameterName = "@UserPassword";
			dbParam_userPassword.Value = userPassword;
			dbParam_userPassword.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userPassword);
        
			int rowsAffected = 0;
			dbConnection.Open();
			try 
			{
				rowsAffected = (int)dbCommand.ExecuteNonQuery();
			}
			finally 
			{
				dbConnection.Close();
			}
        
			return rowsAffected;

		}
		//更新信息
		public int ModifyInformation(string userBarCode, string iDNo, string email, string question, string answer, string sex, string phoneNo, string postCode, string address, string birthDate) 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "UPDATE [Users] SET [Email]=@Email, [BirthDate]=@BirthDate, [PostCode]=@PostCode, " +
				"[Answer]=@Answer, [IDNo]=@IDNo, [PhoneNo]=@PhoneNo, [Address]=@Address, [Questio" +
				"n]=@Question, [Sex]=@Sex WHERE ([Users].[UserBarCode] = @UserBarCode)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_userBarCode = new System.Data.SqlClient.SqlParameter();
			dbParam_userBarCode.ParameterName = "@UserBarCode";
			dbParam_userBarCode.Value = userBarCode;
			dbParam_userBarCode.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userBarCode);
			System.Data.IDataParameter dbParam_iDNo = new System.Data.SqlClient.SqlParameter();
			dbParam_iDNo.ParameterName = "@IDNo";
			dbParam_iDNo.Value = iDNo;
			dbParam_iDNo.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_iDNo);
			System.Data.IDataParameter dbParam_email = new System.Data.SqlClient.SqlParameter();
			dbParam_email.ParameterName = "@Email";
			dbParam_email.Value = email;
			dbParam_email.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_email);
			System.Data.IDataParameter dbParam_question = new System.Data.SqlClient.SqlParameter();
			dbParam_question.ParameterName = "@Question";
			dbParam_question.Value = question;
			dbParam_question.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_question);
			System.Data.IDataParameter dbParam_answer = new System.Data.SqlClient.SqlParameter();
			dbParam_answer.ParameterName = "@Answer";
			dbParam_answer.Value = answer;
			dbParam_answer.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_answer);
			System.Data.IDataParameter dbParam_sex = new System.Data.SqlClient.SqlParameter();
			dbParam_sex.ParameterName = "@Sex";
			dbParam_sex.Value = sex;
			dbParam_sex.DbType = System.Data.DbType.StringFixedLength;
			dbCommand.Parameters.Add(dbParam_sex);
			System.Data.IDataParameter dbParam_phoneNo = new System.Data.SqlClient.SqlParameter();
			dbParam_phoneNo.ParameterName = "@PhoneNo";
			dbParam_phoneNo.Value = phoneNo;
			dbParam_phoneNo.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_phoneNo);
			System.Data.IDataParameter dbParam_postCode = new System.Data.SqlClient.SqlParameter();
			dbParam_postCode.ParameterName = "@PostCode";
			dbParam_postCode.Value = postCode;
			dbParam_postCode.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_postCode);
			System.Data.IDataParameter dbParam_address = new System.Data.SqlClient.SqlParameter();
			dbParam_address.ParameterName = "@Address";
			dbParam_address.Value = address;
			dbParam_address.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_address);
			System.Data.IDataParameter dbParam_birthDate = new System.Data.SqlClient.SqlParameter();
			dbParam_birthDate.ParameterName = "@BirthDate";
			dbParam_birthDate.Value = birthDate;
			dbParam_birthDate.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_birthDate);
        
			int rowsAffected = 0;
			dbConnection.Open();
			try 
			{
				rowsAffected = dbCommand.ExecuteNonQuery();
			}
			finally 
			{
				dbConnection.Close();
			}
        
			return rowsAffected;

		}
	}
}