webforumsoledbdataprovider.cs

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

using System;
using System.Data;
using System.Data.OleDb;
using AspNetForums.Components;
using System.Web;
using System.Web.Mail;
using System.IO;
using System.Text.RegularExpressions;

namespace AspNetForums.Data {

    /// <summary>
    /// Summary description for WebForumsDataProvider.
    /// </summary>
    public class OleDbDataProvider {
        /*********************************************************************************/
		
        /****************** PRIVATE HELPER FUNCTIONS ***************************
            * These functions are private helper functions to the WebForumsSqlDataProvider
            * class.  These include functions that help populate the various WebForums
            * objects from a OleDbDataReader.
            * *********************************************************************/

        /****************************************************************
        // AddForumGroup
        //
        /// <summary>
        /// Creates a new forum group, and exception is raised if the
        /// forum group already exists.
        /// </summary>
        /// <param name="forumGroupName">Name of the forum group to create</param>
        //
        ****************************************************************/
        public void AddForumGroup(string forumGroupName) {

        }


        /****************************************************************
        // UpdateForumGroup
        //
        /// <summary>
        /// Updates the name of an existing forum group
        /// </summary>
        /// <param name="forumGroupName">New name for the forum group</param>
        /// <param name="forumGroupId">Unique identifier for the forum group to update</param>
        //
        *****************************************************************/
        public void UpdateForumGroup(string forumGroupName, int forumGroupId) {

        }

        /// <summary>
        /// Builds and returns an instance of the Post class based on the current row of an
        /// aptly populated OleDbDataReader object.
        /// </summary>
        /// <param name="dr">The OleDbDataReader object that contains, at minimum, the following
        /// columns: PostID, ParentID, Body, ForumID, PostDate, PostLevel, SortOrder, Subject,
        /// ThreadDate, ThreadID, Replies, Username, and Approved.</param>
        /// <returns>An instance of the Post class that represents the current row of the passed 
        /// in OleDbDataReader, dr.</returns>
        private Post PopulatePostFromOleDbDataReader(OleDbDataReader dr) {
            Post post = new Post();
            post.PostID = Convert.ToInt32(dr["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"]);
            post.ThreadDate = Convert.ToDateTime(dr["ThreadDate"]);
            post.ThreadID = Convert.ToInt32(dr["ThreadID"]);
            post.Replies = Convert.ToInt32(dr["Replies"]);
            post.Username = Convert.ToString(dr["Username"]);
            post.Approved = Convert.ToBoolean(dr["Approved"]);
			
            return post;
        }


        /// <summary>
        /// Builds and returns an instance of the Forum class based on the current row of an
        /// aptly populated OleDbDataReader object.
        /// </summary>
        /// <param name="dr">The OleDbDataReader object that contains, at minimum, the following
        /// columns: ForumID, DateCreated, Description, Name, Moderated, and DaysToView.</param>
        /// <returns>An instance of the Forum class that represents the current row of the passed 
        /// in OleDbDataReader, dr.</returns>
        private  Forum PopulateForumFromOleDbDataReader(OleDbDataReader dr) {
            Forum forum = new Forum();
            forum.ForumID = Convert.ToInt32(dr["ForumID"]);
            forum.ForumGroupId = Convert.ToInt32(dr["ForumGroupId"]);
            forum.DateCreated = Convert.ToDateTime(dr["DateCreated"]);
            forum.Description = Convert.ToString(dr["Description"]);
            forum.Name = Convert.ToString(dr["Name"]);
            forum.Moderated = Convert.ToBoolean(dr["Moderated"]);
            forum.DaysToView = Convert.ToInt32(dr["DaysToView"]);
            forum.Active = Convert.ToBoolean(dr["Active"]);			

            return forum;
        }
	
        private ForumGroup PopulateForumGroupFromOleDbDataReader(OleDbDataReader dr) {

            ForumGroup forumGroup = new ForumGroup();
            forumGroup.ForumGroupID = (int) dr["ForumGroupId"];
            forumGroup.Name = (string) dr["Name"];

            return forumGroup;
        }

        /// <summary>
        /// Builds and returns an instance of the User class based on the current row of an
        /// aptly populated OleDbDataReader object.
        /// </summary>
        /// <param name="dr">The OleDbDataReader object that contains, at minimum, the following
        /// columns: Signature, Email, FakeEmail, Url, Password, Username, Administrator, Approved,
        /// Trusted, Timezone, DateCreated, LastLogin, and ForumView.</param>
        /// <returns>An instance of the User class that represents the current row of the passed 
        /// in OleDbDataReader, dr.</returns>
        private  User PopulateUserFromOleDbDataReader(OleDbDataReader dr) {
            User user = new User();
            user.Signature = Convert.ToString(dr["Signature"]);
            user.Email = Convert.ToString(dr["Email"]);
            user.FakeEmail = Convert.ToString(dr["FakeEmail"]);
            user.Url = Convert.ToString(dr["URL"]);
            user.Password = Convert.ToString(dr["Password"]);
            user.Username = Convert.ToString(dr["Username"]);
            user.IsAdministrator = Convert.ToBoolean(dr["Administrator"]);
            user.Approved = Convert.ToBoolean(dr["Approved"]);
            user.Trusted = Convert.ToBoolean(dr["Trusted"]);
            user.Timezone = Convert.ToInt32(dr["Timezone"]);
            user.DateCreated = Convert.ToDateTime(dr["DateCreated"]);
            user.LastLogin = Convert.ToDateTime(dr["LastLogin"]);
            user.TrackPosts = Convert.ToBoolean(dr["TrackYourPosts"]);
			
            switch (Convert.ToInt32(dr["ForumView"])) {
                case 0:
                    user.ForumView = ViewOptions.Flat;
                    break;

                case 1:
                    user.ForumView = ViewOptions.Mixed;
                    break;

                case 2:
                    user.ForumView = ViewOptions.Threaded;
                    break;

                default:
                    user.ForumView = ViewOptions.NotSet;
                    break;
            }
			
            return user;
        }


		
        /// <summary>
        /// Builds and returns an instance of the EmailTemplate class based on the current row of an
        /// aptly populated OleDbDataReader object.
        /// </summary>
        /// <param name="dr">The OleDbDataReader object that contains, at minimum, the following
        /// columns: EmailID, Subject, Message, FromAddress, Importance, and Description.</param>
        /// <returns>An instance of the EmailTemplate class that represents the current row of the passed 
        /// in OleDbDataReader, dr.</returns>
        private  EmailTemplate PopulateEmailTemplateFromOleDbDataReader(OleDbDataReader dr) {
            EmailTemplate email = new EmailTemplate();
			
            email.EmailTemplateID = Convert.ToInt32(dr["EmailID"]);
            email.Subject = Convert.ToString(dr["Subject"]);
            email.Body = Convert.ToString(dr["Message"]);
            email.From = Convert.ToString(dr["FromAddress"]);
            email.Description = Convert.ToString(dr["Description"]);

            switch (Convert.ToInt32(dr["Importance"])) {
                case 0:
                    email.Priority = MailPriority.Low;
                    break;

                case 2:
                    email.Priority = MailPriority.High;
                    break;

                default:		// the default
                    email.Priority = MailPriority.Normal;
                    break;
            }

            return email;
        }
        /*********************************************************************************/


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

        /************************ POST FUNCTIONS ***********************
                 * These functions return information about a post or posts.  They
                 * are called from the WebForums.Posts class.
                 * *************************************************************/

        
        public PostCollection GetAllTopics(int forumID, int pageSize, int pageIndex, DateTime startDate, DateTime endDate, string username) {

            return null; // TODO
        }

        public         void MarkAllTopicsRead(int forumID, string username) {
        }

        /// <summary>
        /// Returns all of the messages for a particular page of posts for a paticular forum in a
        /// particular ForumView mode.
        /// </summary>
        /// <param name="ForumID">The ID of the Forum whose posts you wish to display.</param>
        /// <param name="ForumView">How to display the Forum posts.  The ViewOptions enumeration
        /// supports one of three values: Flat, Mixed, and Threaded.</param>
        /// <param name="PagesBack">How many pages back of data to display.  A value of 0 displays
        /// the posts from the current time to a time that is the Forum's DaysToView days prior to the
        /// current day.</param>
        /// <returns>A PostCollection object containing all of the posts.</returns>
        public  PostCollection GetAllMessages(int ForumID, ViewOptions ForumView, int PagesBack) {
            OleDbConnection myConnection = new OleDbConnection(Globals.DatabaseConnectionString);
            myConnection.Open();
            OleDbCommand myCommandForDaysToView = new OleDbCommand("SELECT DaysToView FROM [Forums] WHERE [ForumID] = " + ForumID.ToString(), myConnection);
            int DaysToView = Convert.ToInt32(myCommandForDaysToView.ExecuteScalar());


            // Create Instance of Connection and Command Object
            String strSQL = "";
            DateTime startDate, stopDate;
            startDate = stopDate = DateTime.Now.AddMinutes(5.0);
			

            if (ForumView == ViewOptions.NotSet) ForumView = (ViewOptions) Globals.DefaultForumView;

            startDate = startDate.AddDays(-PagesBack * DaysToView);
            stopDate = startDate.AddDays(-DaysToView);

            switch (ForumView) {
                case ViewOptions.Flat:
                    strSQL = "SELECT [Subject], [PostID], [ForumID], [ThreadID], [ParentID], [PostLevel], [SortOrder], [Approved], [PostDate], [ThreadDate], [UserName], (SELECT COUNT(*) FROM [Posts] P2 WHERE P2.[ParentID] = P.[PostID] AND P2.[PostLevel[ <> 1) AS [Replies], [Body] " +
                        "FROM [Posts] AS P WHERE [Approved] = Yes AND [ForumID] = @ForumID and [PostDate] >= #" + stopDate.ToString("d-MMM-yyy HH:m") + "# AND [PostDate] <= #" + startDate.ToString("d-MMM-yyy HH:m") + "# ORDER BY [PostDate] DESC";
                    break;

                case ViewOptions.Mixed:
                    strSQL = "SELECT [Subject], [PostID], [ForumID], [ThreadID], [ParentID], [PostLevel], [SortOrder], [Approved], [PostDate], [ThreadDate], [UserName], (SELECT COUNT(*) FROM Posts P2 WHERE P2.ParentID = P.PostID AND P2.PostLevel <> 1) AS Replies, [Body] " +
                        "FROM [Posts] AS P WHERE [PostLevel] = 1 AND [Approved] = Yes AND [ForumID] = @ForumID and [PostDate] >= #" + stopDate.ToString("d-MMM-yyy HH:m") + "# AND [PostDate] <= #" + startDate.ToString("d-MMM-yyy HH:m") + "# ORDER BY [PostDate] DESC";
                    break;

                case ViewOptions.Threaded:
                    strSQL = "SELECT [Subject], [PostID], [ForumID], [ThreadID], [ParentID], [PostLevel], [SortOrder], [Approved], [PostDate], [ThreadDate], [UserName], 0 AS [Replies], [Body] " +
                        "FROM [Posts] AS P WHERE [Approved] = Yes AND [ForumID] = @ForumID and [ThreadDate] >= #" + stopDate.ToString("d-MMM-yyy HH:m") + "# AND [ThreadDate] <= #" + startDate.ToString("d-MMM-yyy HH:m") + "# ORDER BY [ThreadID] DESC, [SortOrder]";
                    //HttpContext.Current.Response.Write(strSQL + " - Pages Back: " + PagesBack.ToString());
                    break;
            }

			
            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;