/// <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]; } } } }