webforumsoledbdataprovider.cs

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

            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 parameterSubject = new OleDbParameter("@Subject", OleDbType.VarChar, 50);
            parameterSubject.Value = UpdatedPost.Subject;
            myCommand.Parameters.Add(parameterSubject);

            OleDbParameter parameterBody = new OleDbParameter("@Body", OleDbType.LongVarWChar);
            parameterBody.Value = UpdatedPost.Body;
            myCommand.Parameters.Add(parameterBody);

            OleDbParameter parameterPostId = new OleDbParameter("@PostID", OleDbType.Integer, 4);
            parameterPostId.Value = UpdatedPost.PostID;
            myCommand.Parameters.Add(parameterPostId);

            // Execute the command
            myConnection.Open();
            try {
                myCommand.ExecuteNonQuery();
            } 
            catch (Exception e) {
                // oops, something went wrong
                throw new Exception(e.Message);
            }
            myConnection.Close();
        }


		
        /// <summary>
        /// This method deletes a particular post and all of its replies.
        /// </summary>
        /// <param name="postID">The PostID that you wish to delete.</param>
        public  void DeletePost(int PostID) {
            // Delete the post
            // Create Instance of Connection and Command Object
            OleDbConnection myConnection = new OleDbConnection(Globals.DatabaseConnectionString);
            myConnection.Open();
            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = myConnection;
            myCommand.CommandType = CommandType.Text;

            Post post = GetPost(PostID, myConnection);

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

            String strSQL = String.Empty;

            // determine if the post we wish to delete is the parent post
            if (post.ThreadID == PostID) {
                // delete all of the posts who have the same threadID
                // also delete all of the threadtrackings for this thread
                strSQL = "DELETE FROM [ThreadTrackings] WHERE [ThreadID] = @ThreadID";
                myCommand.Parameters.Add(parameterThreadId);

                myCommand.CommandText = strSQL;

                myCommand.ExecuteNonQuery();
            }


            // get the next sortorder
            strSQL = "SELECT IsNull(MIN([SortOrder], 10000) AS [NextSortOrder] FROM [Posts] WHERE " +
                "[ThreadID] = @ThreadID AND [SortOrder] > @SortOrder AND [PostLevel] = @PostLevel";
            myCommand.CommandText = strSQL;
            myCommand.Parameters.Clear();
            myCommand.Parameters.Add(parameterThreadId);

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

            OleDbParameter parameterPostLevel = new OleDbParameter("@PostLevel", OleDbType.Integer, 4);
            parameterPostLevel.Value = post.PostLevel;
            myCommand.Parameters.Add(parameterPostLevel);

            int nextSortOrder = Convert.ToInt32(myCommand.ExecuteScalar());


            // now delete all posts between post.SortOrder and nextSortOrder
            strSQL = "DELETE FROM [Posts] WHERE [ThreadID] = @ThreadID AND [SortOrder] >= @SortOrder " +
                "AND [SortOrder] < @NextSortOrder";
            myCommand.CommandText = strSQL;
            myCommand.Parameters.Clear();
            myCommand.Parameters.Add(parameterThreadId);
            myCommand.Parameters.Add(parameterSortOrder);
            
            OleDbParameter parameterNextSortOrder = new OleDbParameter("@NextSortOrder", OleDbType.Integer, 4);
            parameterNextSortOrder.Value = nextSortOrder;
            myCommand.Parameters.Add(parameterNextSortOrder);


            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }
        /*********************************************************************************/




        /*********************************************************************************/

        /************************ FORUM FUNCTIONS ***********************
                 * These functions return information about a forum.
                 * are called from the WebForums.Forums class.
                 * **************************************************************/
	
        /// <summary>
        /// Returns information about a particular forum that contains a particular thread.
        /// </summary>
        /// <param name="ThreadID">The ID of the thread that is contained in the Forum you wish to
        /// retrieve information about.</param>
        /// <returns>A Forum object instance containing the information about the Forum that the
        /// specified thread exists in.</returns>
        /// <remarks>If a ThreadID is passed in that is NOT found in the database, a ForumNotFoundException
        /// exception is thrown.</remarks>
        public  Forum GetForumInfoByThreadID(int ThreadID) {
            String strSQL = "SELECT [ForumGroupId], [ForumID], [Name], [Description], [DateCreated], [Moderated], [DaysToView], [Active] " +
                "FROM [Forums] WHERE [ForumID] = (SELECT DISTINCT [ForumID] FROM [Posts] WHERE [ThreadID] = @ThreadID)";

            // 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);

            if (!dr.Read()) {
                // we didn't get a forum, handle it
                dr.Close();
                myConnection.Close();

                throw new Components.ForumNotFoundException("Did not get back a forum for ThreadID " + ThreadID.ToString());
            }
            else {
                Forum forum = PopulateForumFromOleDbDataReader(dr);

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

                return forum;
            }
        }



        /// <summary>
        /// Returns a Forum object with information on a particular forum.
        /// </summary>
        /// <param name="ForumID">The ID of the Forum you are interested in.</param>
        /// <returns>A Forum object.</returns>
        /// <remarks>If a ForumID is passed in that is NOT found in the database, a ForumNotFoundException
        /// exception is thrown.</remarks>
        public  Forum GetForumInfo(int ForumID) {
            String strSQL = "SELECT	[ForumID], [ForumGroupId], [Name], [Description], [Moderated], [DaysToView], [DateCreated],	[Active] FROM [Forums] WHERE [ForumID] = @ForumID";

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

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

            if (!dr.Read()) {
                // we didn't get a forum, handle it
                dr.Close();
                myConnection.Close();

                throw new Components.ForumNotFoundException("Did not get back a forum for ForumID " + ForumID.ToString());
            }
            else {
                Forum forum = PopulateForumFromOleDbDataReader(dr);

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

                return forum;
            }
        }



        public  Forum GetForumInfo(int ForumID, OleDbConnection myConnection) {
            String strSQL = "SELECT	[ForumID], [Name], [Description], [Moderated], [DaysToView], [DateCreated],	[Active] FROM [Forums] WHERE [ForumID] = @ForumID";

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

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

            if (!dr.Read()) {
                // we didn't get a forum, handle it
                dr.Close();

                throw new Components.ForumNotFoundException("Did not get back a forum for ForumID " + ForumID.ToString());
            }
            else {
                Forum forum = PopulateForumFromOleDbDataReader(dr);

                dr.Close();
                return forum;
            }
        }




        /// <summary>
        /// Returns information about a particular forum that contains a particular post.
        /// </summary>
        /// <param name="PostID">The ID of the post that is contained in the Forum you wish to
        /// retrieve information about.</param>
        /// <returns>A Forum object instance containing the information about the Forum that the
        /// specified thread exists in.</returns>
        /// <remarks>If a Post is passed in that is NOT found in the database, a ForumNotFoundException
        /// exception is thrown.</remarks>
        public  Forum GetForumInfoByPostID(int PostID) {
            String strSQL = "SELECT [ForumID], [ForumGroupId] [Name], [Description], [DateCreated], [Moderated], [DaysToView], [Active] FROM [Forums] WHERE [ForumID] = (SELECT [ForumID] FROM [Posts] 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