public static string GetFileName(Guid id)
{
using (SqlConnection connection = new SqlConnection(Config.ConnectionString))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand("SELECT fileName FROM Documents WHERE id = @id", connection))
{
cmd.CommandTimeout = Config.CommandTimeout;
cmd.AssignParams("id", id);
return (string)cmd.ExecuteScalarRetryOnDeadlock();
}
}
}
public static long SentimentWordOccurrenceToDb(SqlConnection connection, string date, int locationStart, int locationEnd, int sentenceNum, int blockNum, long document_id, string entityUri)
{
using (SqlCommand cmd = new SqlCommand(null, connection))
{
cmd.CommandTimeout = 300;
cmd.CommandText = ClSentimentWordOccurrence;
// Console.WriteLine("\n -------------------"+entityUri);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@date", date);
cmd.Parameters.AddWithValue("@locationStart", locationStart);
cmd.Parameters.AddWithValue("@locationEnd", locationEnd);
cmd.Parameters.AddWithValue("@sentenceNum", sentenceNum);
cmd.Parameters.AddWithValue("@blockNum", blockNum);
cmd.Parameters.AddWithValue("@document_id", document_id);
cmd.Parameters.AddWithValue("@entityUri", entityUri);
try
{
Decimal tmp = (Decimal)cmd.ExecuteScalarRetryOnDeadlock();
long id = Decimal.ToInt32(tmp); //Execute the command, get id of the inserted document
return id;
}
catch (Exception ex)
{
Console.WriteLine("\nError inserting sentiment word occurrence " + entityUri + "\n" + ex);
return 0;
}
}
}
public static long OccurrenceToDb(SqlConnection connection, string date, int locationStart, int locationEnd, int sentenceNum, int blockNum, long document_id, string entityUri)
{
using (SqlCommand cmd = new SqlCommand(null, connection))
{
cmd.CommandTimeout = 300;
cmd.CommandText = ClOccurrence;
// Console.WriteLine("\n -------------------"+entityUri);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@date", date);
cmd.Parameters.AddWithValue("@locationStart", locationStart);
cmd.Parameters.AddWithValue("@locationEnd", locationEnd);
cmd.Parameters.AddWithValue("@sentenceNum", sentenceNum);
cmd.Parameters.AddWithValue("@blockNum", blockNum);
cmd.Parameters.AddWithValue("@document_id", document_id);
cmd.Parameters.AddWithValue("@entityUri", entityUri);
try
{
Decimal tmp = (Decimal)cmd.ExecuteScalarRetryOnDeadlock();
long id = Decimal.ToInt32(tmp); //Execute the command, get id of the inserted document
return id;
}
catch (Exception ex)
{
string errorMessage = String.Format("\nError inserting occurrence: \n\t\t DocumentId:{0} \n\t\tEntityUri:{1}", document_id, entityUri);
throw new Exception(ex + errorMessage);
}
}
}
/// <summary>
/// Inserts the data about the document document table in the semantics database.
/// </summary>
public static long DocumentToDb(SqlConnection connection, string title, string date, string pubDate, string timeGet, string responseUrl, string urlKey, string domainName, bool isFinancial, double pumpDumpIndex, Guid documentGuid)
{
using (SqlCommand cmd = new SqlCommand(null, connection))
{
cmd.CommandTimeout = 300;
cmd.CommandText = ClDocument;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@title", Shorten(title, 1000));
cmd.Parameters.AddWithValue("@date", date);
cmd.Parameters.AddWithValue("@pubDate", Shorten(pubDate, 100));
cmd.Parameters.AddWithValue("@timeGet", timeGet);
cmd.Parameters.AddWithValue("@responseUrl", Shorten(responseUrl, 1000));
cmd.Parameters.AddWithValue("@urlKey", Shorten(urlKey, 1000));
cmd.Parameters.AddWithValue("@domainName", Shorten(domainName, 255));
cmd.Parameters.AddWithValue("@isFinancial", isFinancial);
cmd.Parameters.AddWithValue("@pumpDumpIndex", pumpDumpIndex);
cmd.Parameters.AddWithValue("@documentGuid", documentGuid);
try
{
long id = Decimal.ToInt32((Decimal)cmd.ExecuteScalarRetryOnDeadlock()); //Execute the command, get id of the inserted document
return id;
}
catch (SqlException ex)
{
string errorMessage = String.Format("\nError inserting document: \r\nThe document: \n\t\tTitle:{0} \n\t\tDate:{1} \n\t\tUrlKey:{2}", title, date, urlKey);
throw new Exception(ex + errorMessage);
}
}
}