public List<Post> GetAllPosts()
{
var tagRepo = new TagRepo();
var catRepo = new CategoryRepo();
List<Post> posts = new List<Post>();
using (SqlConnection cn = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"SELECT * From Post p INNER JOIN Categories c ON p.CategoryID = c.CategoryID";
cmd.Connection = cn;
cn.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
posts.Add(PopulateFromDataReader(dr));
}
// Add objects to each post
foreach (var post in posts)
{
post.PostTags = tagRepo.GetTagsByPostId(post.PostID);
post.placeholderText = string.Join(",", post.PostTags);
post.PostCategory = catRepo.GetCategoryByID(post.PostCategory.CategoryID);
}
}
return posts;
}
public Post GetPostById(int id)
{
var tagRepo = new TagRepo();
var catRepo = new CategoryRepo();
var post = new Post();
using (SqlConnection cn = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"SELECT * From Post p INNER JOIN Categories c ON p.CategoryID = c.CategoryID WHERE p.PostID ="+id;
cmd.Connection = cn;
cn.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
post = (PopulateFromDataReader(dr));
}
}
// Get Tags for post and populate string placeholder
post.PostTags = tagRepo.GetTagsByPostId(post.PostID);
string tags = "";
foreach (var tag in post.PostTags)
{
tags += tag.TagName + ",";
}
if (tags.Length > 0)
{
post.placeholderText = tags.Substring(0, tags.Length - 1);
}
post.PostCategory = catRepo.GetCategoryByID(post.PostCategory.CategoryID);
return post;
}