public int setCrimeTweet(CrimeTweets crime_tweet)
{
int statusCode = -1;
SqlConnection connection = new SqlConnection(connectionString);
string query = "UPDATE CrimeTweets " +
"SET message = @message, " +
"latitude = @latitude, " +
"longitude = @longitude, " +
"location = @location, " +
"post_datetime = @post_datetime, " +
"recieved_datetime = @recieved_datetime, " +
"twitter_handle = @twitter_handle, " +
"weather = @weather, " +
"mentions = @mentions, " +
"tags = @tags " +
"WHERE tweet_id = @id;";
SqlCommand command = new SqlCommand(query);
command.Parameters.Add("@id", SqlDbType.Int).Value = crime_tweet.tweet_id;
command.Parameters.Add("@message", SqlDbType.VarChar, 160).Value = crime_tweet.message;
command.Parameters.Add("@latitude", SqlDbType.Decimal);
command.Parameters["@latitude"].Precision = 38;
command.Parameters["@latitude"].Scale = 19;
command.Parameters["@latitude"].Value = crime_tweet.latitude;
command.Parameters.Add("@longitude", SqlDbType.Decimal);
command.Parameters["@longitude"].Precision = 38;
command.Parameters["@longitude"].Scale = 19;
command.Parameters["@longitude"].Value = crime_tweet.longitude;
command.Parameters.Add("@location", SqlDbType.VarChar, 60).Value = crime_tweet.location;
command.Parameters.Add("@post_datetime", SqlDbType.DateTime2, 27).Value = crime_tweet.post_datetime;
command.Parameters.Add("@recieved_datetime", SqlDbType.DateTime2, 27).Value = crime_tweet.recieved_datetime;
command.Parameters.Add("@twitter_handle", SqlDbType.VarChar, 20).Value = crime_tweet.twitter_handle;
command.Parameters.Add("@weather", SqlDbType.VarChar, -1).Value = crime_tweet.weather;
command.Parameters.Add("@mentions", SqlDbType.VarChar, -1).Value = crime_tweet.mentions;
command.Parameters.Add("@tags", SqlDbType.VarChar, -1).Value = crime_tweet.tags;
command.Connection = connection;
command.CommandType = CommandType.Text;
try
{
command.Connection.Open();
command.Prepare();
statusCode = command.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
}
command.Connection.Close();
command.Dispose();
connection.Dispose();
return(statusCode);
}
//Get all tweets from last analysis in the database
public List <CrimeTweets> getCrimeTweetsToAnalyse()
{
List <CrimeTweets> tweets = new List <CrimeTweets>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM CrimeTweets WHERE tweet_id > " + getTextAnalyticsStartPoint();
SqlCommand command = new SqlCommand(query);
command.Connection = connection;
command.CommandType = CommandType.Text;
try
{
command.Connection.Open();
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
//Column Indexs
int tweetIDIndex = reader.GetOrdinal("tweet_id");
int messageIndex = reader.GetOrdinal("message");
int latitudeIndex = reader.GetOrdinal("latitude");
int longitudeIndex = reader.GetOrdinal("longitude");
int locationIndex = reader.GetOrdinal("location");
int postDatetimeIndex = reader.GetOrdinal("post_datetime");
int recievedDatetimeIndex = reader.GetOrdinal("recieved_datetime");
int twitterHandleIndex = reader.GetOrdinal("twitter_handle");
int weatherIndex = reader.GetOrdinal("weather");
int mentionsIndex = reader.GetOrdinal("mentions");
int tagsIndex = reader.GetOrdinal("tags");
int limit = 50;
int currCount = 0;
while (reader.Read() && (currCount <= limit))
{
currCount++;
CrimeTweets tweet = new CrimeTweets();
tweet.tweet_id = reader.GetInt32(tweetIDIndex);
tweet.message = reader.GetString(messageIndex);
tweet.latitude = getDoubleSafe(reader, latitudeIndex); //Nullable
tweet.longitude = getDoubleSafe(reader, longitudeIndex); //Nullable
tweet.location = getStringSafe(reader, locationIndex); // Nullable
tweet.post_datetime = reader.GetDateTime(postDatetimeIndex);
tweet.recieved_datetime = reader.GetDateTime(recievedDatetimeIndex);
tweet.twitter_handle = reader.GetString(twitterHandleIndex);
tweet.weather = getStringSafe(reader, weatherIndex); // Nullable
tweet.mentions = getStringSafe(reader, mentionsIndex); // Nullable
tweet.tags = getStringSafe(reader, tagsIndex); // Nullable
tweets.Add(tweet);
}
}
catch (SqlException ex)
{
Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
}
command.Connection.Close();
command.Dispose();
connection.Dispose();
return(tweets);
}
public CrimeTweets getCrimeTweet(int tweet_id)
{
CrimeTweets mytweet = new CrimeTweets();
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM CrimeTweets WHERE tweet_id = @id";
SqlCommand command = new SqlCommand(query);
command.Parameters.Add("@id", SqlDbType.Int);
command.Parameters["@id"].Value = tweet_id;
command.Connection = connection;
command.CommandType = CommandType.Text;
try
{
command.Connection.Open();
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
//Coloumns Index
int tweetIDIndex = reader.GetOrdinal("tweet_id");
int messageIndex = reader.GetOrdinal("message");
int latitudeIndex = reader.GetOrdinal("latitude");
int longitudeIndex = reader.GetOrdinal("longitude");
int locationIndex = reader.GetOrdinal("location");
int postDatetimeIndex = reader.GetOrdinal("post_datetime");
int recievedDatetimeIndex = reader.GetOrdinal("recieved_datetime");
int twitterHandleIndex = reader.GetOrdinal("twitter_handle");
int weatherIndex = reader.GetOrdinal("weather");
int mentionsIndex = reader.GetOrdinal("mentions");
int tagsIndex = reader.GetOrdinal("tags");
if (reader.HasRows)
{
reader.Read();
mytweet.tweet_id = reader.GetInt32(tweetIDIndex);
mytweet.message = reader.GetString(messageIndex);
mytweet.latitude = getDoubleSafe(reader, latitudeIndex); //Nullable
mytweet.longitude = getDoubleSafe(reader, longitudeIndex); //Nullable
mytweet.location = getStringSafe(reader, locationIndex); // Nullable
mytweet.post_datetime = reader.GetDateTime(postDatetimeIndex);
mytweet.recieved_datetime = reader.GetDateTime(recievedDatetimeIndex);
mytweet.twitter_handle = reader.GetString(twitterHandleIndex);
mytweet.weather = getStringSafe(reader, weatherIndex); // Nullable
mytweet.mentions = getStringSafe(reader, mentionsIndex); // Nullable
mytweet.tags = getStringSafe(reader, tagsIndex); // Nullable
}
}
catch (SqlException ex)
{
Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
}
command.Connection.Close();
command.Dispose();
connection.Dispose();
return(mytweet);
}
// <returns>ID of entry or negative for failure. -1 INSERT failure. -2 duplicate </returns>
public int addCrimeTweet(CrimeTweets crime_tweet)
{
int statusCode = -1;
SqlConnection connection = new SqlConnection(connectionString);
string query = "INSERT INTO CrimeTweets (message, latitude, longitude, location, post_datetime, recieved_datetime, twitter_handle, weather, mentions, tags) " + //"OUTPUT INSERTED.tweet_id " +
"OUTPUT INSERTED.tweet_id " +
"VALUES (@message, @latitude, @longitude, @location, @post_datetime, @recieved_datetime, @twitter_handle, @weather, @mentions, @tags);";
SqlCommand command = new SqlCommand(query);
command.Parameters.Add("@message", SqlDbType.VarChar, 160).Value = crime_tweet.message;
command.Parameters.Add("@latitude", SqlDbType.Decimal);
command.Parameters["@latitude"].Precision = 38;
command.Parameters["@latitude"].Scale = 19;
command.Parameters["@latitude"].Value = crime_tweet.latitude;
command.Parameters.Add("@longitude", SqlDbType.Decimal);
command.Parameters["@longitude"].Precision = 38;
command.Parameters["@longitude"].Scale = 19;
command.Parameters["@longitude"].Value = crime_tweet.longitude;
command.Parameters.Add("@location", SqlDbType.VarChar, 60).Value = crime_tweet.location;
command.Parameters.Add("@post_datetime", SqlDbType.DateTime2, 27).Value = crime_tweet.post_datetime;
command.Parameters.Add("@recieved_datetime", SqlDbType.DateTime2, 27).Value = crime_tweet.recieved_datetime;
command.Parameters.Add("@twitter_handle", SqlDbType.VarChar, 20).Value = crime_tweet.twitter_handle;
command.Parameters.Add("@weather", SqlDbType.VarChar, -1).Value = crime_tweet.weather;
command.Parameters.Add("@mentions", SqlDbType.VarChar, -1).Value = crime_tweet.mentions;
command.Parameters.Add("@tags", SqlDbType.VarChar, -1).Value = crime_tweet.tags;
command.Connection = connection;
command.CommandType = CommandType.Text;
try
{
command.Connection.Open();
command.Prepare();
statusCode = (Int32)command.ExecuteScalar();
}
catch (SqlException ex)
{
Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
}
command.Connection.Close();
command.Dispose();
connection.Dispose();
return(statusCode);
}
