public Models.WhistleObjects[] FlaggedPosts()
{//LOGIC: get all account requests and return them!
Models.WhistleObjects PostInfo = new Models.WhistleObjects();
DataTable sqlDt = new DataTable();
string sqlConnectString = getConString();
//requests just have active set to 0
string sqlSelect = "SELECT Text, Category, Flag, Votes, PostID FROM Posts WHERE Flag=1";
MySqlConnection sqlConnection = new MySqlConnection(sqlConnectString);
MySqlCommand sqlCommand = new MySqlCommand(sqlSelect, sqlConnection);
MySqlDataAdapter sqlDa = new MySqlDataAdapter(sqlCommand);
sqlDa.Fill(sqlDt);
List <Models.WhistleObjects> listOfPosts = new List <Models.WhistleObjects>();
for (int i = 0; i < sqlDt.Rows.Count; i++)
{
listOfPosts.Add(new Models.WhistleObjects
{
Category = sqlDt.Rows[i]["Category"].ToString(),
PostText = sqlDt.Rows[i]["Text"].ToString(),
Flag = Int16.Parse(sqlDt.Rows[i]["Flag"].ToString()),
PostVotes = Int32.Parse(sqlDt.Rows[i]["Votes"].ToString()),
PostID = Int32.Parse(sqlDt.Rows[i]["PostID"].ToString())
});
}
//convert the list of accounts to an array and return!
return(listOfPosts.ToArray());
}
public string AddPost(string text, string category) //fixed, no errors when connecting to DB
{
Models.WhistleObjects PostInfo = new Models.WhistleObjects();
PostInfo.Category = category;
PostInfo.PostText = text;
//flag as an array
string sqlConnectString = getConString();
string sqlStatement = "INSERT INTO Posts (Category, Text) VALUES(@category, @text)";
//set up our connection object to be ready to use our connection string
MySqlConnection sqlConnection = new MySqlConnection(sqlConnectString);
//set up our command object to use our connection, and our query
MySqlCommand sqlCommand = new MySqlCommand(sqlStatement, sqlConnection);
sqlCommand.Parameters.AddWithValue("@category", HttpUtility.UrlDecode(category));
sqlCommand.Parameters.AddWithValue("@text", HttpUtility.UrlDecode(text));
MySqlDataAdapter sqlDa = new MySqlDataAdapter(sqlCommand);
DataTable sqlDt = new DataTable();
//here we go filling it!
sqlDa.Fill(sqlDt);
return("Post added");
}
public string DeletePost(int id)
{//LOGIC: get postid and upvote it
Models.WhistleObjects PostInfo = new Models.WhistleObjects();
PostInfo.PostID = id;
DataTable sqlDt = new DataTable();
string sqlConnectString = getConString();
//requests just have active set to 0
string sqlUpdate = "Delete From Posts WHERE PostID=@id";
MySqlConnection sqlConnection = new MySqlConnection(sqlConnectString);
MySqlCommand sqlCommand = new MySqlCommand(sqlUpdate, sqlConnection);
sqlCommand.Parameters.AddWithValue("@id", HttpUtility.UrlDecode(id.ToString()));
MySqlDataAdapter sqlDa = new MySqlDataAdapter(sqlCommand);
sqlDa.Fill(sqlDt);
return("Post Deleted");
}
public Models.WhistleObjects[] GetPostsbyText(string postText)
{//LOGIC: get all account requests and return them!
Models.WhistleObjects PostInfo = new Models.WhistleObjects();
PostInfo.PostText = postText;
DataTable sqlDt = new DataTable();
string sqlConnectString = getConString();
//requests just have active set to 0
string sqlSelect = "SELECT Text, Category, Flag, Votes, PostID FROM Posts WHERE Text LIKE '%" + @postText + "%' ORDER BY Votes DESC";
MySqlConnection sqlConnection = new MySqlConnection(sqlConnectString);
MySqlCommand sqlCommand = new MySqlCommand(sqlSelect, sqlConnection);
sqlCommand.Parameters.AddWithValue("@postText", HttpUtility.UrlDecode(postText));
MySqlDataAdapter sqlDa = new MySqlDataAdapter(sqlCommand);
sqlDa.Fill(sqlDt);
List <Models.WhistleObjects> listOfPosts = new List <Models.WhistleObjects>();
for (int i = 0; i < sqlDt.Rows.Count; i++)
{
listOfPosts.Add(new Models.WhistleObjects
{
Category = sqlDt.Rows[i]["Category"].ToString(),
PostText = sqlDt.Rows[i]["Text"].ToString(),
Flag = Int16.Parse(sqlDt.Rows[i]["Flag"].ToString()),
PostVotes = Int32.Parse(sqlDt.Rows[i]["Votes"].ToString()),
PostID = Int32.Parse(sqlDt.Rows[i]["PostID"].ToString())
});
}
//convert the list of accounts to an array and return!
return(listOfPosts.ToArray());
}