/// <summary>
/// Using the SimulationName column in the specified 'table', add a
/// SimulationID column.
/// </summary>
/// <param name="table">The table.</param>
/// <exception cref="Models.Core.ApsimXException">Cannot find Simulations table</exception>
private void AddSimulationIDColumnToTable(DataTable table)
{
// Get a list of simulations that are in the DB
DataTable DB = Connection.ExecuteQuery("SELECT * FROM Simulations");
List <string> simulationNamesInDB = DataTableUtilities.GetColumnAsStrings(DB, "Name").ToList();
// Tell SQLite that we're beginning a transaction.
Connection.ExecuteNonQuery("BEGIN");
try
{
// For those simulations in 'table' that aren't in the DB, add them
// to the simulations table
List <string> simulationNamesInTable = DataTableUtilities.GetDistinctValues(table, "SimulationName");
foreach (string simulationNameInTable in simulationNamesInTable)
{
if (!StringUtilities.Contains(simulationNamesInDB, simulationNameInTable))
{
RunQueryWithNoReturnData("INSERT INTO [Simulations] (Name) VALUES ('" + simulationNameInTable + "')");
}
}
}
finally
{
// Tell SQLite that we're ending a transaction.
Connection.ExecuteNonQuery("END");
}
// Get a list of simulation names and IDs from DB
DB = Connection.ExecuteQuery("SELECT * FROM Simulations");
List <double> ids = DataTableUtilities.GetColumnAsDoubles(DB, "ID").ToList();
simulationNamesInDB = DataTableUtilities.GetColumnAsStrings(DB, "Name").ToList();
table.Columns.Add("SimulationID", typeof(int)).SetOrdinal(0);
foreach (DataRow row in table.Rows)
{
string simulationName = row["SimulationName"].ToString();
if (simulationName != null)
{
int index = StringUtilities.IndexOfCaseInsensitive(simulationNamesInDB, simulationName);
if (index == -1)
{
throw new Exception("Cannot find simulation name: " + simulationName);
}
else
{
row["SimulationID"] = ids[index];
}
}
}
}