/BlogEngine/DotNetSlave.BusinessLogic/Providers/DbBlogProvider.cs

using System;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Configuration;

using System.Configuration.Provider;

using System.Data.Common;

using System.IO;

using System.Linq;

using System.Xml.Serialization;

using System.Transactions;



using BlogEngine.Core.DataStore;

using BlogEngine.Core.Packaging;



namespace BlogEngine.Core.Providers

{

    /// <summary>

    /// Generic Database BlogProvider

    /// </summary>

    public partial class DbBlogProvider : BlogProvider

    {

        #region Constants and Fields



        /// <summary>

        /// The conn string name.

        /// </summary>

        private string connStringName;



        /// <summary>

        /// The parm prefix.

        /// </summary>

        private string parmPrefix;



        /// <summary>

        /// The table prefix.

        /// </summary>

        private string tablePrefix;



        #endregion



        #region Public Methods



        /// <summary>

        /// Deletes a BlogRoll from the database

        /// </summary>

        /// <param name="blogRollItem">

        /// The blog Roll Item.

        /// </param>

        public override void DeleteBlogRollItem(BlogRollItem blogRollItem)

        {

            var blogRolls = BlogRollItem.BlogRolls;

            blogRolls.Remove(blogRollItem);

            blogRolls.Add(blogRollItem);



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("DELETE FROM {0}BlogRollItems WHERE BlogId = {1}BlogId AND BlogRollId = {1}BlogRollId", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogRollId"), blogRollItem.Id.ToString()));



                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Deletes the blog's storage container.

        /// </summary>

        /// <param name="blog">

        /// The blog.

        /// </param>

        public override bool DeleteBlogStorageContainer(Blog blog)

        {

            // First delete the blog folder.  Even the DB provider uses

            // the folder.  This is rare and is usually by widgets/extensions.

            if (!blog.DeleteBlogFolder())

            {

                return false;

            }



            // Delete data from all tables except for be_Blogs.  The blog

            // data from be_Blogs will be deleted in DeleteBlog().



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    // Note, the order here is important, especially for the DBs where

                    // foreign key constraints are setup (SQL Server is one).  The data

                    // in the referencing tables needs to be deleted first.



                    var sqlQuery = string.Format("DELETE FROM {0}PostTag WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}PostNotify WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}PostComment WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}PostCategory WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Posts WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}RightRoles WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Profiles WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}UserRoles WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Roles WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Rights WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Users WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Pages WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}StopWords WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Settings WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Referrers WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}PingService WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}DataStoreSettings WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}BlogRollItems WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Categories WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }

                }

            }



            return true;

        }



        /// <summary>

        /// Deletes a Blog from the database

        /// </summary>

        /// <param name="blog">

        /// The blog.

        /// </param>

        public override void DeleteBlog(Blog blog)

        {

            // Only deleting data from be_Blogs.  Data from the other tables

            // will be deleted in DeleteBlogStorageContainer().

            using (TransactionScope ts = new TransactionScope())

            {

                using (var conn = this.CreateConnection())

                {

                    if (conn.HasConnection)

                    {

                        var sqlQuery = string.Format("DELETE FROM {0}Blogs WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);

                        using (var cmd = conn.CreateTextCommand(sqlQuery))

                        {

                            cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

                            cmd.ExecuteNonQuery();

                        }



                    }

                }

                ts.Complete();

            }

        }



        /// <summary>

        /// Deletes a category from the database

        /// </summary>

        /// <param name="category">

        /// category to be removed

        /// </param>

        public override void DeleteCategory(Category category)

        {

            var categories = Category.Categories;

            categories.Remove(category);



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("DELETE FROM {0}PostCategory WHERE BlogID = {1}blogid AND CategoryID = {1}catid", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("catid"), category.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Categories WHERE BlogID = {1}blogid AND CategoryID = {1}catid", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("catid"), category.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Deletes a page from the database

        /// </summary>

        /// <param name="page">

        /// page to be deleted

        /// </param>

        public override void DeletePage(Page page)

        {

            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("DELETE FROM {0}Pages WHERE BlogID = {1}blogid AND PageID = {1}id", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), page.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Deletes a post in the database

        /// </summary>

        /// <param name="post">

        /// post to delete

        /// </param>

        public override void DeletePost(Post post)

        {

            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("DELETE FROM {0}PostTag WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}PostCategory WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}PostNotify WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}PostComment WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Posts WHERE BlogID = @blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                }

            }

        }



        /// <summary>

        /// Remove AuthorProfile from database

        /// </summary>

        /// <param name="profile">An AuthorProfile.</param>

        public override void DeleteProfile(AuthorProfile profile)

        {

            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("DELETE FROM {0}Profiles WHERE BlogID = {1}blogid AND UserName = {1}name", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("name"), profile.Id));

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Gets all BlogRolls in database

        /// </summary>

        /// <returns>

        /// List of BlogRolls

        /// </returns>

        public override List<BlogRollItem> FillBlogRoll()

        {

            var blogRoll = new List<BlogRollItem>();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT BlogRollId, Title, Description, BlogUrl, FeedUrl, Xfn, SortIndex FROM {0}BlogRollItems WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                var br = new BlogRollItem

                                    {

                                        Id = rdr.GetGuid(0),

                                        Title = rdr.GetString(1),

                                        Description = rdr.IsDBNull(2) ? string.Empty : rdr.GetString(2),

                                        BlogUrl = rdr.IsDBNull(3) ? null : new Uri(rdr.GetString(3)),

                                        FeedUrl = rdr.IsDBNull(4) ? null : new Uri(rdr.GetString(4)),

                                        Xfn = rdr.IsDBNull(5) ? string.Empty : rdr.GetString(5),

                                        SortIndex = rdr.GetInt32(6)

                                    };



                                blogRoll.Add(br);

                                br.MarkOld();

                            }

                        }

                    }

                }

            }



            return blogRoll;

        }



        /// <summary>

        /// Gets all Blogs in database

        /// </summary>

        /// <returns>

        /// List of Blogs

        /// </returns>

        public override List<Blog> FillBlogs()

        {

            var blogs = new List<Blog>();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT BlogId, BlogName, Hostname, IsAnyTextBeforeHostnameAccepted, StorageContainerName, VirtualPath, IsPrimary, IsActive FROM {0}Blogs ", this.tablePrefix)))

                    {

                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                var b = new Blog

                                {

                                    Id = rdr.GetGuid(0),

                                    Name = rdr.GetString(1),

                                    Hostname = rdr.GetString(2),

                                    IsAnyTextBeforeHostnameAccepted = rdr.GetBoolean(3),

                                    StorageContainerName = rdr.GetString(4),

                                    VirtualPath = rdr.GetString(5),

                                    IsPrimary = rdr.GetBoolean(6),

                                    IsActive = rdr.GetBoolean(7)

                                };



                                blogs.Add(b);

                                b.MarkOld();

                            }

                        }

                    }

                }

            }



            return blogs;

        }



        /// <summary>

        /// Gets all categories in database

        /// </summary>

        /// <returns>

        /// List of categories

        /// </returns>

        public override List<Category> FillCategories()

        {

            var categories = new List<Category>();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT CategoryID, CategoryName, description, ParentID FROM {0}Categories WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                var cat = new Category

                                    {

                                        Title = rdr.GetString(1),

                                        Description = rdr.IsDBNull(2) ? string.Empty : rdr.GetString(2),

                                        Parent = rdr.IsDBNull(3) ? (Guid?)null : new Guid(rdr.GetGuid(3).ToString()),

                                        Id = new Guid(rdr.GetGuid(0).ToString())

                                    };



                                categories.Add(cat);

                                cat.MarkOld();

                            }

                        }

                    }

                }

            }



            return categories;

        }



        /// <summary>

        /// Gets all pages in database

        /// </summary>

        /// <returns>

        /// List of pages

        /// </returns>

        public override List<Page> FillPages()

        {

            var pageIDs = new List<string>();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT PageID FROM {0}Pages WHERE BlogID = {1}blogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                pageIDs.Add(rdr.GetGuid(0).ToString());

                            }

                        }

                    }

                }

            }



            return pageIDs.Select(id => Page.Load(new Guid(id))).ToList();

        }



        /// <summary>

        /// Gets all post from the database

        /// </summary>

        /// <returns>

        /// List of posts

        /// </returns>

        public override List<Post> FillPosts()

        {

            var postIDs = new List<string>();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT PostID FROM {0}Posts WHERE BlogID = {1}blogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                postIDs.Add(rdr.GetGuid(0).ToString());

                            }

                        }

                    }

                }

            }



            var posts = postIDs.Select(id => Post.Load(new Guid(id))).ToList();



            posts.Sort();

            return posts;

        }



        /// <summary>

        /// Return collection for AuthorProfiles from database

        /// </summary>

        /// <returns>

        /// List of AuthorProfile

        /// </returns>

        public override List<AuthorProfile> FillProfiles()

        {

            var profileNames = new List<string>();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT UserName FROM {0}Profiles WHERE BlogID = {1}blogid GROUP BY UserName", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                profileNames.Add(rdr.GetString(0));

                            }

                        }

                    }

                }

            }



            return profileNames.Select(BusinessBase<AuthorProfile, string>.Load).ToList();

        }



        /// <summary>

        /// Gets all Referrers from the database.

        /// </summary>

        /// <returns>

        /// List of Referrers.

        /// </returns>

        public override List<Referrer> FillReferrers()

        {

            this.DeleteOldReferrers();



            var referrers = new List<Referrer>();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT ReferrerId, ReferralDay, ReferrerUrl, ReferralCount, Url, IsSpam FROM {0}Referrers WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                var refer = new Referrer

                                    {

                                        Id = rdr.GetGuid(0),

                                        Day = rdr.GetDateTime(1),

                                        ReferrerUrl = new Uri(rdr.GetString(2)),

                                        Count = rdr.GetInt32(3),

                                        Url = rdr.IsDBNull(4) ? null : new Uri(rdr.GetString(4)),

                                        PossibleSpam = rdr.IsDBNull(5) ? false : rdr.GetBoolean(5)

                                    };



                                referrers.Add(refer);

                                refer.MarkOld();

                            }

                        }

                    }

                }

            }



            return referrers;

        }



        public override IDictionary<string, IEnumerable<string>> FillRights()

        {

            var rightsWithRoles = new Dictionary<string, IEnumerable<string>>();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("SELECT RightName FROM {0}Rights WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                rightsWithRoles.Add(rdr.GetString(0), new HashSet<string>(StringComparer.OrdinalIgnoreCase));

                            }

                        }



                        // Get Right Roles.

                        cmd.CommandText = string.Format("SELECT RightName, Role FROM {0}RightRoles WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix);

                        // don't need to add "blogid" parameter again since the same cmd is being used.



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                string rightName = rdr.GetString(0);

                                string roleName = rdr.GetString(1);



                                if (rightsWithRoles.ContainsKey(rightName))

                                {

                                    var roles = (HashSet<string>)rightsWithRoles[rightName];

                                    if (!roles.Contains(roleName))

                                    {

                                        roles.Add(roleName);

                                    }

                                }

                            }

                        }

                    }

                }

            }



            return rightsWithRoles;

        }



        /// <summary>

        /// Initializes the provider

        /// </summary>

        /// <param name="name">

        /// Configuration name

        /// </param>

        /// <param name="config">

        /// Configuration settings

        /// </param>

        public override void Initialize(string name, NameValueCollection config)

        {

            if (config == null)

            {

                throw new ArgumentNullException("config");

            }



            if (String.IsNullOrEmpty(name))

            {

                name = "DbBlogProvider";

            }



            if (String.IsNullOrEmpty(config["description"]))

            {

                config.Remove("description");

                config.Add("description", "Generic Database Blog Provider");

            }



            base.Initialize(name, config);



            if (config["connectionStringName"] == null)

            {

                // default to BlogEngine

                config["connectionStringName"] = "BlogEngine";

            }



            this.connStringName = config["connectionStringName"];

            config.Remove("connectionStringName");



            if (config["tablePrefix"] == null)

            {

                // default

                config["tablePrefix"] = "be_";

            }



            this.tablePrefix = config["tablePrefix"];

            config.Remove("tablePrefix");



            if (config["parmPrefix"] == null)

            {

                // default

                config["parmPrefix"] = "@";

            }



            this.parmPrefix = config["parmPrefix"];

            config.Remove("parmPrefix");



            // Throw an exception if unrecognized attributes remain

            if (config.Count > 0)

            {

                var attr = config.GetKey(0);

                if (!String.IsNullOrEmpty(attr))

                {

                    throw new ProviderException(string.Format("Unrecognized attribute: {0}", attr));

                }

            }

        }



        /// <summary>

        /// Adds a new BlogRoll to the database.

        /// </summary>

        /// <param name="blogRollItem">

        /// The blog Roll Item.

        /// </param>

        public override void InsertBlogRollItem(BlogRollItem blogRollItem)

        {

            var blogRolls = BlogRollItem.BlogRolls;

            blogRolls.Add(blogRollItem);



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {



                    var sqlQuery = string.Format("INSERT INTO {0}BlogRollItems (BlogId, BlogRollId, Title, Description, BlogUrl, FeedUrl, Xfn, SortIndex) VALUES ({1}BlogId, {1}BlogRollId, {1}Title, {1}Description, {1}BlogUrl, {1}FeedUrl, {1}Xfn, {1}SortIndex)", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        this.AddBlogRollParametersToCommand(blogRollItem, conn, cmd);

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Adds a new blog to the database.

        /// </summary>

        /// <param name="blog">

        /// The blog.

        /// </param>

        public override void InsertBlog(Blog blog)

        {

            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("INSERT INTO {0}Blogs (BlogId, BlogName, Hostname, IsAnyTextBeforeHostnameAccepted, StorageContainerName, VirtualPath, IsPrimary, IsActive) VALUES ({1}BlogId, {1}BlogName, {1}Hostname, {1}IsAnyTextBeforeHostnameAccepted, {1}StorageContainerName, {1}VirtualPath, {1}IsPrimary, {1}IsActive)", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        this.AddBlogParametersToCommand(blog, conn, cmd);

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Adds a new category to the database

        /// </summary>

        /// <param name="category">

        /// category to add

        /// </param>

        public override void InsertCategory(Category category)

        {

            var categories = Category.Categories;

            categories.Add(category);

            categories.Sort();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("INSERT INTO {0}Categories (BlogID, CategoryID, CategoryName, description, ParentID) VALUES ({1}blogid, {1}catid, {1}catname, {1}description, {1}parentid)", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        var parms = cmd.Parameters;

                        parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        parms.Add(conn.CreateParameter(FormatParamName("catid"), category.Id.ToString()));

                        parms.Add(conn.CreateParameter(FormatParamName("catname"), category.Title));

                        parms.Add(conn.CreateParameter(FormatParamName("description"), category.Description));

                        parms.Add(conn.CreateParameter(FormatParamName("parentid"), (category.Parent == null ? (object)DBNull.Value : category.Parent.ToString())));



                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Adds a page to the database

        /// </summary>

        /// <param name="page">

        /// page to be added

        /// </param>

        public override void InsertPage(Page page)

        {

            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("INSERT INTO {0}Pages (BlogID, PageID, Title, Description, PageContent, DateCreated, DateModified, Keywords, IsPublished, IsFrontPage, Parent, ShowInList, Slug, IsDeleted) VALUES ({1}blogid, {1}id, {1}title, {1}desc, {1}content, {1}created, {1}modified, {1}keywords, {1}ispublished, {1}isfrontpage, {1}parent, {1}showinlist, {1}slug, {1}isdeleted)", this.tablePrefix, this.parmPrefix);

                   

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {



                        var parms = cmd.Parameters;

                        parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        parms.Add(conn.CreateParameter(FormatParamName("id"), page.Id.ToString()));

                        parms.Add(conn.CreateParameter(FormatParamName("title"), page.Title));

                        parms.Add(conn.CreateParameter(FormatParamName("desc"), page.Description));

                        parms.Add(conn.CreateParameter(FormatParamName("content"), page.Content));

                        parms.Add(conn.CreateParameter(FormatParamName("created"), page.DateCreated.AddHours(-BlogSettings.Instance.Timezone)));

                        parms.Add(conn.CreateParameter(FormatParamName("modified"),

                                                                    (page.DateModified == new DateTime() ? DateTime.Now : page.DateModified.AddHours(-BlogSettings.Instance.Timezone))));

                        parms.Add(conn.CreateParameter(FormatParamName("keywords"), page.Keywords));

                        parms.Add(conn.CreateParameter(FormatParamName("ispublished"), page.IsPublished));

                        parms.Add(conn.CreateParameter(FormatParamName("isfrontpage"), page.IsFrontPage));

                        parms.Add(conn.CreateParameter(FormatParamName("parent"), page.Parent.ToString()));

                        parms.Add(conn.CreateParameter(FormatParamName("showinlist"), page.ShowInList));

                        parms.Add(conn.CreateParameter(FormatParamName("slug"), page.Slug));

                        parms.Add(conn.CreateParameter(FormatParamName("isdeleted"), page.IsDeleted));



                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Adds a new post to database

        /// </summary>

        /// <param name="post">

        /// The new post.

        /// </param>

        public override void InsertPost(Post post)

        {

            using (TransactionScope ts = new TransactionScope())

            {

                using (var conn = this.CreateConnection())

                {

                    if (conn.HasConnection)

                    {

                        var sqlQuery = string.Format("INSERT INTO {0}Posts (BlogID, PostID, Title, Description, PostContent, DateCreated, DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug, IsDeleted) VALUES ({1}blogid, {1}id, {1}title, {1}desc, {1}content, {1}created, {1}modified, {1}author, {1}published, {1}commentEnabled, {1}raters, {1}rating, {1}slug, {1}isdeleted)", this.tablePrefix, this.parmPrefix);



                        using (var cmd = conn.CreateTextCommand(sqlQuery))

                        {



                            var parms = cmd.Parameters;

                            parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                            parms.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                            parms.Add(conn.CreateParameter(FormatParamName("title"), post.Title));

                            parms.Add(conn.CreateParameter(FormatParamName("desc"), (post.Description ?? string.Empty)));

                            parms.Add(conn.CreateParameter(FormatParamName("content"), post.Content));

                            parms.Add(conn.CreateParameter(FormatParamName("created"), post.DateCreated.AddHours(-BlogSettings.Instance.Timezone)));

                            parms.Add(conn.CreateParameter(FormatParamName("modified"), (post.DateModified == new DateTime() ? DateTime.Now : post.DateModified.AddHours(-BlogSettings.Instance.Timezone))));

                            parms.Add(conn.CreateParameter(FormatParamName("author"), (post.Author ?? string.Empty)));

                            parms.Add(conn.CreateParameter(FormatParamName("published"), post.IsPublished));

                            parms.Add(conn.CreateParameter(FormatParamName("commentEnabled"), post.HasCommentsEnabled));

                            parms.Add(conn.CreateParameter(FormatParamName("raters"), post.Raters));

                            parms.Add(conn.CreateParameter(FormatParamName("rating"), post.Rating));

                            parms.Add(conn.CreateParameter(FormatParamName("slug"), (post.Slug ?? string.Empty)));

                            parms.Add(conn.CreateParameter(FormatParamName("isdeleted"), post.IsDeleted));



                            cmd.ExecuteNonQuery();

                        }



                        // Tags

                        this.UpdateTags(post, conn);



                        // Categories

                        this.UpdateCategories(post, conn);



                        // Comments

                        this.UpdateComments(post, conn);



                        // Email Notification

                        this.UpdateNotify(post, conn);

                    }

                }

                ts.Complete();

            }

        }



        /// <summary>

        /// Adds AuthorProfile to database

        /// </summary>

        /// <param name="profile">An AuthorProfile.</param>

        public override void InsertProfile(AuthorProfile profile)

        {

            this.UpdateProfile(profile);

        }



        /// <summary>

        /// Adds a new Referrer to the database.

        /// </summary>

        /// <param name="referrer">

        /// Referrer to add.

        /// </param>

        public override void InsertReferrer(Referrer referrer)

        {

            var referrers = Referrer.Referrers;

            referrers.Add(referrer);



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("INSERT INTO {0}Referrers (BlogId, ReferrerId, ReferralDay, ReferrerUrl, ReferralCount, Url, IsSpam) VALUES ({1}BlogId, {1}ReferrerId, {1}ReferralDay, {1}ReferrerUrl, {1}ReferralCount, {1}Url, {1}IsSpam)", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        this.AddReferrersParametersToCommand(referrer, conn, cmd);

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Load user data from DataStore

        /// </summary>

        /// <param name="extensionType">

        /// type of info

        /// </param>

        /// <param name="extensionId">

        /// id of info

        /// </param>

        /// <returns>

        /// stream of detail data

        /// </returns>

        public override object LoadFromDataStore(ExtensionType extensionType, string extensionId)

        {

            // MemoryStream stream;

            object o = null;



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("SELECT Settings FROM {0}DataStoreSettings WHERE BlogId = {1}blogid AND ExtensionType = {1}etype AND ExtensionId = {1}eid", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {



                        var parms = cmd.Parameters;

                        parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        parms.Add(conn.CreateParameter(FormatParamName("etype"), extensionType.GetHashCode()));

                        parms.Add(conn.CreateParameter(FormatParamName("eid"), extensionId));



                        o = cmd.ExecuteScalar();

                    }

                }

            }



            return o;

        }



        /// <summary>

        /// Gets the PingServices from the database

        /// </summary>

        /// <returns>

        /// collection of PingServices

        /// </returns>

        public override StringCollection LoadPingServices()

        {

            var col = new StringCollection();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT Link FROM {0}PingService WHERE BlogID = {1}blogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                if (!col.Contains(rdr.GetString(0)))

                                {

                                    col.Add(rdr.GetString(0));

                                }

                            }

                        }

                    }

                }

            }



            return col;

        }



        /// <summary>

        /// Gets the settings from the database

        /// </summary>

        /// <returns>

        /// dictionary of settings

        /// </returns>

        public override StringDictionary LoadSettings()

        {

            var dic = new StringDictionary();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT SettingName, SettingValue FROM {0}Settings WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                var name = rdr.GetString(0);

                                var value = rdr.GetString(1);



                                dic.Add(name, value);

                            }

                        }

                    }

                }

            }



            return dic;

        }



        /// <summary>

        /// Get stopwords from the database

        /// </summary>

        /// <returns>

        /// collection of stopwords

        /// </returns>

        public override StringCollection LoadStopWords()

        {

            var col = new StringCollection();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT StopWord FROM {0}StopWords WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                var value = rdr.GetString(0);

                                if (!col.Contains(value))

                                {

                                    col.Add(value);

                                }

                            }

                        }

                    }

                }

            }



            return col;

        }



        /// <summary>

        /// Deletes an item from the dataStore

        /// </summary>

        /// <param name="extensionType">

        /// type of item

        /// </param>

        /// <param name="extensionId">

        /// id of item

        /// </param>

        public override void RemoveFromDataStore(ExtensionType extensionType, string extensionId)

        {

            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("DELETE FROM {0}DataStoreSettings WHERE BlogId = {1}blogId AND ExtensionType = {1}type AND ExtensionId = {1}id", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {



                        var p = cmd.Parameters;

                        p.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        p.Add(conn.CreateParameter(FormatParamName("type"), extensionType));

                        p.Add(conn.CreateParameter(FormatParamName("id"), extensionId));



                        cmd.ExecuteNonQuery();

                    }



                }

            }

        }



        /// <summary>

        /// Saves the PingServices to the database

        /// </summary>

        /// <param name="services">

        /// collection of PingServices

        /// </param>

        public override void SavePingServices(StringCollection services)

        {

            if (services == null)

            {

                throw new ArgumentNullException("services");

            }



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {



                    using (var cmd = conn.CreateTextCommand(string.Format("DELETE FROM {0}PingService WHERE BlogID = {1}blogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.ExecuteNonQuery();



                        foreach (var service in services)

                        {

                            cmd.CommandText = string.Format("INSERT INTO {0}PingService (BlogID, Link) VALUES ({1}blogid, {1}link)", this.tablePrefix, this.parmPrefix);

                            cmd.Parameters.Clear();



                            cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                            cmd.Parameters.Add(conn.CreateParameter(FormatParamName("link"), service));



                            cmd.ExecuteNonQuery();

                        }

                    }

                }

            }

        }



        public override void SaveRights(IEnumerable<Right> rights)

        {

            if (rights == null)

            {

                throw new ArgumentNullException("rights");

            }



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("DELETE FROM {0}Rights WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.ExecuteNonQuery();



                        cmd.CommandText = string.Format("DELETE FROM {0}RightRoles WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix);

                        cmd.ExecuteNonQuery();



                        foreach (var right in rights)

                        {

                            cmd.CommandText = string.Format("INSERT INTO {0}Rights (BlogId, RightName) VALUES ({1}BlogId, {1}RightName)", this.tablePrefix, this.parmPrefix);



                            cmd.Parameters.Clear();

                            cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), Blog.CurrentInstance.Id.ToString()));

                            cmd.Parameters.Add(conn.CreateParameter(FormatParamName("RightName"), right.Name));



                            cmd.ExecuteNonQuery();



                            foreach (var role in right.Roles)

                            {

                                cmd.CommandText = string.Format("INSERT INTO {0}RightRoles (BlogId, RightName, Role) VALUES ({1}BlogId, {1}RightName, {1}Role)", this.tablePrefix, this.parmPrefix);



                                cmd.Parameters.Clear();

                                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), Blog.CurrentInstance.Id.ToString()));

                                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("RightName"), right.Name));

                                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("Role"), role));



                                cmd.ExecuteNonQuery();

                            }

                        }

                    }

                }

            }

            

        }



        /// <summary>

        /// Saves the settings to the database

        /// </summary>

        /// <param name="settings">

        /// dictionary of settings

        /// </param>

        public override void SaveSettings(StringDictionary settings)

        {

            if (settings == null)

            {

                throw new ArgumentNullException("settings");

            }



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("DELETE FROM {0}Settings WHERE BlogId = {1}blogid", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.ExecuteNonQuery();



                        foreach (string key in settings.Keys)

                        {

                            cmd.CommandText = string.Format("INSERT INTO {0}Settings (BlogId, SettingName, SettingValue) VALUES ({1}blogid, {1}name, {1}value)", this.tablePrefix, this.parmPrefix);

                            cmd.Parameters.Clear();



                            cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                            cmd.Parameters.Add(conn.CreateParameter(FormatParamName("name"), key));

                            cmd.Parameters.Add(conn.CreateParameter(FormatParamName("value"), settings[key]));



                            cmd.ExecuteNonQuery();

                        }

                    }

                }

            }

        }



        /// <summary>

        /// Save to DataStore

        /// </summary>

        /// <param name="extensionType">

        /// type of info

        /// </param>

        /// <param name="extensionId">

        /// id of info

        /// </param>

        /// <param name="settings">

        /// data of info

        /// </param>

        public override void SaveToDataStore(ExtensionType extensionType, string extensionId, object settings)

        {

            if (settings == null)

            {

                throw new ArgumentNullException("settings");

            }



            // Save



            var xs = new XmlSerializer(settings.GetType());

            string objectXml;

            using (var sw = new StringWriter())

            {

                xs.Serialize(sw, settings);

                objectXml = sw.ToString();

            }



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("DELETE FROM {0}DataStoreSettings WHERE BlogId = {1}blogid AND ExtensionType = {1}type AND ExtensionId = {1}id; ", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {



                        var p = cmd.Parameters;



                        p.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        p.Add(conn.CreateParameter(FormatParamName("type"), extensionType.GetHashCode()));

                        p.Add(conn.CreateParameter(FormatParamName("id"), extensionId));



                        cmd.ExecuteNonQuery();



                        cmd.CommandText = string.Format("INSERT INTO {0}DataStoreSettings (BlogId, ExtensionType, ExtensionId, Settings) VALUES ({1}blogid, {1}type, {1}id, {1}file)", this.tablePrefix, this.parmPrefix);



                        p.Add(conn.CreateParameter(FormatParamName("file"), objectXml));



                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Gets a BlogRoll based on a Guid.

        /// </summary>

        /// <param name="id">

        /// The BlogRoll's Guid.

        /// </param>

        /// <returns>

        /// A matching BlogRoll

        /// </returns>

        public override BlogRollItem SelectBlogRollItem(Guid id)

        {

            var blogRoll = BlogRollItem.BlogRolls.Find(br => br.Id == id) ?? new BlogRollItem();



            blogRoll.MarkOld();

            return blogRoll;

        }



        /// <summary>

        /// Gets a Blog based on a Guid.

        /// </summary>

        /// <param name="id">

        /// The Blog's Guid.

        /// </param>

        /// <returns>

        /// A matching Blog

        /// </returns>

        public override Blog SelectBlog(Guid id)

        {

            var blog = Blog.Blogs.Find(b => b.Id == id) ?? new Blog();



            blog.MarkOld();

            return blog;

        }



        /// <summary>

        /// Returns a category

        /// </summary>

        /// <param name="id">Id of category to return</param>

        /// <returns>A category.</returns>

        public override Category SelectCategory(Guid id)

        {

            var categories = Category.Categories;



            var category = new Category();



            foreach (var cat in categories.Where(cat => cat.Id == id))

            {

                category = cat;

            }



            category.MarkOld();

            return category;

        }



        /// <summary>

        /// Returns a page for given ID

        /// </summary>

        /// <param name="id">

        /// ID of page to return

        /// </param>

        /// <returns>

        /// selected page

        /// </returns>

        public override Page SelectPage(Guid id)

        {

            var page = new Page();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("SELECT PageID, Title, Description, PageContent, DateCreated, DateModified, Keywords, IsPublished, IsFrontPage, Parent, ShowInList, Slug, IsDeleted FROM {0}Pages WHERE BlogID = {1}blogid AND PageID = {1}id", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            if (rdr.Read())

                            {

                                page.Id = rdr.GetGuid(0);

                                page.Title = rdr.IsDBNull(1) ? String.Empty : rdr.GetString(1);

                                page.Content = rdr.IsDBNull(3) ? String.Empty : rdr.GetString(3);

                                page.Description = rdr.IsDBNull(2) ? String.Empty : rdr.GetString(2);

                                if (!rdr.IsDBNull(4))

                                {

                                    page.DateCreated = rdr.GetDateTime(4);

                                }



                                if (!rdr.IsDBNull(5))

                                {

                                    page.DateModified = rdr.GetDateTime(5);

                                }



                                if (!rdr.IsDBNull(6))

                                {

                                    page.Keywords = rdr.GetString(6);

                                }



                                if (!rdr.IsDBNull(7))

                                {

                                    page.IsPublished = rdr.GetBoolean(7);

                                }



                                if (!rdr.IsDBNull(8))

                                {

                                    page.IsFrontPage = rdr.GetBoolean(8);

                                }



                                if (!rdr.IsDBNull(9))

                                {

                                    page.Parent = rdr.GetGuid(9);

                                }



                                if (!rdr.IsDBNull(10))

                                {

                                    page.ShowInList = rdr.GetBoolean(10);

                                }



                                if (!rdr.IsDBNull(11))

                                {

                                    page.Slug = rdr.GetString(11);

                                }



                                if (!rdr.IsDBNull(12))

                                {

                                    page.IsDeleted = rdr.GetBoolean(12);

                                }

                            }

                        }

                    }

                }

            }



            return page;

        }



        /// <summary>

        /// Returns a Post based on Id.

        /// </summary>

        /// <param name="id">

        /// The Post ID.

        /// </param>

        /// <returns>

        /// The Post..

        /// </returns>

        public override Post SelectPost(Guid id)

        {

            var post = new Post();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("SELECT PostID, Title, Description, PostContent, DateCreated, DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug, IsDeleted FROM {0}Posts WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);

                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), id.ToString()));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            if (rdr.Read())

                            {

                                post.Id = rdr.GetGuid(0);

                                post.Title = rdr.GetString(1);

                                post.Content = rdr.GetString(3);

                                post.Description = rdr.IsDBNull(2) ? String.Empty : rdr.GetString(2);

                                if (!rdr.IsDBNull(4))

                                {

                                    post.DateCreated = rdr.GetDateTime(4);

                                }



                                if (!rdr.IsDBNull(5))

                                {

                                    post.DateModified = rdr.GetDateTime(5);

                                }



                                if (!rdr.IsDBNull(6))

                                {

                                    post.Author = rdr.GetString(6);

                                }



                                if (!rdr.IsDBNull(7))

                                {

                                    post.IsPublished = rdr.GetBoolean(7);

                                }



                                if (!rdr.IsDBNull(8))

                                {

                                    post.HasCommentsEnabled = rdr.GetBoolean(8);

                                }



                                if (!rdr.IsDBNull(9))

                                {

                                    post.Raters = rdr.GetInt32(9);

                                }



                                if (!rdr.IsDBNull(10))

                                {

                                    post.Rating = rdr.GetFloat(10);

                                }



                                post.Slug = !rdr.IsDBNull(11) ? rdr.GetString(11) : string.Empty;



                                if (!rdr.IsDBNull(12))

                                {

                                    post.IsDeleted = rdr.GetBoolean(12);

                                }

                            }

                        }



                        // Tags

                        cmd.CommandText = string.Format("SELECT Tag FROM {0}PostTag WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);

                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                if (!rdr.IsDBNull(0))

                                {

                                    post.Tags.Add(rdr.GetString(0));

                                }

                            }

                        }



                        post.Tags.MarkOld();



                        // Categories

                        cmd.CommandText = string.Format("SELECT CategoryID FROM {0}PostCategory WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);

                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                var key = rdr.GetGuid(0);

                                if (Category.GetCategory(key) != null)

                                {

                                    post.Categories.Add(Category.GetCategory(key));

                                }

                            }

                        }



                        // Comments

                        cmd.CommandText = string.Format("SELECT PostCommentID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved, ParentCommentID, ModeratedBy, Avatar, IsSpam, IsDeleted FROM {0}PostComment WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);

                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                var comment = new Comment

                                    {

                                        Id = rdr.GetGuid(0),

                                        IsApproved = true,

                                        Author = rdr.GetString(2)

                                    };

                                if (!rdr.IsDBNull(4))

                                {

                                    Uri website;

                                    if (Uri.TryCreate(rdr.GetString(4), UriKind.Absolute, out website))

                                    {

                                        comment.Website = website;

                                    }

                                }



                                comment.Email = rdr.GetString(3);

                                comment.Content = rdr.GetString(5);

                                comment.DateCreated = rdr.GetDateTime(1);

                                comment.Parent = post;



                                if (!rdr.IsDBNull(6))

                                {

                                    comment.Country = rdr.GetString(6);

                                }



                                if (!rdr.IsDBNull(7))

                                {

                                    comment.IP = rdr.GetString(7);

                                }



                                comment.IsApproved = rdr.IsDBNull(8) || rdr.GetBoolean(8);



                                comment.ParentId = rdr.GetGuid(9);



                                if (!rdr.IsDBNull(10))

                                {

                                    comment.ModeratedBy = rdr.GetString(10);

                                }



                                if (!rdr.IsDBNull(11))

                                {

                                    comment.Avatar = rdr.GetString(11);

                                }



                                if (!rdr.IsDBNull(12))

                                {

                                    comment.IsSpam = rdr.GetBoolean(12);

                                }



                                if (!rdr.IsDBNull(13))

                                {

                                    comment.IsDeleted = rdr.GetBoolean(13);

                                }



                                post.AllComments.Add(comment);

                            }

                        }



                        post.AllComments.Sort();



                        // Email Notification

                        cmd.CommandText = string.Format("SELECT NotifyAddress FROM {0}PostNotify WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);

                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                if (!rdr.IsDBNull(0))

                                {

                                    var email = rdr.GetString(0);

                                    if (post.NotificationEmails.Contains(email))

                                    {

                                        post.NotificationEmails.Add(email);

                                    }

                                }

                            }

                        }

                    }

                }

            }





            return post;

        }



        /// <summary>

        /// Loads AuthorProfile from database

        /// </summary>

        /// <param name="id">The user name.</param>

        /// <returns>An AuthorProfile.</returns>

        public override AuthorProfile SelectProfile(string id)

        {

            var dic = new StringDictionary();

            var profile = new AuthorProfile(id);



            // Retrieve Profile data from Db



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT SettingName, SettingValue FROM {0}Profiles WHERE BlogID = {1}blogid AND UserName = {1}name", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("name"), id));



                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                dic.Add(rdr.GetString(0), rdr.GetString(1));

                            }

                        }

                    }

                }

            }



            // Load profile with data from dictionary

            if (dic.ContainsKey("DisplayName"))

            {

                profile.DisplayName = dic["DisplayName"];

            }



            if (dic.ContainsKey("FirstName"))

            {

                profile.FirstName = dic["FirstName"];

            }



            if (dic.ContainsKey("MiddleName"))

            {

                profile.MiddleName = dic["MiddleName"];

            }



            if (dic.ContainsKey("LastName"))

            {

                profile.LastName = dic["LastName"];

            }



            if (dic.ContainsKey("CityTown"))

            {

                profile.CityTown = dic["CityTown"];

            }



            if (dic.ContainsKey("RegionState"))

            {

                profile.RegionState = dic["RegionState"];

            }



            if (dic.ContainsKey("Country"))

            {

                profile.Country = dic["Country"];

            }



            if (dic.ContainsKey("Birthday"))

            {

                DateTime date;

                if (DateTime.TryParse(dic["Birthday"], out date))

                {

                    profile.Birthday = date;

                }

            }



            if (dic.ContainsKey("AboutMe"))

            {

                profile.AboutMe = dic["AboutMe"];

            }



            if (dic.ContainsKey("PhotoURL"))

            {

                profile.PhotoUrl = dic["PhotoURL"];

            }



            if (dic.ContainsKey("Company"))

            {

                profile.Company = dic["Company"];

            }



            if (dic.ContainsKey("EmailAddress"))

            {

                profile.EmailAddress = dic["EmailAddress"];

            }



            if (dic.ContainsKey("PhoneMain"))

            {

                profile.PhoneMain = dic["PhoneMain"];

            }



            if (dic.ContainsKey("PhoneMobile"))

            {

                profile.PhoneMobile = dic["PhoneMobile"];

            }



            if (dic.ContainsKey("PhoneFax"))

            {

                profile.PhoneFax = dic["PhoneFax"];

            }



            if (dic.ContainsKey("IsPrivate"))

            {

                profile.Private = dic["IsPrivate"] == "true";

            }



            return profile;

        }



        /// <summary>

        /// Gets a Referrer based on an Id.

        /// </summary>

        /// <param name="id">

        /// The Referrer Id.

        /// </param>

        /// <returns>

        /// A matching Referrer

        /// </returns>

        public override Referrer SelectReferrer(Guid id)

        {

            var refer = Referrer.Referrers.Find(r => r.Id.Equals(id)) ?? new Referrer();



            refer.MarkOld();

            return refer;

        }



        /// <summary>

        /// Sets up the required storage files/tables for a new Blog instance, from an existing blog instance.

        /// </summary>

        /// <param name="existingBlog">

        /// The existing blog to copy from.

        /// </param>

        /// <param name="newBlog">

        /// The new blog to copy to.

        /// </param>

        public override bool SetupBlogFromExistingBlog(Blog existingBlog, Blog newBlog)

        {

            // Even for the DbBlogProvider, we call newBlog.CopyExistingBlogFolderToNewBlogFolder().

            // The reasons are that a small number of extensions/widgets use App_Data even if

            // the DbBlogProvider is being used (Newsletter widget, Logger extension, and any

            // other custom components written by other people).  Also, even if the

            // DbBlogProvider is being used, the XmlMembershipProvider and XmlRoleProvider could

            // also be used, which stores data in App_Data.

            // So as a rule of thumb, whenever a new blog instance is created, we will create

            // a new folder in App_Data for that new instance, and copy all the files/folders in.



            bool copyResult = newBlog.CopyExistingBlogFolderToNewBlogFolder(existingBlog);



            if (!copyResult)

            {

                Utils.Log("DbBlogProvider.SetupBlogFromExistingBlog", new Exception("Unsuccessful result from newBlog.CopyExistingBlogFolderToNewBlogFolder."));

                return false;

            }



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    // be_BlogRollItems

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}BlogRollItems ( BlogId, BlogRollId, Title, Description, BlogUrl, FeedUrl, Xfn, SortIndex ) " +

                        " SELECT {1}newblogid, BlogRollId, Title, Description, BlogUrl, FeedUrl, Xfn, SortIndex " +

                        " FROM {0}BlogRollItems " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_Categories

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}Categories ( BlogID, CategoryID, CategoryName, Description, ParentID ) " +

                        " SELECT {1}newblogid, CategoryID, CategoryName, Description, ParentID " +

                        " FROM {0}Categories " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_DataStoreSettings

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}DataStoreSettings ( BlogId, ExtensionType, ExtensionId, Settings ) " +

                        " SELECT {1}newblogid, ExtensionType, ExtensionId, Settings " +

                        " FROM {0}DataStoreSettings " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_Pages

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}Pages ( BlogID, PageID, Title, Description, PageContent, Keywords, DateCreated, DateModified, IsPublished, IsFrontPage, Parent, ShowInList, Slug, IsDeleted ) " +

                        " SELECT {1}newblogid, PageID, Title, Description, PageContent, Keywords, DateCreated, DateModified, IsPublished, IsFrontPage, Parent, ShowInList, Slug, IsDeleted " +

                        " FROM {0}Pages " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_PingService

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}PingService ( BlogID, Link ) " +

                        " SELECT {1}newblogid, Link " +

                        " FROM {0}PingService " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_Profiles

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}Profiles ( BlogID, UserName, SettingName, SettingValue ) " +

                        " SELECT {1}newblogid, UserName, SettingName, SettingValue " +

                        " FROM {0}Profiles " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_Referrers

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}Referrers ( BlogId, ReferrerId, ReferralDay, ReferrerUrl, ReferralCount, Url, IsSpam ) " +

                        " SELECT {1}newblogid, ReferrerId, ReferralDay, ReferrerUrl, ReferralCount, Url, IsSpam " +

                        " FROM {0}Referrers " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_Rights

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}Rights ( BlogId, RightName ) " +

                        " SELECT {1}newblogid, RightName " +

                        " FROM {0}Rights " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_RightRoles

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}RightRoles ( BlogId, RightName, Role ) " +

                        " SELECT {1}newblogid, RightName, Role " +

                        " FROM {0}RightRoles " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_Settings

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}Settings ( BlogId, SettingName, SettingValue ) " +

                        " SELECT {1}newblogid, SettingName, SettingValue " +

                        " FROM {0}Settings " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_StopWords

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}StopWords ( BlogId, StopWord ) " +

                        " SELECT {1}newblogid, StopWord " +

                        " FROM {0}StopWords " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_Posts

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}Posts ( BlogId, PostID, Title, Description, PostContent, DateCreated, DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug, IsDeleted ) " +

                        " SELECT {1}newblogid, PostID, Title, Description, PostContent, DateCreated, DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug, IsDeleted " +

                        " FROM {0}Posts " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_PostCategory

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}PostCategory ( BlogId, PostID, CategoryID ) " +

                        " SELECT {1}newblogid, PostID, CategoryID " +

                        " FROM {0}PostCategory " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_PostComment

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}PostComment ( BlogId, PostCommentID, PostID, ParentCommentID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved, ModeratedBy, Avatar, IsSpam, IsDeleted ) " +

                        " SELECT {1}newblogid, PostCommentID, PostID, ParentCommentID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved, ModeratedBy, Avatar, IsSpam, IsDeleted " +

                        " FROM {0}PostComment " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_PostNotify

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}PostNotify ( BlogId, PostID, NotifyAddress ) " +

                        " SELECT {1}newblogid, PostID, NotifyAddress " +

                        " FROM {0}PostNotify " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_PostTag

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}PostTag ( BlogId, PostID, Tag ) " +

                        " SELECT {1}newblogid, PostID, Tag " +

                        " FROM {0}PostTag " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    //////////////////////////////////////

                    // The DbMembershipProvider and DbRoleProvider may or may not be in use.

                    // Even if it's not in use, copy the rows for the Users and Roles tables.

                    //



                    // be_Users

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}Users ( BlogId, UserName, Password, LastLoginTime, EmailAddress ) " +

                        " SELECT {1}newblogid, UserName, Password, LastLoginTime, EmailAddress " +

                        " FROM {0}Users " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_Roles

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}Roles ( BlogId, Role ) " +

                        " SELECT {1}newblogid, Role " +

                        " FROM {0}Roles " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }



                    // be_UserRoles

                    using (var cmd = conn.CreateTextCommand(string.Format(

                        " INSERT INTO {0}UserRoles ( BlogId, UserName, Role ) " +

                        " SELECT {1}newblogid, UserName, Role " +

                        " FROM {0}UserRoles " +

                        " WHERE BlogID = {1}existingblogid ", this.tablePrefix, this.parmPrefix)))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("newblogid"), newBlog.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("existingblogid"), existingBlog.Id.ToString()));

                        cmd.ExecuteNonQuery();

                    }

                }

            }



            return true;

        }



        /// <summary>

        /// Saves an existing BlogRoll to the database

        /// </summary>

        /// <param name="blogRollItem">

        /// BlogRoll to be saved

        /// </param>

        public override void UpdateBlogRollItem(BlogRollItem blogRollItem)

        {

            var blogRolls = BlogRollItem.BlogRolls;

            blogRolls.Remove(blogRollItem);

            blogRolls.Add(blogRollItem);



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("UPDATE {0}BlogRollItems SET Title = {1}Title, Description = {1}Description, BlogUrl = {1}BlogUrl, FeedUrl = {1}FeedUrl, Xfn = {1}Xfn, SortIndex = {1}SortIndex WHERE BlogId = {1}BlogId AND BlogRollId = {1}BlogRollId", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        this.AddBlogRollParametersToCommand(blogRollItem, conn, cmd);

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Saves an existing Blog to the database

        /// </summary>

        /// <param name="blog">

        /// Blog to be saved

        /// </param>

        public override void UpdateBlog(Blog blog)

        {

            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("UPDATE {0}Blogs SET BlogName = {1}BlogName, Hostname = {1}Hostname, IsAnyTextBeforeHostnameAccepted = {1}IsAnyTextBeforeHostnameAccepted, StorageContainerName = {1}StorageContainerName, VirtualPath = {1}VirtualPath, IsPrimary = {1}IsPrimary, IsActive = {1}IsActive WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        this.AddBlogParametersToCommand(blog, conn, cmd);

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Saves an existing category to the database

        /// </summary>

        /// <param name="category">

        /// category to be saved

        /// </param>

        public override void UpdateCategory(Category category)

        {

            var categories = Category.Categories;

            categories.Remove(category);

            categories.Add(category);

            categories.Sort();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("UPDATE {0}Categories SET CategoryName = {1}catname, Description = {1}description, ParentID = {1}parentid WHERE BlogID = {1}blogid AND CategoryID = {1}catid", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {



                        var p = cmd.Parameters;



                        p.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        p.Add(conn.CreateParameter(FormatParamName("catid"), category.Id.ToString()));

                        p.Add(conn.CreateParameter(FormatParamName("catname"), category.Title));

                        p.Add(conn.CreateParameter(FormatParamName("description"), category.Description));

                        p.Add(conn.CreateParameter(FormatParamName("parentid"), (category.Parent == null ? (object)DBNull.Value : category.Parent.ToString())));



                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Saves an existing page in the database

        /// </summary>

        /// <param name="page">

        /// page to be saved

        /// </param>

        public override void UpdatePage(Page page)

        {

            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("UPDATE {0}Pages SET Title = {1}title, Description = {1}desc, PageContent = {1}content, DateCreated = {1}created, DateModified = {1}modified, Keywords = {1}keywords, IsPublished = {1}ispublished, IsFrontPage = {1}isfrontpage, Parent = {1}parent, ShowInList = {1}showinlist, Slug = {1}slug, IsDeleted = {1}isdeleted WHERE BlogID = {1}blogid AND PageID = {1}id", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        var p = cmd.Parameters;



                        p.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        p.Add(conn.CreateParameter(FormatParamName("id"), page.Id.ToString()));

                        p.Add(conn.CreateParameter(FormatParamName("title"), page.Title));

                        p.Add(conn.CreateParameter(FormatParamName("desc"), page.Description));

                        p.Add(conn.CreateParameter(FormatParamName("content"), page.Content));

                        p.Add(conn.CreateParameter(FormatParamName("created"), page.DateCreated.AddHours(-BlogSettings.Instance.Timezone)));

                        p.Add(conn.CreateParameter(FormatParamName("modified"), (page.DateModified == new DateTime() ? DateTime.Now : page.DateModified.AddHours(-BlogSettings.Instance.Timezone))));

                        p.Add(conn.CreateParameter(FormatParamName("keywords"), page.Keywords));

                        p.Add(conn.CreateParameter(FormatParamName("ispublished"), page.IsPublished));

                        p.Add(conn.CreateParameter(FormatParamName("isfrontpage"), page.IsFrontPage));

                        p.Add(conn.CreateParameter(FormatParamName("parent"), page.Parent.ToString()));

                        p.Add(conn.CreateParameter(FormatParamName("showinlist"), page.ShowInList));

                        p.Add(conn.CreateParameter(FormatParamName("slug"), page.Slug));

                        p.Add(conn.CreateParameter(FormatParamName("isdeleted"), page.IsDeleted));



                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Saves and existing post in the database

        /// </summary>

        /// <param name="post">

        /// post to be saved

        /// </param>

        public override void UpdatePost(Post post)

        {

            using (TransactionScope ts = new TransactionScope())

            {

                using (var conn = this.CreateConnection())

                {

                    if (conn.HasConnection)

                    {

                        var sqlQuery = string.Format("UPDATE {0}Posts SET Title = {1}title, Description = {1}desc, PostContent = {1}content, DateCreated = {1}created, DateModified = {1}modified, Author = {1}Author, IsPublished = {1}published, IsCommentEnabled = {1}commentEnabled, Raters = {1}raters, Rating = {1}rating, Slug = {1}slug, IsDeleted = {1}isdeleted WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);



                        using (var cmd = conn.CreateTextCommand(sqlQuery))

                        {



                            var p = cmd.Parameters;



                            p.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                            p.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                            p.Add(conn.CreateParameter(FormatParamName("title"), post.Title));

                            p.Add(conn.CreateParameter(FormatParamName("desc"), (post.Description ?? string.Empty)));

                            p.Add(conn.CreateParameter(FormatParamName("content"), post.Content));

                            p.Add(conn.CreateParameter(FormatParamName("created"), post.DateCreated.AddHours(-BlogSettings.Instance.Timezone)));

                            p.Add(conn.CreateParameter(FormatParamName("modified"), (post.DateModified == new DateTime() ? DateTime.Now : post.DateModified.AddHours(-BlogSettings.Instance.Timezone))));

                            p.Add(conn.CreateParameter(FormatParamName("author"), (post.Author ?? string.Empty)));

                            p.Add(conn.CreateParameter(FormatParamName("published"), post.IsPublished));

                            p.Add(conn.CreateParameter(FormatParamName("commentEnabled"), post.HasCommentsEnabled));

                            p.Add(conn.CreateParameter(FormatParamName("raters"), post.Raters));

                            p.Add(conn.CreateParameter(FormatParamName("rating"), post.Rating));

                            p.Add(conn.CreateParameter(FormatParamName("slug"), (post.Slug ?? string.Empty)));

                            p.Add(conn.CreateParameter(FormatParamName("isdeleted"), post.IsDeleted));



                            cmd.ExecuteNonQuery();

                        }



                        // Tags

                        this.UpdateTags(post, conn);



                        // Categories

                        this.UpdateCategories(post, conn);



                        // Comments

                        this.UpdateComments(post, conn);



                        // Email Notification

                        this.UpdateNotify(post, conn);

                    }

                }

                ts.Complete();

            }

        }



        /// <summary>

        /// Updates AuthorProfile to database

        /// </summary>

        /// <param name="profile">

        /// An AuthorProfile.

        /// </param>

        public override void UpdateProfile(AuthorProfile profile)

        {

            // Remove Profile

            this.DeleteProfile(profile);



            // Create Profile Dictionary

            var dic = new StringDictionary();



            if (!String.IsNullOrEmpty(profile.DisplayName))

            {

                dic.Add("DisplayName", profile.DisplayName);

            }



            if (!String.IsNullOrEmpty(profile.FirstName))

            {

                dic.Add("FirstName", profile.FirstName);

            }



            if (!String.IsNullOrEmpty(profile.MiddleName))

            {

                dic.Add("MiddleName", profile.MiddleName);

            }



            if (!String.IsNullOrEmpty(profile.LastName))

            {

                dic.Add("LastName", profile.LastName);

            }



            if (!String.IsNullOrEmpty(profile.CityTown))

            {

                dic.Add("CityTown", profile.CityTown);

            }



            if (!String.IsNullOrEmpty(profile.RegionState))

            {

                dic.Add("RegionState", profile.RegionState);

            }



            if (!String.IsNullOrEmpty(profile.Country))

            {

                dic.Add("Country", profile.Country);

            }



            if (!String.IsNullOrEmpty(profile.AboutMe))

            {

                dic.Add("AboutMe", profile.AboutMe);

            }



            if (!String.IsNullOrEmpty(profile.PhotoUrl))

            {

                dic.Add("PhotoURL", profile.PhotoUrl);

            }



            if (!String.IsNullOrEmpty(profile.Company))

            {

                dic.Add("Company", profile.Company);

            }



            if (!String.IsNullOrEmpty(profile.EmailAddress))

            {

                dic.Add("EmailAddress", profile.EmailAddress);

            }



            if (!String.IsNullOrEmpty(profile.PhoneMain))

            {

                dic.Add("PhoneMain", profile.PhoneMain);

            }



            if (!String.IsNullOrEmpty(profile.PhoneMobile))

            {

                dic.Add("PhoneMobile", profile.PhoneMobile);

            }



            if (!String.IsNullOrEmpty(profile.PhoneFax))

            {

                dic.Add("PhoneFax", profile.PhoneFax);

            }



            if (profile.Birthday != DateTime.MinValue)

            {

                dic.Add("Birthday", profile.Birthday.ToString("yyyy-MM-dd"));

            }



            dic.Add("IsPrivate", profile.Private.ToString());



            // Save Profile Dictionary



            using (var conn = this.CreateConnection())

            {

                using (var cmd = conn.CreateCommand())

                {

                    foreach (string key in dic.Keys)

                    {

                        var sqlQuery = string.Format("INSERT INTO {0}Profiles (BlogID, UserName, SettingName, SettingValue) VALUES ({1}blogid, {1}user, {1}name, {1}value)", this.tablePrefix, this.parmPrefix);



                        cmd.CommandText = sqlQuery;

                        cmd.Parameters.Clear();

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("user"), profile.Id));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("name"), key));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("value"), dic[key]));



                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        /// <summary>

        /// Saves an existing Referrer to the database.

        /// </summary>

        /// <param name="referrer">

        /// Referrer to be saved.

        /// </param>

        public override void UpdateReferrer(Referrer referrer)

        {

            var referrers = Referrer.Referrers;

            referrers.Remove(referrer);

            referrers.Add(referrer);



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("UPDATE {0}Referrers SET ReferralDay = {1}ReferralDay, ReferrerUrl = {1}ReferrerUrl, ReferralCount = {1}ReferralCount, Url = {1}Url, IsSpam = {1}IsSpam WHERE BlogId = {1}BlogId AND ReferrerId = {1}ReferrerId", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        this.AddReferrersParametersToCommand(referrer, conn, cmd);



                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        #region Packaging



        /// <summary>

        /// Log of all installed packages

        /// </summary>

        /// <param name="package">Intalled package</param>

        public override void SavePackage(InstalledPackage package)

        {

            using (var conn = CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("INSERT INTO {0}Packages (PackageId, [Version]) VALUES ({1}PackageId, {1}Version)", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        var parms = cmd.Parameters;

                        parms.Add(conn.CreateParameter(FormatParamName("PackageId"), package.PackageId));

                        parms.Add(conn.CreateParameter(FormatParamName("Version"), package.Version));

 

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }

        /// <summary>

        /// Log of all files for installed package

        /// </summary>

        /// <param name="packageFiles">List of intalled package files</param>

        public override void SavePackageFiles(List<PackageFile> packageFiles)

        {

            using (var conn = CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("INSERT INTO {0}PackageFiles (PackageId, FileOrder, FilePath, IsDirectory) VALUES ({1}PackageId, {1}FileOrder, {1}FilePath, {1}IsDirectory)", this.tablePrefix, this.parmPrefix);



                    foreach (var file in packageFiles)

                    {

                        using (var cmd = conn.CreateTextCommand(sqlQuery))

                        {

                            var parms = cmd.Parameters;

                            parms.Add(conn.CreateParameter(FormatParamName("PackageId"), file.PackageId));

                            parms.Add(conn.CreateParameter(FormatParamName("FileOrder"), file.FileOrder));

                            parms.Add(conn.CreateParameter(FormatParamName("FilePath"), file.FilePath));

                            parms.Add(conn.CreateParameter(FormatParamName("IsDirectory"), file.IsDirectory));



                            cmd.ExecuteNonQuery();

                        }

                    }

                }

            }

        }

        /// <summary>

        /// Gets list of files for installed package

        /// </summary>

        /// <param name="packageId">Package ID</param>

        /// <param name="version">Version</param>

        /// <returns>List of files for installed package</returns>

        public override List<PackageFile> FillPackageFiles(string packageId)

        {

            var files = new List<PackageFile>();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT PackageId, FileOrder, FilePath, IsDirectory FROM {0}PackageFiles ", this.tablePrefix)))

                    {

                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                var f = new PackageFile()

                                {

                                    PackageId = rdr.GetString(0),

                                    FileOrder = rdr.GetInt32(1),

                                    FilePath = rdr.GetString(2),

                                    IsDirectory = rdr.GetBoolean(3)

                                };



                                files.Add(f);

                            }

                        }

                    }

                }

            }



            return files;

        }

        /// <summary>

        /// Gets all installed from gallery packages

        /// </summary>

        /// <returns>List of installed packages</returns>

        public override List<InstalledPackage> FillPackages()

        {

            var packages = new List<InstalledPackage>();



            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT PackageId, Version FROM {0}Packages ", tablePrefix)))

                    {

                        using (var rdr = cmd.ExecuteReader())

                        {

                            while (rdr.Read())

                            {

                                var p = new InstalledPackage()

                                {

                                    PackageId = rdr.GetString(0),

                                    Version = rdr.GetString(1)

                                };



                                packages.Add(p);

                            }

                        }

                    }

                }

            }



            return packages;

        }

        /// <summary>

        /// Should delete package and remove all package files

        /// </summary>

        /// <param name="packageId">Package ID</param>

        public override void DeletePackage(string packageId)

        {

            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    var sqlQuery = string.Format("DELETE FROM {0}PackageFiles WHERE PackageId = {1}PackageId", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("PackageId"), packageId));

                        cmd.ExecuteNonQuery();

                    }



                    sqlQuery = string.Format("DELETE FROM {0}Packages WHERE PackageId = {1}PackageId", this.tablePrefix, this.parmPrefix);



                    using (var cmd = conn.CreateTextCommand(sqlQuery))

                    {

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("PackageId"), packageId));

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        #endregion



        /// <summary>

        /// Returns a formatted parameter name to include this DbBlogProvider instance's paramPrefix.

        /// </summary>

        /// <param name="parameterName"></param>

        /// <returns></returns>

        private string FormatParamName(string parameterName)

        {

            return String.Format("{0}{1}", this.parmPrefix, parameterName);

        }



        #endregion



        #region Methods









        /// <summary>

        /// The update categories.

        /// </summary>

        /// <param name="post">

        /// The post to update.

        /// </param>

        /// <param name="conn">

        /// The connection.

        /// </param>

        private void UpdateCategories(Post post, DbConnectionHelper conn)

        {

            var sqlQuery = string.Format("DELETE FROM {0}PostCategory WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);

            using (var cmd = conn.CreateTextCommand(sqlQuery))

            {

                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));



                cmd.ExecuteNonQuery();



                foreach (var cat in post.Categories)

                {

                    cmd.CommandText = string.Format("INSERT INTO {0}PostCategory (BlogID, PostID, CategoryID) VALUES ({1}blogid, {1}id, {1}cat)", this.tablePrefix, this.parmPrefix);

                    cmd.Parameters.Clear();

                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("cat"), cat.Id.ToString()));



                    cmd.ExecuteNonQuery();

                }

            }

        }



        /// <summary>

        /// The update comments.

        /// </summary>

        /// <param name="post">

        /// The post to update.

        /// </param>

        /// <param name="conn">

        /// The connection.

        /// </param>

        private void UpdateComments(Post post, DbConnectionHelper conn)

        {

            var sqlQuery = string.Format("DELETE FROM {0}PostComment WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);

            using (var cmd = conn.CreateTextCommand(sqlQuery))

            {



                var parms = cmd.Parameters;



                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                parms.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));



                cmd.ExecuteNonQuery();



                foreach (var comment in post.AllComments)

                {

                    sqlQuery = string.Format("INSERT INTO {0}PostComment (BlogID, PostCommentID, ParentCommentID, PostID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved, ModeratedBy, Avatar, IsSpam, IsDeleted) VALUES ({1}blogid, {1}postcommentid, {1}parentid, {1}id, {1}date, {1}author, {1}email, {1}website, {1}comment, {1}country, {1}ip, {1}isapproved, {1}moderatedby, {1}avatar, {1}isspam, {1}isdeleted)", this.tablePrefix, this.parmPrefix);



                    cmd.CommandText = sqlQuery;

                    parms.Clear();



                    parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                    parms.Add(conn.CreateParameter(FormatParamName("postcommentid"), comment.Id.ToString()));

                    parms.Add(conn.CreateParameter(FormatParamName("parentid"), comment.ParentId.ToString()));

                    parms.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                    parms.Add(conn.CreateParameter(FormatParamName("date"), comment.DateCreated.AddHours(-BlogSettings.Instance.Timezone)));

                    parms.Add(conn.CreateParameter(FormatParamName("author"), comment.Author));

                    parms.Add(conn.CreateParameter(FormatParamName("email"), (comment.Email ?? string.Empty)));

                    parms.Add(conn.CreateParameter(FormatParamName("website"), (comment.Website == null ? string.Empty : comment.Website.ToString())));

                    parms.Add(conn.CreateParameter(FormatParamName("comment"), comment.Content));

                    parms.Add(conn.CreateParameter(FormatParamName("country"), (comment.Country ?? string.Empty)));

                    parms.Add(conn.CreateParameter(FormatParamName("ip"), (comment.IP ?? string.Empty)));

                    parms.Add(conn.CreateParameter(FormatParamName("isapproved"), comment.IsApproved));

                    parms.Add(conn.CreateParameter(FormatParamName("moderatedby"), (comment.ModeratedBy ?? string.Empty)));

                    parms.Add(conn.CreateParameter(FormatParamName("avatar"), (comment.Avatar ?? string.Empty)));

                    parms.Add(conn.CreateParameter(FormatParamName("isspam"), comment.IsSpam));

                    parms.Add(conn.CreateParameter(FormatParamName("isdeleted"), comment.IsDeleted));



                    cmd.ExecuteNonQuery();

                }

            }

        }



        /// <summary>

        /// The update notify.

        /// </summary>

        /// <param name="post">

        /// The post to update.

        /// </param>

        /// <param name="conn">

        /// The connection.

        /// </param>

        private void UpdateNotify(Post post, DbConnectionHelper conn)

        {

            var sqlQuery = string.Format("DELETE FROM {0}PostNotify WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);

            using (var cmd = conn.CreateTextCommand(sqlQuery))

            {

                var parms = cmd.Parameters;



                parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                parms.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));



                cmd.ExecuteNonQuery();



                foreach (var email in post.NotificationEmails)

                {

                    cmd.CommandText = string.Format("INSERT INTO {0}PostNotify (BlogID, PostID, NotifyAddress) VALUES ({1}blogid, {1}id, {1}notify)", this.tablePrefix, this.parmPrefix);

                    parms.Clear();



                    parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                    parms.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                    parms.Add(conn.CreateParameter(FormatParamName("notify"), email));



                    cmd.ExecuteNonQuery();

                }

            }

        }



        /// <summary>

        /// The update tags.

        /// </summary>

        /// <param name="post">

        /// The post to update.

        /// </param>

        /// <param name="conn">

        /// The connection

        /// </param>

        private void UpdateTags(Post post, DbConnectionHelper conn)

        {

            var sqlQuery = string.Format("DELETE FROM {0}PostTag WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);

            using (var cmd = conn.CreateTextCommand(sqlQuery))

            {

                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                cmd.ExecuteNonQuery();



                foreach (var tag in post.Tags)

                {

                    cmd.CommandText = string.Format("INSERT INTO {0}PostTag (BlogID, PostID, Tag) VALUES ({1}blogid, {1}id, {1}tag)", this.tablePrefix, this.parmPrefix);

                    cmd.Parameters.Clear();



                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));

                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("tag"), tag));



                    cmd.ExecuteNonQuery();

                }

            }

        }



        /// <summary>

        /// The add blog roll parameters to command.

        /// </summary>

        /// <param name="blogRollItem">

        /// The blog roll item.

        /// </param>

        /// <param name="conn">

        /// The connection.

        /// </param>

        /// <param name="cmd">

        /// The command.

        /// </param>

        private void AddBlogRollParametersToCommand(

            BlogRollItem blogRollItem, DbConnectionHelper conn, DbCommand cmd)

        {



            var parms = cmd.Parameters;

            parms.Add(conn.CreateParameter(FormatParamName("BlogId"), Blog.CurrentInstance.Id.ToString()));

            parms.Add(conn.CreateParameter(FormatParamName("BlogRollId"), blogRollItem.Id.ToString()));

            parms.Add(conn.CreateParameter(FormatParamName("Title"), blogRollItem.Title));

            parms.Add(conn.CreateParameter(FormatParamName("Description"), blogRollItem.Description));

            parms.Add(conn.CreateParameter(FormatParamName("BlogUrl"), (blogRollItem.BlogUrl != null ? (object)blogRollItem.BlogUrl.ToString() : DBNull.Value)));

            parms.Add(conn.CreateParameter(FormatParamName("FeedUrl"), (blogRollItem.FeedUrl != null ? (object)blogRollItem.FeedUrl.ToString() : DBNull.Value)));

            parms.Add(conn.CreateParameter(FormatParamName("Xfn"), blogRollItem.Xfn));

            parms.Add(conn.CreateParameter(FormatParamName("SortIndex"), blogRollItem.SortIndex));

        }



        /// <summary>

        /// Adds blog parameters to command.

        /// </summary>

        /// <param name="blog">

        /// The blog.

        /// </param>

        /// <param name="conn">

        /// The connection.

        /// </param>

        /// <param name="cmd">

        /// The command.

        /// </param>

        private void AddBlogParametersToCommand(

            Blog blog, DbConnectionHelper conn, DbCommand cmd)

        {

            var parms = cmd.Parameters;

            parms.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));

            parms.Add(conn.CreateParameter(FormatParamName("BlogName"), blog.Name ?? string.Empty));

            parms.Add(conn.CreateParameter(FormatParamName("Hostname"), blog.Hostname ?? string.Empty));

            parms.Add(conn.CreateParameter(FormatParamName("IsAnyTextBeforeHostnameAccepted"), blog.IsAnyTextBeforeHostnameAccepted));

            parms.Add(conn.CreateParameter(FormatParamName("StorageContainerName"), blog.StorageContainerName));

            parms.Add(conn.CreateParameter(FormatParamName("VirtualPath"), blog.VirtualPath ?? string.Empty));

            parms.Add(conn.CreateParameter(FormatParamName("IsPrimary"), blog.IsPrimary));

            parms.Add(conn.CreateParameter(FormatParamName("IsActive"), blog.IsActive));

        }



        /// <summary>

        /// The add referrers parameters to command.

        /// </summary>

        /// <param name="referrer">

        /// The referrer.

        /// </param>

        /// <param name="conn">

        /// The connection.

        /// </param>

        /// <param name="cmd">

        /// The command.

        /// </param>

        private void AddReferrersParametersToCommand(Referrer referrer, DbConnectionHelper conn, DbCommand cmd)

        {

            var parms = cmd.Parameters;



            parms.Add(conn.CreateParameter("BlogId", Blog.CurrentInstance.Id.ToString()));

            parms.Add(conn.CreateParameter("ReferrerId", referrer.Id.ToString()));

            parms.Add(conn.CreateParameter(FormatParamName("ReferralDay"), referrer.Day));

            parms.Add(conn.CreateParameter(FormatParamName("ReferrerUrl"), (referrer.ReferrerUrl != null ? (object)referrer.ReferrerUrl.ToString() : DBNull.Value)));

            parms.Add(conn.CreateParameter(FormatParamName("ReferralCount"), referrer.Count));

            parms.Add(conn.CreateParameter(FormatParamName("Url"), (referrer.Url != null ? (object)referrer.Url.ToString() : DBNull.Value)));

            parms.Add(conn.CreateParameter(FormatParamName("IsSpam"), referrer.PossibleSpam));

        }



        /// <summary>

        /// The delete old referrers.

        /// </summary>

        private void DeleteOldReferrers()

        {

            using (var conn = this.CreateConnection())

            {

                if (conn.HasConnection)

                {

                    using (var cmd = conn.CreateTextCommand(string.Format("DELETE FROM {0}Referrers WHERE BlogId = {1}BlogId AND ReferralDay < {1}ReferralDay", this.tablePrefix, this.parmPrefix)))

                    {

                        var cutoff = DateTime.Today.AddDays(-BlogSettings.Instance.NumberOfReferrerDays);



                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), Blog.CurrentInstance.Id.ToString()));

                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("ReferralDay"), cutoff));



                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }



        #endregion





        /// <summary>

        /// Creates a new DbConnectionHelper for this DbBlogProvider instance.

        /// </summary>

        /// <returns></returns>

        private DbConnectionHelper CreateConnection()

        {

            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[this.connStringName];

            return new DbConnectionHelper(settings);

        }



    }

}