public static IEnumerable<Post> GetAllPosts()
{
List<Post> posts = new List<Post>();
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["PostgresDB"].ConnectionString;
var conn = new NpgsqlConnection(connstring);
string sqlquery = "Select * from Posts where postedto=0 order by timestamp desc";
var cmd = new NpgsqlCommand(sqlquery, conn);
conn.Open();
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Post post = new Post();
post.id = Convert.ToInt32(reader["postid"]);
post.posterName = reader["postername"].ToString();
post.postContent = reader["postcontent"].ToString();
post.Timestamp = Convert.ToDateTime(reader["timestamp"]);
posts.Add(post);
}
}
return posts;
}
public static List<Post> allUserPosts(string userName, int postedTo)
{
List<Post> posts = new List<Post>();
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["PostgresDB"].ConnectionString;
var conn = new NpgsqlConnection(connstring);
string sqlquery = "select * from posts where postername='" + userName + "' or postedto="+postedTo+" order by timestamp desc";
var cmd = new NpgsqlCommand(sqlquery, conn);
conn.Open();
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Post post = new Post();
post.id = Convert.ToInt32(reader["postid"]);
post.posterName = reader["postername"].ToString();
post.postContent = reader["postcontent"].ToString();
post.Timestamp = Convert.ToDateTime(reader["timestamp"]);
post.posterEmail = reader["posteremail"].ToString();
post.postedTo = Convert.ToInt32(reader["postedto"]);
posts.Add(post);
}
}
return posts;
}
public static void CreateInitialPosts()
{
Post firstPost = new Post() { posterName = "Alice", postContent = "I am happy", Timestamp = DateTime.Now };
Post secondPost = new Post() { posterName = "Bob", postContent = "I love burgers", Timestamp = DateTime.Now };
Post thirdPost = new Post() { posterName = "Susan", postContent = "I love shopping", Timestamp = DateTime.Now };
Post fourthPost = new Post() { posterName = "James", postContent = "I like videogames", Timestamp = DateTime.Now };
listOfPosts.Add(firstPost);
listOfPosts.Add(secondPost);
listOfPosts.Add(thirdPost);
listOfPosts.Add(fourthPost);
}
public static int CreatePost(Post newPost)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["PostgresDB"].ConnectionString;
var conn = new NpgsqlConnection(connstring);
conn.Open();
string sqlquery = "insert into posts (posterName,postContent,timestamp, posterEmail,postedto) values ('" + newPost.posterName + "','" + newPost.postContent + "','" + newPost.Timestamp + "','" + newPost.posterEmail + "','"+newPost.postedTo+"')";
var cmd = new NpgsqlCommand(sqlquery, conn);
int rowsaffected = cmd.ExecuteNonQuery();
conn.Close();
return rowsaffected;
}
public ActionResult Create()
{
Post newPost = new Post();
ViewBag.UserName = Session["UserName"];
return View(newPost);
}
public static void AddToList(Post newPost)
{
listOfPosts.Add(newPost);
}