A few days ago I realized that my blog wasn't working properly. This came with more than a bit of frustration because I had upgraded ito to DasBlog on May 25th (almost 1 month ago exactly!). What clued me off was the fact that I wasn't getting any comments to prior posts. While the vast majority of comments came from my download control and my rating control (neither of which has yet been ported over), I would get the occasional comment through the blog directly. But since the upgrade nothing. Well, that's not true, I got two comments on my transition post but I didn't get either one because I didn't set up my mail setting correctly. That has since been fixed.
As it turns out, the code that I originally used to port the blog site over was flawed, but I didn't realize it until it was too late. Pretty much all of my google links where my blog was the top page or in the top few have all but disappeared :(. I have, however, updated the code (which I present below) in the event there is any other poor soul out there needing to migrate from .Text to DasBlog.
Comments should now work on the blog.
I hope this works better for everyone moving forward:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using newtelligence.DasBlog.Runtime;
namespace ConvDotTextToDasBlog {
internal class Program {
private class EntryData {
public EntryData(string id, string title) {
Id = id;
Title = title;
}
public readonly string Id, Title;
}
private static readonly Dictionary<int, EntryData> _postDict = new Dictionary<int, EntryData>();
private static void Main() {
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "output");
Directory.CreateDirectory(path);
IBlogDataService dataService = BlogDataServiceFactory.GetService(path, null);
string connStr = @"Initial Catalog=Blog; Data Source=(local)\SQLEXPRESS; Integrated Security=True";
using ( SqlConnection conn = new SqlConnection(connStr) ) {
conn.Open();
using ( SqlCommand cmPosts = new SqlCommand("SELECT COUNT(ID) FROM blog_Content WHERE PostType=1; SELECT * FROM blog_Content WHERE PostType=1", conn) )
using ( SqlDataReader drPosts = cmPosts.ExecuteReader() ) {
drPosts.Read();
int totalCount = drPosts.GetInt32(0);
drPosts.NextResult();
int currIndex = 0;
while ( drPosts.Read() ) {
int postId = drPosts.GetInt32(0);
string postTitle = drPosts.IsDBNull(1) ? string.Empty : drPosts.GetString(1);
DateTime dtCreated = drPosts.GetDateTime(2);
DateTime dtModified = drPosts.GetDateTime(10);
string postText = drPosts.GetString(12);
string postAuthor = drPosts.GetString(5);
// catalog the id, assigning it a guid
string newPostId = Guid.NewGuid().ToString().ToLowerInvariant();
string newPostTitle = ( postTitle.Length > 0 ? postTitle : postText.Substring(0, Math.Min(20, postText.Length)) );
_postDict.Add(postId, new EntryData(newPostId, newPostTitle));
Console.WriteLine("Processing Post #{0} ({1} of {2})", postId, ++currIndex, totalCount);
Entry entry = new Entry();
entry.CreatedUtc = dtCreated;
entry.ModifiedUtc = dtModified;
entry.Title = newPostTitle;
entry.Content = postText;
entry.EntryId = newPostId;
entry.Categories = getPostCategories(postId, connStr);
entry.Author = postAuthor;
dataService.SaveEntry(entry);
}
}
using ( SqlCommand cmComments = new SqlCommand("SELECT COUNT(ID) FROM blog_Content WHERE PostType=3; SELECT * FROM blog_Content WHERE PostType=3", conn) )
using ( SqlDataReader drComments = cmComments.ExecuteReader() ) {
drComments.Read();
int totalCount = drComments.GetInt32(0);
drComments.NextResult();
int currIndex = 0;
while ( drComments.Read() ) {
int commentId = drComments.GetInt32(0);
int refPostId = drComments.GetInt32(13);
DateTime dtCreated = drComments.GetDateTime(2);
string commentAuthorName = drComments.IsDBNull(5) ? string.Empty : drComments.GetString(5);
string commentAuthorIp = drComments.IsDBNull(7) ? string.Empty : drComments.GetString(7);
string commentAuthorUrl = drComments.IsDBNull(11) ? string.Empty : drComments.GetString(11);
string commentText = drComments.GetString(12);
EntryData refEntry;
if ( !_postDict.TryGetValue(refPostId, out refEntry) )
Console.WriteLine("Error processing comment #{0} ({1} of {2}); post {3} was not resolved.", commentId, ++currIndex, totalCount, refPostId);
else {
Console.WriteLine("Processing Comment #{0} ({1} of {2})", commentId, ++currIndex, totalCount);
Comment comment = new Comment();
comment.EntryId = Guid.NewGuid().ToString().ToLowerInvariant();
comment.CreatedUtc = dtCreated;
comment.ModifiedUtc = dtCreated;
comment.TargetEntryId = refEntry.Id;
comment.TargetTitle = refEntry.Title;
comment.Author = commentAuthorName;
comment.AuthorHomepage = commentAuthorUrl;
comment.AuthorIPAddress = commentAuthorIp;
comment.Content = commentText;
dataService.AddComment(comment);
}
}
}
}
}
private static string getPostCategories(int postId, string connStr) {
const string sql = "SELECT cat.Title FROM blog_Links AS links INNER JOIN blog_LinkCategories AS cat ON links.CategoryID = cat.CategoryID WHERE links.PostID = @PostID";
List<string> categories = new List<string>();
using ( SqlConnection cn = new SqlConnection(connStr) )
using ( SqlCommand cm = new SqlCommand(sql, cn) ) {
cn.Open();
cm.Parameters.Add("@PostID", SqlDbType.Int).Value = postId;
using ( SqlDataReader dr = cm.ExecuteReader(CommandBehavior.CloseConnection) ) {
while ( dr.Read() ) {
string category = dr.GetString(0);
categories.Add(category);
}
}
}
return string.Join(";", categories.ToArray());
}
}
}