public ArticleItemModel GetPostById(int ID)
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(String.Format("SELECT Post.PostID, UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Post.Date, Post.Text, Post.Title FROM Post INNER JOIN UserInfo ON UserInfo.UserID = Post.UserID WHERE @ID = Post.PostID")))
{
command.Connection = connection;
command.Parameters.Add(new SqlParameter("ID", ID));
using (var reader = command.ExecuteReader())
{
using (var connection2 = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString))
{
connection2.Open();
using (var command2 = new SqlCommand(String.Format("SELECT Post.PostID, UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Comment.CommentDate, Comment.CommentText FROM Post INNER JOIN Comment ON Post.PostID = Comment.PostID INNER JOIN UserInfo ON UserInfo.UserID = Comment.UserID WHERE Post.PostID = @id ORDER BY Comment.CommentDate DESC")))
{
command2.Connection = connection2;
UserModel user = null;
ArticleItemModel model = new ArticleItemModel();
if (reader.Read())
{
int id = Convert.ToInt32(reader["PostID"]);
if (id == 0)
{
return(null);
}
model.PostID = id;
command2.Parameters.Add(new SqlParameter("id", id));
user = new UserModel(Convert.ToInt32(reader["UserID"]), reader["UserName"].ToString(), reader["Email"].ToString(), Convert.ToInt32(reader["Age"]));
model.User = user;
model.Date = DateTime.Parse(reader["Date"].ToString());
model.Text = reader["Text"].ToString();
model.Title = reader["Title"].ToString();
model.NewComment = new CommentItemModel();
model.NewComment.PostID = id;
}
using (var reader2 = command2.ExecuteReader())
{
CommentModel commentModel = new CommentModel();
CommentItemModel commentItem = null;
while (reader2.Read())
{
user = new UserModel(Convert.ToInt32(reader["UserID"]), reader2["UserName"].ToString(), reader2["Email"].ToString(), Convert.ToInt32(reader2["Age"]));
commentItem = new CommentItemModel(DateTime.Parse(reader2["CommentDate"].ToString()), reader2["CommentText"].ToString(), Convert.ToInt32(reader2["PostID"]), user);
commentModel.Comments.Add(commentItem);
}
model.Comments = commentModel;
model.Categories = new string[2];
model.Categories[0] = "Category2352";
model.Categories[1] = "Category31231";
return(model);
}
}
}
}
}
}
}
public ArticleItemModel(int PostID, UserModel User, String Text, String Title, CommentModel Comments, String[] Categories)
{
this.PostID = PostID;
Date = DateTime.Now;
this.User = User;
this.Text = Text;
this.Title = Title;
this.Comments = Comments;
this.Categories = Categories;
this.NewComment = new CommentItemModel();
this.NewComment.PostID = PostID;
}
public ArticleModel GetPosts()
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString))
{
connection.Open();
ArticleModel articleModel = new ArticleModel();
using (var command = new SqlCommand(String.Format("SELECT UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Post.PostID, Post.Date, Post.Text, Post.Title FROM Post INNER JOIN UserInfo ON UserInfo.UserID = Post.UserID ORDER BY Post.Date DESC")))
{
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
using (var connection2 = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString))
{
connection2.Open();
using (var command2 = new SqlCommand(String.Format("SELECT Comment.PostID, UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Comment.CommentDate, Comment.CommentText FROM Post INNER JOIN Comment ON Post.PostID = Comment.PostID INNER JOIN UserInfo ON UserInfo.UserID = Comment.UserID WHERE @PostID = Comment.PostID ORDER BY Comment.CommentDate")))
{
command2.Connection = connection2;
while (reader.Read())
{
UserModel user = null;
ArticleItemModel model = new ArticleItemModel();
user = new UserModel(Convert.ToInt32(reader["UserID"]), reader["UserName"].ToString(), reader["Email"].ToString(), Convert.ToInt32(reader["Age"]));
model.Date = DateTime.Parse(reader["Date"].ToString());
model.Text = reader["Text"].ToString();
model.Title = reader["Title"].ToString();
model.User = user;
int postID = Convert.ToInt32(reader["PostID"]);
if (command2.Parameters.Contains("PostID"))
{
command2.Parameters.RemoveAt("PostID");
}
command2.Parameters.Add(new SqlParameter("postID", postID));
CommentModel commentModel = new CommentModel();
using (var reader2 = command2.ExecuteReader())
{
CommentItemModel commentItem = null;
while (reader2.Read())
{
int commentPostID = Convert.ToInt32(reader2["PostID"]);
if (postID == commentPostID)
{
user = new UserModel(Convert.ToInt32(reader2["UserID"]), reader2["UserName"].ToString(), reader2["Email"].ToString(), Convert.ToInt32(reader2["Age"]));
commentItem = new CommentItemModel(DateTime.Parse(reader2["CommentDate"].ToString()), reader2["CommentText"].ToString(), postID, user);
commentModel.Comments.Add(commentItem);
}
}
model.Categories = new string[2];
model.Categories[0] = "Category2352";
model.Categories[1] = "Category31231";
model.Comments = commentModel;
}
articleModel.Articles.Add(model);
}
return(articleModel);
}
}
}
}
}
}