sqlnewsprovider.cs

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

using System;
using System.Configuration;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
/// <summary>
///Sql Server新闻提供者
/// </summary>
public class SqlNewsProvider : NewsProvider
{
    /// <summary>
    /// 从Web.config配置文件中获取连接字符串信息。
    /// </summary>
    private string connectionString()
    {
        SmallBusinessDataProvidersSection sec = (ConfigurationManager.GetSection("SmallBusinessDataProviders")) as SmallBusinessDataProvidersSection;
        string connectionStringName = sec.NewsProviders[sec.NewsProviderName].Parameters["connectionStringName"];
        return WebConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
    }

   /// <summary>
   /// 获取所有的新闻列表
   /// </summary>
   /// <returns></returns>
    public override List<NewsItem> GetAllNews()
    {
        List<NewsItem> list = new List<NewsItem>();
        // 使用指定的字符串创建连建，调用存储过程GetNews返回新闻列表结果。
        using (SqlConnection con = new SqlConnection(connectionString()))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("GetNews", con);        
            SqlDataReader r = cmd.ExecuteReader();
            NewsItem curr;
            //遍历存储过程返回的SqlDataReader对象。
            while (r.Read())
            {
                if (r["id"] is DBNull || r["visible"] is DBNull || r["title"] is DBNull)
                    throw new InvalidOperationException(Messages.NewsItemRequiredAttributesMissing);
                //为NewsItem实体类赋值。
                curr = new NewsItem((string)r["id"], (Boolean)r["visible"], (string)r["title"]);
                // 如果没有指定日期的话，将返回DateTime.MinValue 
                curr.Date = (r["date"] is DBNull) ? DateTime.MinValue : (DateTime)r["date"];
                curr.Content = (r["content"] is DBNull) ? String.Empty : (string)r["content"];
                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);
            }
            //执行完毕关闭连接。
            con.Close();
        }
        //返回新闻列表
        return list;
    }
    /// <summary>
    /// 根据指定的新闻ID号获取新闻项
    /// </summary>
    /// <param name="newsItemId"></param>
    /// <returns></returns>
    public override NewsItem GetNewsItem(string newsItemId)
    {
        //检查参数是否为空
        if (String.IsNullOrEmpty(newsItemId)) return null;

        // 连接到数据库
        NewsItem curr;
        using (SqlConnection con = new SqlConnection(connectionString()))
        { 
            con.Open();
            //调用GetNewsItem存储过程
            SqlCommand cmd = new SqlCommand("GetNewsItem", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@id", SqlDbType.NVarChar);
            cmd.Parameters["@id"].Value = newsItemId;
            //执行存储过程返回结果
            SqlDataReader r = cmd.ExecuteReader();
            //这里不用遍历，因为只返回一项。
            r.Read();
            //如果结果值为空的话，抛出异常 
            if (r["id"] is DBNull || r["visible"] is DBNull || r["title"] is DBNull)
                throw new InvalidOperationException(Messages.NewsItemRequiredAttributesMissing);
            //用执行结果数据初始化NewsItems实体对象
            curr = new NewsItem((string)r["id"], (Boolean)r["visible"], (string)r["title"]);
            curr.Date = (r["date"] is DBNull) ? DateTime.MinValue : (DateTime)r["date"];
            curr.Content = (r["content"] is DBNull) ? String.Empty : (string)r["content"];
            curr.ImageUrl = (r["imageUrl"] is DBNull) ? String.Empty : (string)r["imageUrl"];
            curr.ImageAltText = (r["imageAltText"] is DBNull) ? String.Empty : (string)r["imageAltText"];
        }
        //返回实体对象
        return curr;
    }
} // end class