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

using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Spencer.BO;

namespace Spencer.DAL
{
    public class CategoryDB
    {
        public static CategoryList GetList()
        {
            CategoryList tempList = null;

            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

            string mySql = "select * from Categories  ";
            SqlCommand myCommand = new SqlCommand(mySql, myConnection);

            myConnection.Open();
            SqlDataReader myReader = null;

            myReader = myCommand.ExecuteReader();

            if (myReader.HasRows)
            {
                tempList = new CategoryList();

                while (myReader.Read())
                    tempList.Add(FillDataRecord(myReader));
            }

            myReader.Close();
            myConnection.Close();

            return tempList;

        }

        public static Category GetItem(int categoryId)
        {
            Category myCategory = new Category();

            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

            SqlCommand myCommand = new SqlCommand("sprocCategorySelectSingleItem", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.AddWithValue("@categoryId", categoryId);

            myConnection.Open();

            SqlDataReader myReader = myCommand.ExecuteReader();

            if (myReader.Read())
            {
                myCategory = FillDataRecord(myReader);
            }

            myReader.Close();
            myConnection.Close();

            return myCategory;
        }

        public static int Save(Category myCategory)
        {
            int result = 0;

            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

            SqlCommand myCommand = new SqlCommand("sprocCategoryUpdateSingleItem", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            if (myCategory.CategoryId == -1)
                myCommand.Parameters.AddWithValue("@categoryId", DBNull.Value);
            else
                myCommand.Parameters.AddWithValue("@categoryId", myCategory.CategoryId);

            myCommand.Parameters.AddWithValue("@categoryName", myCategory.CategoryName);
            myCommand.Parameters.AddWithValue("@description", myCategory.Description);

            DbParameter returnValue = myCommand.CreateParameter();
            returnValue.Direction = ParameterDirection.ReturnValue;
            myCommand.Parameters.Add(returnValue);

            myConnection.Open();

            myCommand.ExecuteNonQuery();
            result = Convert.ToInt32(returnValue.Value);

            myConnection.Close();

            return result;

        }

        public static bool Delete(Category myCategory)
        {
            int categoryId = myCategory.CategoryId;
            int result = 0;

            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

            SqlCommand myCommand = new SqlCommand("sprocCategoryDeleteItem", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.AddWithValue("@categoryId", categoryId);

            myConnection.Open();

            result = myCommand.ExecuteNonQuery();

            myConnection.Close();

            return result > 0;
        }

        private static Category FillDataRecord(IDataRecord myDataRecord)
        {
            Category myCategory = new Category();

            myCategory.CategoryId = myDataRecord.GetInt32(myDataRecord.GetOrdinal("CategoryId"));
            myCategory.CategoryName = myDataRecord.GetString(myDataRecord.GetOrdinal("CategoryName"));
            myCategory.Description = myDataRecord.GetString(myDataRecord.GetOrdinal("Description"));

            return myCategory;
        }
    }
}