forumssqldataprovider.cs

解压即可使用

                // Allow Thread to update sticky properties.
                //
                if (post.ParentID == 0) 
                {
                    Thread thread = (Thread) post;
                    myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = thread.IsSticky;
                    myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = thread.StickyDate;
                }

                SerializerData data = post.GetSerializerData();

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

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        #endregion

        #region Threads 

        /****************************************************************
        // GetNextThreadID
        //
        /// <summary>
        /// Gets the next threadid based on the postid
        /// </summary>
        // 
        ****************************************************************/
        public override int GetNextThreadID(int postID) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_GetPrevNextThreadID", myConnection);
                SqlDataReader reader;
                int threadID = postID;

                // 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("@NextThread", SqlDbType.Bit).Value = 1;

                // Execute the command
                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {
                    while (reader.Read())
                        threadID = (int) reader["ThreadID"];

                    reader.Close();
                }
                myConnection.Close();

                return threadID;
            }
        }

        /****************************************************************
        // GetPrevThreadID
        //
        /// <summary>
        /// Gets the prev threadid based on the postid
        /// </summary>
        //
        ****************************************************************/
        public override int GetPrevThreadID(int postID) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_GetPrevNextThreadID", myConnection);
                SqlDataReader reader;
                int threadID = postID;

                // 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("@NextThread", SqlDbType.Bit).Value = 0;

                // Execute the command
                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {

                    while (reader.Read())
                        threadID = (int) reader["ThreadID"];

                    reader.Close();
                }
                myConnection.Close();

                return threadID;
            }
        }

        public override void UpdateThreadStatus (int threadID, ThreadStatus status) {
            // Create Instance of Connection and Command Object
            using (SqlConnection myConnection = GetSqlConnection()) {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Thread_Status_Update", myConnection);

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

                myCommand.Parameters.Add( "@ThreadID", SqlDbType.Int ).Value = threadID;
                myCommand.Parameters.Add( "@Status", SqlDbType.Int ).Value = (int) status;
                myCommand.Parameters.Add( SettingsIDParameter() );

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        #endregion

        #region #### Threads ####

        #region Public Static Helper Functions
        public static DateTime GetSafeSqlDateTime (DateTime date) 
        {

            if (date == DateTime.MinValue)
                return (DateTime) System.Data.SqlTypes.SqlDateTime.MinValue;
            return date;

        }
        #endregion

		/// <summary>
		/// Get basic information about a single thread.  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="threadID">The ID of the thread whose information we are interested in.</param>
		/// <returns>An instance of the Post class.</returns>
		/// <remarks>If a ThreadID is passed in that is NOT found in the database, null is returned.</remarks>
		public override Thread GetThread(int threadID, int userID)
		{

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

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = threadID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
                Thread thread = null;
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleRow))
                {
                    if(dr.Read())
                    thread = PopulateThreadFromIDataReader(dr);

                    dr.Close();
                }
				myConnection.Close();


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

        
        public override ThreadSet GetThreads(
            int forumID, 
            int pageIndex, 
            int pageSize, 
            User user, 
            DateTime threadsNewerThan, 
            SortThreadsBy sortBy, 
            SortOrder sortOrder, 
            ThreadStatus threadStatus, 
            ThreadUsersFilter userFilter, 
            bool activeTopics,
            bool unreadOnly, 
            bool unansweredOnly, 
            bool returnRecordCount) 
        {

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

                ThreadSet threadSet             = new ThreadSet();
                StringBuilder sqlCountSelect    = new StringBuilder("SELECT count(T.ThreadID) ");      
                StringBuilder sqlPopulateSelect = new StringBuilder("SELECT T.ThreadID ");
                StringBuilder fromClause        = new StringBuilder(" FROM " + this.databaseOwner + ".cs_Threads T ");
                StringBuilder whereClause       = new StringBuilder(" WHERE ");
                StringBuilder orderClause       = new StringBuilder(" ORDER BY ");

                // Ensure DateTime is min value for SQL
                //
                threadsNewerThan = ProviderHelper.GetSafeSqlDateTime(threadsNewerThan);

				if (activeTopics) {
					threadsNewerThan = DateTime.Now.AddDays(-7);
				}

                // Construct the clauses
                #region Constrain Forums

                whereClause.AppendFormat(" T.SettingsID = {0} ",this.GetSettingsID());

                // Contrain the selectivness to a set of specified forums. The ForumID is our
                // clustered index so we want this to be first
                if (forumID > 0) 
                {
                    whereClause.Append(" AND T.SectionID = ");
                    whereClause.Append(forumID);
                } 
                else if (forumID < 0) 
                {
                    // Get a list of all the forums the user has access to
                    //
                    ArrayList forumList = Forums.GetForums(false, true, false );

					// Remove the private messaging forum, so our next check step is better
					for(int i = forumList.Count - 1 ; i >= 0 ; i--)
					{
						Forum f = forumList[i] as Forum;
						if(f != null)
						{
							if(f.SectionID == 0 || f.ForumType == ForumType.Deleted || f.ForumType == ForumType.Reporting)
							forumList.RemoveAt(i);
						}
					}

                    //should add a new GetForums method to specify permissions instead of doing this twice!
                    forumList = Sections.FilterByAccessControl(forumList,Permission.Read);

					// Only append this restriction if the user actually has access to any forums
                    if(forumList.Count > 0) 
                    {
                        whereClause.Append(" AND T.SectionID IN (");

                        for (int i = 0; i < forumList.Count; i++) 
                        {
                            // SectionIDs are greater then 0, as filtered above

                            if ( (i + 1) < forumList.Count) 
                            {
                                whereClause.Append( ((Forum) forumList[i]).SectionID + ", ");
                            } 
                            else 
                            {
                                whereClause.Append( ((Forum) forumList[i]).SectionID );
                            }
                        }
                        whereClause.Append(")");
                    }
                } 
                else 
                {
                    whereClause.Append(" AND T.SectionID = 0 AND P.UserID = ");
                    whereClause.Append(user.UserID);
                    whereClause.Append(" AND P.ThreadID = T.ThreadID ");
                    fromClause.Append(", " + this.databaseOwner + ".cs_PrivateMessages P ");
                }
                #endregion

                #region Constrain Date
                whereClause.Append(" AND StickyDate >= '");
                whereClause.Append( threadsNewerThan.ToString( System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.SortableDateTimePattern ));
                whereClause.Append(" '");
                #endregion

                #region Constain Approval
                whereClause.Append(" AND IsApproved = 1");
                #endregion

				#region Unread
				//TODO: See if we can clean this up and remove the inner select
				if(unreadOnly && !user.IsAnonymous)
				{
					whereClause.AppendFormat(" AND (Select Convert(bit,Count(*)) FROM {0}.cs_vw_HasReadForum where ((ThreadID is null and MarkReadAfter > T.ThreadID) or (MarkReadAfter is null and ThreadID = T.ThreadID)) and UserID = {1} and SectionID = T.SectionID) = 0 ",this.databaseOwner,user.UserID);
				}
				#endregion

                #region Unanswered topics
                if (unansweredOnly) 
                {
                    whereClause.Append(" AND TotalReplies = 0 AND IsLocked = 0");
                }
                #endregion

                #region Users filter
                if (userFilter != ThreadUsersFilter.All) 
                {

                    if ((userFilter == ThreadUsersFilter.HideTopicsParticipatedIn) || (userFilter == ThreadUsersFilter.HideTopicsNotParticipatedIn)) 
                    {

                        whereClause.Append(" AND ");
                        whereClause.Append(user.UserID);

                        if (userFilter == ThreadUsersFilter.HideTopicsNotParticipatedIn)
                            whereClause.Append(" NOT");

                        whereClause.Append(" IN (SELECT UserID FROM " + this.databaseOwner + ".cs_Posts P WHERE P.ThreadID = T.ThreadID)");

                    }