sqldataprovider.cs

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

        /****************************************************************
        // GetMessage
        //
        /// <summary>
        /// Returns a message to display to the user.
        /// </summary>
        //
        ****************************************************************/
        public ForumMessage GetMessage(int messageId) {
            // 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_GetMessage", myConnection);
            ForumMessage message;

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

            // Pass sproc parameters
            myCommand.Parameters.Add("@MessageId", SqlDbType.Int).Value = messageId;

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

            message = new ForumMessage();

            while (dr.Read()) {

                message.Title = Convert.ToString(dr["Title"]);
                message.Body = Convert.ToString(dr["Body"]);

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

            return message;

        }

        
        /****************************************************************
        // GetModeratedPostsByForumId
        //
        /// <summary>
        /// Returns all the posts in a given forum that require moderation.
        /// </summary>
        //
        ****************************************************************/
        private  PostCollection GetModeratedPostsByForumId(int forumId) {
            // 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_GetModeratedPostsByForumId", myConnection);

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

            // Pass sproc parameters
            myCommand.Parameters.Add("@Username", SqlDbType.Int).Value = forumId;

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

            PostCollection posts = new PostCollection();
            Post post = null;

            while (dr.Read()) {
                post = PopulatePostFromSqlDataReader(dr);
                post.ForumName = Convert.ToString(dr["ForumName"]);

                posts.Add(post);
            }
            dr.Close();
            myConnection.Close();

            return posts;
        }

        /****************************************************************
        // GetForumsRequiringModeration
        //
        /// <summary>
        /// Returns a Moderated Foru
        /// </summary>
        //
        ****************************************************************/
        public ModeratedForumCollection GetForumsRequiringModeration(string username) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetModeratedForums", myConnection);
            SqlDataReader reader;
            ModeratedForumCollection moderatedForums = new ModeratedForumCollection();
            ModeratedForum moderatedForum;
            PostCollection posts;

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

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

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

            // Loop through the returned results
            while (reader.Read()) {

                // Populate all the forum details
                moderatedForum = new ModeratedForum();
                moderatedForum = (ModeratedForum) PopulateForumFromSqlDataReader(reader);

                // Get all the posts in the forum awaiting moderation
                posts = new PostCollection();
                posts = GetModeratedPostsByForumId(moderatedForum.ForumID);
                moderatedForum.PostsAwaitingModeration = posts;
            }

            myConnection.Close();

            return moderatedForums;

        }


        /****************************************************************
        // MarkPostAsRead
        //
        /// <summary>
        /// Flags a post a 'read' in the database
        /// </summary>
        //
        ****************************************************************/
        public void MarkPostAsRead(int postID, string username) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_MarkPostAsRead", myConnection);

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

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

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

        /****************************************************************
        // GetTotalPostsForThread
        //
        /// <summary>
        /// Returns the total number of posts in a given thread. This call
        /// is used mainly by paging when viewing posts.
        /// </summary>
        //
        ****************************************************************/
        public int GetTotalPostsForThread(int postID) {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetTotalPostsForThread", myConnection);
            int postCount = 0;

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

            // Pass sproc parameters
            myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;

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

            // Populate the colleciton of users
            while (dr.Read())
                postCount = Convert.ToInt32(dr["TotalPostsForThread"]);

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


        /****************************************************************
        // GetAllUsers
        //
        /// <summary>
        /// Returns a collection of all users.
        /// </summary>
        //
        ****************************************************************/
        public UserCollection GetAllUsers(int pageIndex, int pageSize, Users.SortUsersBy sortBy, int sortOrder, string usernameBeginsWith) {

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

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

            // Pass sproc parameters
            myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            myCommand.Parameters.Add("@SortBy", SqlDbType.Int).Value = sortBy;
            myCommand.Parameters.Add("@SortOrder", SqlDbType.Bit).Value = Convert.ToBoolean(sortOrder);

            if ((usernameBeginsWith == "All") || (usernameBeginsWith == null))
                myCommand.Parameters.Add("@UsernameBeginsWith", SqlDbType.NVarChar, 1).Value = System.DBNull.Value;
            else
                myCommand.Parameters.Add("@UsernameBeginsWith", SqlDbType.NVarChar, 1).Value = usernameBeginsWith;

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

            // Populate the colleciton of users
            while (dr.Read())
                users.Add(PopulateUserFromSqlDataReader(dr));

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

            return users;
        }

        /****************************************************************
        // GetTotalThreadsInForum
        //
        /// <summary>
        /// Returns the total number of threads in a given forum
        /// </summary>
        /// <param name="username">forum id to look up</param>
        //
        ****************************************************************/
        public int GetTotalThreadsInForum(int ForumID, DateTime maxDateTime, DateTime minDateTime, string username, bool unreadThreadsOnly) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_TopicCountForForum", myConnection);
            int totalThreads = 0;

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

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

            // Control the returned values by DateTime
            myCommand.Parameters.Add("@MaxDate", SqlDbType.DateTime).Value = maxDateTime;
            myCommand.Parameters.Add("@MinDate", SqlDbType.DateTime).Value = minDateTime;

            // Do we have a username?
            if (username == null)
                myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = System.DBNull.Value;
            else
                myCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username;

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

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

            while (dr.Read())
                totalThreads = Convert.ToInt32(dr["TotalTopics"]);

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

            return totalThreads;
        }

        /****************************************************************
        // GetUserRoles
        //
        /// <summary>
        /// Returns a string array of role names that the user belongs to
        /// </summary>
        /// <param name="username">username to find roles for</param>
        //
        ****************************************************************/
        public String[] GetUserRoles(string username) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetRolesByUser", myConnection);

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

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

            // Open the database connection and execute the command
            SqlDataReader dr;

            myConnection.Open();
            dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);