/// <summary>
/// GetPostByID returns a post with the approved comment count.
/// </summary>
public Post GetPostById(int postId)
{
Collection <Post> posts = new Collection <Post>();
using (SqlConnection connection = this.GetConnection())
{
connection.Open();
SqlCommand sc = new SqlCommand();
sc.CommandText = "GetPostById";
sc.Parameters.AddWithValue("PostId", postId);
sc.CommandType = CommandType.StoredProcedure;
sc.Connection = connection;
using (SqlDataReader reader = sc.ExecuteReader())
{
posts = SqlDataMap.CreatePostsFromReader(reader);
}
}
if (posts.Count > 0)
{
return(posts[0]);
}
else
{
return(null);
}
}
public Category GetCategoryById(int categoryId)
{
Collection <Category> cats;
using (SqlConnection connection = this.GetConnection())
{
connection.Open();
SqlCommand sc = new SqlCommand("GetCategoryById", connection);
sc.CommandType = CommandType.StoredProcedure;
sc.Parameters.AddWithValue("CategoryId", categoryId);
using (SqlDataReader reader = sc.ExecuteReader())
{
cats = SqlDataMap.CreateCategoriesFromReader(reader);
}
}
if (cats.Count > 0)
{
return(cats[0]);
}
else
{
return(null);
}
}
public Collection <Comment> GetAllComments(CommentType ctype)
{
using (SqlConnection connection = this.GetConnection())
{
connection.Open();
SqlCommand sc = new SqlCommand("GetAllComments", connection);
sc.CommandType = CommandType.StoredProcedure;
Collection <Comment> clist = SqlDataMap.CreateCommentsFromReader(sc.ExecuteReader());
return(clist);
}
}
public Collection <Category> GetAllCategories()
{
Collection <Category> cats;
using (SqlConnection connection = this.GetConnection())
{
connection.Open();
SqlCommand sc = new SqlCommand("GetAllCategories", connection);
sc.CommandType = CommandType.StoredProcedure;
using (SqlDataReader reader = sc.ExecuteReader())
{
cats = SqlDataMap.CreateCategoriesFromReader(reader);
}
}
return(cats);
}
public Collection <Post> GetAllPosts(int categoryId, DateTime startDate, DateTime endDate, CommentType ctype, int numberOfPosts)
{
//// int numberOfPosts = 10;
Collection <Post> posts = new Collection <Post>();
using (SqlConnection connection = this.GetConnection())
{
connection.Open();
SqlCommand sc = new SqlCommand();
sc.CommandText = "GetAllPosts";
sc.Parameters.AddWithValue("NumberOfPosts", numberOfPosts);
sc.Parameters.AddWithValue("CategoryId", categoryId);
sc.Parameters.AddWithValue("DateFrom", startDate);
sc.Parameters.AddWithValue("DateTo", endDate);
if (ctype != CommentType.All)
{
sc.Parameters.AddWithValue("@ApprovedCommentsOnly", ctype == CommentType.Approved ? 1 : 0);
}
else
{
sc.Parameters.AddWithValue("@ApprovedCommentsOnly", DBNull.Value);
}
sc.CommandType = CommandType.StoredProcedure;
sc.Connection = connection;
using (SqlDataReader reader = sc.ExecuteReader())
{
posts = SqlDataMap.CreatePostsFromReader(reader);
}
}
return(posts);
}
public Collection <Comment> GetCommentsForPost(int postId, CommentType ctype)
{
using (SqlConnection connection = this.GetConnection())
{
connection.Open();
SqlCommand sc = new SqlCommand("GetCommentsByPost", connection);
sc.CommandType = CommandType.StoredProcedure;
sc.Parameters.AddWithValue("PostId", postId);
if (ctype == CommentType.All)
{
sc.Parameters.AddWithValue("Approved", DBNull.Value);
}
else
{
int bitValue = 0;
if (ctype == CommentType.Approved)
{
bitValue = 1;
}
else if (ctype == CommentType.UnApproved)
{
bitValue = 0;
}
sc.Parameters.AddWithValue("Approved", bitValue);
}
Collection <Comment> clist = SqlDataMap.CreateCommentsFromReader(sc.ExecuteReader());
return(clist);
}
}