private void CreatePartialQuery(int from, int to) { if (to > Datasets.Count) { to = Datasets.Count; } string query = @"WITH data(" + QuandlDataset.GetColumnsForQuery() + @") as ( values"; //string query = ""; for (int i = from; i < to; i++) { // Current QuandlDataset item = Datasets[i]; // Base insert query += String.Format(@"({0}, '{1}', '{2}', '{3}', '{4}', to_date('{5}', 'YYYY-MM_DD'), to_date('{6}', 'YYYY-MM_DD'), '{7}', '{8}', '{9}', {10}, {11}, {12})", item.Id, item.DatasetCode, item.DatabaseCode, item.Name, item.Description, // 0 - 4 item.NewestAvailableDate.GetValueOrDefault(DateTime.Now).ToString("yyyy-MM-dd"), item.OldestAvailableDate.GetValueOrDefault(DateTime.Now).ToString("yyyy-MM-dd"), // 5 - 6 string.Join(",", item.ColumnNames), // 7 item.Frequency, item.Type, // 8 - 9 item.Premium, item.DatabaseId, item.Import); // 10 - 12 query += ","; } // Remove last comma "," query = query.Remove(query.Length - 1); query += ")"; // Close (values ... ) query += "\nINSERT INTO quandl.datasets (" + QuandlDataset.GetColumnsForQuery() + ")" + " SELECT " + QuandlDataset.GetColumnsForQuery() + " FROM data" + " WHERE NOT EXISTS (SELECT 1 FROM quandl.datasets ds WHERE ds.Id = data.Id)"; //Execute query using (var conn = new NpgsqlConnection(Utils.Constants.CONNECTION_STRING)) { using (var cmd = new NpgsqlCommand()) { // Open connection // =============================================================== conn.Open(); cmd.Connection = conn; cmd.CommandText = query; try { cmd.ExecuteNonQuery(); } catch (PostgresException ex) { conn.Close(); Helpers.ExitWithError(ex.Message); } // Close connection // =============================================================== conn.Close(); } } }
// Creates query to insert dataset public string MakeInsertQueryFile() { // Init bulk query file InitFile(); // Inital part string query = @"WITH data(" + QuandlDataset.GetColumnsForQuery() + @") as ( values"; WriteToQueryFile(query); // Data elements to be formated for each thread int elementsPerThread = 1000; // Init where all queries generated will belong //queries = new List<string>(); // Init taks var tasks = new List <Task>(); // Create query only if needed if (Datasets.Count > 0) { tasks.AddRange(CreateQueryThreadsFile(elementsPerThread)); } else { Utils.ConsoleInformer.Inform("Database [" + DatabaseCode + "] is already in its last version"); } // If nothing to do just skip if (tasks.Count <= 0) { return(""); } // Wait for all the threads to complete Task.WaitAll(tasks.ToArray()); // Remove last comma "," RemoveLastCommaInQueryFile(); WriteToQueryFile(")"); // Close (values ... ) WriteToQueryFile("\nINSERT INTO quandl.datasets (" + QuandlDataset.GetColumnsForQuery() + ")" + " SELECT " + QuandlDataset.GetColumnsForQuery() + " FROM data" + " WHERE NOT EXISTS (SELECT 1 FROM quandl.datasets ds WHERE ds.Id = data.Id)"); return(queryFilePath); }