PageRenderTime 49ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 0ms

/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

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

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