/BlogEngine/DotNetSlave.BusinessLogic/Providers/DbBlogProvider.cs
C# | 2857 lines | 1986 code | 388 blank | 483 comment | 172 complexity | 03be528e128817d70379092dfd1fb2e8 MD5 | raw file
Possible License(s): LGPL-2.1, Apache-2.0, BSD-3-Clause
Large files files are truncated, but you can click here to view the full file
- 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>
- …
Large files files are truncated, but you can click here to view the full file