sqldataprovider.cs

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

        /// <summary>
        /// Returns forums in a given forum group for moderation
        /// </summary>
        //
        ****************************************************************/
        public ModeratedForumCollection GetForumsForModerationByForumGroupId(int forumGroupId, string username) {

            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetForumsForModerationByForumGroupId", myConnection);

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

            // Add Parameters to SPROC
            myCommand.Parameters.Add("@ForumGroupId", SqlDbType.Int).Value = forumGroupId;
            myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username;

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

            // populate a ForumCollection object
            ModeratedForumCollection forums = new ModeratedForumCollection();
            ModeratedForum moderatedForum;

            while (dr.Read()) {

                moderatedForum = PopulateModeratedForumFromSqlDataReader(dr);

                moderatedForum.TotalPosts = Convert.ToInt32(dr["TotalPosts"]);
                moderatedForum.TotalThreads = Convert.ToInt32(dr["TotalTopics"]);
                moderatedForum.ForumGroupId = (int) dr["ForumGroupId"];
                moderatedForum.TotalPostsAwaitingModeration = (int) dr["TotalPostsAwaitingModeration"];

                // Handle Nulls in the case that we don't have a 'most recent post...'
                if (Convert.IsDBNull(dr["MostRecentPostAuthor"]))
                    moderatedForum.MostRecentPostAuthor = null;
                else
                    moderatedForum.MostRecentPostAuthor = Convert.ToString(dr["MostRecentPostAuthor"]);

                if (Convert.IsDBNull(dr["MostRecentPostId"])) {
                    moderatedForum.MostRecentPostId = 0;
                    moderatedForum.MostRecentThreadId = 0;
                } else {
                    moderatedForum.MostRecentPostId = Convert.ToInt32(dr["MostRecentPostId"]);
                    moderatedForum.MostRecentThreadId = Convert.ToInt32(dr["MostRecentThreadId"]);
                }

                if (Convert.IsDBNull(dr["MostRecentPostDate"]))
                    moderatedForum.MostRecentPostDate = DateTime.MinValue.AddMonths(1);
                else
                    moderatedForum.MostRecentPostDate = Convert.ToDateTime(dr["MostRecentPostDate"]);

                // Last time the user was active in the forum
                if (username != null) {
                    if (Convert.IsDBNull(dr["LastUserActivity"]))
                        moderatedForum.LastUserActivity = DateTime.MinValue.AddMonths(1);
                    else
                        moderatedForum.LastUserActivity = Convert.ToDateTime(dr["LastUserActivity"]);
                } else {
                    moderatedForum.LastUserActivity = DateTime.MinValue;
                }
            
                forums.Add(moderatedForum);

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

            return forums;   
        }

        /****************************************************************
        // GetForumGroupsForModeration
        //
        /// <summary>
        /// Returns a forum group collection of all the forum groups containing
        /// forums that the current user has moderation abilities on.
        /// </summary>
        //
        ****************************************************************/
        public ForumGroupCollection GetForumGroupsForModeration(string username) {
            // Connect to the database
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetAllForumGroupsForModeration", myConnection);

            myCommand.CommandType = CommandType.StoredProcedure;

            myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username;
    
            // Execute the command
            myConnection.Open();
            SqlDataReader dr = myCommand.ExecuteReader();

            // populate a ForumGroupCollection object
            ForumGroupCollection forumGroups = new ForumGroupCollection();
            ForumGroup forumGroup;

            while (dr.Read()) {
                forumGroup = PopulateForumGroupFromSqlDataReader(dr);
                forumGroups.Add(forumGroup);
            }
            dr.Close();
            myConnection.Close();

            return forumGroups;
        }

        /****************************************************************
        // ToggleOptions
        //
        /// <summary>
        /// Allows use to change various settings without updating the profile directly
        /// </summary>
        //
        ****************************************************************/
        public void ToggleOptions(string username, bool hideReadThreads, ViewOptions viewOptions) {
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_ToggleOptions", myConnection);

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

            // Pass sproc parameters
            myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username;
            myCommand.Parameters.Add("@HideReadThreads", SqlDbType.Bit).Value = hideReadThreads;

            if (ViewOptions.NotSet == viewOptions)
                myCommand.Parameters.Add("@FlatView", SqlDbType.Bit).Value = System.DBNull.Value;
            else if (ViewOptions.Threaded == viewOptions)
                myCommand.Parameters.Add("@FlatView", SqlDbType.Bit).Value = false;
            else
                myCommand.Parameters.Add("@FlatView", SqlDbType.Bit).Value = true;

            // Execute the command
            myConnection.Open();
            myCommand.ExecuteNonQuery();

            myConnection.Close();
        }

        /****************************************************************
        // ChangeForumGroupSortOrder
        //
        /// <summary>
        /// Used to move forums group sort order up or down
        /// </summary>
        //
        ****************************************************************/
        public void ChangeForumGroupSortOrder(int forumGroupID, bool moveUp) {
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_ChangeForumGroupSortOrder", myConnection);

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

            // Pass sproc parameters
            myCommand.Parameters.Add("@ForumGroupID", SqlDbType.Int).Value = forumGroupID;
            myCommand.Parameters.Add("@MoveUp", SqlDbType.Bit).Value = moveUp;

            // Execute the command
            myConnection.Open();
            myCommand.ExecuteNonQuery();

            myConnection.Close();
        }

        /****************************************************************
        // UpdateMessageTemplate
        //
        /// <summary>
        /// update the message in the database
        /// </summary>
        //
        ****************************************************************/
        public void UpdateMessageTemplate(ForumMessage message) {
            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_UpdateMessageTemplateList", myConnection);

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

            // Pass sproc parameters
            myCommand.Parameters.Add("@MessageID", SqlDbType.Int).Value = message.MessageID;
            myCommand.Parameters.Add("@Title", SqlDbType.NVarChar, 128).Value = message.Title;
            myCommand.Parameters.Add("@Body", SqlDbType.NVarChar, 4000).Value = message.Body;

            // Execute the command
            myConnection.Open();
            myCommand.ExecuteNonQuery();

            myConnection.Close();

        }


        /****************************************************************
        // GetMessageTemplateList
        //
        /// <summary>
        /// Returns a collection of ForumMessages
        /// </summary>
        //
        ****************************************************************/
        public ForumMessageTemplateCollection GetMessageTemplateList() {
            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetForumMessageTemplateList", myConnection);
            SqlDataReader reader;
            ForumMessageTemplateCollection messages = new ForumMessageTemplateCollection();
            ForumMessage message;


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

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

            while (reader.Read()) {
                message = new ForumMessage();

                message.MessageID = Convert.ToInt32(reader["MessageID"]);
                message.Title = (string) reader["Title"];
                message.Body = (string) reader["Body"];

                messages.Add(message);

            }

            myConnection.Close();

            return messages;
        }

        /****************************************************************
        // GetUsernameByEmail
        //
        /// <summary>
        /// Returns the username given an email address
        /// </summary>
        //
        ****************************************************************/
        public string GetUsernameByEmail(string emailAddress) {
            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetUsernameByEmail", myConnection);
            SqlDataReader reader;
            string username = null;

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

            // Pass sproc parameters
            myCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = emailAddress;

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

            while (reader.Read()) {
                username = (String) reader["username"];
            }

            myConnection.Close();

            return username;
        }


        /****************************************************************
        // ChangePasswordForLoggedOnUser
        //
        /// <summary>
        /// Change the password for the logged on user.
        /// </summary>
        //
        ****************************************************************/
        public void ChangePasswordForLoggedOnUser(string username, string newPassword) {
            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_ChangeUserPassword", myConnection);

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

            // Pass sproc parameters
            myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username;
            myCommand.Parameters.Add("@NewPassword", SqlDbType.NVarChar, 50).Value = newPassword;

            // Execute the command
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();

        }