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;


public class CategoryManager
{
    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  ;
    }
}
