private bool TryGetFile(string relativePath, string fileName, out ImageRow file) { ColumnTuplesWithWhere fileQuery = new ColumnTuplesWithWhere(); fileQuery.SetWhere(relativePath, fileName); List<ImageRow> files = this.Files.Select(fileQuery.Where); if (files.Count == 0) { file = null; return false; } if (files.Count == 1) { file = files[0]; return true; } throw new ArgumentOutOfRangeException("relativePath, fileName", String.Format("{0} files match '{1}'.", files.Count, fileQuery.Where)); }
protected List<FileExpectations> PopulateDefaultDatabase(FileDatabase fileDatabase, bool excludeSubfolderFiles) { TimeZoneInfo imageSetTimeZone = fileDatabase.ImageSet.GetTimeZone(); // files in same folder as .tdb and .ddb DateTimeAdjustment martenDateTimeAdjustment; ImageRow martenImage = this.CreateFile(fileDatabase, imageSetTimeZone, TestConstant.FileExpectation.InfraredMarten, out martenDateTimeAdjustment); Assert.IsTrue(martenDateTimeAdjustment.HasFlag(DateTimeAdjustment.MetadataDate) && martenDateTimeAdjustment.HasFlag(DateTimeAdjustment.MetadataTime)); DateTimeAdjustment bobcatDatetimeAdjustment; ImageRow bobcatImage = this.CreateFile(fileDatabase, imageSetTimeZone, TestConstant.FileExpectation.DaylightBobcat, out bobcatDatetimeAdjustment); Assert.IsTrue(bobcatDatetimeAdjustment.HasFlag(DateTimeAdjustment.MetadataDate) && bobcatDatetimeAdjustment.HasFlag(DateTimeAdjustment.MetadataTime)); fileDatabase.AddFiles(new List<ImageRow>() { martenImage, bobcatImage }, null); fileDatabase.SelectFiles(FileSelection.All); FileTableEnumerator fileEnumerator = new FileTableEnumerator(fileDatabase); Assert.IsTrue(fileEnumerator.TryMoveToFile(0)); Assert.IsTrue(fileEnumerator.MoveNext()); ColumnTuplesWithWhere bobcatUpdate = new ColumnTuplesWithWhere(); bobcatUpdate.Columns.Add(new ColumnTuple(TestConstant.DefaultDatabaseColumn.Choice0, "choice b")); bobcatUpdate.Columns.Add(new ColumnTuple(TestConstant.DefaultDatabaseColumn.Counter0, 1.ToString())); bobcatUpdate.Columns.Add(new ColumnTuple(TestConstant.DefaultDatabaseColumn.FlagNotVisible, true)); bobcatUpdate.Columns.Add(new ColumnTuple(TestConstant.DefaultDatabaseColumn.Note3, "bobcat")); bobcatUpdate.Columns.Add(new ColumnTuple(TestConstant.DefaultDatabaseColumn.NoteNotVisible, "adult")); bobcatUpdate.SetWhere(fileEnumerator.Current.ID); fileDatabase.UpdateFiles(new List<ColumnTuplesWithWhere>() { bobcatUpdate }); long martenImageID = fileDatabase.Files[0].ID; fileDatabase.UpdateFile(martenImageID, TestConstant.DefaultDatabaseColumn.Choice0, "choice b"); fileDatabase.UpdateFile(martenImageID, TestConstant.DefaultDatabaseColumn.Counter0, 1.ToString()); fileDatabase.UpdateFile(martenImageID, TestConstant.DefaultDatabaseColumn.FlagNotVisible, Boolean.TrueString); fileDatabase.UpdateFile(martenImageID, TestConstant.DefaultDatabaseColumn.Note3, "American marten"); fileDatabase.UpdateFile(martenImageID, TestConstant.DefaultDatabaseColumn.NoteNotVisible, "adult"); // generate expectations List<FileExpectations> fileExpectations = new List<FileExpectations>() { new FileExpectations(TestConstant.FileExpectation.InfraredMarten), new FileExpectations(TestConstant.FileExpectation.DaylightBobcat), }; // files in subfolder if (excludeSubfolderFiles == false) { DateTimeAdjustment martenPairDateTimeAdjustment; ImageRow martenPairImage = this.CreateFile(fileDatabase, imageSetTimeZone, TestConstant.FileExpectation.DaylightMartenPair, out martenPairDateTimeAdjustment); Assert.IsTrue(martenPairDateTimeAdjustment.HasFlag(DateTimeAdjustment.MetadataDate) && martenPairDateTimeAdjustment.HasFlag(DateTimeAdjustment.MetadataTime)); DateTimeAdjustment coyoteDatetimeAdjustment; ImageRow coyoteImage = this.CreateFile(fileDatabase, imageSetTimeZone, TestConstant.FileExpectation.DaylightCoyote, out coyoteDatetimeAdjustment); Assert.IsTrue(coyoteDatetimeAdjustment.HasFlag(DateTimeAdjustment.MetadataDate) && coyoteDatetimeAdjustment.HasFlag(DateTimeAdjustment.MetadataTime)); fileDatabase.AddFiles(new List<ImageRow>() { martenPairImage, coyoteImage }, null); fileDatabase.SelectFiles(FileSelection.All); ColumnTuplesWithWhere coyoteImageUpdate = new ColumnTuplesWithWhere(); coyoteImageUpdate.Columns.Add(new ColumnTuple(TestConstant.DefaultDatabaseColumn.Note3, "coyote")); coyoteImageUpdate.Columns.Add(new ColumnTuple(TestConstant.DefaultDatabaseColumn.NoteNotVisible, "adult")); coyoteImageUpdate.Columns.Add(new ColumnTuple(TestConstant.DefaultDatabaseColumn.NoteWithCustomDataLabel, String.Empty)); coyoteImageUpdate.Columns.Add(new ColumnTuple(TestConstant.DefaultDatabaseColumn.Note0, "escaped field, because a comma is present")); coyoteImageUpdate.SetWhere(fileEnumerator.Current.ID); fileDatabase.UpdateFiles(new List<ColumnTuplesWithWhere>() { coyoteImageUpdate }); long martenPairImageID = fileDatabase.Files[3].ID; fileDatabase.UpdateFile(martenPairImageID, TestConstant.DefaultDatabaseColumn.Note3, "American marten"); fileDatabase.UpdateFile(martenPairImageID, TestConstant.DefaultDatabaseColumn.NoteNotVisible, "adult"); fileDatabase.UpdateFile(martenPairImageID, TestConstant.DefaultDatabaseColumn.NoteWithCustomDataLabel, String.Empty); fileDatabase.UpdateFile(martenPairImageID, TestConstant.DefaultDatabaseColumn.Note0, "escaped field due to presence of \",\""); fileExpectations.Add(new FileExpectations(TestConstant.FileExpectation.DaylightMartenPair)); fileExpectations.Add(new FileExpectations(TestConstant.FileExpectation.DaylightCoyote)); } // pull the file data table again so the updates are visible to .csv export fileDatabase.SelectFiles(FileSelection.All); // complete setting expectations for (int fileIndex = 0; fileIndex < fileDatabase.Files.RowCount; ++fileIndex) { FileExpectations fileExpectation = fileExpectations[fileIndex]; fileExpectation.ID = fileIndex + 1; } return fileExpectations; }
public void UpdateFiles(ImageRow valueSource, DataEntryControl control, int fromIndex, int toIndex) { if (fromIndex < 0) { throw new ArgumentOutOfRangeException("fromIndex"); } if (toIndex < fromIndex || toIndex > this.CurrentlySelectedFileCount - 1) { throw new ArgumentOutOfRangeException("toIndex"); } string value = valueSource.GetValueDatabaseString(control.DataLabel); List<ColumnTuplesWithWhere> imagesToUpdate = new List<ColumnTuplesWithWhere>(); for (int index = fromIndex; index <= toIndex; index++) { // update data table ImageRow image = this.Files[index]; image.SetValueFromDatabaseString(control.DataLabel, value); // update database List<ColumnTuple> columnToUpdate = new List<ColumnTuple>() { new ColumnTuple(control.DataLabel, value) }; ColumnTuplesWithWhere imageUpdate = new ColumnTuplesWithWhere(columnToUpdate, image.ID); imagesToUpdate.Add(imageUpdate); } this.CreateBackupIfNeeded(); this.Database.Update(Constant.DatabaseTable.FileData, imagesToUpdate); }
/// <summary> /// Update a column's value (identified by its data label) in the row of an existing file (identified by its ID) /// </summary> public void UpdateFile(long fileID, string dataLabel, string value) { // update the data table ImageRow file = this.Files.Find(fileID); file.SetValueFromDatabaseString(dataLabel, value); // update the row in the database this.CreateBackupIfNeeded(); ColumnTuplesWithWhere columnToUpdate = new ColumnTuplesWithWhere(); columnToUpdate.Columns.Add(new ColumnTuple(dataLabel, value)); // Populate the data columnToUpdate.SetWhere(fileID); this.Database.Update(Constant.DatabaseTable.FileData, columnToUpdate); }
public List<string> MoveFilesToFolder(string destinationFolderPath) { Debug.Assert(destinationFolderPath.StartsWith(this.FolderPath, StringComparison.OrdinalIgnoreCase), String.Format("Destination path '{0}' is not under '{1}'.", destinationFolderPath, this.FolderPath)); List<ColumnTuplesWithWhere> filesToUpdate = new List<ColumnTuplesWithWhere>(); List<string> immovableFiles = new List<string>(); foreach (ImageRow file in this.Files) { if (file.TryMoveToFolder(this.FolderPath, destinationFolderPath, false)) { ColumnTuple relativePath = new ColumnTuple(Constant.DatabaseColumn.RelativePath, file.RelativePath); ColumnTuplesWithWhere fileUpdate = new ColumnTuplesWithWhere(new List<ColumnTuple>() { relativePath }, file.ID); filesToUpdate.Add(fileUpdate); } } this.UpdateFiles(filesToUpdate); return immovableFiles; }
// Return a single update query as a string private string CreateUpdateQuery(string tableName, ColumnTuplesWithWhere columnsToUpdate) { if (columnsToUpdate.Columns.Count < 1) { return String.Empty; } // UPDATE tableName SET // colname1 = value1, // colname2 = value2, // ... // colnameN = valueN // WHERE // <condition> e.g., ID=1; string query = "UPDATE " + tableName + " SET "; foreach (ColumnTuple column in columnsToUpdate.Columns) { // column_name = 'value' if (column.Value == null) { query += String.Format(" {0} = {1}{2}", column.Name.ToString(), "NULL", Constant.Sql.Comma); } else { query += String.Format(" {0} = {1}{2}", column.Name, Utilities.QuoteForSql(column.Value), Constant.Sql.Comma); } } query = query.Substring(0, query.Length - Constant.Sql.Comma.Length); // Remove the last comma if (String.IsNullOrWhiteSpace(columnsToUpdate.Where) == false) { query += Constant.Sql.Where + columnsToUpdate.Where; } query += ";"; return query; }
/// <summary> /// Update specific rows in the DB as specified in the where clause. /// </summary> /// <param name="tableName">The table to update.</param> /// <param name="columnsToUpdate">The column names and their new values.</param> public void Update(string tableName, ColumnTuplesWithWhere columnsToUpdate) { // UPDATE table_name SET // colname1 = value1, // colname2 = value2, // ... // colnameN = valueN // WHERE // <condition> e.g., ID=1; string query = this.CreateUpdateQuery(tableName, columnsToUpdate); this.ExecuteNonQuery(query); }