sqldataprovider.cs

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

        /// <summary>
        /// Builds and returns an instance of the User class based on the current row of an
        /// aptly populated SqlDataReader object.
        /// </summary>
        /// <param name="dr">The SqlDataReader 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 SqlDataReader, dr.</returns>
        private User PopulateUserFromSqlDataReader(SqlDataReader dr) {
            User user = new User();
            user.Signature = Convert.ToString(dr["Signature"]);
            user.Email = Convert.ToString(dr["Email"]);
            user.PublicEmail = Convert.ToString(dr["FakeEmail"]);
            user.Url = Convert.ToString(dr["URL"]);
            user.Password = Convert.ToString(dr["Password"]);
            user.Username = Convert.ToString(dr["Username"]);
            user.IsApproved = Convert.ToBoolean(dr["Approved"]);
            user.IsProfileApproved = Convert.ToBoolean(dr["ProfileApproved"]);
            user.IsTrusted = Convert.ToBoolean(dr["Trusted"]);
            user.Timezone = Convert.ToInt32(dr["Timezone"]);
            user.DateCreated = Convert.ToDateTime(dr["DateCreated"]);
            user.LastLogin = Convert.ToDateTime(dr["LastLogin"]);
            user.LastActivity = Convert.ToDateTime(dr["LastActivity"]);
            user.TrackPosts = Convert.ToBoolean(dr["TrackYourPosts"]);
            user.Location = (string) dr["Location"];
            user.Occupation = (string) dr["Occupation"];
            user.Interests = (string) dr["Interests"];
            user.MsnIM = (string) dr["MSN"];
            user.AolIM = (string) dr["AIM"];
            user.YahooIM = (string) dr["Yahoo"];
            user.IcqIM = (string) dr["ICQ"];
            user.TotalPosts = (int) dr["TotalPosts"];
            user.HasIcon = (bool) dr["HasIcon"];
            user.HideReadThreads = (bool) dr["ShowUnreadTopicsOnly"];
            user.SiteStyle = (string) dr["Style"];
            user.IconExtension = (string) dr["ImageType"];
            user.ShowIcon = (bool) dr["ShowIcon"];
            user.DateFormat = (string) dr["DateFormat"];
            user.ShowPostsAscending = (bool) dr["PostViewOrder"];
            user.ViewPostsInFlatView = (bool) dr["FlatView"];
            user.IsModerator = Convert.ToBoolean(dr["IsModerator"]);
		
            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 SqlDataReader object.
        /// </summary>
        /// <param name="dr">The SqlDataReader 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 SqlDataReader, dr.</returns>
        private  EmailTemplate PopulateEmailTemplateFromSqlDataReader(SqlDataReader 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 ThreadCollection GetAllThreads(int forumID, string username, bool unreadThreadsOnly) {

            // TODO - might want to do some more work here
            return GetAllThreads(forumID, 9999, 0, DateTime.Now.AddYears(-20), username, unreadThreadsOnly);

        }


        public ThreadCollection GetAllThreads(int forumID, int pageSize, int pageIndex, DateTime endDate, string username, bool unreadThreadsOnly) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetAllTopicsPaged", myConnection);
            ThreadCollection threads;

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

            // Add Parameters to SPROC
            myCommand.Parameters.Add("@ForumId", SqlDbType.Int, 4).Value = forumID;
            myCommand.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = pageSize;
            myCommand.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = pageIndex;
            myCommand.Parameters.Add("@DateFilter", SqlDbType.DateTime).Value = endDate;
            
            // Only pass username if it's not null
            if (username == null)
                myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = System.DBNull.Value;
            else
                myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username;

            myCommand.Parameters.Add("@UnReadTopicsOnly", SqlDbType.Bit).Value = unreadThreadsOnly;

            // Execute the command
            myConnection.Open();
            SqlDataReader dr = myCommand.ExecuteReader();

            threads = new ThreadCollection();

            while (dr.Read()) {
                threads.Add(PopulateThreadFromSqlDataReader(dr));
            }
            dr.Close();
            myConnection.Close();

            // Only return the posts specified through paging

            return threads;

        }

        /// <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) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetAllMessages", myConnection);

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

            // Add Parameters to SPROC
            SqlParameter parameterForumId = new SqlParameter("@ForumId", SqlDbType.Int, 4);
            parameterForumId.Value = ForumID;
            myCommand.Parameters.Add(parameterForumId);

            SqlParameter parameterViewType = new SqlParameter("@ViewType", SqlDbType.Int, 4);
            parameterViewType.Value = (int) ForumView;
            myCommand.Parameters.Add(parameterViewType);

            SqlParameter parameterPagesBack = new SqlParameter("@PagesBack", SqlDbType.Int, 4);
            parameterPagesBack.Value = PagesBack;
            myCommand.Parameters.Add(parameterPagesBack);

            // Execute the command
            myConnection.Open();
            SqlDataReader dr = myCommand.ExecuteReader();

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

            return posts;
        }

        /// <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 GetSubjectsByForum(int ForumID, ViewOptions ForumView, int PagesBack) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetAllMessages", myConnection);

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

            // Add Parameters to SPROC
            SqlParameter parameterForumId = new SqlParameter("@ForumId", SqlDbType.Int, 4);
            parameterForumId.Value = ForumID;
            myCommand.Parameters.Add(parameterForumId);

            SqlParameter parameterViewType = new SqlParameter("@ViewType", SqlDbType.Int, 4);
            parameterViewType.Value = (int) ForumView;
            myCommand.Parameters.Add(parameterViewType);

            SqlParameter parameterPagesBack = new SqlParameter("@PagesBack", SqlDbType.Int, 4);
            parameterPagesBack.Value = PagesBack;
            myCommand.Parameters.Add(parameterPagesBack);

            // Execute the command
            myConnection.Open();
            SqlDataReader dr = myCommand.ExecuteReader();

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

            return posts;
        }

        /// <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) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetSingleMessage", myConnection);

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

            // Add Parameters to SPROC
            myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;

            if ( (username == null) || (username == String.Empty))
                myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = System.DBNull.Value;
            else
                myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username;

            // Execute the command
            myConnection.Open();
            SqlDataReader dr = myCommand.ExecuteReader();

            if (!dr.Read())
                // we did not get back a post
                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"]);
            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.NextPostID = Convert.ToInt32(dr["NextPostID"]);
            post.PrevPostID = Convert.ToInt32(dr["PrevPostID"]);
            post.NextThreadID = Convert.ToInt32(dr["NextThreadID"]);
            post.PrevThreadID = Convert.T