public void ExecuteQuery(string sql)
{
if (database == null || queryExecuting)
{
return;
}
Task.Factory.StartNew(() =>
{
var errors = new StringBuilder();
var messages = new StringBuilder();
var stopwatch = Stopwatch.StartNew();
try
{
queryExecuting = true;
QueryStringIsBusy = QueryIsBusy = true;
ResultSetMessages = "Executing query...";
ResultSetErrors = string.Empty;
Application.Current.Dispatcher.Invoke((Action) delegate
{
if (string.IsNullOrEmpty(sql))
{
sql = Query.Text;
}
ResultSetXml.Text = string.Empty;
ResultsContainer.Clear();
});
int resultCount;
var result = database.ExecuteQuery(sql, errors, messages, out resultCount) as DataSet;
QueryResultCount = resultCount;
if (result == null)
{
return;
}
if (DisplayResultsInGrid)
{
Application.Current.Dispatcher.Invoke((Action) delegate
{
foreach (DataTable table in result.Tables)
{
ResultsContainer.Add(new DataGridViewEx {
DataSource = table
});
}
});
}
if (DisplayResultsAsXml)
{
var sb = new StringBuilder();
using (var writer = new StringWriter(sb))
using (var xml = new XmlTextWriter(writer)
{
Formatting = Formatting.Indented
})
{
result.WriteXml(xml);
writer.WriteLine(string.Empty);
}
Application.Current.Dispatcher.Invoke((Action) delegate
{
ResultSetXml.Text = sb.ToString();
RaisePropertyChanged("ResultSetXml");
});
}
CurrentResultsTabIndex = result.Tables.Count > 0 ? 0 : 2;
}
catch (Exception e)
{
ResultSetErrors = e.ToString();
CurrentResultsTabIndex = 3;
}
finally
{
QueryExecutionTime = stopwatch.Elapsed;
ResultSetMessages = messages.ToString();
ResultSetErrors = errors.ToString();
QueryStringIsBusy = QueryIsBusy = false;
queryExecuting = false;
if (!string.IsNullOrEmpty(ResultSetErrors) && ResultsContainer.Count == 0)
{
CurrentResultsTabIndex = 3;
}
sql = sql.ToLower();
if (sql.Contains("create") || sql.Contains("alter") || sql.Contains("drop") ||
sql.Contains("insert") || sql.Contains("update") || sql.Contains("delete"))
{
AnalyzeDatabase();
}
}
});
}
private Metadata ReadMetadata(MetadataBrokerType broker, string filename, string windowsLivePhotoGalleryDirectory, string directory, int serialNumber)
{
Metadata metadata = null;
var errors = new StringBuilder();
var messages = new StringBuilder();
string sql = "SELECT tblVolume.Label, tblPath.Path, tblObject.Filename, tblObject.FileSize, " +
"tblLocation.LocationName, tblLocation.LocationLat, tblLocation.LocationLong, tblObject.Latitude, tblObject.Longitude, " +
"tblObject.DateModified, tblObject.Title, tblObject.\"Desc\" as Desciption, tblObject.DateTaken, tblObject.Rating, tblObject.Author, tblObject.LabelCount, " +
"tblObject.ResolutionX, tblObject.ResolutionY, tblObject.CameraMake, tblObject.CameraModel, " +
"tblObject.CameraShutterSpeed, tblObject.CameraFocalLength, tblObject.CameraAperture, tblObject.CameraISO, " +
"tblObject.VideoBitrate, tblObject.VideoFramerate, tblObject.MediaDuration " +
"FROM tblObject " +
"INNER JOIN tblPath ON tblObject.FilePathId = tblPath.PathId " +
"INNER JOIN tblVolume ON tblPath.VolumeId = tblVolume.VolumeId " +
"LEFT OUTER JOIN tblocationUsage ON tblocationUsage.ObjectID = tblObject.ObjectID " +
"LEFT OUTER JOIN tblLocation ON tblocationUsage.LocationId = tblLocation.LocationID " +
"WHERE " +
"tblObject.Filename = '" + filename + "' " +
"AND tblPath.Path = '" + windowsLivePhotoGalleryDirectory + "' " +
"AND tblVolume.SerialNo = " + serialNumber.ToString(CultureInfo.InvariantCulture);
int resultCount;
var result = database.ExecuteQuery(sql, errors, messages, out resultCount) as DataSet;
if (result == null)
{
return(metadata); //No point to contine, when header of information missing.
}
foreach (DataTable table in result.Tables)
{
if (table.Rows.Count > 0)
{
metadata = new Metadata(broker);
//File
metadata.FileName = table.Rows[0].Field <String>("Filename");
//metadata.FileDirectory = table.Rows[0].Field<String>("Path");
metadata.FileDirectory = directory; //Override path from database, it's not a complete folder path, missing root path
metadata.FileSize = table.Rows[0].Field <Int64>("FileSize");
DateTime newDateTime = new DateTime(new DateTime(1600, 12, 31, 23, 59, 59, 0, DateTimeKind.Utc).AddSeconds(1).Ticks + table.Rows[0].Field <long>("DateModified"));
metadata.FileDateModified = newDateTime.ToLocalTime();
string fullFilePath = Path.Combine(metadata.FileDirectory, metadata.FileName);
if (File.Exists(Path.Combine(metadata.FileDirectory, metadata.FileName)))
{
metadata.FileDateModified = File.GetLastWriteTime(fullFilePath);
metadata.FileDateCreated = File.GetCreationTime(fullFilePath);
metadata.FileDateAccessed = File.GetLastAccessTime(fullFilePath);
}
//Personal
metadata.PersonalTitle = table.Rows[0].Field <String>("Title");
metadata.PersonalDescription = table.Rows[0].Field <String>("Desciption");
metadata.PersonalRating = table.Rows[0].Field <Byte?>("Rating");
metadata.PersonalAuthor = table.Rows[0].Field <String>("Author");
//Media
metadata.MediaWidth = table.Rows[0].Field <Int32?>("ResolutionX");
metadata.MediaHeight = table.Rows[0].Field <Int32?>("ResolutionY");
metadata.MediaDateTaken = table.Rows[0].Field <DateTime?>("DateTaken");
//Camera
metadata.CameraMake = table.Rows[0].Field <String>("CameraMake");
metadata.CameraModel = table.Rows[0].Field <String>("CameraModel");
//Location
metadata.LocationName = table.Rows[0].Field <String>("LocationName");
metadata.LocationLatitude = (float?)table.Rows[0].Field <double?>("LocationLat"); //Fixed address location, tagged location in map
metadata.LocationLongitude = (float?)table.Rows[0].Field <double?>("LocationLong"); //Fixed address location, tagged location in map
//If -999, GPS location missiong, use tagged location
if (table.Rows[0].Field <double?>("Latitude") != -999)
{
metadata.LocationLatitude = (float?)table.Rows[0].Field <double?>("Latitude"); //Real GPS location
}
if (table.Rows[0].Field <double?>("Longitude") != -999)
{
metadata.LocationLongitude = (float?)table.Rows[0].Field <double?>("Longitude"); //Real GPS location
}
}
}
if (metadata == null)
{
return(metadata);
}
#region Region Name and Size
sql = "SELECT tblVolume.Label, tblPath.Path, tblObject.Filename, tblPerson.Name, tblRegion.\"Top\", tblRegion.\"Left\"," +
"tblRegion.Width, tblRegion.Height " +
"FROM tblObject " +
"INNER JOIN tblPath ON tblObject.FilePathId = tblPath.PathId " +
"INNER JOIN tblVolume ON tblPath.VolumeId = tblVolume.VolumeId " +
"INNER JOIN tblRegion ON tblRegion.ObjectID = tblObject.ObjectId " +
"INNER JOIN tblPerson ON tblRegion.PersonID = tblPerson.PersonId " +
"WHERE " +
"tblObject.Filename = '" + filename + "' " +
"AND tblPath.Path = '" + windowsLivePhotoGalleryDirectory + "' " +
"AND tblVolume.SerialNo = " + serialNumber.ToString();
result = database.ExecuteQuery(sql, errors, messages, out resultCount) as DataSet;
if (result != null)
{
foreach (DataTable table in result.Tables)
{
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
RegionStructure region = new RegionStructure();
region.Name = (String)row["Name"];
region.AreaX = (float)Math.Round((double)row["Left"], 5);
region.AreaY = (float)Math.Round((double)row["Top"], 5);
region.AreaWidth = (float)Math.Round((double)row["Width"], 5);
region.AreaHeight = (float)Math.Round((double)row["Height"], 5);
region.Type = "Face";
region.RegionStructureType = RegionStructureTypes.WindowsLivePhotoGalleryDatabase;
metadata.PersonalRegionListAddIfNotExists(region);
}
}
}
}
#endregion
#region Keywrods tags
sql = "SELECT tblVolume.Label, tblPath.Path, tblObject.Filename, LabelName " +
"FROM tblObject " +
"INNER JOIN tblPath ON tblObject.FilePathId = tblPath.PathId " +
"INNER JOIN tblVolume ON tblPath.VolumeId = tblVolume.VolumeId " +
"INNER JOIN tblLabelUsage ON tblLabelUsage.ObjectID = tblObject.ObjectId " +
"INNER JOIN tblLabel ON tblLabel.LabelID = tblLabelUsage.LabelId " +
"WHERE " +
"tblObject.Filename = '" + filename + "' " +
"AND tblPath.Path = '" + windowsLivePhotoGalleryDirectory + "' " +
"AND tblVolume.SerialNo = " + serialNumber.ToString();
result = database.ExecuteQuery(sql, errors, messages, out resultCount) as DataSet;
if (result != null)
{
foreach (DataTable table in result.Tables)
{
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
metadata.PersonalKeywordTagsAddIfNotExists(new KeywordTag((String)row["LabelName"]));
}
}
}
}
#endregion
return(metadata);
}