webforumsoledbdataprovider.cs

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

            myCommand.Parameters.Add(parameterForumId);

            // Execute the command
            OleDbDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            PostCollection posts = new PostCollection();
            while (dr.Read()) {
                posts.Add(PopulatePostFromOleDbDataReader(dr));
            }
            dr.Close();
            myConnection.Close();

            return posts;
        }

        /// <summary>
        /// Given a particular ThreadID, this method returns the ID of the next thread in the specified forum.
        /// </summary>
        /// <param name="ForumID">The ForumID of the post we are viewing.</param>
        /// <param name="ThreadID">The ThreadID of the post we are viewing.</param>
        /// <param name="myConnection">An open OleDbConnection object.</param>
        /// <returns>The PostID of the first post in the next thread.</returns>
        /// <remarks>If there is no next thread, 0 is returned.</remarks>
        private int GetNextThreadID(int ForumID, int ThreadID, OleDbConnection myConnection) {
            String strSQL = "SELECT TOP 1 [ThreadID] FROM [Posts] WHERE [ThreadID] > @ThreadID AND [ForumID] = @ForumID AND [Approved] = YES ORDER BY [ThreadID] DESC";

            // Create Instance of Connection and Command Object
            OleDbCommand myCommand = new OleDbCommand(strSQL, myConnection);
            myCommand.CommandType = CommandType.Text;

            // Add Parameters to SPROC
            OleDbParameter parameterThreadId = new OleDbParameter("@ThreadID", OleDbType.Integer, 4);
            parameterThreadId.Value = ThreadID;
            myCommand.Parameters.Add(parameterThreadId);
			
            OleDbParameter parameterForumID = new OleDbParameter("@ForumID", OleDbType.Integer, 4);
            parameterForumID.Value = ForumID;
            myCommand.Parameters.Add(parameterForumID);

            Object returnResult = myCommand.ExecuteScalar();

            if (Convert.IsDBNull(returnResult) || returnResult == null)
                return 0;
            else
                return (int) returnResult;
        }


        /// <summary>
        /// Given a particular ThreadID, this method returns the ID of the previous thread in the specified forum.
        /// </summary>
        /// <param name="ForumID">The ForumID of the post we are viewing.</param>
        /// <param name="ThreadID">The ThreadID of the post we are viewing.</param>
        /// <param name="myConnection">An open OleDbConnection object.</param>
        /// <returns>The PostID of the first post in the previous thread.</returns>
        /// <remarks>If there is no previous thread, 0 is returned.</remarks>
        private int GetPrevThreadID(int ForumID, int ThreadID, OleDbConnection myConnection) {
            String strSQL = "SELECT TOP 1 [ThreadID] FROM [Posts] WHERE [ThreadID] < @ThreadID AND [ForumID] = @ForumID AND [Approved] = YES ORDER BY [ThreadID] DESC";

            // Create Instance of Connection and Command Object
            OleDbCommand myCommand = new OleDbCommand(strSQL, myConnection);
            myCommand.CommandType = CommandType.Text;

            // Add Parameters to SPROC
            OleDbParameter parameterThreadId = new OleDbParameter("@ThreadID", OleDbType.Integer, 4);
            parameterThreadId.Value = ThreadID;
            myCommand.Parameters.Add(parameterThreadId);
			
            OleDbParameter parameterForumID = new OleDbParameter("@ForumID", OleDbType.Integer, 4);
            parameterForumID.Value = ForumID;
            myCommand.Parameters.Add(parameterForumID);
			
            Object returnResult = myCommand.ExecuteScalar();

            if (Convert.IsDBNull(returnResult) || returnResult == null)
                return 0;
            else
                return (int) returnResult;
        }


		
		
        /// <summary>
        /// Given a particular ThreadID, this method returns the ID of the previous post in the thread.
        /// </summary>
        /// <param name="ForumID">The ForumID of the post we are viewing.</param>
        /// <param name="ThreadID">The ThreadID of the post we are viewing.</param>
        /// <param name="myConnection">An open OleDbConnection object.</param>
        /// <returns>The PostID of the previous post in the thread.</returns>
        /// <remarks>If there is no previous post in the thread, 0 is returned.</remarks>
        private int GetPrevPostID(int ForumID, int ThreadID, int SortOrder, OleDbConnection myConnection) {
            String strSQL = "SELECT TOP 1 [PostID] FROM [Posts] WHERE [ThreadID] = @ThreadID AND [ForumID] = @ForumID AND [SortOrder] = @SortOrder-1 AND [Approved] = YES";

            // Create Instance of Connection and Command Object
            OleDbCommand myCommand = new OleDbCommand(strSQL, myConnection);
            myCommand.CommandType = CommandType.Text;

            // Add Parameters to SPROC
            OleDbParameter parameterThreadId = new OleDbParameter("@ThreadID", OleDbType.Integer, 4);
            parameterThreadId.Value = ThreadID;
            myCommand.Parameters.Add(parameterThreadId);
			
            OleDbParameter parameterForumID = new OleDbParameter("@ForumID", OleDbType.Integer, 4);
            parameterForumID.Value = ForumID;
            myCommand.Parameters.Add(parameterForumID);

            OleDbParameter parameterSortOrder = new OleDbParameter("@SortOrder", OleDbType.Integer, 4);
            parameterSortOrder.Value = SortOrder;
            myCommand.Parameters.Add(parameterSortOrder);

            Object returnResult = myCommand.ExecuteScalar();

            if (Convert.IsDBNull(returnResult) || returnResult == null)
                return 0;
            else
                return (int) returnResult;
        }


		
        /// <summary>
        /// Given a particular ThreadID, this method returns the ID of the next post in the thread.
        /// </summary>
        /// <param name="ForumID">The ForumID of the post we are viewing.</param>
        /// <param name="ThreadID">The ThreadID of the post we are viewing.</param>
        /// <param name="myConnection">An open OleDbConnection object.</param>
        /// <returns>The PostID of the next post in the thread.</returns>
        /// <remarks>If there is no next post in the thread, 0 is returned.</remarks>
        private int GetNextPostID(int ForumID, int ThreadID, int SortOrder, OleDbConnection myConnection) {
            String strSQL = "SELECT TOP 1 [PostID] FROM [Posts] WHERE [ThreadID] = @ThreadID AND [ForumID] = @ForumID AND [SortOrder] = @SortOrder+1 AND [Approved] = YES";

            // Create Instance of Connection and Command Object
            OleDbCommand myCommand = new OleDbCommand(strSQL, myConnection);
            myCommand.CommandType = CommandType.Text;

            // Add Parameters to SPROC
            OleDbParameter parameterThreadId = new OleDbParameter("@ThreadID", OleDbType.Integer, 4);
            parameterThreadId.Value = ThreadID;
            myCommand.Parameters.Add(parameterThreadId);
			
            OleDbParameter parameterForumID = new OleDbParameter("@ForumID", OleDbType.Integer, 4);
            parameterForumID.Value = ForumID;
            myCommand.Parameters.Add(parameterForumID);

            OleDbParameter parameterSortOrder = new OleDbParameter("@SortOrder", OleDbType.Integer, 4);
            parameterSortOrder.Value = SortOrder;
            myCommand.Parameters.Add(parameterSortOrder);

            Object returnResult = myCommand.ExecuteScalar();

            if (Convert.IsDBNull(returnResult) || returnResult == null)
                return 0;
            else
                return (int) returnResult;
        }


		
        /// <summary>
        /// Given a particular ThreadID, this method returns a Boolean indicating whether or not the
        /// user viewing the post is signed up to receive email notification when new messages are
        /// posted to the thread.
        /// </summary>
        /// <param name="ThreadID">The ThreadID of the post we are viewing.</param>
        /// <param name="Username">The Username of the user viewing the post.</param>
        /// <param name="myConnection">An open OleDbConnection object.</param>
        /// <returns>A Boolean indicating whether or not the user is signed up to receive email notifications
        /// when new posts are made to this thread.</returns>
        private bool GetThreadTrackingForUser(int ThreadID, String Username, OleDbConnection myConnection) {
            String strSQL = "SELECT [ThreadID] FROM [ThreadTrackings] WHERE [ThreadID] = @ThreadID AND [UserName] = @UserName";

            // Create Instance of Connection and Command Object
            OleDbCommand myCommand = new OleDbCommand(strSQL, myConnection);
            myCommand.CommandType = CommandType.Text;

            // Add Parameters to SPROC
            OleDbParameter parameterThreadId = new OleDbParameter("@ThreadID", OleDbType.Integer, 4);
            parameterThreadId.Value = ThreadID;
            myCommand.Parameters.Add(parameterThreadId);
			
            OleDbParameter parameterUsername = new OleDbParameter("@ForumID", OleDbType.VarChar, 50);
            parameterUsername.Value = Username;
            myCommand.Parameters.Add(parameterUsername);

            Object returnResult = myCommand.ExecuteScalar();

            if (Convert.IsDBNull(returnResult) || returnResult == null)
                return false;
            else
                return true;
        }


        /// <summary>
        /// Returns count of all posts in system
        /// </summary>
        /// <returns></returns>
        public int GetTotalPostCount() {
            String strSQL = "SELECT Count(*) FROM Posts";
            int totalPostCount;

            // Create Instance of Connection and Command Object
            OleDbConnection myConnection = new OleDbConnection(Globals.DatabaseConnectionString);
            OleDbCommand myCommand = new OleDbCommand(strSQL, myConnection);

            // Execute the command
            myConnection.Open();
            OleDbDataReader dr = myCommand.ExecuteReader();		// Don't use CommandBehavior.CloseConnection b/c we need to reuse this connection

            dr.Read();
            totalPostCount = (int) dr[0];

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

            return totalPostCount;
        }

        /// <summary>
        /// Gets the details for a particular post.  These details include the IDs of the next/previous
        /// post and the next/prev thread, along with information about the user who posted the post.
        /// </summary>
        /// <param name="PostID">The ID of the Post to get the information from.</param>
        /// <param name="Username">The Username of the person viewing the post.  Used to determine if
        /// the particular user has email tracking turned on for the thread that this message resides.</param>
        /// <returns>A PostDetails instance with rich information about the particular post.</returns>
        /// <remarks>If a PostID is passed in that is NOT found in the database, a PostNotFoundException
        /// exception is thrown.</remarks>
        public  PostDetails GetPostDetails(int PostID, String Username) {

            String strSQL = "SELECT [Subject], [ForumID], [ThreadID], [ParentID], [PostLevel], [SortOrder], [PostDate], [ThreadDate], P.[UserName], U.[FakeEmail], U.[URL], U.[Signature], P.[Approved], " +
                "(SELECT COUNT(*) FROM [Posts] AS P2 WHERE P2.[ParentID] = P.[PostID] AND P2.[PostLevel] <> 1) AS [Replies],	[Body] FROM [Posts] AS P INNER JOIN [Users] AS U ON	U.[UserName] = P.[UserName] WHERE [PostID] = @PostID";

            // Create Instance of Connection and Command Object
            OleDbConnection myConnection = new OleDbConnection(Globals.DatabaseConnectionString);
            OleDbCommand myCommand = new OleDbCommand(strSQL, myConnection);

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

            // Add Parameters to SPROC
            OleDbParameter parameterPostId = new OleDbParameter("@PostID", OleDbType.Integer, 4);
            parameterPostId.Value = PostID;
            myCommand.Parameters.Add(parameterPostId);
			
            // Execute the command
            myConnection.Open();
            OleDbDataReader dr = myCommand.ExecuteReader();		// Don't use CommandBehavior.CloseConnection b/c we need to reuse this connection

            if (!dr.Read()) {
                // we did not get back a post
                dr.Close();
                myConnection.Close();

                throw new Components.PostNotFoundException("Did not get back a post for PostID " + PostID.ToString());
            }

            // we have a post to work with
            PostDetails post = new PostDetails();
            post.PostID = PostID;
            post.ParentID = Convert.ToInt32(dr["ParentID"]);
            post.Body = Convert.ToString(dr["Body"]);
            post.ForumID = Convert.ToInt32(dr["ForumID"]);
            post.PostDate = Convert.ToDateTime(dr["PostDate"]);
            post.PostLevel = Convert.ToInt32(dr["PostLevel"]);
            post.SortOrder = Convert.ToInt32(dr["SortOrder"]);
            post.Subject = Convert.ToString(dr["Subject"]);