public List<Tweets> getRelatedTweets(string keyword, string date, string hour)
{
// empty list
List<Tweets> tweetList = new List<Tweets>();
DataSet ds = new DataSet();
using (MySqlConnection cn = new MySqlConnection())
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlDataAdapter da = new MySqlDataAdapter())
{
// obtain connection string information from app.config
cn.ConnectionString = "server=localhost; userid=root; password=; database=twitter_stream;";
// tell the cmd to use the cn
cmd.Connection = cn;
// supply the cmd with the necessary SQL Y-M-D FULL
cmd.CommandText = "SELECT * FROM tagsretrievedtemp tR INNER JOIN contentsretrievedtemp cT ON tR.tagId = cT.tagId WHERE tag = '" + keyword
+ "'and date='" + date
+ "' and hour='" + hour
+ "'ORDER BY hour DESC;";
// tell the DataAdapter to use the cmd
da.SelectCommand = cmd;
// open an active connection
cn.Open();
// returns the results
da.Fill(ds, "relatedTweets");
}
// close the connection
cn.Close();
}
}
// loop throught the datarows in the DataTable
// fetch all the data and pump them into list
foreach (DataRow dr in ds.Tables["relatedTweets"].Rows)
{
Tweets tweets = new Tweets();
tweets.Mood = dr["mood"].ToString();
tweets.Content = dr["content"].ToString();
tweetList.Add(tweets);
}
return tweetList;
}
public static object getRelatedTweets(string selectedWord, string selectedDate, string selectedTime)
{
// create a list, which can hold tweets instances
List<Tweets> tweetList = new List<Tweets>();
//convert to correct date format
string format = "MM-dd-yyyy";
string format2 = "MM/dd/yyyy";
DateTime dateTime;
string str1 = "";
if (DateTime.TryParseExact(selectedDate, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime))
{
str1 = dateTime.ToString("yyyy'-'MM'-'dd");
}
else if (DateTime.TryParseExact(selectedDate, format2, CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime))
{
str1 = dateTime.ToString("yyyy'-'MM'-'dd");
}
// create a tweets class instance so that i
// can talk to database
Tweets tweets = new Tweets();
tweetList = tweets.getRelatedTweets(selectedWord, str1, selectedTime);
// assign the tweetList to the response object
object response = tweetList;
return response;
}