forumssqldataprovider.cs

解压即可使用

                myCommand.Parameters.Add("@UserID", SqlDbType.Int);
                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int);
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Open the connection
                myConnection.Open();

                // Add multiple times
                //
                foreach (User user in users) 
                {
                    myCommand.Parameters["@UserID"].Value = user.UserID;
                    myCommand.Parameters["@ThreadID"].Value = threadID;


                    myCommand.ExecuteNonQuery();

                }

                myConnection.Close();
            }
        }

        public override void DeletePrivateMessage(int userID, ArrayList deleteList) 
        {

            // 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.Int).Value = DataProviderAction.Delete;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int);
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Open the connection
                myConnection.Open();

                // Add multiple times
                //
                foreach (int threadID in deleteList) 
                {
                    myCommand.Parameters["@ThreadID"].Value = threadID;

                    myCommand.ExecuteNonQuery();

                }

                myConnection.Close();
            }
        }

        public override HybridDictionary GetPrivateMessageRecipients(int threadID) 
        {
            HybridDictionary list = new HybridDictionary();

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

                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = threadID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Open the connection
                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {

                    while (reader.Read()) 
                    {

                        if (list[ (int) reader["UserID"] ] == null)
                            list.Add( (int) reader["UserID"], reader["Username"].ToString() );
                    }
                    reader.Close();
                }

                myConnection.Close();
            }

            return list;
        }

        #endregion

        #region #####Forums Permissions #####

        public override ArrayList GetForumPermissions(int sectionID) 
        {

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

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

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = sectionID;
                myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ApplicationType.Forum;
                myCommand.Parameters.Add(this.SettingsIDParameter());
                

                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {
                    while(reader.Read()) 
                    {
                        ForumPermission fp = new ForumPermission();
                        CommonDataProvider.PopulatePermissionFromIDataReader (fp,reader);

                        // Merge the permission
                        if (forumPermissions[fp.RoleID] != null) 
                        {

                        } 
                        else 
                        {
                            forumPermissions.Add(fp.RoleID, fp);
                        }

                    }

                    reader.Close();
                }

                myConnection.Close();


                return new ArrayList(forumPermissions.Values);
            }
        }

        #endregion

        #region #### RSS ####
        public override void RssPingback (Hashtable pingbackList) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_RssPingback_Update", myConnection);
                //            int totalAnonymousUsers = 0;

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

                // Set parameters
                //
                myCommand.Parameters.Add("@SectionID", SqlDbType.Char, 36);
                myCommand.Parameters.Add("@Pingback", SqlDbType.NVarChar, 512);
                myCommand.Parameters.Add("@Count", SqlDbType.Int);

                // Open the connection
                //
                myConnection.Open();

                foreach (string key in pingbackList.Keys) 
                {
                
                    myCommand.Parameters["@SectionID"].Value = ((RssPingback) pingbackList[key]).ForumID;
                    myCommand.Parameters["@Pingback"].Value = ((RssPingback) pingbackList[key]).Url;
                    myCommand.Parameters["@Count"].Value = ((RssPingback) pingbackList[key]).Count;
                    myCommand.Parameters.Add(this.SettingsIDParameter());

                    myCommand.ExecuteNonQuery();

                }

                // Close the connection
                //
                myConnection.Close();
            }
        }
        #endregion


        #region #### Moderation ####
        public override ArrayList GetForumsToModerate(int userID, ApplicationType applicationType) 
        {
            // Create Instance of Connection and Command Object
            //
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_Forums", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                ArrayList forums = new ArrayList();

                // Set parameters
                //
                myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.SmallInt).Value = applicationType;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;

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

                    // Get the results
                    //
                    while (dr.Read())
                        forums.Add( PopulateForumFromIDataReader(dr) );
                    
                    dr.Close();
                }
                myConnection.Close();

                return forums;
            }
        }

        /// <summary>
        /// Approves a particular post that is waiting to be moderated.
        /// </summary>
        /// <param name="PostID">The ID of the post to approve.</param>
        /// <returns>A boolean indicating if the post has already been approved.</returns>
        /// <remarks>Keep in mind that multiple moderators may be working on approving/moving/editing/deleting
        /// posts at the same time.  Hence, these moderation functions may not perform the desired task.
        /// For example, if one opts to delete a post that has already been approved, the deletion will
        /// fail.</remarks>
        public override bool ApprovePost(int postID, int userIDApprovedBy) 
        {

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

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

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

                // Execute the command
                myConnection.Open();
                int iResult = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
                myConnection.Close();

                return iResult == 1;        // was the post previously approved?
            }
        }

        public override bool CheckIfUserIsModerator (int userID, int forumID) 
        {

            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_CheckUser", myConnection);
                bool canModerate = false;

                // 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("@SectionID", SqlDbType.Int).Value = forumID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

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

                    // Check to see if the user can moderate
                    if (dr.Read())
                        if ( (int) dr[0] > 0 )
                            canModerate = true;

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



        public override PostSet GetPostsToModerate(int forumID, int pageIndex, int pageSize, int sortBy, int sortOrder, int userID, bool returnRecordCount) 
        {
            // Create Instance of Connection and Command Object
            //
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Moderate_PostSet", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                PostSet postSet = new PostSet();

                // Set parameters
                //
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
                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.Int).Value = sortOrder;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = returnRecordCount;
                myCommand.Parameters.Add(this.SettingsIDParameter());

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

                    // Get the results
                    //