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);
 }