sqldataprovider.cs

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

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

namespace AspNetForums.Data {


    /// <summary>
    /// Summary description for WebForumsDataProvider.
    /// </summary>
    public class SqlDataProvider : IWebForumsDataProviderBase {


        /****************************************************************
        // GetThreadsUserMostRecentlyParticipatedIn
        //
        /// <summary>
        /// Returns a collection of threads that the user has recently partipated in.
        /// </summary>
        //
        ****************************************************************/
        public ThreadCollection GetThreadsUserMostRecentlyParticipatedIn(string username) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetTopicsUserMostRecentlyParticipatedIn", myConnection);
            ThreadCollection threads;

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

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

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


        /****************************************************************
        // GetThreadsUserIsTracking
        //
        /// <summary>
        /// Returns a collection of threads that the user is tracking
        /// </summary>
        //
        ****************************************************************/
        public ThreadCollection GetThreadsUserIsTracking(string username) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetTopicsUserIsTracking", myConnection);
            ThreadCollection threads;

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

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

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


        /****************************************************************
        // FindUsersByName
        //
        /// <summary>
        /// Returns a collection of users matching the name value provided.
        /// </summary>
        //
        ****************************************************************/
        public UserCollection FindUsersByName(int pageIndex, int pageSize, string usernameToMatch) {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_FindUsersByName", myConnection);
            SqlDataReader reader;
            UserCollection users = new UserCollection();
            User user;

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

            myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            myCommand.Parameters.Add("@UsernameToFind", SqlDbType.NVarChar, 50).Value = usernameToMatch;

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

            while (reader.Read()) {
                user = this.PopulateUserFromSqlDataReader(reader);
                users.Add(user);
            }

            reader.Close();
            myConnection.Close();

            return users;
        }

        /****************************************************************
        // GetModerationAuditSummary
        //
        /// <summary>
        /// Returns a summary of moderation audit details.
        /// </summary>
        //
        ****************************************************************/
        public ModerationAuditCollection GetModerationAuditSummary() {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("Statistics_GetModerationActions", myConnection);
            SqlDataReader reader;
            ModerationAuditCollection moderationAudits = new ModerationAuditCollection();
            ModerationAuditSummary moderationAudit;

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

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

            while (reader.Read()) {
                moderationAudit = new ModerationAuditSummary();
                moderationAudit.Action = (string) reader["Description"];
                moderationAudit.ActionSummary = Convert.ToInt32(reader["TotalActions"]);

                moderationAudits.Add(moderationAudit);
            }

            reader.Close();
            myConnection.Close();

            return moderationAudits;

        }

        /****************************************************************
        // GetMostActiveModerators
        //
        /// <summary>
        /// Returns a collection of the most active moderators.
        /// </summary>
        //
        ****************************************************************/
        public ModeratorCollection GetMostActiveModerators() {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("Statistics_GetMostActiveModerators", myConnection);
            SqlDataReader reader;
            ModeratorCollection moderators = new ModeratorCollection();
            Moderator moderator;

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

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

            while (reader.Read()) {
                moderator = new Moderator();
                moderator.Username = (string) reader["Username"];
                moderator.TotalPostsModerated = Convert.ToInt32(reader["PostsModerated"]);

                moderators.Add(moderator);
            }

            reader.Close();
            myConnection.Close();

            return moderators;

        }


        /****************************************************************
        // GetMostActiveUsers
        //
        /// <summary>
        /// Returns a collection of the most active users.
        /// </summary>
        //
        ****************************************************************/
        public UserCollection GetMostActiveUsers() {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("Statistics_GetMostActiveUsers", myConnection);
            SqlDataReader reader;
            UserCollection users = new UserCollection();
            User user;

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

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

            while (reader.Read()) {
                user = new User();
                user.Username = (string) reader["Username"];
                user.TotalPosts = Convert.ToInt32(reader["TotalPosts"]);

                users.Add(user);
            }

            reader.Close();
            myConnection.Close();

            return users;

        }


        /****************************************************************
        // GetAllUnmoderatedThreads
        //
        /// <summary>
        /// Returns a collection of all posts that have yet to be approved.
        /// </summary>
        //
        ****************************************************************/
        public ThreadCollection GetAllUnmoderatedThreads(int forumID, int pageSize, int pageIndex, string username) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetAllUnmoderatedTopicsPaged", 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("@Username", SqlDbType.NVarChar, 50).Value = username;

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

        /****************************************************************
        // GetTotalUnModeratedThreadsInForum
        //
        /// <summary>
        /// Returns a count of all posts that have yet to be approved.
        /// </summary>
        //
        ****************************************************************/
        public int GetTotalUnModeratedThreadsInForum(int ForumID, DateTime maxDateTime, DateTime minDateTime, string username, bool unreadThreadsOnly) {
            return 0;
        }

        /****************************************************************
        // GetForumsForModerationByForumGroupId
        //