sqldataprovider.cs

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

            // create a String array from the data
            ArrayList userRoles = new ArrayList();

            while (dr.Read()) {
                userRoles.Add(dr["RoleName"]);
            }

            dr.Close();

            // Return the String array of roles
            return (string[]) userRoles.ToArray(typeof(String));
        }

        /****************************************************************
        // TrackAnonymousUsers
        //
        /// <summary>
        /// Keep track of anonymous users.
        /// </summary>
        /// <param name="userId">user id to uniquely identify the user</param>
        //
        ****************************************************************/
        public void TrackAnonymousUsers(string userId) {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_TrackAnonymousUsers", myConnection);
            SqlParameter param;

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

            // Add Parameters to SPROC
            param = new SqlParameter("@UserId", SqlDbType.Char, 36);
            param.Value = userId;
            myCommand.Parameters.Add(param);

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

        /****************************************************************
        // GetForumGroupByForumId
        //
        /// <summary>
        /// Returns the name of a forum group based on the id of the forum.
        /// </summary>
        /// <param name="forumGroupName">ID of the forum group to lookup</param>
        //
        ****************************************************************/
        public ForumGroup GetForumGroupByForumId(int forumID) {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Globals.DatabaseConnectionString);
            SqlCommand myCommand = new SqlCommand("sp_GetForumGroupByForumID", myConnection);
            SqlDataReader dr;
            ForumGroup forumGroup = null;

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

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

            // Open the connection
            myConnection.Open();

            dr = myCommand.ExecuteReader();

            if (dr.Read())
                forumGroup = PopulateForumGroupFromSqlDataReader(dr);

            myConnection.Close();

            return forumGroup;
        }

        /****************************************************************
        // 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) {

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

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

            // Add Parameters to SPROC
            SqlParameter param = new SqlParameter("@ForumGroupName", SqlDbType.NVarChar, 256);
            param.Value = forumGroupName;
            myCommand.Parameters.Add(param);

            // Open the connection
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();

        }

        /****************************************************************
        // MarkAllThreadsRead
        //
        /// <summary>
        /// Marks all threads from Forum ID and below as read
        /// </summary>
        //
        *****************************************************************/
        public void MarkAllThreadsRead(int forumID, string username) {

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

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

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

            param = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
            param.Value = username;
            myCommand.Parameters.Add(param);


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


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

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

            // Add Parameters to SPROC
            param = new SqlParameter("@ForumGroupName", SqlDbType.NVarChar, 256);
            
            // If forumGroupName is null we want to delete
            if (null == forumGroupName)
                param.Value = System.DBNull.Value;
            else
                param.Value = forumGroupName;
            
            myCommand.Parameters.Add(param);

            // Add Parameters to SPROC
            param = new SqlParameter("@ForumGroupId", SqlDbType.Int);
            param.Value = forumGroupId;
            myCommand.Parameters.Add(param);

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


        /// <summary>
        /// Builds and returns an instance of the Post 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: 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 SqlDataReader, dr.</returns>
        private Post PopulatePostFromSqlDataReader(SqlDataReader dr) {

            Post post = new Post();
            post.PostID = Convert.ToInt32(dr["PostID"]);
            post.ParentID = Convert.ToInt32(dr["ParentID"]);
            post.Body = Convert.ToString(dr["Body"]);
            post.ForumName = Convert.ToString(dr["ForumName"]);
            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"]);
            post.IsLocked = Convert.ToBoolean(dr["IsLocked"]);
            post.Views = Convert.ToInt32(dr["TotalViews"]);
            post.HasRead = Convert.ToBoolean(dr["HasRead"]);
			
            return post;
        }

        
        // *********************************************************************
        //
        //  PopulateThreadFromSqlDataReader
        //
        /// <summary>
        /// This private method accepts a datareader and attempts to create and
        /// populate a thread class instance which is returned to the caller. For
        /// all practical purposes, a thread is simply a lightweigh version of a 
        /// post - no details, such as the body, are provided though and a thread is
        /// always considered the first post in a thread.
        /// </summary>
        //
        // ********************************************************************/
        private Thread PopulateThreadFromSqlDataReader(SqlDataReader reader) {
            Thread thread = new Thread();

            thread.PostID = Convert.ToInt32(reader["PostID"]);
            thread.PostDate = Convert.ToDateTime(reader["PostDate"]);
            thread.Subject = Convert.ToString(reader["Subject"]);
            thread.Body = Convert.ToString(reader["Body"]);
            thread.ThreadDate = Convert.ToDateTime(reader["ThreadDate"]);
            thread.PinnedDate = Convert.ToDateTime(reader["PinnedDate"]);
            thread.Replies = Convert.ToInt32(reader["Replies"]);
            thread.Username = Convert.ToString(reader["Username"]);
            thread.IsLocked = Convert.ToBoolean(reader["IsLocked"]);
            thread.IsPinned = Convert.ToBoolean(reader["IsPinned"]);
            thread.Views = Convert.ToInt32(reader["TotalViews"]);
            thread.HasRead = Convert.ToBoolean(reader["HasRead"]);
            thread.MostRecentPostAuthor = Convert.ToString(reader["MostRecentPostAuthor"]);
            thread.MostRecentPostID = Convert.ToInt32(reader["MostRecentPostID"]);
            thread.ThreadID = Convert.ToInt32(reader["ThreadID"]);

            return thread;
        }


        /// <summary>
        /// Builds and returns an instance of the Forum 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: ForumID, DateCreated, Description, Name, Moderated, and DaysToView.</param>
        /// <returns>An instance of the Forum class that represents the current row of the passed 
        /// in SqlDataReader, dr.</returns>
        private  Forum PopulateForumFromSqlDataReader(SqlDataReader 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"]);
            forum.SortOrder = Convert.ToInt32(dr["SortOrder"]);
            forum.IsPrivate = Convert.ToBoolean(dr["IsPrivate"]);

            return forum;
        }

        private  ModeratedForum PopulateModeratedForumFromSqlDataReader(SqlDataReader dr) {
            ModeratedForum forum = new ModeratedForum();
            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"]);
            forum.SortOrder = Convert.ToInt32(dr["SortOrder"]);
            forum.IsPrivate = Convert.ToBoolean(dr["IsPrivate"]);

            return forum;
        }
        
        private ForumGroup PopulateForumGroupFromSqlDataReader(SqlDataReader dr) {

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

            return forumGroup;
        }