public Comment GetCommentById(int id)
{
var comment = new Comment();
using (var conn = ADODataProvider.GetSqlConnection())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = Scripts.GetCommentByIdStoredProc;
cmd.CommandType = CommandType.StoredProcedure;
var p1 = new SqlParameter("CommentId", SqlDbType.Int);
p1.Value = id;
cmd.Parameters.Add(p1);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
comment = LoadComment(reader);
}
}
}
return(comment);
}
public List <Comment> GetCommentsByPost(Post post)
{
var comments = new List <Comment>();
using (var conn = ADODataProvider.GetSqlConnection())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = Scripts.GetCommentsByPostStoredProc;
cmd.CommandType = CommandType.StoredProcedure;
var p1 = new SqlParameter("PostId", SqlDbType.Int);
p1.Value = post.Id;
cmd.Parameters.Add(p1);
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
comments.Add(LoadComment(reader));
}
}
}
}
return(comments);
}
public bool InsertPost(Post post)
{
int result;
using (var conn = ADODataProvider.GetSqlConnection())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = Scripts.InsertPostStoredProc;
cmd.CommandType = CommandType.StoredProcedure;
var p1 = new SqlParameter("AuthorId", SqlDbType.Int);
p1.Value = post.AuthorId;
cmd.Parameters.Add(p1);
var p2 = new SqlParameter("Category", SqlDbType.NVarChar);
p2.Value = post.Category;
cmd.Parameters.Add(p2);
var p3 = new SqlParameter("Text", SqlDbType.NVarChar);
p3.Value = post.Text;
cmd.Parameters.Add(p3);
result = cmd.ExecuteNonQuery();
}
}
return(result != 0);
}
public List <Post> GetPostsByAuthorAndCategory(Author author, string category)
{
var posts = new List <Post>();
using (var conn = ADODataProvider.GetSqlConnection())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = Scripts.GetPostByAuthorStoredProc;
cmd.CommandType = CommandType.StoredProcedure;
var p1 = new SqlParameter("AuthorId", SqlDbType.Int);
p1.Value = author.Id;
cmd.Parameters.Add(p1);
var p2 = new SqlParameter("CategoryName", SqlDbType.NVarChar);
p2.Value = category;
cmd.Parameters.Add(p2);
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
posts.Add(LoadPostEager(reader));
}
}
}
}
return(posts);
}
public List <Post> GetAllPosts()
{
var posts = new List <Post>();
using (var conn = ADODataProvider.GetSqlConnection())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = Scripts.GetAllPosts;
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
posts.Add(LoadPostEager(reader));
}
}
}
}
return(posts);
}
