PageRenderTime 66ms CodeModel.GetById 16ms app.highlight 40ms RepoModel.GetById 1ms app.codeStats 1ms

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

#
C# | 2857 lines | 1986 code | 388 blank | 483 comment | 172 complexity | 03be528e128817d70379092dfd1fb2e8 MD5 | raw file

Large files files are truncated, but you can click here to view the full file

   1using System;
   2using System.Collections.Generic;
   3using System.Collections.Specialized;
   4using System.Configuration;
   5using System.Configuration.Provider;
   6using System.Data.Common;
   7using System.IO;
   8using System.Linq;
   9using System.Xml.Serialization;
  10using System.Transactions;
  11
  12using BlogEngine.Core.DataStore;
  13using BlogEngine.Core.Packaging;
  14
  15namespace BlogEngine.Core.Providers
  16{
  17    /// <summary>
  18    /// Generic Database BlogProvider
  19    /// </summary>
  20    public partial class DbBlogProvider : BlogProvider
  21    {
  22        #region Constants and Fields
  23
  24        /// <summary>
  25        /// The conn string name.
  26        /// </summary>
  27        private string connStringName;
  28
  29        /// <summary>
  30        /// The parm prefix.
  31        /// </summary>
  32        private string parmPrefix;
  33
  34        /// <summary>
  35        /// The table prefix.
  36        /// </summary>
  37        private string tablePrefix;
  38
  39        #endregion
  40
  41        #region Public Methods
  42
  43        /// <summary>
  44        /// Deletes a BlogRoll from the database
  45        /// </summary>
  46        /// <param name="blogRollItem">
  47        /// The blog Roll Item.
  48        /// </param>
  49        public override void DeleteBlogRollItem(BlogRollItem blogRollItem)
  50        {
  51            var blogRolls = BlogRollItem.BlogRolls;
  52            blogRolls.Remove(blogRollItem);
  53            blogRolls.Add(blogRollItem);
  54
  55            using (var conn = this.CreateConnection())
  56            {
  57                if (conn.HasConnection)
  58                {
  59                    var sqlQuery = string.Format("DELETE FROM {0}BlogRollItems WHERE BlogId = {1}BlogId AND BlogRollId = {1}BlogRollId", this.tablePrefix, this.parmPrefix);
  60
  61                    using (var cmd = conn.CreateTextCommand(sqlQuery))
  62                    {
  63                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), Blog.CurrentInstance.Id.ToString()));
  64                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogRollId"), blogRollItem.Id.ToString()));
  65
  66                        cmd.ExecuteNonQuery();
  67                    }
  68                }
  69            }
  70        }
  71
  72        /// <summary>
  73        /// Deletes the blog's storage container.
  74        /// </summary>
  75        /// <param name="blog">
  76        /// The blog.
  77        /// </param>
  78        public override bool DeleteBlogStorageContainer(Blog blog)
  79        {
  80            // First delete the blog folder.  Even the DB provider uses
  81            // the folder.  This is rare and is usually by widgets/extensions.
  82            if (!blog.DeleteBlogFolder())
  83            {
  84                return false;
  85            }
  86
  87            // Delete data from all tables except for be_Blogs.  The blog
  88            // data from be_Blogs will be deleted in DeleteBlog().
  89
  90            using (var conn = this.CreateConnection())
  91            {
  92                if (conn.HasConnection)
  93                {
  94                    // Note, the order here is important, especially for the DBs where
  95                    // foreign key constraints are setup (SQL Server is one).  The data
  96                    // in the referencing tables needs to be deleted first.
  97
  98                    var sqlQuery = string.Format("DELETE FROM {0}PostTag WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
  99                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 100                    {
 101                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 102                        cmd.ExecuteNonQuery();
 103                    }
 104
 105                    sqlQuery = string.Format("DELETE FROM {0}PostNotify WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 106                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 107                    {
 108                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 109                        cmd.ExecuteNonQuery();
 110                    }
 111
 112                    sqlQuery = string.Format("DELETE FROM {0}PostComment WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 113                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 114                    {
 115                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 116                        cmd.ExecuteNonQuery();
 117                    }
 118
 119                    sqlQuery = string.Format("DELETE FROM {0}PostCategory WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 120                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 121                    {
 122                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 123                        cmd.ExecuteNonQuery();
 124                    }
 125
 126                    sqlQuery = string.Format("DELETE FROM {0}Posts WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 127                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 128                    {
 129                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 130                        cmd.ExecuteNonQuery();
 131                    }
 132
 133                    sqlQuery = string.Format("DELETE FROM {0}RightRoles WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 134                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 135                    {
 136                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 137                        cmd.ExecuteNonQuery();
 138                    }
 139
 140                    sqlQuery = string.Format("DELETE FROM {0}Profiles WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 141                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 142                    {
 143                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 144                        cmd.ExecuteNonQuery();
 145                    }
 146
 147                    sqlQuery = string.Format("DELETE FROM {0}UserRoles WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 148                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 149                    {
 150                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 151                        cmd.ExecuteNonQuery();
 152                    }
 153
 154                    sqlQuery = string.Format("DELETE FROM {0}Roles WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 155                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 156                    {
 157                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 158                        cmd.ExecuteNonQuery();
 159                    }
 160
 161                    sqlQuery = string.Format("DELETE FROM {0}Rights WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 162                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 163                    {
 164                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 165                        cmd.ExecuteNonQuery();
 166                    }
 167
 168                    sqlQuery = string.Format("DELETE FROM {0}Users WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 169                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 170                    {
 171                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 172                        cmd.ExecuteNonQuery();
 173                    }
 174
 175                    sqlQuery = string.Format("DELETE FROM {0}Pages WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 176                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 177                    {
 178                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 179                        cmd.ExecuteNonQuery();
 180                    }
 181
 182                    sqlQuery = string.Format("DELETE FROM {0}StopWords WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 183                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 184                    {
 185                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 186                        cmd.ExecuteNonQuery();
 187                    }
 188
 189                    sqlQuery = string.Format("DELETE FROM {0}Settings WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 190                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 191                    {
 192                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 193                        cmd.ExecuteNonQuery();
 194                    }
 195
 196                    sqlQuery = string.Format("DELETE FROM {0}Referrers WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 197                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 198                    {
 199                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 200                        cmd.ExecuteNonQuery();
 201                    }
 202
 203                    sqlQuery = string.Format("DELETE FROM {0}PingService WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 204                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 205                    {
 206                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 207                        cmd.ExecuteNonQuery();
 208                    }
 209
 210                    sqlQuery = string.Format("DELETE FROM {0}DataStoreSettings WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 211                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 212                    {
 213                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 214                        cmd.ExecuteNonQuery();
 215                    }
 216
 217                    sqlQuery = string.Format("DELETE FROM {0}BlogRollItems WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 218                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 219                    {
 220                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 221                        cmd.ExecuteNonQuery();
 222                    }
 223
 224                    sqlQuery = string.Format("DELETE FROM {0}Categories WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 225                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 226                    {
 227                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 228                        cmd.ExecuteNonQuery();
 229                    }
 230                }
 231            }
 232
 233            return true;
 234        }
 235
 236        /// <summary>
 237        /// Deletes a Blog from the database
 238        /// </summary>
 239        /// <param name="blog">
 240        /// The blog.
 241        /// </param>
 242        public override void DeleteBlog(Blog blog)
 243        {
 244            // Only deleting data from be_Blogs.  Data from the other tables
 245            // will be deleted in DeleteBlogStorageContainer().
 246            using (TransactionScope ts = new TransactionScope())
 247            {
 248                using (var conn = this.CreateConnection())
 249                {
 250                    if (conn.HasConnection)
 251                    {
 252                        var sqlQuery = string.Format("DELETE FROM {0}Blogs WHERE BlogId = {1}BlogId", this.tablePrefix, this.parmPrefix);
 253                        using (var cmd = conn.CreateTextCommand(sqlQuery))
 254                        {
 255                            cmd.Parameters.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
 256                            cmd.ExecuteNonQuery();
 257                        }
 258
 259                    }
 260                }
 261                ts.Complete();
 262            }
 263        }
 264
 265        /// <summary>
 266        /// Deletes a category from the database
 267        /// </summary>
 268        /// <param name="category">
 269        /// category to be removed
 270        /// </param>
 271        public override void DeleteCategory(Category category)
 272        {
 273            var categories = Category.Categories;
 274            categories.Remove(category);
 275
 276            using (var conn = this.CreateConnection())
 277            {
 278                if (conn.HasConnection)
 279                {
 280                    var sqlQuery = string.Format("DELETE FROM {0}PostCategory WHERE BlogID = {1}blogid AND CategoryID = {1}catid", this.tablePrefix, this.parmPrefix);
 281
 282                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 283                    {
 284                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 285                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("catid"), category.Id.ToString()));
 286                        cmd.ExecuteNonQuery();
 287                    }
 288
 289                    sqlQuery = string.Format("DELETE FROM {0}Categories WHERE BlogID = {1}blogid AND CategoryID = {1}catid", this.tablePrefix, this.parmPrefix);
 290
 291                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 292                    {
 293                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 294                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("catid"), category.Id.ToString()));
 295                        cmd.ExecuteNonQuery();
 296                    }
 297                }
 298            }
 299        }
 300
 301        /// <summary>
 302        /// Deletes a page from the database
 303        /// </summary>
 304        /// <param name="page">
 305        /// page to be deleted
 306        /// </param>
 307        public override void DeletePage(Page page)
 308        {
 309            using (var conn = this.CreateConnection())
 310            {
 311                if (conn.HasConnection)
 312                {
 313                    using (var cmd = conn.CreateTextCommand(string.Format("DELETE FROM {0}Pages WHERE BlogID = {1}blogid AND PageID = {1}id", this.tablePrefix, this.parmPrefix)))
 314                    {
 315                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 316                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), page.Id.ToString()));
 317                        cmd.ExecuteNonQuery();
 318                    }
 319                }
 320            }
 321        }
 322
 323        /// <summary>
 324        /// Deletes a post in the database
 325        /// </summary>
 326        /// <param name="post">
 327        /// post to delete
 328        /// </param>
 329        public override void DeletePost(Post post)
 330        {
 331            using (var conn = this.CreateConnection())
 332            {
 333                if (conn.HasConnection)
 334                {
 335                    var sqlQuery = string.Format("DELETE FROM {0}PostTag WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
 336
 337                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 338                    {
 339                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 340                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
 341                        cmd.ExecuteNonQuery();
 342                    }
 343
 344                    sqlQuery = string.Format("DELETE FROM {0}PostCategory WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
 345
 346                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 347                    {
 348                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 349                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
 350                        cmd.ExecuteNonQuery();
 351                    }
 352
 353                    sqlQuery = string.Format("DELETE FROM {0}PostNotify WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
 354
 355                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 356                    {
 357                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 358                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
 359                        cmd.ExecuteNonQuery();
 360                    }
 361
 362                    sqlQuery = string.Format("DELETE FROM {0}PostComment WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
 363
 364                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 365                    {
 366                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 367                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
 368                        cmd.ExecuteNonQuery();
 369                    }
 370
 371                    sqlQuery = string.Format("DELETE FROM {0}Posts WHERE BlogID = @blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
 372
 373                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 374                    {
 375                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 376                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
 377                        cmd.ExecuteNonQuery();
 378                    }
 379
 380                }
 381            }
 382        }
 383
 384        /// <summary>
 385        /// Remove AuthorProfile from database
 386        /// </summary>
 387        /// <param name="profile">An AuthorProfile.</param>
 388        public override void DeleteProfile(AuthorProfile profile)
 389        {
 390            using (var conn = this.CreateConnection())
 391            {
 392                if (conn.HasConnection)
 393                {
 394                    using (var cmd = conn.CreateTextCommand(string.Format("DELETE FROM {0}Profiles WHERE BlogID = {1}blogid AND UserName = {1}name", this.tablePrefix, this.parmPrefix)))
 395                    {
 396                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 397                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("name"), profile.Id));
 398                        cmd.ExecuteNonQuery();
 399                    }
 400                }
 401            }
 402        }
 403
 404        /// <summary>
 405        /// Gets all BlogRolls in database
 406        /// </summary>
 407        /// <returns>
 408        /// List of BlogRolls
 409        /// </returns>
 410        public override List<BlogRollItem> FillBlogRoll()
 411        {
 412            var blogRoll = new List<BlogRollItem>();
 413
 414            using (var conn = this.CreateConnection())
 415            {
 416                if (conn.HasConnection)
 417                {
 418                    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)))
 419                    {
 420                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 421
 422                        using (var rdr = cmd.ExecuteReader())
 423                        {
 424                            while (rdr.Read())
 425                            {
 426                                var br = new BlogRollItem
 427                                    {
 428                                        Id = rdr.GetGuid(0),
 429                                        Title = rdr.GetString(1),
 430                                        Description = rdr.IsDBNull(2) ? string.Empty : rdr.GetString(2),
 431                                        BlogUrl = rdr.IsDBNull(3) ? null : new Uri(rdr.GetString(3)),
 432                                        FeedUrl = rdr.IsDBNull(4) ? null : new Uri(rdr.GetString(4)),
 433                                        Xfn = rdr.IsDBNull(5) ? string.Empty : rdr.GetString(5),
 434                                        SortIndex = rdr.GetInt32(6)
 435                                    };
 436
 437                                blogRoll.Add(br);
 438                                br.MarkOld();
 439                            }
 440                        }
 441                    }
 442                }
 443            }
 444
 445            return blogRoll;
 446        }
 447
 448        /// <summary>
 449        /// Gets all Blogs in database
 450        /// </summary>
 451        /// <returns>
 452        /// List of Blogs
 453        /// </returns>
 454        public override List<Blog> FillBlogs()
 455        {
 456            var blogs = new List<Blog>();
 457
 458            using (var conn = this.CreateConnection())
 459            {
 460                if (conn.HasConnection)
 461                {
 462                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT BlogId, BlogName, Hostname, IsAnyTextBeforeHostnameAccepted, StorageContainerName, VirtualPath, IsPrimary, IsActive FROM {0}Blogs ", this.tablePrefix)))
 463                    {
 464                        using (var rdr = cmd.ExecuteReader())
 465                        {
 466                            while (rdr.Read())
 467                            {
 468                                var b = new Blog
 469                                {
 470                                    Id = rdr.GetGuid(0),
 471                                    Name = rdr.GetString(1),
 472                                    Hostname = rdr.GetString(2),
 473                                    IsAnyTextBeforeHostnameAccepted = rdr.GetBoolean(3),
 474                                    StorageContainerName = rdr.GetString(4),
 475                                    VirtualPath = rdr.GetString(5),
 476                                    IsPrimary = rdr.GetBoolean(6),
 477                                    IsActive = rdr.GetBoolean(7)
 478                                };
 479
 480                                blogs.Add(b);
 481                                b.MarkOld();
 482                            }
 483                        }
 484                    }
 485                }
 486            }
 487
 488            return blogs;
 489        }
 490
 491        /// <summary>
 492        /// Gets all categories in database
 493        /// </summary>
 494        /// <returns>
 495        /// List of categories
 496        /// </returns>
 497        public override List<Category> FillCategories()
 498        {
 499            var categories = new List<Category>();
 500
 501            using (var conn = this.CreateConnection())
 502            {
 503                if (conn.HasConnection)
 504                {
 505                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT CategoryID, CategoryName, description, ParentID FROM {0}Categories WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix)))
 506                    {
 507                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 508
 509                        using (var rdr = cmd.ExecuteReader())
 510                        {
 511                            while (rdr.Read())
 512                            {
 513                                var cat = new Category
 514                                    {
 515                                        Title = rdr.GetString(1),
 516                                        Description = rdr.IsDBNull(2) ? string.Empty : rdr.GetString(2),
 517                                        Parent = rdr.IsDBNull(3) ? (Guid?)null : new Guid(rdr.GetGuid(3).ToString()),
 518                                        Id = new Guid(rdr.GetGuid(0).ToString())
 519                                    };
 520
 521                                categories.Add(cat);
 522                                cat.MarkOld();
 523                            }
 524                        }
 525                    }
 526                }
 527            }
 528
 529            return categories;
 530        }
 531
 532        /// <summary>
 533        /// Gets all pages in database
 534        /// </summary>
 535        /// <returns>
 536        /// List of pages
 537        /// </returns>
 538        public override List<Page> FillPages()
 539        {
 540            var pageIDs = new List<string>();
 541
 542            using (var conn = this.CreateConnection())
 543            {
 544                if (conn.HasConnection)
 545                {
 546                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT PageID FROM {0}Pages WHERE BlogID = {1}blogid ", this.tablePrefix, this.parmPrefix)))
 547                    {
 548                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 549
 550                        using (var rdr = cmd.ExecuteReader())
 551                        {
 552                            while (rdr.Read())
 553                            {
 554                                pageIDs.Add(rdr.GetGuid(0).ToString());
 555                            }
 556                        }
 557                    }
 558                }
 559            }
 560
 561            return pageIDs.Select(id => Page.Load(new Guid(id))).ToList();
 562        }
 563
 564        /// <summary>
 565        /// Gets all post from the database
 566        /// </summary>
 567        /// <returns>
 568        /// List of posts
 569        /// </returns>
 570        public override List<Post> FillPosts()
 571        {
 572            var postIDs = new List<string>();
 573
 574            using (var conn = this.CreateConnection())
 575            {
 576                if (conn.HasConnection)
 577                {
 578                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT PostID FROM {0}Posts WHERE BlogID = {1}blogid ", this.tablePrefix, this.parmPrefix)))
 579                    {
 580                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 581
 582                        using (var rdr = cmd.ExecuteReader())
 583                        {
 584                            while (rdr.Read())
 585                            {
 586                                postIDs.Add(rdr.GetGuid(0).ToString());
 587                            }
 588                        }
 589                    }
 590                }
 591            }
 592
 593            var posts = postIDs.Select(id => Post.Load(new Guid(id))).ToList();
 594
 595            posts.Sort();
 596            return posts;
 597        }
 598
 599        /// <summary>
 600        /// Return collection for AuthorProfiles from database
 601        /// </summary>
 602        /// <returns>
 603        /// List of AuthorProfile
 604        /// </returns>
 605        public override List<AuthorProfile> FillProfiles()
 606        {
 607            var profileNames = new List<string>();
 608
 609            using (var conn = this.CreateConnection())
 610            {
 611                if (conn.HasConnection)
 612                {
 613                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT UserName FROM {0}Profiles WHERE BlogID = {1}blogid GROUP BY UserName", this.tablePrefix, this.parmPrefix)))
 614                    {
 615                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 616
 617                        using (var rdr = cmd.ExecuteReader())
 618                        {
 619                            while (rdr.Read())
 620                            {
 621                                profileNames.Add(rdr.GetString(0));
 622                            }
 623                        }
 624                    }
 625                }
 626            }
 627
 628            return profileNames.Select(BusinessBase<AuthorProfile, string>.Load).ToList();
 629        }
 630
 631        /// <summary>
 632        /// Gets all Referrers from the database.
 633        /// </summary>
 634        /// <returns>
 635        /// List of Referrers.
 636        /// </returns>
 637        public override List<Referrer> FillReferrers()
 638        {
 639            this.DeleteOldReferrers();
 640
 641            var referrers = new List<Referrer>();
 642
 643            using (var conn = this.CreateConnection())
 644            {
 645                if (conn.HasConnection)
 646                {
 647                    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)))
 648                    {
 649                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 650
 651                        using (var rdr = cmd.ExecuteReader())
 652                        {
 653                            while (rdr.Read())
 654                            {
 655                                var refer = new Referrer
 656                                    {
 657                                        Id = rdr.GetGuid(0),
 658                                        Day = rdr.GetDateTime(1),
 659                                        ReferrerUrl = new Uri(rdr.GetString(2)),
 660                                        Count = rdr.GetInt32(3),
 661                                        Url = rdr.IsDBNull(4) ? null : new Uri(rdr.GetString(4)),
 662                                        PossibleSpam = rdr.IsDBNull(5) ? false : rdr.GetBoolean(5)
 663                                    };
 664
 665                                referrers.Add(refer);
 666                                refer.MarkOld();
 667                            }
 668                        }
 669                    }
 670                }
 671            }
 672
 673            return referrers;
 674        }
 675
 676        public override IDictionary<string, IEnumerable<string>> FillRights()
 677        {
 678            var rightsWithRoles = new Dictionary<string, IEnumerable<string>>();
 679
 680            using (var conn = this.CreateConnection())
 681            {
 682                if (conn.HasConnection)
 683                {
 684                    var sqlQuery = string.Format("SELECT RightName FROM {0}Rights WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix);
 685                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 686                    {
 687                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 688
 689                        using (var rdr = cmd.ExecuteReader())
 690                        {
 691                            while (rdr.Read())
 692                            {
 693                                rightsWithRoles.Add(rdr.GetString(0), new HashSet<string>(StringComparer.OrdinalIgnoreCase));
 694                            }
 695                        }
 696
 697                        // Get Right Roles.
 698                        cmd.CommandText = string.Format("SELECT RightName, Role FROM {0}RightRoles WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix);
 699                        // don't need to add "blogid" parameter again since the same cmd is being used.
 700
 701                        using (var rdr = cmd.ExecuteReader())
 702                        {
 703                            while (rdr.Read())
 704                            {
 705                                string rightName = rdr.GetString(0);
 706                                string roleName = rdr.GetString(1);
 707
 708                                if (rightsWithRoles.ContainsKey(rightName))
 709                                {
 710                                    var roles = (HashSet<string>)rightsWithRoles[rightName];
 711                                    if (!roles.Contains(roleName))
 712                                    {
 713                                        roles.Add(roleName);
 714                                    }
 715                                }
 716                            }
 717                        }
 718                    }
 719                }
 720            }
 721
 722            return rightsWithRoles;
 723        }
 724
 725        /// <summary>
 726        /// Initializes the provider
 727        /// </summary>
 728        /// <param name="name">
 729        /// Configuration name
 730        /// </param>
 731        /// <param name="config">
 732        /// Configuration settings
 733        /// </param>
 734        public override void Initialize(string name, NameValueCollection config)
 735        {
 736            if (config == null)
 737            {
 738                throw new ArgumentNullException("config");
 739            }
 740
 741            if (String.IsNullOrEmpty(name))
 742            {
 743                name = "DbBlogProvider";
 744            }
 745
 746            if (String.IsNullOrEmpty(config["description"]))
 747            {
 748                config.Remove("description");
 749                config.Add("description", "Generic Database Blog Provider");
 750            }
 751
 752            base.Initialize(name, config);
 753
 754            if (config["connectionStringName"] == null)
 755            {
 756                // default to BlogEngine
 757                config["connectionStringName"] = "BlogEngine";
 758            }
 759
 760            this.connStringName = config["connectionStringName"];
 761            config.Remove("connectionStringName");
 762
 763            if (config["tablePrefix"] == null)
 764            {
 765                // default
 766                config["tablePrefix"] = "be_";
 767            }
 768
 769            this.tablePrefix = config["tablePrefix"];
 770            config.Remove("tablePrefix");
 771
 772            if (config["parmPrefix"] == null)
 773            {
 774                // default
 775                config["parmPrefix"] = "@";
 776            }
 777
 778            this.parmPrefix = config["parmPrefix"];
 779            config.Remove("parmPrefix");
 780
 781            // Throw an exception if unrecognized attributes remain
 782            if (config.Count > 0)
 783            {
 784                var attr = config.GetKey(0);
 785                if (!String.IsNullOrEmpty(attr))
 786                {
 787                    throw new ProviderException(string.Format("Unrecognized attribute: {0}", attr));
 788                }
 789            }
 790        }
 791
 792        /// <summary>
 793        /// Adds a new BlogRoll to the database.
 794        /// </summary>
 795        /// <param name="blogRollItem">
 796        /// The blog Roll Item.
 797        /// </param>
 798        public override void InsertBlogRollItem(BlogRollItem blogRollItem)
 799        {
 800            var blogRolls = BlogRollItem.BlogRolls;
 801            blogRolls.Add(blogRollItem);
 802
 803            using (var conn = this.CreateConnection())
 804            {
 805                if (conn.HasConnection)
 806                {
 807
 808                    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);
 809
 810                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 811                    {
 812                        this.AddBlogRollParametersToCommand(blogRollItem, conn, cmd);
 813                        cmd.ExecuteNonQuery();
 814                    }
 815                }
 816            }
 817        }
 818
 819        /// <summary>
 820        /// Adds a new blog to the database.
 821        /// </summary>
 822        /// <param name="blog">
 823        /// The blog.
 824        /// </param>
 825        public override void InsertBlog(Blog blog)
 826        {
 827            using (var conn = this.CreateConnection())
 828            {
 829                if (conn.HasConnection)
 830                {
 831                    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);
 832
 833                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 834                    {
 835                        this.AddBlogParametersToCommand(blog, conn, cmd);
 836                        cmd.ExecuteNonQuery();
 837                    }
 838                }
 839            }
 840        }
 841
 842        /// <summary>
 843        /// Adds a new category to the database
 844        /// </summary>
 845        /// <param name="category">
 846        /// category to add
 847        /// </param>
 848        public override void InsertCategory(Category category)
 849        {
 850            var categories = Category.Categories;
 851            categories.Add(category);
 852            categories.Sort();
 853
 854            using (var conn = this.CreateConnection())
 855            {
 856                if (conn.HasConnection)
 857                {
 858                    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);
 859
 860                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 861                    {
 862                        var parms = cmd.Parameters;
 863                        parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 864                        parms.Add(conn.CreateParameter(FormatParamName("catid"), category.Id.ToString()));
 865                        parms.Add(conn.CreateParameter(FormatParamName("catname"), category.Title));
 866                        parms.Add(conn.CreateParameter(FormatParamName("description"), category.Description));
 867                        parms.Add(conn.CreateParameter(FormatParamName("parentid"), (category.Parent == null ? (object)DBNull.Value : category.Parent.ToString())));
 868
 869                        cmd.ExecuteNonQuery();
 870                    }
 871                }
 872            }
 873        }
 874
 875        /// <summary>
 876        /// Adds a page to the database
 877        /// </summary>
 878        /// <param name="page">
 879        /// page to be added
 880        /// </param>
 881        public override void InsertPage(Page page)
 882        {
 883            using (var conn = this.CreateConnection())
 884            {
 885                if (conn.HasConnection)
 886                {
 887                    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);
 888                   
 889                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 890                    {
 891
 892                        var parms = cmd.Parameters;
 893                        parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 894                        parms.Add(conn.CreateParameter(FormatParamName("id"), page.Id.ToString()));
 895                        parms.Add(conn.CreateParameter(FormatParamName("title"), page.Title));
 896                        parms.Add(conn.CreateParameter(FormatParamName("desc"), page.Description));
 897                        parms.Add(conn.CreateParameter(FormatParamName("content"), page.Content));
 898                        parms.Add(conn.CreateParameter(FormatParamName("created"), page.DateCreated.AddHours(-BlogSettings.Instance.Timezone)));
 899                        parms.Add(conn.CreateParameter(FormatParamName("modified"),
 900                                                                    (page.DateModified == new DateTime() ? DateTime.Now : page.DateModified.AddHours(-BlogSettings.Instance.Timezone))));
 901                        parms.Add(conn.CreateParameter(FormatParamName("keywords"), page.Keywords));
 902                        parms.Add(conn.CreateParameter(FormatParamName("ispublished"), page.IsPublished));
 903                        parms.Add(conn.CreateParameter(FormatParamName("isfrontpage"), page.IsFrontPage));
 904                        parms.Add(conn.CreateParameter(FormatParamName("parent"), page.Parent.ToString()));
 905                        parms.Add(conn.CreateParameter(FormatParamName("showinlist"), page.ShowInList));
 906                        parms.Add(conn.CreateParameter(FormatParamName("slug"), page.Slug));
 907                        parms.Add(conn.CreateParameter(FormatParamName("isdeleted"), page.IsDeleted));
 908
 909                        cmd.ExecuteNonQuery();
 910                    }
 911                }
 912            }
 913        }
 914
 915        /// <summary>
 916        /// Adds a new post to database
 917        /// </summary>
 918        /// <param name="post">
 919        /// The new post.
 920        /// </param>
 921        public override void InsertPost(Post post)
 922        {
 923            using (TransactionScope ts = new TransactionScope())
 924            {
 925                using (var conn = this.CreateConnection())
 926                {
 927                    if (conn.HasConnection)
 928                    {
 929                        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);
 930
 931                        using (var cmd = conn.CreateTextCommand(sqlQuery))
 932                        {
 933
 934                            var parms = cmd.Parameters;
 935                            parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
 936                            parms.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
 937                            parms.Add(conn.CreateParameter(FormatParamName("title"), post.Title));
 938                            parms.Add(conn.CreateParameter(FormatParamName("desc"), (post.Description ?? string.Empty)));
 939                            parms.Add(conn.CreateParameter(FormatParamName("content"), post.Content));
 940                            parms.Add(conn.CreateParameter(FormatParamName("created"), post.DateCreated.AddHours(-BlogSettings.Instance.Timezone)));
 941                            parms.Add(conn.CreateParameter(FormatParamName("modified"), (post.DateModified == new DateTime() ? DateTime.Now : post.DateModified.AddHours(-BlogSettings.Instance.Timezone))));
 942                            parms.Add(conn.CreateParameter(FormatParamName("author"), (post.Author ?? string.Empty)));
 943                            parms.Add(conn.CreateParameter(FormatParamName("published"), post.IsPublished));
 944                            parms.Add(conn.CreateParameter(FormatParamName("commentEnabled"), post.HasCommentsEnabled));
 945                            parms.Add(conn.CreateParameter(FormatParamName("raters"), post.Raters));
 946                            parms.Add(conn.CreateParameter(FormatParamName("rating"), post.Rating));
 947                            parms.Add(conn.CreateParameter(FormatParamName("slug"), (post.Slug ?? string.Empty)));
 948                            parms.Add(conn.CreateParameter(FormatParamName("isdeleted"), post.IsDeleted));
 949
 950                            cmd.ExecuteNonQuery();
 951                        }
 952
 953                        // Tags
 954                        this.UpdateTags(post, conn);
 955
 956                        // Categories
 957                        this.UpdateCategories(post, conn);
 958
 959                        // Comments
 960                        this.UpdateComments(post, conn);
 961
 962                        // Email Notification
 963                        this.UpdateNotify(post, conn);
 964                    }
 965                }
 966                ts.Complete();
 967            }
 968        }
 969
 970        /// <summary>
 971        /// Adds AuthorProfile to database
 972        /// </summary>
 973        /// <param name="profile">An AuthorProfile.</param>
 974        public override void InsertProfile(AuthorProfile profile)
 975        {
 976            this.UpdateProfile(profile);
 977        }
 978
 979        /// <summary>
 980        /// Adds a new Referrer to the database.
 981        /// </summary>
 982        /// <param name="referrer">
 983        /// Referrer to add.
 984        /// </param>
 985        public override void InsertReferrer(Referrer referrer)
 986        {
 987            var referrers = Referrer.Referrers;
 988            referrers.Add(referrer);
 989
 990            using (var conn = this.CreateConnection())
 991            {
 992                if (conn.HasConnection)
 993                {
 994                    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);
 995
 996                    using (var cmd = conn.CreateTextCommand(sqlQuery))
 997                    {
 998                        this.AddReferrersParametersToCommand(referrer, conn, cmd);
 999                        cmd.ExecuteNonQuery();
1000                    }
1001                }
1002            }
1003        }
1004
1005        /// <summary>
1006        /// Load user data from DataStore
1007        /// </summary>
1008        /// <param name="extensionType">
1009        /// type of info
1010        /// </param>
1011        /// <param name="extensionId">
1012        /// id of info
1013        /// </param>
1014        /// <returns>
1015        /// stream of detail data
1016        /// </returns>
1017        public override object LoadFromDataStore(ExtensionType extensionType, string extensionId)
1018        {
1019            // MemoryStream stream;
1020            object o = null;
1021
1022            using (var conn = this.CreateConnection())
1023            {
1024                if (conn.HasConnection)
1025                {
1026                    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);
1027                    using (var cmd = conn.CreateTextCommand(sqlQuery))
1028                    {
1029
1030                        var parms = cmd.Parameters;
1031                        parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
1032                        parms.Add(conn.CreateParameter(FormatParamName("etype"), extensionType.GetHashCode()));
1033                        parms.Add(conn.CreateParameter(FormatParamName("eid"), extensionId));
1034
1035                        o = cmd.ExecuteScalar();
1036                    }
1037                }
1038            }
1039
1040            return o;
1041        }
1042
1043        /// <summary>
1044        /// Gets the PingServices from the database
1045        /// </summary>
1046        /// <returns>
1047        /// collection of PingServices
1048        /// </returns>
1049        public override StringCollection LoadPingServices()
1050        {
1051            var col = new StringCollection();
1052
1053            using (var conn = this.CreateConnection())
1054            {
1055                if (conn.HasConnection)
1056                {
1057                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT Link FROM {0}PingService WHERE BlogID = {1}blogid ", this.tablePrefix, this.parmPrefix)))
1058                    {
1059                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
1060
1061                        using (var rdr = cmd.ExecuteReader())
1062                        {
1063                            while (rdr.Read())
1064                            {
1065                                if (!col.Contains(rdr.GetString(0)))
1066                                {
1067                                    col.Add(rdr.GetString(0));
1068                                }
1069                            }
1070                        }
1071                    }
1072                }
1073            }
1074
1075            return col;
1076        }
1077
1078        /// <summary>
1079        /// Gets the settings from the database
1080        /// </summary>
1081        /// <returns>
1082        /// dictionary of settings
1083        /// </returns>
1084        public override StringDictionary LoadSettings()
1085        {
1086            var dic = new StringDictionary();
1087
1088            using (var conn = this.CreateConnection())
1089            {
1090                if (conn.HasConnection)
1091                {
1092                    using (var cmd = conn.CreateTextCommand(string.Format("SELECT SettingName, SettingValue FROM {0}Settings WHERE BlogId = {1}blogid ", this.tablePrefix, this.parmPrefix)))
1093                    {
1094                        cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
1095
1096                        using (var rdr = cmd.ExecuteReader())
1097                        {
1098                            while (rdr.Read())
1099                            {
1100                                var name = rdr.GetString(0);
1101                                var value = rdr.GetString(1);
1102
1103                                dic.Add(name, value);
1104                            }
1105                        }
1106                    }
1107                }
1108            }
1109
1110            return dic;
1111        }
1112
1113        /// <summary>
1114        /// Get stopwords from the database
1115        /// </summary>
1116        /// <returns>
1117        

Large files files are truncated, but you can click here to view the full file