// Create datatables data model
public static void CreateQuandlDatatableModelTable(QuandlDatatable datatable)
{
// Do not make data tables without name
if (String.IsNullOrWhiteSpace(datatable.Name))
{
return;
}
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
// Query
string query = @"CREATE TABLE quandl.""" + datatable.Name + @"""(" +
// Column names [specific data]
datatable.MakeExtraColumnsWithDataType() + @",
date_insert timestamptz
);";
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
//Console.WriteLine(ex.Message);
if (ex.SqlState == "42P07")
{
createDateInsertColumnIfNeeded(@"quandl.""" + datatable.Name + @"""");
ConsoleInformer.Inform("Table model [" + datatable.Name + "] already exists. Truncating...");
cmd.CommandText = @"TRUNCATE TABLE quandl.""" + datatable.Name + @"""";
try { cmd.ExecuteNonQuery(); }
catch (PostgresException exception) { conn.Close(); Helpers.ExitWithError(exception.Message); }
}
else
{
conn.Close(); Helpers.ExitWithError(ex.Message);
}
}
ConsoleInformer.PrintProgress("3D", "[" + datatable.Name + "] Creating table model: ", "100%");
// Close connection
// ===============================================================
conn.Close();
}
}
}
public static List <QuandlDatatable> GetImportedDatatables()
{
// Query
string query = @"SELECT name
FROM quandl.datatables
WHERE import = true";
List <QuandlDatatable> databases = new List <QuandlDatatable>();
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand(query))
{
// Open connection
// ===============================================================
conn.Open();
cmd.Connection = conn;
try
{
// Execute the query and obtain a result set
NpgsqlDataReader dr = cmd.ExecuteReader();
// Each row
while (dr.Read())
{
databases.Add(QuandlDatatable.MakeQuandlDatabase(dr));
}
}
catch (Exception ex)
{
conn.Close();
Helpers.ExitWithError(ex.Message);
}
ConsoleInformer.PrintProgress("2D", "Querying imported databases: ", "100%");
// Close connection
// ===============================================================
conn.Close();
}
}
return(databases);
}
private static DatatableResponse DownloadDatatable(QuandlDatatable datatable)
{
using (PatientWebClient client = new PatientWebClient())
{
try
{
var json = client.DownloadString(" https://www.quandl.com/api/v3/datatables/" + datatable.Name + ".json?api_key=" + Utils.Constants.API_KEY);
DatatableResponse response =
JsonConvert.DeserializeObject <DatatableResponse>(json, new JsonSerializerSettings {
ContractResolver = Utils.Converters.MakeUnderscoreContract()
});
Utils.ConsoleInformer.PrintProgress("1B", "Fetching datatable [" + datatable.Name + "]: ", Utils.Helpers.GetPercent(count, datatables.Count).ToString() + "%");
return(response);
}
catch (Exception e)
{
// Add error to inform and log later
errors.Add(new Tuple <string, string>("Failed to fetch datatable: [" + datatable.Name + "]", "Ex: " + e.Message));
return(new DatatableResponse());
}
}
}