private string InsertNewsSource(IBaseNewsParser parser)
{
using (NpgsqlConnection connection = new NpgsqlConnection(connectString + ";Database=" + databaseName))
{
try
{
connection.Open();
state = PostgreSQLState.Running;
}
catch
{
state = PostgreSQLState.ConnectionError;
return(string.Empty);
}
using (NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO news_source (name, category, url) VALUES ('" +
parser.GetName() + "','" + parser.GetCategory() + "','" + parser.GetUrl() + "') ON CONFLICT DO NOTHING;"
, connection))
{
cmd.ExecuteNonQuery();
}
using (NpgsqlCommand cmd = new NpgsqlCommand("SELECT id FROM news_source WHERE name = '" + parser.GetName() + "'", connection))
{
return(cmd.ExecuteScalar().ToString());
}
}
}
public PostgreSQLState InsertNewsItems(IBaseNewsParser parser)
{
string sourceId = InsertNewsSource(parser);
if (string.IsNullOrEmpty(sourceId))
{
return(state);
}
using (NpgsqlConnection connection = new NpgsqlConnection(connectString + ";Database=" + databaseName))
{
try
{
connection.Open();
state = PostgreSQLState.Running;
}
catch
{
return(state = PostgreSQLState.ConnectionError);
}
lastQuery = "INSERT INTO news (title, annotation, url, id_source, publication_date, upload_date) VALUES\n";
foreach (var item in parser.GetNewsItems())
{
if (string.IsNullOrEmpty(item.date))
{
continue;
}
string annotation = item.annotation;
if (annotation.Length > 1023)
{
annotation = annotation.Remove(1019);
annotation += "...";
}
lastQuery += "('" + item.title.Replace("'", "''") + "','" + annotation.Replace("'", "''") + "','" + item.newsUrl.Replace("'", "''") + "','" + sourceId.Replace("'", "''") + "','" + item.date + "', (SELECT * FROM transaction_timestamp())),\n";
}
lastQuery = lastQuery.Remove(lastQuery.Length - 2);
lastQuery += "\nON CONFLICT DO NOTHING;";
using (NpgsqlCommand cmd = new NpgsqlCommand(lastQuery, connection))
{
try
{
var answer = cmd.ExecuteNonQuery();
return(state = PostgreSQLState.ExecutionCompleted);
}
catch
{
return(state = PostgreSQLState.ExecutionError);
}
}
}
}