/// <summary> /// Add a select based view to the data table for SQLite datastores /// </summary> /// <param name="name">name of the view</param> /// <param name="selectSQL">select SQL statement</param> public void AddView(string name, string selectSQL) { if (connection is SQLite) { if (connection.ViewExists(name)) { connection.ExecuteNonQuery($"DROP VIEW {name}"); } connection.ExecuteNonQuery($"CREATE VIEW {name} AS {selectSQL}"); } else { throw new NotImplementedException(); } }
internal void SavePatients(Collection <Patient> patients) { try { foreach (var row in patients) { _database.ExecuteNonQuery(String.Format("EXEC insertOrUpdatePatients {0}, '{1}', '{2}', '{3}', '{4}', '{5}';", row.Id, row.Name, row.Firstname, row.Birthday, row.Adress, row.Entry)); } } catch (Exception e) { log.Error(e.Message); MessageBox.Show("Das Verbidnen mit der Datenbank ist nicht möglich. Patientendaten können nicht eingefügt werden", "Keine Verbindung"); } }
/// <summary>Alter an existing table ensuring all columns exist.</summary> /// <param name="connection">The database connection to write to</param> private void AlterTable(IDatabaseConnection connection) { List <string> existingColumns = connection.GetTableColumns(Name); lock (lockObject) { foreach (Column col in Columns) { if (!existingColumns.Contains(col.Name)) { string dataTypeString; if (col.DatabaseDataType == null) { dataTypeString = "integer"; } else { dataTypeString = col.DatabaseDataType; } string sql = "ALTER TABLE " + Name + " ADD COLUMN [" + col.Name + "] " + dataTypeString; connection.ExecuteNonQuery(sql); } } } }
/// <summary>Called to run the command. Can throw on error.</summary> /// <param name="cancelToken">Is cancellation pending?</param> public void Run(CancellationTokenSource cancelToken) { if (database.TableExists(table)) { string sql = string.Empty; bool dropEntireTable = false; if (simIds == null) { if (checkId == 0) { dropEntireTable = true; } else { sql = string.Format("DELETE FROM [{0}] WHERE [CheckpointID]={1}", table, checkId); } } else { List <string> columns = database.GetTableColumns(table); string ids = StringUtilities.BuildString(simIds, ","); if (columns.Contains("SimulationID")) { sql = string.Format("DELETE FROM [{0}] WHERE [CheckpointID]={1} AND [SimulationID] IN ({2})", table, checkId, ids); } else if (columns.Contains("SimulationName")) { sql = string.Format("DELETE T FROM [{0}] T INNER JOIN [_Simulations] S ON T.[SimulationName]=S.[SimulationName] WHERE [SimulationID] IN ({1})", table, ids); if (checkId != 0) { sql += string.Format(" AND [CheckpointID]={0}", checkId); } } else { dropEntireTable = true; } } if (dropEntireTable) { database.DropTable(table); } else { database.ExecuteNonQuery(sql); // If there are no rows left in table then drop the table. if (database.TableIsEmpty(table)) { database.DropTable(table); } } } }
/// <summary>Called to run the command. Can throw on error.</summary> /// <param name="cancelToken">Is cancellation pending?</param> public void Run(CancellationTokenSource cancelToken) { if (dataToWrite.Rows.Count > 0) { if (!tables.TryGetValue(dataToWrite.TableName, out var table)) { table = new DatabaseTableDetails(connection, dataToWrite.TableName); tables.Add(dataToWrite.TableName, table); } var query = new InsertQuery(dataToWrite); // Make sure the table has the correct columns. table.EnsureTableExistsAndHasRequiredColumns(dataToWrite); // Get a list of column names. var columnNames = dataToWrite.Columns.Cast <DataColumn>().Select(col => col.ColumnName); try { connection.BeginTransaction(); if (deleteExistingRows) { // fixme - this assumes that "Current" checkpoint ID is always 1. // This should always be correct afaik, but it would be better to // verify this at runtime. bool tableHasCheckpointID = connection.GetColumns(dataToWrite.TableName).Any(c => c.Item1 == "CheckpointID"); connection.ExecuteNonQuery($"DELETE FROM [{dataToWrite.TableName}] {(tableHasCheckpointID ? "WHERE CheckpointID = 1" : "")}"); } // Write all rows. foreach (DataRow row in dataToWrite.Rows) { query.ExecuteQuery(connection, columnNames, row.ItemArray); } } finally { connection.EndTransaction(); query.Close(connection); } } }
/// <summary>Called to run the command. Can throw on error.</summary> /// <param name="cancelToken">Is cancellation pending?</param> public void Run(CancellationTokenSource cancelToken) { if (database.TableExists(table)) { string sql; if (simIds == null) { if (checkId == 0) { sql = string.Format("DROP TABLE {0}", table); } else { sql = string.Format("DELETE FROM {0} WHERE CheckpointID={1}", table, checkId); } } else { List <string> columns = database.GetTableColumns(table); string ids = StringUtilities.BuildString(simIds, ","); if (columns.Contains("SimulationID")) { sql = string.Format("DELETE FROM {0} WHERE CheckpointID={1} AND SimulationID IN ({2})", table, checkId, ids); } else if (columns.Contains("SimulationName")) { sql = string.Format("DELETE T FROM {0} T INNER JOIN _Simulations S ON SimulationName=SimulationName WHERE SimulationID IN ({1})", table, ids); if (checkId != 0) { sql += string.Format(" AND CheckpointID={0}", checkId); } } else { sql = string.Format("DROP TABLE {0}", table); } } database.ExecuteNonQuery(sql); } }
/// <summary>Called to run the command. Can throw on error.</summary> /// <param name="cancelToken">Is cancellation pending?</param> public void Run(CancellationTokenSource cancelToken) { if (database == null) { return; } if (database.TableExists("_Checkpoints")) { var checkpointData = new DataView(database.ExecuteQuery("SELECT * FROM [_Checkpoints]")); checkpointData.RowFilter = "Name='Current'"; if (checkpointData.Count == 1) { int checkId = Convert.ToInt32(checkpointData[0]["ID"], CultureInfo.InvariantCulture); // Delete current data from all tables. foreach (string tableName in database.GetTableNames()) { if (!tableName.StartsWith("_") && database.TableExists(tableName)) { database.ExecuteNonQuery(string.Format("DELETE FROM [{0}] WHERE [CheckpointID]={1}", tableName, checkId)); } } } } else { // No checkpoint information, so get rid of everything // Delete current data from all tables. foreach (string tableName in database.GetTableNames()) { if (!tableName.StartsWith("_")) { database.ExecuteNonQuery(string.Format("DELETE FROM [{0}]", tableName)); } } } // Delete empty tables. List <string> tableNamesToDelete = new List <string>(); bool allTablesEmpty = true; foreach (string tableName in database.GetTableNames()) { if (!tableName.StartsWith("_")) { if (database.TableIsEmpty(tableName)) { tableNamesToDelete.Add(tableName); } else { allTablesEmpty = false; } } } // If all data tables were emptied then delete all tables. if (allTablesEmpty) { tableNamesToDelete = database.GetTableNames(); } foreach (string tableName in tableNamesToDelete) { database.DropTable(tableName); } }
/// <summary>Called to run the command. Can throw on error.</summary> /// <param name="cancelToken">Is cancellation pending?</param> public void Run(CancellationTokenSource cancelToken) { if (database.TableExists("_Checkpoints")) { var checkpointData = new DataView(database.ExecuteQuery("SELECT * FROM _Checkpoints")); checkpointData.RowFilter = "Name='Current'"; if (checkpointData.Count == 1) { int checkId = Convert.ToInt32(checkpointData[0]["ID"]); // Delete current data from all tables. foreach (string tableName in database.GetTableNames()) { if (!tableName.StartsWith("_")) { database.ExecuteNonQuery(string.Format("DELETE FROM {0} WHERE CheckpointID={1}", tableName, checkId)); } } } } else { // No checkpoint information, so get rid of everything // Delete current data from all tables. foreach (string tableName in database.GetTableNames()) { if (!tableName.StartsWith("_")) { database.ExecuteNonQuery(string.Format("DELETE FROM {0}", tableName)); } } } // Delete empty tables. List <string> tableNamesToDelete = new List <string>(); bool allTablesEmpty = true; foreach (string tableName in database.GetTableNames()) { if (!tableName.StartsWith("_")) { var data = database.ExecuteQuery(string.Format("SELECT * FROM {0} LIMIT 1", tableName)); if (data.Rows.Count == 0) { tableNamesToDelete.Add(tableName); } else { allTablesEmpty = false; } } } // If all data tables were emptied then delete all tables. if (allTablesEmpty) { tableNamesToDelete = database.GetTableNames(); } foreach (string tableName in tableNamesToDelete) { database.ExecuteNonQuery(string.Format("DROP TABLE {0}", tableName)); } database.ExecuteNonQuery("VACUUM"); }