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

using qminoa.Common.Data;

namespace qminoa.DA
{
	public class CatalogsDB
	{
		private string conStr ;
		private SqlConnection con ;
		private SqlDataAdapter commandAdp ;
		public CatalogsDB()
		{
			conStr = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
			con = new SqlConnection(conStr);
			commandAdp = new SqlDataAdapter();
		}
		private string paramChg(string str)
		{
			str = "@"+str;
			return str;
		}

		public DataTable GetTab(string tabid)
		{
			SqlDataAdapter Command = new SqlDataAdapter();
			Command.SelectCommand = new SqlCommand();
			DataSet ds_Tabs = new DataSet();
			Command.SelectCommand.Connection = con;

			Command.SelectCommand.CommandText ="GetsysTab";
			Command.SelectCommand.CommandType = CommandType.StoredProcedure;

			SqlParameter para_tabid = new SqlParameter("@tabid",SqlDbType.VarChar,20);
			para_tabid.Value = tabid;
			Command.SelectCommand.Parameters.Add(para_tabid);

			Command.Fill(ds_Tabs,"thisTab");

			return ds_Tabs.Tables["thisTab"];
		}

		public DataSet GetCatalogsData()
		{
			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;
		}

		public bool DeleteCatalog(int pkid)
		{
			SqlCommand command = new SqlCommand();

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

			SqlParameter param = new SqlParameter(paramChg(CatalogData.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;
			}
		}

		public bool UpdateCatalog(CatalogData catalogData)
		{
			commandAdp.UpdateCommand = GetCommand();

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

			commandAdp.UpdateCommand.CommandText = "UpdateCatalogs";
			commandAdp.Update(catalogData,CatalogData.CATALOG_TABLE_NAME);

			if(catalogData.HasErrors)
			{
				catalogData.Tables[CatalogData.CATALOG_TABLE_NAME].GetErrors()[0].ClearErrors();
				return false;
			}
			else
			{
				catalogData.AcceptChanges();
				return true;
			}
		}

			 public bool CreateCatalog(CatalogData catalogData)
			 {
				 commandAdp.InsertCommand = GetCommand();
				 commandAdp.InsertCommand.CommandText = "InsertCatalog";
	
				 commandAdp.Update(catalogData,CatalogData.CATALOG_TABLE_NAME);

				 if(catalogData.HasErrors)
				 {
					 catalogData.Tables[CatalogData.CATALOG_TABLE_NAME].GetErrors()[0].ClearErrors();
					 return false;
				 }
				 else
				 {
					 catalogData.AcceptChanges();
					 return true;
				 }
			 }

		private SqlCommand GetCommand()
		{
			SqlCommand command = new SqlCommand();

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

			SqlParameterCollection param = command.Parameters;

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

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

			return command;

		}
	}
}