forumssqldataprovider.cs

解压即可使用

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

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommunityServer;
using CommunityServer.Components;
using CommunityServer.Discussions.Components;

namespace CommunityServer.Data
{
	/// <summary>
	/// Summary description for ForumsSqlDataProvider.
	/// </summary>
	public class ForumsSqlDataProvider : ForumDataProvider
	{
        #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 Member variables
        protected string databaseOwner	= "dbo";	// overwrite in web.config
        string connectionString = null;
        ProviderHelper sqlHelper = null;
        #endregion

        #region Constructor
        public ForumsSqlDataProvider(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 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 GetPosts
        /// <summary>
        /// Returns a collection of Posts that make up a particular thread with paging
        /// </summary>
        /// <param name="PostID">The ID of a Post in the thread that you are interested in retrieving.</param>
        /// <returns>A PostCollection object that contains the posts in the thread.</returns>
        /// 
        public override PostSet GetPosts(int postID, int pageIndex, int pageSize, int sortBy, int sortOrder, int userID, bool returnRecordCount) 
        {

            // Create Instance of Connection and Command Object
            //
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Posts_PostSet", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                PostSet postSet = new PostSet();

                // Set parameters
                //
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value            = postID;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value         = pageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value          = pageSize;
                myCommand.Parameters.Add("@SortBy", SqlDbType.Int).Value            = sortBy;
                myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value         = sortOrder;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value            = userID;
                myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = returnRecordCount;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                //
                myConnection.Open();
                using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Get the results
                    //
                    while (reader.Read())
                        postSet.Posts.Add( PopulatePostFromIDataReader(reader) );

                    // Are we expecting more results?
                    //
                    if ((returnRecordCount) && (reader.NextResult()) ) 
                    {
                        reader.Read();

                        // Read the value
                        //
                        postSet.TotalRecords = (int) reader[0];
                    }
                    reader.Close();
                }

                myConnection.Close();

                return postSet;
            }
        }
        #endregion

        #region #### Post ####

        /// <summary>
        /// Get basic information about a single post.  This method returns an instance of the Post class,
        /// which contains less information than the PostDeails class, which is what is returned by the
        /// GetPostDetails method.
        /// </summary>
        /// <param name="PostID">The ID of the post whose information we are interested in.</param>
        /// <returns>An instance of the Post class.</returns>
        /// <remarks>If a PostID is passed in that is NOT found in the database, a PostNotFoundException
        /// exception is thrown.</remarks>
        public override ForumPost GetPost(int postID, int userID, bool trackViews) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Post", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@TrackViews", SqlDbType.Bit).Value = trackViews;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                ForumPost p = null;
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    if (!dr.Read()) 
                    {
                        dr.Close();
                        myConnection.Close();
                        // we did not get back a post
                        throw new CSException(CSExceptionType.PostNotFound, postID.ToString());
                    }

                    p = PopulatePostFromIDataReader(dr);
                    dr.Close();
                }
                myConnection.Close();


                // we have a post to work with  
                return p;
            }
        }

        #endregion

        #region Add/Update Post
        /// <summary>
        /// Adds a new Post.  This method checks the allowDuplicatePosts settings to determine whether
        /// or not to allow for duplicate posts.  If allowDuplicatePosts is set to false and the user
        /// attempts to enter a duplicate post, a PostDuplicateException exception is thrown.
        /// </summary>
        /// <param name="PostToAdd">A Post object containing the information needed to add a new
        /// post.  The essential fields of the Post class that must be set are: the Subject, the
        /// Body, the Username, and a ForumID or a ParentID (depending on whether the post to add is
        /// a new post or a reply to an existing post, respectively).</param>
        /// <returns>A Post object with information on the newly inserted post.  This returned Post
        /// object includes the ID of the newly added Post (PostID) as well as if the Post is
        /// Approved or not.</returns>
        public override ForumPost AddPost (ForumPost post, int userID, bool autoApprove) 
        {
            int postID = -1;

            // Create Instance of Connection and Command Object
            //
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_CreateUpdate", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add parameters
                //
                myCommand.Parameters.Add(this.SettingsIDParameter());
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = post.ParentID;
                myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = sqlHelper.GetSiteSettings().EnableDuplicatePosts;
                myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = sqlHelper.GetSiteSettings().DuplicatePostIntervalInMinutes;
                myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;
                myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;
                myCommand.Parameters.Add("@IsTracked", SqlDbType.Bit).Value = post.IsTracked;
                myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = post.PostType;
                myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = post.EmoticonID;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;
                myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = post.FormattedBody;
                myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = post.UserHostAddress;
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Direction = ParameterDirection.Output;

                if( userID == 0 ) 
                {
                    myCommand.Parameters.Add("@PostAuthor", SqlDbType.NVarChar, 128).Value = post.Username;
                }

                if (post is Thread) 
                {
                    myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = ((Thread) post).IsSticky;
                    myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = ((Thread) post).StickyDate;
                }

                SerializerData data = post.GetSerializerData();

                myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
                myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

				// If autoApprove is true, mark it as approved
				if(autoApprove)
					myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = true;

                myCommand.Parameters.Add("@PostConfiguration", SqlDbType.Int).Value = post.PostConfiguration;

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();

                // LN 5/27/04: try/catch added to get rid of exceptions
                try 
                {
                    postID = (int) myCommand.Parameters["@PostID"].Value;
                } 
                catch {}

                if (postID == -1) 
                {
                    throw new CSException(CSExceptionType.PostDuplicate);
                }

                
            
                // Return the newly inserted Post
                //
                return GetPost(postID, userID, false);
            }
        }
        

        /// <summary>
        /// Updates a post.
        /// </summary>
        /// <param name="UpdatedPost">The Post data used to update the Post.  The ID of the UpdatedPost
        /// Post object corresponds to what post is to be updated.  The only other fields used to update
        /// the Post are the Subject and Body.</param>
        public override void UpdatePost(ForumPost post, int editedBy) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_Update", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
                myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = post.PostID;
                myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;
                myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;
                myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = post.FormattedBody;
                myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = post.EmoticonID;
                myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;

				if (post is IThread)
					myCommand.Parameters.Add("@IsAnnouncement", SqlDbType.Bit).Value = ((IThread) post).IsAnnouncement;
				else
					myCommand.Parameters.Add("@IsAnnouncement", SqlDbType.Bit).Value = false;

                myCommand.Parameters.Add("@EditedBy", SqlDbType.Int).Value = editedBy;
                myCommand.Parameters.Add("@EditNotes", SqlDbType.NText).Value = post.EditNotes;
                myCommand.Parameters.Add(this.SettingsIDParameter());