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); } } } }