<%@ WebService Language="C#" Class="ProductsService" %>

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;

[WebService(Namespace = "http://www.wrox.com/Books/ProASPNET20XML/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class ProductsService  : System.Web.Services.WebService 
{

    [WebMethod(Description = "This method allows a remote client to retrieve all the products available in the site.", EnableSession = false)]
    public List<Product> GetProducts()
    {
        List<Product> list = new List<Product>();
        using (SqlConnection conn = new SqlConnection())
        {
            string connString = WebConfigurationManager.ConnectionStrings["contentPublisher"].ConnectionString;
            conn.ConnectionString = connString;
            SqlCommand command = new SqlCommand("GetProducts", conn);
            command.CommandType = CommandType.StoredProcedure;
            conn.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Product prod = ConvertReaderToProduct(reader);                                    
                    list.Add(prod);
                }
            }
        }
        return list;
    }

    [WebMethod(Description = "This method allows a remote client to retrieve all the products based on the category id", EnableSession = false)]
    public List<Product> GetProductsByCategoryID(int categoryID)
    {
        List<Product> list = new List<Product>();
        using (SqlConnection conn = new SqlConnection())
        {
            string connString = WebConfigurationManager.ConnectionStrings["contentPublisher"].ConnectionString;
            conn.ConnectionString = connString;
            conn.Open();
            SqlCommand command = new SqlCommand("GetProductsByCategoryID", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@CategoryID", SqlDbType.Int));
            command.Parameters["@CategoryID"].Value = categoryID;            
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Product prod = ConvertReaderToProduct(reader);                     
                    list.Add(prod);
                }
            }
        }
        return list;
    }  
    
    [WebMethod(Description = "The ProductDetailsGetObject method allows a remote client to retrieve the details of a product based on the product id in the form of a ProductDetails object.", EnableSession = false)]
    public List<Product> GetProductDetails(int productID)
    {               
        List<Product> list = new List<Product>(); 
        using (SqlConnection conn = new SqlConnection())
        {
            string connString = WebConfigurationManager.ConnectionStrings["contentPublisher"].ConnectionString;
            conn.ConnectionString = connString;
            conn.Open();
            SqlCommand command = new SqlCommand("GetProductDetails", conn);            
            command.CommandType = CommandType.StoredProcedure;
            
            command.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.Int)); 
            command.Parameters["@ProductID"].Value = productID;            
            SqlDataReader reader = command.ExecuteReader();
            Product prod = null;
            while (reader.Read())
            {
                prod = ConvertReaderToProduct(reader);                
            }
            //This will be a collection with just one object
            list.Add(prod);
        }
        return list;
    }

    public Product ConvertReaderToProduct(SqlDataReader reader)
    {
        Product prod = new Product();                       
        //Assign the column values to the Product object
        prod.ProductID = Convert.ToInt32(reader["ProductID"].ToString());
        prod.CategoryID = Convert.ToInt32(reader["CategoryID"].ToString());
        prod.ModelNo = reader["ModelNo"].ToString();
        prod.ModelName = reader["ModelName"].ToString();
        prod.Image = reader["Image"].ToString();
        prod.Price = reader["Price"].ToString();
        prod.Description = reader["Description"].ToString();
        return prod;
    }
}