forumssqldataprovider.cs

解压即可使用

                    else 
                    {

                        if (userFilter == ThreadUsersFilter.HideTopicsByNonAnonymousUsers)
                            whereClause.Append(" AND 0 NOT");
                        else
                            whereClause.Append(" AND 0");

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

                #region Thread Status
                /*
                if (threadStatus != ThreadStatus.NotSet) {
                    switch (threadStatus) {
                        case ThreadStatus.NotAnswered:
                            whereClause.Append(" AND ThreadStatus = 0");
                            break;

                        case ThreadStatus.Answered:
                            whereClause.Append(" AND ThreadStatus = 0");
                            break;

                        default:
                            break;
                    }
                }*/
                #endregion

				#region Order By and Active Topics
				if (activeTopics) {
					whereClause.Append(" AND IsLocked = 0");
				}

				switch (sortBy) {
					case SortThreadsBy.LastPost:
				
						//SMW: Removed IsSticky as a sort condidtion
						//We should always be ordering by the date only, otherwise a post will always be stick!
						
						if (sortOrder == SortOrder.Ascending) {
							if (activeTopics || unansweredOnly)
								orderClause.Append("ThreadDate");
							else
								orderClause.Append(" StickyDate");
						} 
						else {
							if (activeTopics || unansweredOnly)
								orderClause.Append("ThreadDate DESC");
							else
								orderClause.Append(" StickyDate DESC");
						}
						break;

					case SortThreadsBy.TotalRatings:
						if (sortOrder == SortOrder.Ascending)
							orderClause.Append("TotalRatings");
						else
							orderClause.Append("TotalRatings DESC");
						break;
	        
					case SortThreadsBy.TotalReplies:
						if (sortOrder == SortOrder.Ascending)
							orderClause.Append("TotalReplies");
						else
							orderClause.Append("TotalReplies DESC");
						break;

					case SortThreadsBy.ThreadAuthor:
						if (sortOrder == SortOrder.Ascending)
							orderClause.Append("PostAuthor DESC");
						else
							orderClause.Append("PostAuthor");
						break;

					case SortThreadsBy.TotalViews:
						if (sortOrder == SortOrder.Ascending)
							orderClause.Append("TotalViews");
						else
							orderClause.Append("TotalViews DESC");
						break;
				}
                #endregion

                // Build the SQL statements
                sqlCountSelect.Append(fromClause.ToString());
                sqlCountSelect.Append(whereClause.ToString());

                sqlPopulateSelect.Append(fromClause.ToString());
                sqlPopulateSelect.Append(whereClause.ToString());
                sqlPopulateSelect.Append(orderClause.ToString());

                // Add Parameters to SPROC
                //
                command.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
                command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = pageIndex;
                command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = pageSize;
                command.Parameters.Add("@sqlCount", SqlDbType.NVarChar, 4000).Value = sqlCountSelect.ToString();
                command.Parameters.Add("@sqlPopulate", SqlDbType.NVarChar, 4000).Value = sqlPopulateSelect.ToString();
                command.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserID;
                command.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = returnRecordCount;
                command.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                connection.Open();
                using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Populate the ThreadSet
                    //
                    while (dr.Read()) 
                    {

                        // Add threads
                        //
                        if (forumID == 0)
                            threadSet.Threads.Add( PopulatePrivateMessageFromIDataReader (dr) );
                        else
                            threadSet.Threads.Add( PopulateThreadFromIDataReader(dr) );

                    }

                    // Do we need to return record count?
                    //
                    if (returnRecordCount) 
                    {

                        dr.NextResult();

                        dr.Read();

                        // Read the total records
                        //
                        threadSet.TotalRecords = (int) dr[0];

                    }

                    // Get the recipients if this is a request for
                    // the private message list
                    if ((forumID == 0) && (dr.NextResult()) ) 
                    {
                        Hashtable recipientsLookupTable = new Hashtable();

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

                            if (recipientsLookupTable[threadID] == null) 
                            {
                                recipientsLookupTable[threadID] = new ArrayList();
                            }

                            ((ArrayList) recipientsLookupTable[threadID]).Add(CommonDataProvider.cs_PopulateUserFromIDataReader(dr) );
                        }

                        // Map recipients to the threads
                        //
                        foreach (PrivateMessage thread in threadSet.Threads) 
                        {
                            thread.Recipients = (ArrayList) recipientsLookupTable[thread.ThreadID];
                        }

                    }


                    dr.Close();
                }
                connection.Close();

                return threadSet;
            }
        }

        #endregion

		#region Threads Read
		public override HybridDictionary GetThreadsRead (int sectionID, int userID) {

			HybridDictionary threadsRead = new HybridDictionary();

			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_threads_ThreadsRead", myConnection);
				SqlDataReader reader;
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add(this.SettingsIDParameter());
                
				myConnection.Open();
                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Have we marked all read?
                    reader.Read();
                    threadsRead.Add("ReadAfter", reader["ThreadID"]);

                    // Get the threads read for this section
                    reader.NextResult();
                    while (reader.Read()) 
                    {
                        threadsRead.Add( reader["ThreadID"], reader["ThreadID"]);
                    }
                    reader.Close();
                }

				myConnection.Close();

				return threadsRead;
			}		
		}
		#endregion

        public override SearchResultSet GetSearchResults(SearchQuery query, SearchTerms terms) 
        {

           string searchSQL =  SqlGenerator.SearchText(query,terms,GetSettingsID(),ApplicationType.Forum);//,out searchSQL,out recordCount);

            SearchResultSet result = new SearchResultSet();
            DateTime searchDuration;
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand("cs_forums_Search", connection);
                SqlDataReader reader;

                // Mark as stored procedure
                command.CommandType = CommandType.StoredProcedure;

                // Add parameters
                command.Parameters.Add("@SearchSQL", SqlDbType.NVarChar, 4000).Value = searchSQL;
                //command.Parameters.Add("@RecordCountSQL", SqlDbType.NVarChar, 4000).Value = recordCount;
                command.Parameters.Add("@PageIndex", SqlDbType.Int).Value = query.PageIndex;
                command.Parameters.Add("@PageSize", SqlDbType.Int).Value = query.PageSize;
                command.Parameters.Add(this.SettingsIDParameter());

                connection.Open();
                using(reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Process first record set 
                    //
                    while (reader.Read()) 
                    {
                        ForumPost p = new ForumPost();

                        CommonDataProvider.PopulatePostFromIDataReader(reader,p);

                        result.Posts.Add(p);

                    }

                    // Move to the next result
                    //
                    if (reader.NextResult()) 
                    {
                        reader.Read();
                        result.TotalRecords = Convert.ToInt32(reader[0]);
                    }

                    // Get the duration of the search?
                    //
                    if (reader.NextResult()) 
                    {
                        reader.Read();

                        searchDuration = (DateTime) reader["Duration"];

                        // Calculate the number of seconds it took the search to execute
                        //
                        int ms = Convert.ToInt32(searchDuration.ToString("ff"));
                        result.SearchDuration = (double) ms / 1000;
                    }

                    reader.Close();
                }

                connection.Close();

                return result;
            }
        }


        public override PostSet SearchReindexPosts (int setsize, int settingsID) 
        {
            PostSet postSet = new PostSet();

            using(SqlConnection myConnection = GetSqlConnection()) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Search_PostReindex", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@RowCount", SqlDbType.Int).Value = setsize;
                myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;
                
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult)) 
                {
                    
                    while(dr.Read())
                    {
                        ForumPost fp = new ForumPost();
						CommonDataProvider.PopulateIndexPostFromIDataReader(dr,fp);
                        postSet.Posts.Add(fp);
                    }

                    dr.Close();
                }

                // Close the connection
                myConnection.Close();
                return postSet;
            }
        }

	
	/// <summary>
        /// Returns count of all posts in system
        /// </summary>
        /// <returns></returns>
        public override int GetTotalPostCount() 
        {
            int totalPostCount = 0;

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() )