/// <summary> /// Returns a collection of Posts that make up a particular thread. /// </summary> /// <param name="ThreadID">The ID of the Thread to retrieve the posts of.</param> /// <returns>A PostCollection object that contains the posts in the thread specified by /// ThreadID.</returns> public PostCollection GetThread(int ThreadID) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString); SqlCommand myCommand = new SqlCommand("dbo.forums_GetThread", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterThreadId = new SqlParameter("@ThreadID", SqlDbType.Int, 4); parameterThreadId.Value = ThreadID; myCommand.Parameters.Add(parameterThreadId); // Execute the command myConnection.Open(); SqlDataReader dr = myCommand.ExecuteReader(); // loop through the results PostCollection posts = new PostCollection(); while (dr.Read()) { posts.Add(PopulatePostFromSqlDataReader(dr)); } dr.Close(); myConnection.Close(); return posts; }
/// <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 PostCollection GetThreadByPostID(int postID, int currentPageIndex, int pageSize, string username) { SqlParameter param; // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString); SqlCommand myCommand = new SqlCommand("dbo.forums_GetThreadByPostIDPaged", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // PostID Parameter param = new SqlParameter("@PostID", SqlDbType.Int, 4); param.Value = postID; myCommand.Parameters.Add(param); // CurrentPage Parameter param = new SqlParameter("@PageIndex", SqlDbType.Int); param.Value = currentPageIndex; myCommand.Parameters.Add(param); // PageSize Parameter param = new SqlParameter("@PageSize", SqlDbType.Int, 4); param.Value = pageSize; myCommand.Parameters.Add(param); // Username if ( (username == null) || (username == String.Empty)) myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = System.DBNull.Value; else myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = int.Parse(username); //myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username; // Execute the command myConnection.Open(); SqlDataReader dr = myCommand.ExecuteReader(); // loop through the results PostCollection posts = new PostCollection(); while (dr.Read()) { Post p = PopulatePostFromSqlDataReader(dr); //p.PostType = (Posts.PostType) dr["PostType"]; posts.Add(p); } dr.Close(); myConnection.Close(); return posts; }
/*********************************************************************************/ /************************ SEARCH FUNCTIONS *********************** * These functions are used to perform searching. * ***************************************************************/ /// <summary> /// Performs a search, returning a PostCollection object with appropriate posts. /// </summary> /// <param name="ToSearch">Specifies what to search, specifically. Must be set to a valid /// ToSearchEnum value, which supports two possible values: PostsSearch and PostsBySearch.</param> /// <param name="SearchWhat">A SearchWhatEnum value, this parameter specifies what to search. /// Acceptable values are: SearchAllWords, SearchAnyWord, and SearchExactPhrase.</param> /// <param name="ForumToSearch">Specifies what forum to search. To search all forums, pass in a /// value of 0.</param> /// <param name="SearchTerms">Specifies the terms to search on.</param> /// <param name="Page">Specifies what page of the search results to display.</param> /// <param name="RecsPerPage">Specifies how many records per page to show on the search /// results.</param> /// <returns>A PostCollection object, containing the posts to display for the particular page /// of the search results.</returns> public PostCollection GetSearchResults(ToSearchEnum ToSearch, SearchWhatEnum SearchWhat, int ForumToSearch, String SearchTerms, int Page, int RecsPerPage, string username) { // return all of the forums and their total and daily posts // first, though, we've got to put our search phrase in the right order String strColumnName = ""; String strWhereClause = " WHERE ("; String [] aTerms = null; // Are we searching for a particular user? if (ToSearch == ToSearchEnum .PostsSearch) { strColumnName = "Body"; // depending on the search style, our WHERE clause will differ switch(SearchWhat) { case SearchWhatEnum.SearchExactPhrase: // easy, we want to search for the exact search term strWhereClause += strColumnName + " LIKE '%" + SearchTerms + "%' "; break; case SearchWhatEnum.SearchAllWords: // allrighty, we want to find rows where each word is found // split up the search term string into an array aTerms = SearchTerms.Split(new char[]{' '}); // now, loop through the aTerms array strWhereClause += strColumnName + " LIKE '%" + String.Join("%' AND " + strColumnName + " LIKE '%", aTerms) + "%'"; break; case SearchWhatEnum.SearchAnyWord: // allrighty, we want to find rows where each word is found // split up the search term string into an array aTerms = SearchTerms.Split(new char[]{' '}); // now, loop through the aTerms array strWhereClause += strColumnName + " LIKE '%" + String.Join("%' OR " + strColumnName + " LIKE '%", aTerms) + "%'"; break; } strWhereClause += ")"; } else if (ToSearch == ToSearchEnum.PostsBySearch) { strColumnName = "UserName"; strWhereClause += strColumnName + " = '" + SearchTerms + "') "; } // see if we need to add a restriction on the ForumID if (ForumToSearch > 0) strWhereClause += " AND P.ForumID = " + ForumToSearch.ToString() + " "; // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString); SqlCommand myCommand = new SqlCommand("dbo.forums_GetSearchResults", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; SqlParameter parameterPage = new SqlParameter("@Page", SqlDbType.Int, 4); parameterPage.Value = Page; myCommand.Parameters.Add(parameterPage); SqlParameter parameterRecsPerPage = new SqlParameter("@RecsPerPage", SqlDbType.Int, 4); parameterRecsPerPage.Value = RecsPerPage; myCommand.Parameters.Add(parameterRecsPerPage); SqlParameter parameterSearchTerms = new SqlParameter("@SearchTerms", SqlDbType.NVarChar, 500); parameterSearchTerms.Value = strWhereClause; myCommand.Parameters.Add(parameterSearchTerms); if ( (username == null) || (username == String.Empty)) myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = System.DBNull.Value; else myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = int.Parse(username); //myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username; // Execute the command myConnection.Open(); SqlDataReader dr = myCommand.ExecuteReader(); // populate the Posts collection PostCollection posts = new PostCollection(); if (!dr.Read()) { dr.Close(); myConnection.Close(); // we have an empty result, return the empty post collection return posts; } else { // we have to populate our postcollection posts.TotalRecordCount = Convert.ToInt32(dr["MoreRecords"]); do { posts.Add(PopulatePostFromSqlDataReader(dr)); ((Post) posts[posts.Count - 1]).ForumName = Convert.ToString(dr["ForumName"]); } while (dr.Read()); dr.Close(); myConnection.Close(); return posts; } }