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

using qminoa.Common.Data;

namespace qminoa.DA
{
	/// <summary>
	/// ModulesDB 的摘要說明。
	/// </summary>
	public class ModulesDB
	{
		//
		//創建聯接所需要的字符串
		//
		private string conStr ;
		//
		//連接對象
		//
		private SqlConnection con ;
		//
		//適配器對象，該對象對DataSet操作，將數據庫的信息填入dataSet
		//
		private SqlDataAdapter commandAdp ;
		public ModulesDB()
		{
			conStr = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
			con = new SqlConnection(conStr);
			commandAdp = new SqlDataAdapter();
		}
		/// <summary>
		/// 提供存儲過程的參數
		/// </summary>
		/// <param name="str"></param>
		/// <returns></returns>
		private string paramChg(string str)
		{
			str = "@"+str;
			return str;
		}
		public DataSet GetModulesData()
		{
			SqlCommand command = new SqlCommand();
			command.CommandText = "select * from sysTab order by tabid";
			command.Connection = con;

			commandAdp.SelectCommand = command;

			DataSet data = new DataSet();
			commandAdp.Fill(data);

			return data;

		}
		/// <summary>
		/// 刪除指定的部門
		/// </summary>
		/// <param name="depID"></param>
		public bool DeleteModule(int pkid)
		{
			SqlCommand command = new SqlCommand();

			command.CommandText = "DeleteModules";
			command.CommandType = CommandType.StoredProcedure;

			SqlParameter param = new SqlParameter(paramChg(ModuleData.PK_FIELD),SqlDbType.Int);
			param.Value = pkid;
			command.Parameters.Add(param);

			command.Connection = con;

			con.Open();
			int i = command.ExecuteNonQuery();
			con.Close();
			if(i==0)
			{
				return false;
			}
			else
			{
				return true;
			}
		}

		/// <summary>
		/// 更新指定的部門信息
		/// </summary>
		/// <param name="depData"></param>
		/// <returns></returns>
		public bool UpdateModule(ModuleData moduleData)
		{
			commandAdp.UpdateCommand = GetCommand();

			commandAdp.UpdateCommand.Parameters.Add(new SqlParameter(paramChg(ModuleData.PK_FIELD),SqlDbType.Int));
			commandAdp.UpdateCommand.Parameters[paramChg(ModuleData.PK_FIELD)].SourceColumn = ModuleData.PK_FIELD;

			commandAdp.UpdateCommand.CommandText = "UpdateModules";
			commandAdp.Update(moduleData,ModuleData.MODULE_TABLE_NAME);

			if(moduleData.HasErrors)
			{
				moduleData.Tables[ModuleData.MODULE_TABLE_NAME].GetErrors()[0].ClearErrors();
				return false;
			}
			else
			{
				moduleData.AcceptChanges();
				return true;
			}
		}

			 /// <summary>
			 /// 新建子模塊
			 /// </summary>
			 /// <param name="moduleData"></param>
			 /// <returns></returns>
			 public bool CreateModule(ModuleData moduleData)
			 {
				 commandAdp.InsertCommand = GetCommand();
				 commandAdp.InsertCommand.CommandText = "InsertModule";
	
				 commandAdp.Update(moduleData,ModuleData.MODULE_TABLE_NAME);

				 if(moduleData.HasErrors)
				 {
					 moduleData.Tables[ModuleData.MODULE_TABLE_NAME].GetErrors()[0].ClearErrors();
					 return false;
				 }
				 else
				 {
					 moduleData.AcceptChanges();
					 return true;
				 }
			 }

		/// <summary>
		/// 提供給適配器的命令
		/// </summary>
		/// <param name="depID"></param>
		/// <returns></returns>
		private SqlCommand GetCommand()
		{
			SqlCommand command = new SqlCommand();

			command.Connection = con;
			command.CommandType = CommandType.StoredProcedure;

			SqlParameterCollection param = command.Parameters;

			param.Add(new SqlParameter(paramChg(ModuleData.TABID_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(ModuleData.INDEXID_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(ModuleData.NAME_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(ModuleData.DESCRIPTION_FIELD),SqlDbType.VarChar));
			param.Add(new SqlParameter(paramChg(ModuleData.URL_FIELD),SqlDbType.VarChar));

			param[paramChg(ModuleData.TABID_FIELD)].SourceColumn = ModuleData.TABID_FIELD;
			param[paramChg(ModuleData.INDEXID_FIELD)].SourceColumn = ModuleData.INDEXID_FIELD;
			param[paramChg(ModuleData.NAME_FIELD)].SourceColumn = ModuleData.NAME_FIELD;
			param[paramChg(ModuleData.DESCRIPTION_FIELD)].SourceColumn = ModuleData.DESCRIPTION_FIELD;
			param[paramChg(ModuleData.URL_FIELD)].SourceColumn = ModuleData.URL_FIELD;

			return command;

		}
	}
}