public override void WriteTable(DatasetElement dataset)
{
if (dataset.Data == null)
{
return;
}
bool isDatabseEmpty = !dataset.Data.Elements().Any();
if (isDatabseEmpty)
{
return;
}
StringBuilder sb = new StringBuilder();
foreach (var element in dataset.Data.Elements())
{
var queryFieldNamesArray = element.Attributes().Select(a => a.Name.ToString()).ToArray();
var queryFieldNames = string.Join(", ", queryFieldNamesArray);
var queryFieldValuesArray = element.Attributes().Select(a => a.Value).ToArray();
queryFieldValuesArray = queryFieldValuesArray
.Select(a => string.Format("'{0}'", a))
.ToArray();
var queryFieldValues = string.Join(", ", queryFieldValuesArray);
var insert = string.Format("INSERT INTO {0} ({1}) VALUES ({2});", dataset.Table, queryFieldNames, queryFieldValues);
sb.AppendLine(insert);
}
ExecuteCommand(sb.ToString());
}
public void CompareDatasets_Given_datasets_with_same_unsorted_data_Method_fails()
{
var elementA = XElement.Parse(@"
<dataset name=""B"" table=""tableA"">
<data>
<row id=""10"" name=""AA""/>
<row id=""11"" name=""BB""/>
</data>
</dataset>
");
var elementB = XElement.Parse(@"
<dataset name=""B"" table=""tableA"">
<data>
<row id=""11"" name=""BB""/>
<row id=""10"" name=""AA""/>
</data>
</dataset>
");
var datasetElementA = DatasetElement.Load(elementA);
var datasetElementB = DatasetElement.Load(elementB);
try
{
DbSafeManagerHelper.CompareDatasets(datasetElementA, datasetElementB, new string[] { "id" }, true);
}
catch (AssertFailedException)
{
return;
}
Assert.Fail("An exception was not thrown");
}
public static void CompareDatasets(DatasetElement expected, DatasetElement actual, string[] keys, bool sorted, bool compareTableName = true)
{
if (compareTableName)
{
UnitTesting.Assert.AreEqual(expected.Table, actual.Table, "Table names are different.");
}
if (expected.Data == null)
{
UnitTesting.Assert.IsNull(actual.Data, "Expected Data is null but actual Data is not null");
return;
}
else
{
UnitTesting.Assert.IsNotNull(actual.Data, "Expected Data is not null but actual Data is null");
}
UnitTesting.Assert.AreEqual(expected.Data.Elements().Count(), actual.Data.Elements().Count(), "The number of rows are different.");
if (sorted)
{
CompareSortedElements(expected.Data, actual.Data);
}
else
{
CompareUnsortedElements(expected.Data, actual.Data, keys);
}
}
public override DatasetElement ReadTable(string command, FormatterManager formatter)
{
DatasetElement result = new DatasetElement();
result.Data = new XElement("data");
using (var conn = CreateDbConnection(ConnectionString))
{
conn.Open();
using (var comm = CreateDbCommand(command, conn))
{
var reader = comm.ExecuteReader(CommandBehavior.KeyInfo);
var tableSchema = reader.GetSchemaTable();
result.Table = tableSchema.TableName;
while (reader.Read())
{
XElement xmlRow = new XElement("row");
result.Data.Add(xmlRow);
for (int i = 0; i < reader.FieldCount; i++)
{
var columnName = reader.GetName(i);
var value = formatter.Format(tableSchema.TableName, columnName, reader[i]);
var attribute = new XAttribute(columnName, value);
xmlRow.Add(attribute);
}
}
}
}
return(result);
}
public static DatasetElement BuildDatasetElementFromSpecFlowTable(string tableName, Table table, bool setIdentityInsert)
{
DatasetElement result = new DatasetElement
{
Data = new XElement("data"),
Table = tableName,
Name = tableName,
SetIdentityInsert = setIdentityInsert
};
var headers = table.Header.ToArray();
foreach (var row in table.Rows)
{
XElement xmlRow = new XElement("row");
result.Data.Add(xmlRow);
for (int i = 0; i < headers.Length; i++)
{
if (row[i] == "[NULL]")
{
continue;
}
var columnName = headers[i];
var attribute = new XAttribute(columnName, row[i]);
xmlRow.Add(attribute);
}
}
return(result);
}
public DatasetInstanceValue SetInstanceValue(DatasetElement datasetElement, string instanceValue)
{
datasetElement.Validate(instanceValue);
var datasetInstanceValue = DatasetInstanceValues.SingleOrDefault(div => div.DatasetElement.Id == datasetElement.Id);
if (datasetInstanceValue == null)
{
if (!string.IsNullOrWhiteSpace(instanceValue))
{
datasetInstanceValue = AddInstanceValue(datasetElement, instanceValue);
}
}
else
{
if (string.IsNullOrWhiteSpace(instanceValue))
{
_datasetInstanceValues.Remove(datasetInstanceValue);
datasetInstanceValue = null;
}
else
{
datasetInstanceValue.InstanceValue = instanceValue;
}
}
return(datasetInstanceValue);
}
public void Connection_is_not_reused()
{
// This test uses a global temp table (GTT) since a GTT is deleted when the connection that created it goes out of scope.
var target = new SqlDatabaseClient(false)
{
ConnectionString = _connectionString
};
target.ExecuteCommand(_createGlobalTempTableCommand);
var formatterManager = new FormatterManager();
var actual = target.ReadTable(_verifyIfTempTableExistsQuery, formatterManager);
var expectedDatasetXml = @"
<dataset name=""a-name"" table=""a-name"">
<data>
<row TempTableExists=""0"" />
</data>
</dataset>
";
var xml = XElement.Parse(expectedDatasetXml);
var expectedDataset = DatasetElement.Load(xml);
Assert.AreEqual(expectedDataset.Data.ToString(), actual.Data.ToString());
}
public DatasetElement GetTerminologyMedDra()
{
var meddraElement = _unitOfWork.Repository <DatasetElement>().Queryable().SingleOrDefault(u => u.ElementName == "TerminologyMedDra");
if (meddraElement == null)
{
meddraElement = new DatasetElement()
{
// Prepare new element
DatasetElementType = _unitOfWork.Repository <DatasetElementType>().Queryable().Single(x => x.Description == "Generic"),
Field = new Field()
{
Anonymise = false,
Mandatory = false,
FieldType = _unitOfWork.Repository <FieldType>().Queryable().Single(x => x.Description == "AlphaNumericTextbox")
},
ElementName = "TerminologyMedDra",
DefaultValue = string.Empty,
Oid = string.Empty,
System = true
};
var rule = meddraElement.GetRule(DatasetRuleType.ElementCanoOnlyLinkToSingleDataset);
rule.RuleActive = true;
_unitOfWork.Repository <DatasetElement>().Save(meddraElement);
}
return(meddraElement);
}
public void CompareDatasets_Given_different_number_of_rows_Method_fails()
{
var elementA = XElement.Parse(@"<dataset name=""A"" table=""tableA""><data/></dataset>");
var elementB = XElement.Parse(@"
<dataset name=""B"" table=""tableA"">
<data>
<row/>
</data>
</dataset>
");
var datasetElementA = DatasetElement.Load(elementA);
var datasetElementB = DatasetElement.Load(elementB);
try
{
DbSafeManagerHelper.CompareDatasets(datasetElementA, datasetElementB, new string[] { "key" }, false);
}
catch (AssertFailedException ex)
{
StringAssert.Contains(ex.Message, "The number of rows are different.");
return;
}
Assert.Fail("An exception was not thrown");
}
public DatasetInstanceValue AddInstanceValue(DatasetElement datasetElement, string instanceValue)
{
var datasetInstanceValue = new DatasetInstanceValue(datasetElement, this, instanceValue);
_datasetInstanceValues.Add(datasetInstanceValue);
return(datasetInstanceValue);
}
public bool HasAssociatedData(DatasetElement element)
{
var hasData = false;
hasData = (element.DatasetCategoryElements.Count > 0 || element.DatasetElementSubs.Count > 0 || _instanceValueRepository.Queryable().Any(div => div.DatasetElement.Id == element.Id));
return(hasData);
}
public void AddFeatureClass(MemFeatureClass fc)
{
DatasetElement element = new DatasetElement(fc);
element.Title = fc.Name;
_elements.Add(element);
}
public void CompareDatasets_Given_null_expected_data_and_actual_data_Method_succeed()
{
var elementA = XElement.Parse(@"<dataset name=""A"" table=""tableA""></dataset>");
var elementB = XElement.Parse(@"<dataset name=""B"" table=""tableA""></dataset>");
var datasetElementA = DatasetElement.Load(elementA);
var datasetElementB = DatasetElement.Load(elementB);
DbSafeManagerHelper.CompareDatasets(datasetElementA, datasetElementB, new string[] { "key" }, false);
}
public Guid?GetContextForInstanceSubValue(DatasetElement datasetElement, DatasetElementSub datasetSubElement, string instanceValue)
{
var datasetInstanceValue = DatasetInstanceValues.SingleOrDefault(div => div.DatasetElement.Id == datasetElement.Id);
if (datasetInstanceValue != null && datasetInstanceValue.DatasetInstanceSubValues.Any())
{
return(datasetInstanceValue.DatasetInstanceSubValues.Where(disv => disv.DatasetElementSub.Id == datasetSubElement.Id && disv.InstanceValue.Trim() == instanceValue.Trim()).Select(disv => disv.ContextValue).First());
}
return(null);
}
public IDbSafeManager LoadTables(params string[] datasetNames)
{
ValidateDependencies();
foreach (var datasetName in datasetNames)
{
DatasetElement dataset = FindDataset(datasetName);
DatabaseClient.WriteTable(dataset);
}
return(this);
}
private DatasetElement FindDataset(string datasetName, XElement xml)
{
var dataset = DbSafeManagerHelper.FindChild(xml, FileDefinitionHelper.ElementNameDatasets, datasetName);
if (dataset != null)
{
return(DatasetElement.Load(dataset));
}
return(null);
}
public void CompareDatasets_Given_datasets_with_same_data_Method_succeed()
{
var element = XElement.Parse(@"
<dataset name=""B"" table=""tableA"">
<data>
<row id=""10"" name=""AA""/>
<row id=""11"" name=""BB""/>
</data>
</dataset>
");
var datasetElementA = DatasetElement.Load(element);
var datasetElementB = DatasetElement.Load(element);
DbSafeManagerHelper.CompareDatasets(datasetElementA, datasetElementB, new string[] { "id" }, false);
}
public void AssertDatasetVsScript(string expectedDatasetName, string actualScriptName, bool sorted, string key, params string[] otherKeys)
{
ValidateDependencies();
DatasetElement expectedData = FindDataset(expectedDatasetName);
ScriptElement actualDataScript = FindScript(actualScriptName);
DatasetElement actualData = DatabaseClient.ReadTable(actualDataScript.Value, _formatterManager);
string[] keys = new string[] { key };
if (otherKeys != null)
{
keys = keys.Union(otherKeys).ToArray();
}
DbSafeManagerHelper.CompareDatasets(expectedData, actualData, keys, sorted, false);
}
private string GetValueForElement(DatasetInstance instance, DatasetElement element)
{
if (instance == null)
{
return(string.Empty);
}
;
var value = instance.DatasetInstanceValues.SingleOrDefault(div => div.DatasetElement.Id == element.Id);
if (value == null)
{
return(string.Empty);
}
;
return(value.InstanceValue);
}
public override bool Match(IExplorerObject exObject)
{
if (exObject == null)
{
return(false);
}
if (exObject.Object is IDatasetElement)
{
return(_aData.ProcessAble((IDatasetElement)exObject.Object));
}
if (exObject.Object is IClass)
{
DatasetElement element = new DatasetElement((IClass)exObject.Object);
return(_aData.ProcessAble(element));
}
return(false);
}
public void CompareDatasets_Given_not_null_expected_data_and_null_actual_data_Method_fails()
{
var elementA = XElement.Parse(@"<dataset name=""A"" table=""tableA""><data/></dataset>");
var elementB = XElement.Parse(@"<dataset name=""B"" table=""tableA""></dataset>");
var datasetElementA = DatasetElement.Load(elementA);
var datasetElementB = DatasetElement.Load(elementB);
try
{
DbSafeManagerHelper.CompareDatasets(datasetElementA, datasetElementB, new string[] { "key" }, false);
}
catch (AssertFailedException ex)
{
StringAssert.Contains(ex.Message, "Expected Data is not null but actual Data is null");
return;
}
Assert.Fail("An exception was not thrown");
}
public DatasetInstanceValueList GetElementValuesForPatient(Patient patient, DatasetElement element, int records)
{
var encounters = _unitOfWork.Repository <Encounter>().Queryable().Where(e => e.Patient.Id == patient.Id).OrderByDescending(e => e.EncounterDate).Take(records);
var model = new DatasetInstanceValueList()
{
DatasetElement = element
};
foreach (Encounter encounter in encounters)
{
var val = GetValueForElement(_unitOfWork.Repository <DatasetInstance>().Queryable().SingleOrDefault(di => di.ContextID == encounter.Id), element);
var modelItem = new DatasetInstanceValueListItem()
{
Value = !String.IsNullOrWhiteSpace(val) ? val : "NO VALUE",
ValueDate = encounter.EncounterDate
};
model.Values.Add(modelItem);
}
return(model);
}
async public override Task <bool> Match(IExplorerObject exObject)
{
if (exObject == null)
{
return(false);
}
var instatnce = await exObject.GetInstanceAsync();
if (instatnce is IDatasetElement)
{
return(_aData.ProcessAble((IDatasetElement)instatnce));
}
if (instatnce is IClass)
{
DatasetElement element = new DatasetElement((IClass)instatnce);
return(_aData.ProcessAble(element));
}
return(false);
}
public void CompareDatasets_Given_dataset_with_different_table_names_Method_fails()
{
var elementA = XElement.Parse(@"<dataset name=""A"" table=""tableA""></dataset>");
var elementB = XElement.Parse(@"<dataset name=""B"" table=""tableB""></dataset>");
var datasetElementA = DatasetElement.Load(elementA);
var datasetElementB = DatasetElement.Load(elementB);
var compareTableName = true;
try
{
DbSafeManagerHelper.CompareDatasets(datasetElementA, datasetElementB, new string[] { "key" }, false, compareTableName);
}
catch (AssertFailedException ex)
{
StringAssert.Contains(ex.Message, "Table names are different");
return;
}
Assert.Fail("An exception was not thrown");
}
private string GetCurrentValue(Patient patient, DatasetElement element)
{
if (patient.Encounters.Count == 0)
{
return("NO VALUE");
}
else
{
var encounter = patient.GetCurrentEncounter();
// Get Dataset Instance for encounter
var instance = GetDatasetInstance(encounter.Id);
if (instance != null)
{
var value = instance.GetInstanceValue(element);
return(value);
}
else
{
return("NO VALUE");
}
}
}
public void Write_read_and_compare_records()
{
var target = new SqlDatabaseClient(false)
{
ConnectionString = _connectionString
};
var deleteDataCommand = @"
DELETE [dbo].[Product];
DELETE [dbo].[Category];
DELETE [dbo].[Supplier];
";
target.ExecuteCommand(deleteDataCommand);
var datasetXml = @"
<dataset name=""suppliers"" setIdentityInsert=""true"" table=""Supplier"">
<data>
<row Id=""1"" Name=""supplier-1"" ContactName=""contact-name-1"" ContactPhone=""100-200-0001"" ContactEmail=""*****@*****.**"" />
<row Id=""2"" Name=""supplier-2"" ContactName=""contact-name-2"" ContactPhone=""100-200-0002"" ContactEmail=""*****@*****.**"" />
<row Id=""3"" Name=""supplier-3"" ContactName=""contact-name-3"" ContactPhone=""100-200-0003"" ContactEmail=""*****@*****.**"" />
</data>
</dataset>
";
var xml = XElement.Parse(datasetXml);
var dataset = DatasetElement.Load(xml);
target.WriteTable(dataset);
var formatterManager = new FormatterManager();
var selectRecordsQuery = "SELECT * FROM [dbo].[Supplier];";
var actual = target.ReadTable(selectRecordsQuery, formatterManager);
DbSafeManagerHelper.CompareDatasets(dataset, actual, new string[] { "Id" }, false, false);
}
public void Write_read_and_compare_records()
{
var datasetXml = @"
<dataset name=""suppliers"" setIdentityInsert=""true"" table=""Supplier"">
<data>
<row id=""1"" name=""supplier-1"" contact_name=""contact-name-1"" contact_phone=""100-200-0001"" contact_email=""*****@*****.**"" />
<row id=""2"" name=""supplier-2"" contact_name=""contact-name-2"" contact_phone=""100-200-0002"" contact_email=""*****@*****.**"" />
<row id=""3"" name=""supplier-3"" contact_name=""contact-name-3"" contact_phone=""100-200-0003"" contact_email=""*****@*****.**"" />
</data>
</dataset>
";
var xml = XElement.Parse(datasetXml);
var dataset = DatasetElement.Load(xml);
_target.WriteTable(dataset);
var formatterManager = new FormatterManager();
var selectRecordsQuery = "SELECT * FROM public.supplier;";
var actual = _target.ReadTable(selectRecordsQuery, formatterManager);
DbSafeManagerHelper.CompareDatasets(dataset, actual, new string[] { "id" }, false, false);
}
public async Task <IActionResult> CreateDatasetElement(
[FromBody] DatasetElementForUpdateDto datasetElementForUpdate)
{
if (datasetElementForUpdate == null)
{
ModelState.AddModelError("Message", "Unable to load payload for new request");
}
if (Regex.Matches(datasetElementForUpdate.ElementName, @"[a-zA-Z() ']").Count < datasetElementForUpdate.ElementName.Length)
{
ModelState.AddModelError("Message", "Element contains invalid characters (Enter A-Z, a-z, open and Close brackets)");
}
if (Regex.Matches(datasetElementForUpdate.OID, @"[-a-zA-Z0-9 ']").Count < datasetElementForUpdate.OID.Length)
{
ModelState.AddModelError("Message", "OID contains invalid characters (Enter A-Z, a-z, 0-9, hyphen)");
}
if (Regex.Matches(datasetElementForUpdate.DefaultValue, @"[-a-zA-Z0-9 ']").Count < datasetElementForUpdate.DefaultValue.Length)
{
ModelState.AddModelError("Message", "Default value contains invalid characters (Enter A-Z, a-z, 0-9, hyphen)");
}
if (_unitOfWork.Repository <DatasetElement>().Queryable().
Where(l => l.ElementName == datasetElementForUpdate.ElementName)
.Count() > 0)
{
ModelState.AddModelError("Message", "Item with same name already exists");
}
var fieldType = await _fieldTypeRepository.GetAsync(ft => ft.Description == datasetElementForUpdate.FieldTypeName.ToString());
if (fieldType == null)
{
ModelState.AddModelError("Message", "Unable to locate field type");
}
var elementType = await _datasetElementTypeRepository.GetAsync(ft => ft.Description == "Generic");
if (elementType == null)
{
ModelState.AddModelError("Message", "Unable to locate element type");
}
long id = 0;
if (ModelState.IsValid)
{
var newDatasetElement = new DatasetElement()
{
DatasetElementType = elementType,
ElementName = datasetElementForUpdate.ElementName,
Oid = datasetElementForUpdate.OID,
DefaultValue = datasetElementForUpdate.DefaultValue,
Field = new Field()
{
Anonymise = (datasetElementForUpdate.Anonymise == Models.ValueTypes.YesNoValueType.Yes),
Mandatory = (datasetElementForUpdate.Mandatory == Models.ValueTypes.YesNoValueType.Yes),
FieldType = fieldType,
MaxLength = datasetElementForUpdate.FieldTypeName == FieldTypes.AlphaNumericTextbox ? datasetElementForUpdate.MaxLength : (short?)null,
Decimals = datasetElementForUpdate.FieldTypeName == FieldTypes.NumericTextbox ? datasetElementForUpdate.Decimals : (short?)null,
MinSize = datasetElementForUpdate.FieldTypeName == FieldTypes.NumericTextbox ? datasetElementForUpdate.MinSize : (decimal?)null,
MaxSize = datasetElementForUpdate.FieldTypeName == FieldTypes.NumericTextbox ? datasetElementForUpdate.MaxSize : (decimal?)null
},
System = (datasetElementForUpdate.System == Models.ValueTypes.YesNoValueType.Yes)
};
var rule = newDatasetElement.GetRule(DatasetRuleType.ElementCanoOnlyLinkToSingleDataset);
rule.RuleActive = (datasetElementForUpdate.SingleDatasetRule == Models.ValueTypes.YesNoValueType.Yes);
_datasetElementRepository.Save(newDatasetElement);
id = newDatasetElement.Id;
var mappedDatasetElement = await GetDatasetElementAsync <DatasetElementIdentifierDto>(id);
if (mappedDatasetElement == null)
{
return(StatusCode(500, "Unable to locate newly added item"));
}
return(CreatedAtAction("GetDatasetElementByIdentifier",
new
{
id = mappedDatasetElement.Id
}, CreateLinksForDatasetElement <DatasetElementIdentifierDto>(mappedDatasetElement)));
}
return(BadRequest(ModelState));
}
public abstract void WriteTable(DatasetElement dataset);
private DatasetElement ExecuteLoad(string xmlText)
{
XElement xml = XElement.Parse(xmlText);
return(DatasetElement.Load(xml));
}
