webforumsoledbdataprovider.cs

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

            post.ThreadDate = Convert.ToDateTime(dr["ThreadDate"]);
            post.ThreadID = Convert.ToInt32(dr["ThreadID"]);
            post.Replies = Convert.ToInt32(dr["Replies"]);
            post.Username = Convert.ToString(dr["Username"]);
			
            // populate information about the User
            User user = new User();
            user.Username = post.Username;
            user.FakeEmail = Convert.ToString(dr["FakeEmail"]);
            user.Url = Convert.ToString(dr["URL"]);
            user.Signature = Convert.ToString(dr["Signature"]);
			
            post.UserInfo = user;

            dr.Close();

            // Now that we've closed the datareader, reuse the connection object to find the next/prev threads/posts
            post.NextPostID = GetNextPostID(post.ForumID, post.ThreadID, post.SortOrder, myConnection);
            post.PrevPostID = GetPrevPostID(post.ForumID, post.ThreadID, post.SortOrder, myConnection);
            post.NextThreadID = GetNextThreadID(post.ForumID, post.ThreadID, myConnection);
            post.PrevThreadID = GetPrevThreadID(post.ForumID, post.ThreadID, myConnection);
            post.ThreadTracking = this.GetThreadTrackingForUser(post.ThreadID, post.Username, myConnection);

            myConnection.Close();

            return post;
        }



        /// <summary>
        /// Get basic information about a single post.  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="PostID">The ID of the post whose information we are interested in.</param>
        /// <returns>An instance of the Post class.</returns>
        /// <remarks>If a PostID is passed in that is NOT found in the database, a PostNotFoundException
        /// exception is thrown.</remarks>
        public  Post GetPost(int PostID, string username, bool trackViews) {
            String strSQL = "SELECT [Subject],	[PostID], [UserName], P.[ForumID], (SELECT [Name] FROM [Forums] AS F WHERE F.[ForumID] = P.[ForumID]), " +
                "[ParentID], [ThreadID], [Approved], [PostDate], [PostLevel], [SortOrder], [ThreadDate], (SELECT COUNT(*) FROM [Posts] AS P2 WHERE P2.[ParentID] = P.[PostID] AND P2.[PostLevel] <> 1) AS [Replies], [Body] " +
                "FROM [Posts] AS P WHERE P.[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(CommandBehavior.CloseConnection);

            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	
            Post post = PopulatePostFromOleDbDataReader(dr);

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

            return post;
        }


        /// <summary>
        /// Get basic information about a single post.  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="PostID">The ID of the post whose information we are interested in.</param>
        /// <param name="myConnection">An open OleDbConnection object.</param>
        /// <returns>An instance of the Post class.</returns>
        /// <remarks>If a PostID is passed in that is NOT found in the database, a PostNotFoundException
        /// exception is thrown.  This form of GetPost should be used when we already have an openned
        /// OleDbConnection object.  If this is not the case, use the other form of the GetPost method.</remarks>
        public  Post GetPost(int PostID, OleDbConnection myConnection) {
            String strSQL = "SELECT [Subject],	[PostID], [UserName], P.[ForumID], (SELECT [Name] FROM [Forums] AS F WHERE F.[ForumID] = P.[ForumID]), " +
                "[ParentID], [ThreadID], [Approved], [PostDate], [PostLevel], [SortOrder], [ThreadDate], (SELECT COUNT(*) FROM [Posts] AS P2 WHERE P2.[ParentID] = P.[PostID] AND P2.[PostLevel] <> 1) AS [Replies], [Body] " +
                "FROM [Posts] AS P WHERE P.[PostID] = @PostID";

            // Create Instance of Connection and Command Object
            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
            OleDbDataReader dr = myCommand.ExecuteReader();

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

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

            // we have a post to work with	
            Post post = PopulatePostFromOleDbDataReader(dr);

            dr.Close();
            return post;
        }


	
        /// <summary>
        /// Reverses a particular user's email thread tracking options for the thread that contains
        /// the post specified by PostID.  That is, if a User has email thread tracking turned on for
        /// a particular thread, a call to this method will turn off the email thread tracking; conversely,
        /// if a user has thread tracking turned off for a particular thread, a call to this method will
        /// turn it on.
        /// </summary>
        /// <param name="Username">The User whose email thread tracking options we wish to reverse.</param>
        /// <param name="PostID"></param>
        public  void ReverseThreadTracking(String Username, int PostID) {
            // Create Instance of Connection and Command Object
            OleDbConnection myConnection = new OleDbConnection(Globals.DatabaseConnectionString);
            myConnection.Open();

            Post post = this.GetPost(PostID, myConnection);			// get the threadID for the specific post
            int threadID = post.ThreadID;

            String strSQL = "SELECT COUNT(*) FROM [ThreadTrackings] WHERE [ThreadID] = @ThreadID AND [Username] = @Username";


            OleDbCommand myCommand = new OleDbCommand(strSQL, myConnection);

            // Mark the Command as a SPROC
            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("@Username", OleDbType.VarChar, 50);
            parameterUsername.Value = Username;
            myCommand.Parameters.Add(parameterUsername);


            // Execute the command
            if ((int) myCommand.ExecuteScalar() == 0) {
                // we need to add the thread tracking to the table
                strSQL = "INSERT INTO [ThreadTrackings] ([ThreadID], [Username]) VALUES (@ThreadID, @Username)";

                myCommand.CommandText = strSQL;
                myCommand.Parameters.Clear();

                myCommand.Parameters.Add(parameterThreadId);
                myCommand.Parameters.Add(parameterUsername);

                myCommand.ExecuteNonQuery();
            }
            else {
                // we need to remove the thread tracking to the table
                strSQL = "DELETE FROM [ThreadTrackings] WHERE [ThreadID] = @ThreadID AND [Username] = @Username";

                myCommand.CommandText = strSQL;
                myCommand.Parameters.Clear();

                myCommand.Parameters.Add(parameterThreadId);
                myCommand.Parameters.Add(parameterUsername);

                myCommand.ExecuteNonQuery();
            }

            myConnection.Close();
        }



        /// <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) {
            String strSQL = "SELECT [PostID], [ForumID], [Subject], [ParentID], [ThreadID], [PostLevel], [SortOrder], [PostDate], [ThreadDate], [UserName], (SELECT COUNT(*) FROM [Posts] AS P2 WHERE P2.[ParentID] = P.[PostID] AND P2.[PostLevel] <> 1) AS [Replies], " +
                "[Body] FROM [Posts] AS P WHERE [Approved] = YES AND [ThreadID] = @ThreadID ORDER BY [SortOrder]";

            // 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 parameterThreadId = new OleDbParameter("@ThreadID", OleDbType.Integer, 4);
            parameterThreadId.Value = ThreadID;
            myCommand.Parameters.Add(parameterThreadId);

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

            // loop through the results
            PostCollection posts = new PostCollection();
            while (dr.Read()) {
                posts.Add(PopulatePostFromOleDbDataReader(dr));
            }
            dr.Close();
            myConnection.Close();

            return posts;
        }


        public PostCollection GetThreadByPostID(int postID, int currentPageIndex, int pageSize) {
            // TODO
            return null;
        }
		
        /// <summary>
        /// Returns a collection of Posts that make up a particular thread.
        /// </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) {
            String strSQL = "SELECT [PostID], [ThreadID], [ForumID], [Subject], [ParentID],	[PostLevel], [SortOrder], [PostDate], [ThreadDate], [UserName], [Approved], (SELECT COUNT(*) FROM [Posts] P2 WHERE P2.[ParentID] = P.[PostID] AND P2.[PostLevel] <> 1) AS Replies, [Body] " +
                "FROM [Posts] P WHERE [Approved]=YES AND [ThreadID] = (SELECT [ThreadID] FROM [Posts] WHERE [PostID] = @PostID) ORDER BY [SortOrder]";

            // 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(CommandBehavior.CloseConnection);

            // loop through the results
            PostCollection posts = new PostCollection();
            while (dr.Read()) {
                posts.Add(PopulatePostFromOleDbDataReader(dr));
            }

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

            return posts;