webforumsoledbdataprovider.cs

微软的.NET论坛的源代码（COOL!!!）

        }




        /// <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 Post AddPost(Post PostToAdd) {
            // Get information about the user and forum
            OleDbConnection myConnection = new OleDbConnection(Globals.DatabaseConnectionString);
            myConnection.Open();

            // check for any duplicate messages
            String strSQL = "";

            OleDbParameter parameterUserName = new OleDbParameter("@Username", OleDbType.VarChar, 50);
            parameterUserName.Value = PostToAdd.Username;

            OleDbParameter parameterBody = new OleDbParameter("@Body", OleDbType.LongVarChar);
            parameterBody.Value = PostToAdd.Body;

            // see if we have dups, if needed
            if (!Globals.AllowDuplicatePosts) {
                strSQL = "SELECT COUNT(*) FROM [Posts] WHERE [Username] = @Username AND [Body] = @Body";
                OleDbCommand checkForDupsCommand = new OleDbCommand(strSQL, myConnection);

                checkForDupsCommand.CommandType = CommandType.Text;

                checkForDupsCommand.Parameters.Add(parameterUserName);
                checkForDupsCommand.Parameters.Add(parameterBody);


                if (((int) checkForDupsCommand.ExecuteScalar()) > 0) {
                    // we have a dup
                    myConnection.Close();

                    throw new PostDuplicateException("Attempting to insert a duplicate post.");
                }

                checkForDupsCommand.Parameters.Clear();			// clear the parameters
            }


            User user = this.GetUserInfo(PostToAdd.Username, myConnection);
            Forum forum;
			
			
            // how are we going to get the forum information?
            if (PostToAdd.ForumID == 0 && PostToAdd.ParentID != 0)
                // we need to get the forum ID from replyToPostID
                forum = this.GetForumInfoByPostID(PostToAdd.ParentID, myConnection);
            else
                // get the forum information by forumID
                forum = this.GetForumInfo(PostToAdd.ForumID, myConnection);


            bool ModeratedForum = forum.Moderated;
            int newID, threadID;

            bool ApprovedPost = true;
            if (ModeratedForum)
                // this is a moderated forum, so the post is not approved if the user is not trusted
                if (!user.Trusted)
                    ApprovedPost = false;

            OleDbParameter parameterForumId = new OleDbParameter("@ForumID", OleDbType.Integer, 4);
            parameterForumId.Value = PostToAdd.ForumID;

            OleDbParameter parameterApproved = new OleDbParameter("@ApprovedPost", OleDbType.Boolean, 1);
            parameterApproved.Value = ApprovedPost;

            OleDbParameter parameterSubject = new OleDbParameter("@Subject", OleDbType.VarChar, 50);
            parameterSubject.Value = PostToAdd.Subject;
			
            if (PostToAdd.ParentID == 0) {
                // we've got a new post here
                strSQL = "INSERT INTO [Posts] ([ForumID], [ThreadID], [ParentID], [PostLevel], [SortOrder], [PostDate], [Subject], [Username],[Approved], [Body]) " +
                    "VALUES (@ForumID, 0, 0, 1, 1, Now(), @Subject, @Username, @ApprovedPost,  @Body)";
				
                OleDbCommand myCommandNewPost = new OleDbCommand(strSQL, myConnection);
                myCommandNewPost.CommandType = CommandType.Text;

                myCommandNewPost.Parameters.Add(parameterForumId);				
                myCommandNewPost.Parameters.Add(parameterSubject);
                myCommandNewPost.Parameters.Add(parameterUserName);
                myCommandNewPost.Parameters.Add(parameterApproved);
                myCommandNewPost.Parameters.Add(parameterBody);

                // insert the record
                myCommandNewPost.ExecuteNonQuery();

                // get the ID of the just inserted record
                strSQL = "SELECT @@IDENTITY AS NewPost FROM [Posts]";

                myCommandNewPost.Parameters.Clear();
                myCommandNewPost.CommandText = strSQL;
                threadID = newID = (int) myCommandNewPost.ExecuteScalar();

                strSQL = "UPDATE [Posts] SET [ThreadID] = @NewPostID, [ParentID] = @NewPostID WHERE [PostID] = @NewPostID";
                myCommandNewPost.CommandText = strSQL;
                myCommandNewPost.Parameters.Clear();

                OleDbParameter parameterNewID = new OleDbParameter("@NewPostID", OleDbType.Integer, 4);
                parameterNewID.Value = newID;
                myCommandNewPost.Parameters.Add(parameterNewID);

                myCommandNewPost.ExecuteNonQuery();				
            } 
            else {
                // we are adding a reply to an existing post

                // get information about the parent post
                Post parentPost = this.GetPost(PostToAdd.ParentID, myConnection);
                parameterForumId.Value = parentPost.ForumID;		// update the forumID parameter

                // find the next post at the same level or higher (if it exists)
                strSQL = "SELECT Min([SortOrder]) AS NextSortOrder FROM [Posts] WHERE [PostLevel] <= @ParentLevel AND [SortOrder] > @ParentSortOrder AND [ThreadID] = @ThreadID";

                OleDbCommand myCommandPostReply = new OleDbCommand(strSQL, myConnection);
                myCommandPostReply.CommandType = CommandType.Text;

                OleDbParameter parameterParentLevel = new OleDbParameter("@ParentLevel", OleDbType.Integer, 4);
                parameterParentLevel.Value = parentPost.PostLevel;
                myCommandPostReply.Parameters.Add(parameterParentLevel);

                OleDbParameter parameterParentSortOrder = new OleDbParameter("@ParentSortOrder", OleDbType.Integer, 4);
                parameterParentSortOrder.Value = parentPost.SortOrder;
                myCommandPostReply.Parameters.Add(parameterParentSortOrder);

                OleDbParameter parameterParentThreadID = new OleDbParameter("@ThreadID", OleDbType.Integer, 4);
                parameterParentThreadID.Value = parentPost.ThreadID;
                myCommandPostReply.Parameters.Add(parameterParentThreadID);

                Object objMinSortOrder = myCommandPostReply.ExecuteScalar();
                int MinSortOrder;
                if (Convert.IsDBNull(objMinSortOrder) || objMinSortOrder == null) {
                    strSQL = "SELECT Max(SortOrder) AS MaxSortOrder FROM [Posts] WHERE [ThreadID] = @ThreadID";

                    myCommandPostReply.CommandText = strSQL;
                    myCommandPostReply.Parameters.Clear();
                    myCommandPostReply.Parameters.Add(parameterParentThreadID);

                    MinSortOrder = (int) myCommandPostReply.ExecuteScalar() + 1;
                } 
                else {					
                    MinSortOrder = (int) objMinSortOrder;

                    // move the existing posts down
                    strSQL = "UPDATE [Posts] SET [SortOrder] = [SortOrder] + 1 WHERE [ThreadID] = @ThreadID AND [SortOrder] >= @NextSortOrder";
                    myCommandPostReply.CommandText = strSQL;
                    myCommandPostReply.Parameters.Clear();

                    myCommandPostReply.Parameters.Add(parameterParentThreadID);
					
                    OleDbParameter parameterNextSortOrder = new OleDbParameter("@NextSortOrder", OleDbType.Integer, 4);
                    parameterNextSortOrder.Value = MinSortOrder;
                    myCommandPostReply.Parameters.Add(parameterNextSortOrder);

                    myCommandPostReply.ExecuteNonQuery();					
                }

                // Now insert the post
                strSQL = "INSERT INTO [Posts] ([ForumID], [ThreadID], [ParentID], [PostLevel], [SortOrder], [Subject], [PostDate], [Username], [Approved], [Body]) " +
                    "VALUES (@ForumID, @ThreadID, @ReplyToPostID, @ParentLevel, @NextSortOrder, @Subject, Now(), @Username, @ApprovedPost, @Body)";

                myCommandPostReply.CommandText = strSQL;
                myCommandPostReply.CommandType = CommandType.Text;

                myCommandPostReply.Parameters.Clear();
                myCommandPostReply.Parameters.Add(parameterForumId);
                myCommandPostReply.Parameters.Add(parameterParentThreadID);

                OleDbParameter parameterReplyToPostID = new OleDbParameter("@ReplyToPostID", OleDbType.Integer, 4);
                parameterReplyToPostID.Value = PostToAdd.ParentID;
                myCommandPostReply.Parameters.Add(parameterReplyToPostID);

                parameterParentLevel.Value = (int) parameterParentLevel.Value + 1;
                myCommandPostReply.Parameters.Add(parameterParentLevel);

                OleDbParameter parameterProperSortOrder = new OleDbParameter("@NextSortOrder", OleDbType.Integer, 4);
                parameterProperSortOrder.Value = MinSortOrder;
                myCommandPostReply.Parameters.Add(parameterProperSortOrder);

                myCommandPostReply.Parameters.Add(parameterSubject);				
                myCommandPostReply.Parameters.Add(parameterUserName);
                myCommandPostReply.Parameters.Add(parameterApproved);
                myCommandPostReply.Parameters.Add(parameterBody);
				
                myCommandPostReply.ExecuteNonQuery();			// insert the record


                // get the ID of the just inserted record
                strSQL = "SELECT @@IDENTITY AS NewPost FROM [Posts]";

                myCommandPostReply.CommandText = strSQL;
                myCommandPostReply.Parameters.Clear();
                newID = (int) myCommandPostReply.ExecuteScalar();

                // if this message is approved, update the threaddate
                if (ApprovedPost) {
                    strSQL = "UPDATE [Posts] SET [ThreadDate] = Now() WHERE [ThreadID] = @ThreadID";

                    myCommandPostReply.CommandText = strSQL;
                    myCommandPostReply.Parameters.Clear();
                    myCommandPostReply.Parameters.Add(parameterParentThreadID);
                    myCommandPostReply.ExecuteNonQuery();
                }

                threadID = parentPost.ThreadID;
            }

            // if the user wants to track this thread, add a row to threadtrackings, if necessary
            if (user.TrackPosts) {
                strSQL = "SELECT COUNT(*) FROM [ThreadTrackings] WHERE [ThreadID] = @ThreadID AND [Username] = @Username";
                OleDbCommand myCommandTrackPosts = new OleDbCommand(strSQL, myConnection);
                myCommandTrackPosts.CommandType = CommandType.Text;

                OleDbParameter parameterGlobalThreadID = new OleDbParameter("@ThreadID", OleDbType.Integer, 4);
                parameterGlobalThreadID.Value = threadID;
                myCommandTrackPosts.Parameters.Add(parameterGlobalThreadID);

                myCommandTrackPosts.Parameters.Add(parameterUserName);

                int exists = (int) myCommandTrackPosts.ExecuteScalar();

                if (exists == 0) {
                    // need to add a new row
                    strSQL = "INSERT INTO [ThreadTrackings] ([ThreadID], [Username]) VALUES (@ThreadID, @Username)";

                    myCommandTrackPosts.CommandText = strSQL;
                    myCommandTrackPosts.Parameters.Clear();
                    myCommandTrackPosts.Parameters.Add(parameterGlobalThreadID);
                    myCommandTrackPosts.Parameters.Add(parameterUserName);

                    myCommandTrackPosts.ExecuteNonQuery();
                }
            }

            // Return a Post instance with info from the newly inserted post.
            Post post = GetPost(newID, myConnection);

            myConnection.Close();

            return post;
        }

		

        /// <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 void UpdatePost(Post UpdatedPost) {
            String strSQL = "UPDATE [Posts] SET [Subject] = @Subject, [Body] = @Body WHERE [PostID] = @PostID";

            // Create Instance of Connection and Command Object