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