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 CategoryManager2
{

    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();

        
        tempList = new CategoryList();


        Category myCategory;

        while (myReader.Read()) 
        {
          myCategory = new Category();

       //   myCategory.CategoryId = (int)myReader["categoryId"];
       //   myCategory.CategoryName = myReader["categoryName"].ToString(); 
       //   myCategory.Description = myReader["description"].ToString(); 

         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);

        string sql = "SELECT * FROM [Categories] WHERE [CategoryID] = @categoryID";

        SqlCommand myCommand = new SqlCommand(sql, myConnection);

        myCommand.Parameters.AddWithValue("@categoryId", categoryId);

        myConnection.Open();

        SqlDataReader myReader = myCommand.ExecuteReader();

        if (myReader.Read())
        {
            //myCategory = new Category();

            //myCategory.CategoryId = (int)myReader["categoryId"];
            //myCategory.CategoryName = myReader["categoryName"].ToString();
            //myCategory.Description = myReader["description"].ToString();         

            myCategory = FillDataRecord(myReader);
        }

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

        return myCategory;
    }


    public static int Save(Category myCategory)
    {
        int result = 0;
        string updateSql = "UPDATE [Categories] SET [CategoryName] = @CategoryName, [Description] = @Description  WHERE [CategoryID] = @CategoryID";
        string insertSql = "INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)";

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

        SqlCommand myCommand;

        if (myCategory.CategoryId == -1)
        {
             myCommand = new SqlCommand(insertSql , myConnection);

        }
        else 
        {        
             myCommand = new SqlCommand(updateSql  , myConnection);
             myCommand.Parameters.AddWithValue("@categoryId", myCategory.CategoryId);        
        }

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

        result = myCommand.ExecuteNonQuery();

        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);

        string sql = "DELETE FROM [Categories] WHERE [CategoryID] = @categoryID";

        SqlCommand myCommand = new SqlCommand(sql, myConnection);

        myCommand.Parameters.AddWithValue("@categoryId", categoryId);

        myConnection.Open();

        result = myCommand.ExecuteNonQuery();

        myConnection.Close();

        return result > 0;
    }

    private static Category FillDataRecord(SqlDataReader myReader) 
    {
        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"));

        myCategory.CategoryId = (int)myReader["categoryId"];
        myCategory.CategoryName = myReader["categoryName"].ToString();
        myCategory.Description = myReader["description"].ToString(); 


        return myCategory  ;
    }
}
