using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace TinyHeels.App_Code
{
    class Category
    {
        public int categoryId { get; set; }
        public string categoryName { get; set; }
        public byte[] image { get; set; }
        DBConnection strcon = new DBConnection();

        public bool checkCategory()
        {
            try
            {
                SqlConnection conneciton = strcon.connect();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "select count(*) from category where categoryId='" + this.categoryId + "'";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conneciton;
                int count = Int32.Parse(cmd.ExecuteScalar().ToString());
                conneciton.Close();
                if (count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }

        public void addCategory()
        {
            try
            {
                bool x = this.checkCategory();
                if (x)
                {
                    //update
                    SqlConnection conneciton = strcon.connect();
                    SqlCommand cmd = conneciton.CreateCommand();
                    cmd.CommandText = "update category set categoryName=@name, categoryImage=@image where categoryId='" + this.categoryId + "'";
                    cmd.Parameters.AddWithValue("@name", this.categoryName);
                    cmd.Parameters.AddWithValue("@image", this.image);
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    conneciton.Close();
                }
                else
                {
                    //insert new data
                    SqlConnection conneciton = strcon.connect();
                    string sql = "insert into category(categoryName,categoryImage) values(@name,@image)";
                    SqlCommand cmd = new SqlCommand(sql, conneciton);
                    cmd.Parameters.AddWithValue("@name", this.categoryName);
                    cmd.Parameters.AddWithValue("@image", this.image);
                    cmd.ExecuteNonQuery();
                    conneciton.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        public DataTable getAllCategories()
        {
            try
            {
                SqlConnection conneciton = strcon.connect();
                using (SqlDataAdapter dat = new SqlDataAdapter("Select categoryId as [Category ID], categoryName as [Name],categoryImage as Image from category", conneciton))
                {
                    DataSet ds = new DataSet();
                    dat.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    conneciton.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        public void deleteCategory()
        {
            try
            {
                SqlConnection conneciton = strcon.connect();
                SqlCommand cmd = conneciton.CreateCommand();
                cmd.CommandText = "delete from category where categoryId='" + this.categoryId + "'";
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                conneciton.Close();
            }
            catch (Exception ex)
            {

            }
        }
        public DataTable getById()
        {
            DataTable dt=null;
            try
            {
                SqlConnection conneciton = strcon.connect();
                using (SqlDataAdapter dat = new SqlDataAdapter("Select categoryId as [Category ID], categoryName as [Name],categoryImage as [Image] from category where categoryId='"+this.categoryId+"'", conneciton))
                {
                    DataSet ds = new DataSet();
                    dat.Fill(ds);
                    dt = ds.Tables[0];
                    return dt;
                }
            }
            catch (Exception ex)
            {

            }
            return dt;
        }
    }
}
