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

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

[WebService(Namespace = "http://www.wrox.com/Books/ProASPNET20XML/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class CategoriesService  : System.Web.Services.WebService 
{
    [WebMethod(Description = "This method allows a remote client to retrieve all the categories available in the site.", EnableSession = false)]
    public List<Category> GetCategories() 
    {
        List<Category> list = new List<Category>();
        using (SqlConnection conn = new SqlConnection())
        {
            string connString = WebConfigurationManager.ConnectionStrings["contentPublisher"].ConnectionString;
            conn.ConnectionString = connString;
            SqlCommand command = new SqlCommand("GetCategories", conn);
            command.CommandType = CommandType.StoredProcedure;
            conn.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.CategoryID = Convert.ToInt32(reader["CategoryID"]);
                    cate.CategoryName = reader["CategoryName"].ToString();
                    list.Add(cate);
                }
            }            
        }
        return list;        
    }    
}