weblogsqldataprovider.cs

本系统是在asp版《在线文件管理器》的基础上设计制作

                            dr.NextResult();
                            ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;
					
                        }
                    }

                    return ts;

                }
            }
        }
        #endregion

        #region GetBlogPostSet
        /// <summary>
        /// Returns an instance of PostSet based on the BlogPostQuery
        /// </summary>
	    public override PostSet GetPosts(BlogPostQuery query)
	    {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_Postset", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.BlogID;
                    command.Parameters.Add("@PostID", SqlDbType.Int).Value = query.PostID;
                    command.Parameters.Add("@PostName", SqlDbType.NVarChar).Value = query.Name;
                    command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
                    command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = query.PageSize;
                    command.Parameters.Add("@ReturnFullThread", SqlDbType.Bit, 1).Value = query.ReturnFullThread;
                    command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;
                    command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;
                    command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;

                    PostSet ps = new PostSet();

                    connection.Open();

                    //NOTE TO SELF: We return the results as seperate sets so that we can avoid some of the more
                    //expensive lookups done ont the main posts!
                    
                    using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        //this will return just one post, forget the rest if nothing found here
                        if(reader.Read())
                        {
                            WeblogPost entry = PopulateWeblogEntryFromIDataReader(reader);
                            //PopulateWeblogEntryFromIDataReader(reader,entry);
                            
                            if(query.IncludeCategories)
                            {
                                reader.NextResult();
                                ArrayList categories = new ArrayList();
                                while(reader.Read())
                                {
                                    categories.Add(reader["Name"] as string);
                                }
                                
                                entry.Categories = (string[])categories.ToArray(typeof(string));
                            }
                            
                            //we could be setting the thread starter right here?
                            ps.Posts.Add(entry);

                            //comment list
                            reader.NextResult();

                            while(reader.Read())
                            {
                                entry = PopulateWeblogEntryFromIDataReader(reader);
                                //PopulateWeblogEntryFromIDataReader(reader,entry);

                                ps.Posts.Add(entry);
                            }

                            reader.NextResult();
                            ps.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;

                            reader.Close();
                        }
                        else
                        {
                            reader.Close();
                        }
                    }

                    connection.Close();

                    return ps;

                }
            }
	    }

        #endregion

        #region CreateUpdateDelete Posts
        #region AddPost
        /// <summary>
        /// Adds a new Weblog Post to the data store
        /// </summary>
	    public override BlogPostResults AddPost(WeblogPost post, User user, out int postID)
	    {
            postID = -1;

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

                myCommand.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                // Add parameters
                //
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = post.ParentID;
                myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = true;
                myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = 1;
                myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;
                myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;
                myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = post.IsApproved;
                myCommand.Parameters.Add("@IsTracked", SqlDbType.Bit).Value = post.IsTracked;
                myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = post.PostType;
                myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = post.EmoticonID;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserID;
                myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;
                myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = post.FormattedBody;
                myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = post.UserHostAddress;
                myCommand.Parameters.Add("@PostDate",SqlDbType.DateTime,8).Value = post.PostDate;
                myCommand.Parameters.Add("@BloggerTime",SqlDbType.DateTime,8).Value = post.BloggerTime;

				SerializerData data = post.GetSerializerData();


				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

                myCommand.Parameters.Add(SettingsIDParameter());
				
                
                myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = false;
                myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;


                myCommand.Parameters.Add("@Excerpt",SqlDbType.NVarChar,500).Value = DBValue(post.Excerpt);
                myCommand.Parameters.Add("@PostName",SqlDbType.NVarChar,256).Value = DBValue(post.Name);
                myCommand.Parameters.Add("@TitleUrl",SqlDbType.NVarChar,256).Value = DBValue(post.TitleUrl);
                myCommand.Parameters.Add("@PostConfig",SqlDbType.Int,4).Value = post.PostConfig;
                myCommand.Parameters.Add("@BlogPostType",SqlDbType.TinyInt).Value = post.BlogPostType;

                if(post.BlogPostType == BlogPostType.Post)
                {
                    myCommand.Parameters.Add("@CategoryType",SqlDbType.TinyInt).Value = CategoryType.BlogPost;
                    myCommand.Parameters.Add("@Categories",SqlDbType.NVarChar,4000).Value = ConvertCategoriesToXML(post.Categories);
                }
                else if(post.BlogPostType == BlogPostType.Article)
                {
                    myCommand.Parameters.Add("@CategoryType",SqlDbType.TinyInt).Value = CategoryType.BlogArticle;
                    myCommand.Parameters.Add("@Categories",SqlDbType.NVarChar,4000).Value = ConvertCategoriesToXML(post.Categories);
                }

                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Direction = ParameterDirection.Output;

                myConnection.Open();
                myCommand.ExecuteNonQuery();

                // LN 5/27/04: try/catch added to get rid of exceptions
                   BlogPostResults result =  (BlogPostResults)(int)myCommand.Parameters["RETURN_VALUE"].Value;
                    if(result == BlogPostResults.Success)
                        postID = (int) myCommand.Parameters["@PostID"].Value;

                myConnection.Close();
                return result;
            }

	    }
        #endregion

        #region UpdatePost
	    public override BlogPostResults UpdatePost(WeblogPost post, int editedBy)
	    {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_weblog_Post_Update", myConnection);

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

                myCommand.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
                myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = post.PostID;
                myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;
                myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;
                myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = post.FormattedBody;
                myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = post.EmoticonID;
                myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;
                myCommand.Parameters.Add("@IsTracked", SqlDbType.Bit).Value = post.IsTracked;
                myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = post.IsApproved;
                myCommand.Parameters.Add("@EditedBy", SqlDbType.Int, 4).Value = editedBy;

                myCommand.Parameters.Add("@PostDate",SqlDbType.DateTime,8).Value = post.PostDate;
                myCommand.Parameters.Add("@BloggerTime",SqlDbType.DateTime,8).Value = post.BloggerTime;

				SerializerData data = post.GetSerializerData();


				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

                myCommand.Parameters.Add(SettingsIDParameter());

                // Allow Thread to update sticky properties.
                //
                if (post is IThread) 
                {
                    IThread thread = (IThread) post;
                    myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = thread.IsSticky;
                    myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = thread.StickyDate;
                }

                myCommand.Parameters.Add("@Excerpt",SqlDbType.NVarChar,500).Value = DBValue(post.Excerpt);
                myCommand.Parameters.Add("@PostName",SqlDbType.NVarChar,256).Value = DBValue(post.Name);
                myCommand.Parameters.Add("@TitleUrl",SqlDbType.NVarChar,256).Value = DBValue(post.TitleUrl);
                myCommand.Parameters.Add("@PostConfig",SqlDbType.Int,4).Value = post.PostConfig;
                myCommand.Parameters.Add("@BlogPostType",SqlDbType.TinyInt).Value = post.BlogPostType;

                if(post.BlogPostType == BlogPostType.Post)
                {
                    myCommand.Parameters.Add("@CategoryType",SqlDbType.TinyInt).Value = CategoryType.BlogPost;
                    myCommand.Parameters.Add("@Categories",SqlDbType.NVarChar,4000).Value = ConvertCategoriesToXML(post.Categories);
                }
                else if(post.BlogPostType == BlogPostType.Article)
                {
                    myCommand.Parameters.Add("@CategoryType",SqlDbType.TinyInt).Value = CategoryType.BlogArticle;
                    myCommand.Parameters.Add("@Categories",SqlDbType.NVarChar,4000).Value = ConvertCategoriesToXML(post.Categories);
                }

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

                BlogPostResults result =  (BlogPostResults)(int)myCommand.Parameters["RETURN_VALUE"].Value;

                return result;
            }
	    }
        #endregion

        #region DeletePost
	    public override void DeletePost(int sectionID, int postID, int userID, BlogPostType blogPostType)
	    {
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_weblog_Post_Delete", myConnection);

                // 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("@PostID", SqlDbType.Int, 4).Value = postID;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = userID;
                myCommand.Parameters.Add("@BlogPostType", SqlDbType.TinyInt).Value = blogPostType;
                myCommand.Parameters.Add(this.SettingsIDParameter());

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

        #region GetWeblogs
        /// <summary>
        /// Returns all of the Weblogs for the current settingsID
        /// </summary>
        /// <returns></returns>
	    public override Hashtable GetWeblogs()
	    {
            Hashtable weblogs = new Hashtable();
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_weblog_Weblogs_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
				

                // Add Parameters to SPROC
                myCommand.Parameters.Add(this.SettingsIDParameter());

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

                // Get the requested weblogs
                //
                Weblog w = null;
                while (dr.Read()) 
                {
                    w = new Weblog();
                    PopulateWeblogData(dr,w);

                    // add all weblogs into the Hashtable
                    //
                    weblogs.Add(w.ApplicationKey.ToLower(), w);

                }