public CommentModel getRecentComments()
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(String.Format("SELECT TOP 3 Post.Title, Comment.PostID, UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Comment.CommentDate, Comment.CommentText FROM Comment INNER JOIN Post ON Post.PostID = Comment.PostID INNER JOIN UserInfo ON UserInfo.UserID = Comment.UserID ORDER BY Comment.CommentDate DESC")))
{
command.Connection = connection;
using (var reader = command.ExecuteReader())
{
CommentModel commentModel = new CommentModel();
CommentItemModel commentItem = null;
UserModel user = null;
while (reader.Read())
{
user = new UserModel(Convert.ToInt32(reader["UserID"]), reader["UserName"].ToString(), reader["Email"].ToString(), Convert.ToInt32(reader["Age"]));
commentItem = new CommentItemModel(DateTime.Parse(reader["CommentDate"].ToString()), reader["CommentText"].ToString(), Convert.ToInt32(reader["PostID"]), user, reader["Title"].ToString());
commentModel.Comments.Add(commentItem);
}
return(commentModel);
}
}
}
}
public ArticleItemModel GetLastPost()
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(String.Format("SELECT TOP 1 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 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 Post.PostID, UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Comment.CommentDate, Comment.CommentText, Comment.CommentID 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"]);
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);
commentItem.CommentID = Convert.ToInt32(reader2["CommentID"]);
commentModel.Comments.Add(commentItem);
}
model.Comments = commentModel;
model.Categories = new string[2];
model.Categories[0] = "Category2352";
model.Categories[1] = "Category31231";
return(model);
}
}
}
}
}
}
}
public void addComment(CommentItemModel comment)
{
int UserID = addUser(comment.User);
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(String.Format("INSERT INTO Comment(UserID, PostID, CommentText, CommentDate) VALUES(@UserID, @PostID, @CommentText, @CommentDate)")))
{
command.Connection = connection;
command.Parameters.Add(new SqlParameter("UserID", UserID));
command.Parameters.Add(new SqlParameter("PostID", comment.PostID));
command.Parameters.Add(new SqlParameter("CommentText", comment.Text));
command.Parameters.Add(new SqlParameter("CommentDate", comment.Date));
command.ExecuteNonQuery();
}
}
}
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);
}
}
}
}
}
}