forumssqldataprovider.cs

解压即可使用

            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_GetTotalPostCount", myConnection);

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

                myCommand.Parameters.Add(SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleRow))
                {

                    if(dr.Read())
                        totalPostCount = (int) dr[0];

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

                return totalPostCount;
            }
        }

        /****************************************************************
        // MarkPostAsRead
        //
        /// <summary>
        /// Flags a post a 'read' in the database
        /// </summary>
        //
        ****************************************************************/
        public override void MarkPostAsRead(int postID, string username) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_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;
                myCommand.Parameters.Add(this.SettingsIDParameter());

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


        #region TopN Posts
        /****************************************************************
        // GetTopNPopularPosts
        //
        /// <summary>
        /// TODO
        /// </summary>
        //
        ****************************************************************/
        public override PostSet GetTopNPopularPosts(string username, int postCount, int days) 
        {
            return GetTopNPosts(username, postCount, days, "TotalViews");
        }
        
        /****************************************************************
        // GetTopNPopularPosts
        //
        /// <summary>
        /// ToDO
        /// </summary>
        //
        ****************************************************************/
        public override PostSet GetTopNNewPosts(string username, int postCount) 
        {
            return GetTopNPosts(username, postCount, 0, "ThreadDate");
        }
        
        /****************************************************************
        // GetTopNPopularPosts
        //
        /// <summary>
        /// TODO
        /// </summary>
        //
        ****************************************************************/
        private PostSet GetTopNPosts(string username, int postCount, int days, string sort) 
        {
            PostSet postSet = new PostSet();

            using(SqlConnection myConnection = GetSqlConnection()) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_GetTopNPosts", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = username;
                myCommand.Parameters.Add("@SortType", SqlDbType.NVarChar, 50).Value = sort;
                myCommand.Parameters.Add("@PostCount", SqlDbType.Int, 4).Value = postCount;
                myCommand.Parameters.Add("@DaysToCount", SqlDbType.Int, 4).Value = days;
                
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult)) 
                {
                    
                    while(dr.Read())
                        postSet.Posts.Add( PopulatePostFromIDataReader(dr) );

                    dr.Close();

                }
            

                // Close the connection
                myConnection.Close();
                return postSet;
            }
        }

        #endregion

        #region Forums
        public override Hashtable GetForums() 
        {
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Sections_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                Hashtable forums = new Hashtable();

                // Add Parameters to SPROC
                myCommand.Parameters.Add(this.SettingsIDParameter());
                //myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ApplicationType.Forum;
				myCommand.Parameters.Add("@AllowInactive", SqlDbType.Bit).Value = true;

                // Execute the command
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Get the requested forums
                    //
                    while (dr.Read()) 
                    {

                        Forum f = PopulateForumFromIDataReader(dr);

                        // add all forums into the Hashtable
                        //
                        forums.Add(f.SectionID, f);

                    }

                    // Loop back through and link up any sub forums
                    // now that the Hashtable is fully populated.
                    // (5/24/2004 fix for subforums with parentid > it's forumid)
                    //
                    foreach (DictionaryEntry di in forums) 
                    {
					
                        // assign a forum to the forumID
                        //
                        Forum f = (Forum) di.Value;		

                        if (f.ParentID > 0)
                            ((Forum) forums[f.ParentID]).Sections.Add(f);
                    }

                    // Get the permissions
                    //
                    if (dr.NextResult()) 
                    {

                        while (dr.Read()) 
                        {
                    
                            // Get the forum id
                            //
                            int forumID = (int) dr["SectionID"];

                            ForumPermission fp = new ForumPermission();
                            CommonDataProvider.PopulatePermissionFromIDataReader( fp, dr);

                            // Are we applying this to a particular forum?
                            if (forums[forumID] != null)  
                            {
                                Forum f = forums[forumID] as Forum;

                                fp.SectionID = forumID;
                                f.PermissionSet.Add( fp.Name, fp );

                                if( !fp.View || !fp.Read )
                                    f.IsPrivate = true;
                            } 
                            else if (forumID == -1) 
                            {

                                // Apply the permission to all forums
                                foreach(Forum forum in forums.Values) 
                                {

                                    fp.SectionID = forum.SectionID;

                                    // Merge the permission
                                    if (forum.PermissionSet[fp.Name] != null) 
                                    {
                                        /*
                                                                            ForumPermission inheritedPermission = forum.PermissionSet[fp.Name] as ForumPermission;

                                                                            inheritedPermission.Merge(fp);
                                        */
                                    } 
                                    else 
                                    {
                                        forum.PermissionSet.Add( fp.Name, fp );
                                    }

                                    if( !fp.View || !fp.Read )
                                        forum.IsPrivate = true;
                                }
                            }


                        }

                    }
            
                    // Done with the reader and the connection
                    //
                    dr.Close();
                }
                myConnection.Close();

                return forums;
            }
        }


        #endregion

        #region Forum Data
        /// <summary>
        /// Returns a Forum object with information on a particular forum.
        /// </summary>
        /// <param name="SectionID">The ID of the Forum you are interested in.</param>
        /// <returns>A Forum object.</returns>
        /// <remarks>If a ForumID is passed in that is NOT found in the database, a ForumNotFoundException
        /// exception is thrown.</remarks>
        public override int GetForumIDByPostID(int postID) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_GetSectionIDByPostID", myConnection);
                int forumID = 0;

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

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


                // Execute the command
                myConnection.Open();
                object obj = myCommand.ExecuteScalar();
                myConnection.Close();
                if(obj != null)
                   forumID = (int)obj;

                return forumID;
            }
        }

        /****************************************************************
        // MarkAllForumsRead
        //
        /// <summary>
        /// Marks all forums as read
        /// </summary>
        //
        *****************************************************************/
        public override void MarkAllForumsRead(int userID, int forumGroupID, int forumID, bool markAllThreadsRead) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_MarkRead", myConnection);

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

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@GroupID", SqlDbType.Int).Value = forumGroupID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
                myCommand.Parameters.Add("@MarkAllThreadsRead", SqlDbType.Bit).Value = markAllThreadsRead;
                myCommand.Parameters.Add(this.SettingsIDParameter());

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


        #endregion

        #region #### Private Messages ####
        public override void CreatePrivateMessage(ArrayList users, int threadID) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PrivateMessages_CreateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Action", SqlDbType.Bit).Value = DataProviderAction.Create;