sqlcatalogprovider.cs

详细介绍中小企业的网站编程,附有详细的注释,对需要制作网站的朋友有很大的帮助,有需要的朋友可下载,

using System;
using System.Configuration;
using System.Web.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
 /// <summary>
 ///提供访问SQL Server数据库的方法
 /// </summary>
public class SqlCatalogProvider : CatalogProvider
{
    //从Web.Config配置文件中获取连接字符串信息。
    private string connectionString()
    {
        SmallBusinessDataProvidersSection sec = (ConfigurationManager.GetSection("SmallBusinessDataProviders")) as SmallBusinessDataProvidersSection;
        string connectionStringName = sec.CatalogProviders[sec.CatalogProviderName].Parameters["connectionStringName"];
        return WebConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
    }
    ///<summary>
    /// 返回属于指定分类ID的产品列表。如果产品中没有价格，则Double.MinValue被返回。
    /// </summary>
    public override List<Item> GetChildItems(string parentCategoryId)
    {
        List<Item> list = new List<Item>();
        //参数检验
        if (String.IsNullOrEmpty(parentCategoryId)) return list;
        // 连接到数据源
        using (SqlConnection con = new SqlConnection(connectionString()))
        { 
            con.Open();
            //使用存储过程GetChildItems获取产品列表
            SqlCommand cmd = new SqlCommand("GetChildItems", con);
            cmd.CommandType = CommandType.StoredProcedure;
            //初始化参数
            cmd.Parameters.Add("@categoryId", SqlDbType.NVarChar);            
            cmd.Parameters["@categoryId"].Value = parentCategoryId;
            //执行存储过程返回SqldataReader.
            SqlDataReader r = cmd.ExecuteReader();
            Item curr;
            //遍历SqlDataRader对象，用数据初始化Item实体类，并添加到List<Item>泛型集合。
            while (r.Read())
            {
                if (r["id"] is DBNull || r["visible"] is DBNull || r["title"] is DBNull)
                    throw new InvalidOperationException(Messages.ItemRequiredAttributesMissing);

                curr = new Item((string) r["id"],
                                (Boolean)r["visible"],
                                (string) r["title"]);
                curr.Description = (r["description"] is DBNull) ? String.Empty : (string)r["description"];
                curr.Price = (r["price"] is DBNull) ? Double.MinValue : (double)r["price"];
                curr.InStock = (r["inStock"] is DBNull) ? true : (Boolean)r["inStock"];
                curr.ImageUrl = (r["imageUrl"] is DBNull) ? String.Empty : (string)r["imageUrl"];
                curr.ImageAltText = (r["imageAltText"] is DBNull) ? String.Empty : (string)r["imageAltText"];
                list.Add(curr);
            }
        }
        //返回列表
        return list;
    }
    ///<summary>
    ///返回指定分类下面的子分类。
    ///</summary>
    public override List<Category> GetChildCategories(string parentCategoryId)
    {   
        List<Category> list       = new List<Category>();        
        using (SqlConnection con = new SqlConnection(connectionString()))
        {
            con.Open();
            SqlCommand cmd;
            if( String.IsNullOrEmpty(parentCategoryId))
            {
                //使用GetRootCategories方法获取子分类数据。
                cmd = new SqlCommand("GetRootCategories", con);
            }
            else
            {
                //如果没有指定分类ID，则使用GetNonRootCategories存储过程获取分类数据。
                cmd = new SqlCommand("GetNonRootCategories ", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@categoryId", SqlDbType.NVarChar);
                cmd.Parameters["@categoryId"].Value = parentCategoryId;
            }
            //返回SqldataReader
            SqlDataReader r = cmd.ExecuteReader();
            Category curr;
            //遍历SqlDataReader对象，将其中的数据赋给Category实体对象，并将Category实体对象添加到List<Catagory>中。
            while (r.Read())
            {
                if (r["id"] is DBNull || r["visible"] is DBNull || r["title"] is DBNull)
                    throw new InvalidOperationException(Messages.CategoryRequiredAttributesMissing);

                curr = new Category((string)r["id"],(Boolean)r["visible"],(string)r["title"]);
                curr.Description = (r["description"] is DBNull) ? String.Empty : (string)r["description"];
                curr.ImageUrl = (r["imageUrl"] is DBNull) ? String.Empty : (string)r["imageUrl"];
                curr.ImageAltText = (r["imageAltText"] is DBNull) ? String.Empty : (string)r["imageAltText"];
                list.Add(curr);
            }
        }
        return list;
    }
    ///<summary>
    ///根据指定的ItemID返回产品
    ///</summary>
    public override Item GetItem(string itemId)
    {
        //检查参数
        if (String.IsNullOrEmpty(itemId)) return null;
        // 连接到数据库
        Item curr;
        using (SqlConnection con = new SqlConnection(connectionString()))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("GetItem", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@itemId", SqlDbType.NVarChar);
            cmd.Parameters["@itemId"].Value = itemId;
            //调用GetItem存储过程，返回SqlDataReader对象。
            SqlDataReader r = cmd.ExecuteReader();
            r.Read();
            //遍历SqlDataReader对象，返回一个被赋值的Item实体对象。
            if (r["id"] is DBNull || r["visible"] is DBNull || r["title"] is DBNull)
                throw new InvalidOperationException(Messages.ItemRequiredAttributesMissing);

            curr = new Item((string)r["id"],
                            (Boolean)r["visible"],
                            (string)r["title"]);
            curr.Description = (r["description"] is DBNull) ? String.Empty : (string)r["description"];
            curr.Price = (r["price"] is DBNull) ? Double.MinValue : (double)r["price"];
            curr.InStock = (r["inStock"] is DBNull) ? true : (Boolean)r["inStock"];
            curr.ImageUrl = (r["imageUrl"] is DBNull) ? String.Empty : (string)r["imageUrl"];
            curr.ImageAltText = (r["imageAltText"] is DBNull) ? String.Empty : (string)r["imageAltText"];
        }
        return curr;    
    }
}