using System;
using System.Data.OleDb;
using StudentsMIS.DataAccess;

namespace StudentsMIS.Business
{
	/// <summary>
	/// CollegeManager 實現(xiàn)了對College表的操作。
	/// </summary>
	public class CollegeManager
	{
		public CollegeManager()
		{
			
		}

    public int Insert(string collegeID,string collegeName)
    {
      OleDbCommand objCommand = CommandBuilder.BuildOleDbCommand("Select college_ID From colleges Where college_ID=@CollegeID");
      objCommand.Parameters.Add("@CollegeID",collegeID);
      if(objCommand.ExecuteReader().HasRows)
      {
        //銷毀
        objCommand.Connection.Close();
        objCommand.Connection.Dispose();
        objCommand.Dispose();
        return 0;
      }
      objCommand = CommandBuilder.BuildOleDbCommand("Select college_name From colleges Where college_name=@CollegeName");
      objCommand.Parameters.Add("@CollegeName",collegeName);
      if(objCommand.ExecuteReader().HasRows)
      {
        //銷毀
        objCommand.Connection.Close();
        objCommand.Connection.Dispose();
        objCommand.Dispose();
        return 1;
      }
      objCommand = CommandBuilder.BuildOleDbCommand("Insert Into colleges(college_ID,college_name) Values(@CollegeID,@CollegeName)");
      objCommand.Parameters.Add("@CollegeID",collegeID);
      objCommand.Parameters.Add("@CollegeName",collegeName);
      objCommand.ExecuteNonQuery();
      //銷毀
      objCommand.Connection.Close();
      objCommand.Connection.Dispose();
      objCommand.Dispose();
      return 2;
    }

    public void Delete(string collegeID)
    {
      OleDbCommand objCommand = CommandBuilder.BuildOleDbCommand("Delete From colleges Where college_ID=@CollegeID");
      objCommand.Parameters.Add("@CollegeID",collegeID);
      objCommand.ExecuteNonQuery();
      //銷毀
      objCommand.Connection.Close();
      objCommand.Dispose();
      objCommand.Dispose();
    }

    public int Update(string collegeID,string collegeName)
    {
      OleDbCommand objCommand = CommandBuilder.BuildOleDbCommand("Select college_name From colleges Where college_name=@CollegeName");
      objCommand.Parameters.Add("@CollegeName",collegeName);
      if(objCommand.ExecuteReader().HasRows)
      {
        //銷毀
        objCommand.Connection.Close();
        objCommand.Connection.Dispose();
        objCommand.Dispose();
        return 0;
      }
      objCommand = CommandBuilder.BuildOleDbCommand("Update colleges Set college_name=@CollageName Where college_ID=@CollegeID");
      //Access Bug. You must give the parameters value in order.
      objCommand.Parameters.Add("@CollageName",collegeName);
      objCommand.Parameters.Add("@CollegeID",collegeID); 
      objCommand.ExecuteNonQuery();
      //銷毀
      objCommand.Connection.Close();
      objCommand.Connection.Dispose();
      objCommand.Dispose();
      return 1;
    }
	}
}