weblogsqldataprovider.cs

本系统是在asp版《在线文件管理器》的基础上设计制作

//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using CommunityServer.Blogs;
using CommunityServer.Blogs.Components;
using CommunityServer.Components;

namespace CommunityServer.Data
{
	/// <summary>
	/// Summary description for WeblogSqlDataProvider.
	/// </summary>
	public  class WeblogSqlDataProvider : WeblogDataProvider
	{
        #region Member variables
        protected string databaseOwner	= "dbo";	// overwrite in web.config
        string connectionString = null;
        ProviderHelper sqlHelper = null;
        #endregion

        #region Constructor
        /****************************************************************
        // SqlDataProvider
        //
        /// <summary>
        /// Class constructor
        /// </summary>
        //
        ****************************************************************/
        public WeblogSqlDataProvider(string databaseOwner, string connectionString) 
        {

            // Read the connection string for this provider
            //
            this.connectionString = connectionString;

            // Read the database owner name for this provider
            //
            this.databaseOwner = databaseOwner;

            sqlHelper = ProviderHelper.Instance();

        }
        #endregion

        #region SettingsID 
        
        protected int GetSettingsID()
        {
            return  sqlHelper.GetSettingsID();
        }

        protected SqlParameter SettingsIDParameter()
        {
            SqlParameter p = new SqlParameter("@SettingsID",SqlDbType.Int);
            p.Value = GetSettingsID();
            return p;
        }
        #endregion

        #region Helpers
        protected SqlConnection GetSqlConnection () 
        {

            try 
            {
                return new SqlConnection(ConnectionString);
            } 
            catch 
            {
                throw new CSException(CSExceptionType.DataProvider, "SQL Connection String is invalid.");
            }

        }

        public string ConnectionString 
        {
            get 
            {
                return connectionString;
            }
            set 
            {
                connectionString = value;
            }
        }
        #endregion

		#region Archive

		public override Hashtable GetPostsByMonth(int BlogID, DateTime month)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_PostsByMonth", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = BlogID;
					command.Parameters.Add("@Year", SqlDbType.Int, 4).Value = month.Year;
					command.Parameters.Add("@Month", SqlDbType.Int, 4).Value = month.Month;

					connection.Open();
					using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
					{
						Hashtable items = new Hashtable();
						ArchiveDataItem ad = null;
						while(reader.Read())
						{
							ad = new ArchiveDataItem();
							ad.Date = new DateTime((int)reader["Year"],(int)reader["Month"],(int)reader["Day"]);
							ad.Count = (int)reader["Count"];
							items.Add(ad.Date.Day,ad);
                            
						}

						reader.Close();
						connection.Close();
						command.Dispose();

						return items;
					}
				}
			}
		}



	    public override ArrayList GetPostsByMonths(int BlogID)
        {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_PostsByMonthList", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = BlogID;

                    connection.Open();
                    using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                    {
                        ArrayList items = new ArrayList();
                        ArchiveDataItem ad = null;
                        while(reader.Read())
                        {
                            ad = new ArchiveDataItem();
                            ad.Date = new DateTime((int)reader["Year"],(int)reader["Month"],1);
                            ad.Count = (int)reader["Count"];
                            items.Add(ad);
                            
                        }

                        reader.Close();
                        connection.Close();
                        command.Dispose();

                        return items;
                    }
               }
            }
        }

		#endregion


        #region GetWeblogPost
        /// <summary>
        /// Returns an instance of WeblogPost.
        /// </summary>
	    public override WeblogPost GetWeblogPost(int postID, bool includeCategories, bool isApproved)
	    {
            WeblogPost entry = null;
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_Post_Get", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
                    command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = includeCategories;
                    command.Parameters.Add("@IsApprovedFilter", SqlDbType.Bit).Value = isApproved;
                    command.Parameters.Add(SettingsIDParameter());

                    connection.Open();
                    using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if(reader.Read())
                        {
                            entry = PopulateWeblogEntryFromIDataReader(reader);
                        }

                        if(includeCategories && reader.NextResult())
                        {
                            ArrayList al = new ArrayList();
                            while(reader.Read())
                            {
                                al.Add(reader["Name"] as string);
                            }
                            entry.Categories = (string[])al.ToArray(typeof(string));
                        }

                        reader.Close();
                    }
                }
            }

            return entry;
	    }
        #endregion

        #region GetBlogThreads

		public override PostSet GetThreadsTrackedByUser(int SectionID, int UserID)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_TrackedThreadsByUser", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@SectionID", SqlDbType.Int).Value = SectionID;
					command.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID;
					command.Parameters.Add(this.SettingsIDParameter());

					connection.Open();
					
					using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
					{
						PostSet ps = new PostSet();	
						while(dr.Read())
						{
							WeblogPost post = PopulateWeblogEntryFromIDataReader(dr);
							//PopulateWeblogEntryFromIDataReader(dr,post);
							ps.Posts.Add(post);
						}
						
						dr.Close();
						return ps;
					}
					
					
				}
			}
		}

        /// <summary>
        /// Returns an instance of ThreadSet by building a dynamic query based on 
        /// supplied BlogThreadQuery
	    public override ThreadSet GetBlogThreads(BlogThreadQuery query)
	    {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_Posts_GetPostSet", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.BlogID;
                    command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
                    command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = query.PageSize;
                    command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = SqlGenerator.BuildBlogThreadQuery(query,databaseOwner);
                    command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;
                    command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;
                    command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
                    command.Parameters.Add(SettingsIDParameter());

                    ThreadSet ts = new ThreadSet();

                    // Execute the command
					
                    //If we want to display the list of categories for the post, we will need to 
                    //use a dataset so we can join the child records
                    if(query.IncludeCategories)
                    {
                        DataSet ds = new DataSet();
                        SqlDataAdapter da = new SqlDataAdapter(command);

                        //wait as long as possible to open the conn
                        connection.Open();
                        da.Fill(ds);
                        connection.Close();

                        //keep a referece to the relation since it is used in the GetChildRows look up anyway
                        DataRelation relation = new DataRelation("Categories",ds.Tables[0].Columns["PostID"],ds.Tables[1].Columns["PostID"],false);
                        ds.Relations.Add(relation);
                        
                        DataRowCollection posts = ds.Tables[0].Rows;
                        foreach(DataRow dr in posts)
                        {
                            ts.Threads.Add(PopulateWeblogPostContentFromDataRow(dr,relation));
                        }
                        ds.Clear();
                        ds.Dispose();

                        ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;

                    }
                    else
                    {
                        //No categories needed, so we can use a datareader.

                        connection.Open();
                        using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
                        {
						
                            while(dr.Read())
                            {
                                ts.Threads.Add(PopulateWeblogPostContentFromIDataReader(dr));
                            }

                            //                            if(query.IncludeCategories)
                            //                                dr.NextResult();