Ejemplo n.º 1
0
 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());
         }
     }
 }
Ejemplo n.º 2
0
    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);
                }
            }
        }
    }