| public SelectPost ( System.Guid id ) : Post | ||
| id | System.Guid | /// The Post ID. /// |
| return | Post | |
public override Post SelectPost(Guid id)
{
var post = new Post();
using (var conn = this.CreateConnection())
{
if (conn.HasConnection)
{
var sqlQuery = string.Format("SELECT PostID, Title, Description, PostContent, DateCreated, DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug, IsDeleted FROM {0}Posts WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
using (var cmd = conn.CreateTextCommand(sqlQuery))
{
cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), id.ToString()));
using (var rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
post.Id = rdr.GetGuid(0);
post.Title = rdr.GetString(1);
post.Content = rdr.GetString(3);
post.Description = rdr.IsDBNull(2) ? String.Empty : rdr.GetString(2);
if (!rdr.IsDBNull(4))
{
post.DateCreated = rdr.GetDateTime(4);
}
if (!rdr.IsDBNull(5))
{
post.DateModified = rdr.GetDateTime(5);
}
if (!rdr.IsDBNull(6))
{
post.Author = rdr.GetString(6);
}
if (!rdr.IsDBNull(7))
{
post.IsPublished = rdr.GetBoolean(7);
}
if (!rdr.IsDBNull(8))
{
post.HasCommentsEnabled = rdr.GetBoolean(8);
}
if (!rdr.IsDBNull(9))
{
post.Raters = rdr.GetInt32(9);
}
if (!rdr.IsDBNull(10))
{
post.Rating = rdr.GetFloat(10);
}
post.Slug = !rdr.IsDBNull(11) ? rdr.GetString(11) : string.Empty;
if (!rdr.IsDBNull(12))
{
post.IsDeleted = rdr.GetBoolean(12);
}
}
}
// Tags
cmd.CommandText = string.Format("SELECT Tag FROM {0}PostTag WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
post.Tags.Add(rdr.GetString(0));
}
}
}
post.Tags.MarkOld();
// Categories
cmd.CommandText = string.Format("SELECT CategoryID FROM {0}PostCategory WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var key = rdr.GetGuid(0);
if (Category.GetCategory(key) != null)
{
post.Categories.Add(Category.GetCategory(key));
}
}
}
// Comments
cmd.CommandText = string.Format("SELECT PostCommentID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved, ParentCommentID, ModeratedBy, Avatar, IsSpam, IsDeleted FROM {0}PostComment WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var comment = new Comment
{
Id = rdr.GetGuid(0),
IsApproved = true,
Author = rdr.GetString(2)
};
if (!rdr.IsDBNull(4))
{
Uri website;
if (Uri.TryCreate(rdr.GetString(4), UriKind.Absolute, out website))
{
comment.Website = website;
}
}
comment.Email = rdr.GetString(3);
comment.Content = rdr.GetString(5);
comment.DateCreated = rdr.GetDateTime(1);
comment.Parent = post;
if (!rdr.IsDBNull(6))
{
comment.Country = rdr.GetString(6);
}
if (!rdr.IsDBNull(7))
{
comment.IP = rdr.GetString(7);
}
comment.IsApproved = rdr.IsDBNull(8) || rdr.GetBoolean(8);
comment.ParentId = rdr.GetGuid(9);
if (!rdr.IsDBNull(10))
{
comment.ModeratedBy = rdr.GetString(10);
}
if (!rdr.IsDBNull(11))
{
comment.Avatar = rdr.GetString(11);
}
if (!rdr.IsDBNull(12))
{
comment.IsSpam = rdr.GetBoolean(12);
}
if (!rdr.IsDBNull(13))
{
comment.IsDeleted = rdr.GetBoolean(13);
}
post.AllComments.Add(comment);
}
}
post.AllComments.Sort();
// Email Notification
cmd.CommandText = string.Format("SELECT NotifyAddress FROM {0}PostNotify WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
var email = rdr.GetString(0);
if (post.NotificationEmails.Contains(email))
{
post.NotificationEmails.Add(email);
}
}
}
}
}
}
}
return post;
}
