protected override bool Execute()
{//*
ActiveObject = GetName();
if (column == null)
{
column = AddColumn(table);
}
SetAttributeValue(column, "Name", ColumnName);
if (ColumnName.IndexOfAny(new[] { '#', '*', '.' }) > -1)
{
SetAttributeValue(column, "Member", ColumnName.Replace("#", "_").Replace(".", "_").Replace("*", "_"));
}
else
{
SetAttributeValue(column, "Member", null);
}
if (!string.IsNullOrEmpty(DataType))
{
SetAttributeValue(column, "Type", DataType);
if (DataType == "timestamp")
{
SetAttributeValue(column, "IsVersion", "true");
}
else
{
SetAttributeValue(column, "IsVersion", null);
}
if (DbType.Equals("varbinary(max)", StringComparison.OrdinalIgnoreCase))
{
SetAttributeValue(column, "IsDelayLoaded", "true");
SetAttributeValue(column, "UpdateCheck", "Never");
}
if (DbType.Equals("xml", StringComparison.OrdinalIgnoreCase))
{
SetAttributeValue(column, "UpdateCheck", "Never");
}
}
if (!string.IsNullOrEmpty(DbType))
{
SetAttributeValue(column, "DbType", DbType);
}
SetAttributeValue(column, "CanBeNull", (!Mandatory).ToString().ToLower());
if (ComputeInDataBase)
{
SetAttributeValue(column, "IsDbGenerated", "true");
}
else
{
SetAttributeValue(column, "IsDbGenerated", null);
}
// SetAttributeValue(column, "IsPrimaryKey", IsPrimaryKey.ToString());*/
return(true);
}
/// <summary>
/// Based on a specified PredictedObserved item, searches for the corresponding sqlite db file and extracts the
/// datatable information for this PredictedObserved item.
/// </summary>
/// <param name="predictedObservedName">Name of the PredictedObserved table.</param>
/// <param name="databasePath">Path to the .db file.</param>
private static DataTable GetPredictedObservedDataTable(string predictedObservedName, string databasePath)
{
DataTable POdata = new DataTable(predictedObservedName);
try
{
if (File.Exists(databasePath))
{
string dbFileName = Path.GetFileName(databasePath);
using (SQLiteConnection con = new SQLiteConnection("Data Source=" + databasePath))
{
con.Open();
string selectSQL = "SELECT * FROM " + predictedObservedName;
SQLiteCommand cmd = new SQLiteCommand(selectSQL, con);
try
{
SQLiteDataReader rdr = cmd.ExecuteReader();
if (rdr != null)
{
POdata.Load(rdr);
WriteToLogFile(string.Format(" There are {0} Predicted Observed records in database {1}, table {2}.", POdata.Rows.Count, dbFileName, predictedObservedName));
}
}
catch (Exception ex)
{
if (ex.Message.ToString().IndexOf("no such table") > 0)
{
WriteToLogFile(string.Format(" For Database {0}, Table {1} does not exist: {2}", dbFileName, predictedObservedName, ex.ToString()));
}
else
{
WriteToLogFile(string.Format(" ERROR reading database {0}: {1}!", dbFileName, ex.ToString()));
}
}
}
}
else
{
throw new Exception(string.Format("ERROR Database file {0} does not exist", databasePath));
}
string ColumnName, ObservedName, PredictedName;
bool removeCols = false, colRemoved = false;
int cIndex;
//modLMC - 31-Jan-2018 - (as instructed by Dean) if the Predicted column is defined as String, then remove both Predicted & Observed
for (int i = POdata.Columns.Count - 1; i >= 0; i--)
{
ColumnName = POdata.Columns[i].ColumnName.Trim();
//modLMC - 20/02/2018 - as per phone conversation with Dean, remove any columns with CheckpointID
if (ColumnName.IndexOf("CheckpointID") >= 0)
{
//Remove any columns called CheckpointID
POdata.Columns.RemoveAt(i);
//WriteToLogFile(String.Format(" NOTE: {0}.{1} was dropped.");
//i--;
}
else if (ColumnName.StartsWith("Predicted"))
{
//if datatype is not numeric need to remove it, and its corresponding observed column
colRemoved = false;
//check if the "Observed" Column exists, if it doesn't, then delete the Predicted
ObservedName = ColumnName.Replace("Predicted", "Observed");
try
{
cIndex = POdata.Columns[ObservedName].Ordinal;
}
catch (System.NullReferenceException)
{
POdata.Columns.RemoveAt(i);
WriteToLogFile(String.Format(" NOTE: {0}.{1} was dropped the Observed column {2} does not exist.", POdata.TableName, ColumnName, ObservedName));
colRemoved = true;
//i--;
}
if (colRemoved == false)
{
removeCols = false;
if (POdata.Columns[i].DataType == typeof(DateTime))
{
removeCols = true;
}
if (POdata.Columns[i].DataType == typeof(System.String))
{
removeCols = true;
}
if (removeCols == true)
{
WriteToLogFile(String.Format(" NOTE: {0}.{1} dropped as not in correct Format, was of Type {2} is not the correct; it should be a numeric column", POdata.TableName, ColumnName, POdata.Columns[i].DataType));
POdata.Columns.RemoveAt(i);
ObservedName = ColumnName.Replace("Predicted", "Observed");
try
{
cIndex = POdata.Columns[ObservedName].Ordinal;
if (cIndex > 0)
{
POdata.Columns.RemoveAt(cIndex);
WriteToLogFile(String.Format(" NOTE: {0}.{1} was also dropped as {2} was not defined as a numeric column", POdata.TableName, ObservedName, ColumnName));
if (i >= POdata.Columns.Count)
{
//make sure we don't go out of bounds with the columns
i = POdata.Columns.Count;
}
}
}
catch (Exception)
{
}
}
}
}
else if (ColumnName.StartsWith("Observed"))
{
//check if the "Predicted" Column exists, if it doesn't, then delete the Predicted
PredictedName = ColumnName.Replace("Observed", "Predicted");
try
{
cIndex = POdata.Columns[PredictedName].Ordinal;
}
catch (System.NullReferenceException)
{
POdata.Columns.RemoveAt(i);
WriteToLogFile(String.Format(" NOTE: {0}.{1} was dropped the Predicted column {2} does not exist.", POdata.TableName, ColumnName, PredictedName));
//i--;
}
}
}
POdata.AcceptChanges();
//need to ensure that we can convert test/string/char columns to real for all Predicted and Observed Columns
//need to work backwards, just in case we need to delete any columns
for (int i = POdata.Columns.Count - 1; i >= 0; i--)
{
ColumnName = POdata.Columns[i].ColumnName.Trim();
if (ColumnName.StartsWith("Observed") || ColumnName.StartsWith("Predicted"))
{
if (POdata.Columns[i].DataType == typeof(DateTime))
{
POdata.Columns.RemoveAt(i);
}
else if ((POdata.Columns[i].DataType != typeof(System.Double)) && (POdata.Columns[i].DataType != typeof(System.Int64)))
{
try
{
//Update the log file to report incorrect data types
WriteToLogFile(String.Format(" NOTE: {0}.{1} Format Type {2} is not the correct; it should be a numeric column", POdata.TableName, ColumnName, POdata.Columns[i].DataType));
////rename the original
string origCol = "orig" + ColumnName;
POdata.Columns[i].ColumnName = origCol;
//create a new column, with the correct type
POdata.Columns.Add(ColumnName, typeof(System.Double));
for (int ri = 0; ri < POdata.Rows.Count; ri++)
{
double value;
if (double.TryParse(POdata.Rows[ri][i].ToString(), out value))
{
POdata.Rows[ri][ColumnName] = value;
}
else
{
POdata.Rows[ri][ColumnName] = DBNull.Value;
}
}
//now remove the original column
POdata.Columns.RemoveAt(i);
}
catch (Exception ex)
{
WriteToLogFile(String.Format(" ERROR: Unable to convert {0}.{1} to double: {2}", POdata.TableName, ColumnName, ex.ToString()));
}
}
}
}
return(POdata);
}
catch (Exception ex)
{
WriteToLogFile(" ERROR: Unable to access Data: " + ex.ToString());
return(POdata);
}
}
public string FlattenedColumnName()
{
return(ColumnName.Replace(".", "_"));
}