/// <summary>
/// This function retrieves a list of property strings for a data base record id.
/// </summary>
public string[] GetDataBaseRecProperties(string database, string rec_id)
{
DASDataTable dt = null;
string[] ret;
try
{
_databaseObject.AddParameter(P_DATABASE, database, DAS.ParameterModes.PARM_IN,
DAS.ServerTypes.STRING);
_databaseObject.AddParameter(P_REC_ID, rec_id, DAS.ParameterModes.PARM_IN,
DAS.ServerTypes.STRING);
dt = _databaseObject.CreateAndFillDataTable(
$"SELECT {QRY_DATABASE_REC_PROPERTIES_COLUMNS} FROM {QRY_DATABASE_REC_PROPERTIES_BY_ID} WHERE {P_DATABASE} = @{P_DATABASE} AND {P_REC_ID} = @{P_REC_ID}");
ret = new string[dt.Rows.Count()];
int i = 0;
foreach (DASDataRow dr in dt.Rows)
{
DASColumnValue property = new DASColumnValue(dr, dr.Table.Columns.ItemByName("PROPERTY"));
DASColumnValue propertyValue = new DASColumnValue(dr, dr.Table.Columns.ItemByName("PROPERTY_VALUE"));
ret[i++] += $"{property.GetValueAsString()}: {propertyValue.GetValueAsString()}";
}
}
finally
{
_databaseObject.RemoveParameter(P_DATABASE);
_databaseObject.RemoveParameter(P_REC_ID);
}
return(ret);
}
public static DASDataTable ExecuteQueryForDataTable(this DAS connection, string query)
{
var table = new DASDataTable(connection);
connection.FillDataTable(table, query);
return(table);
}
public virtual DataTable ExecuteStatementForDataTable(string selectStatement)
{
var dataTable = new DASDataTable(databaseConnection);
databaseConnection.FillDataTable(dataTable, selectStatement);
return(dataTable);
}
/// <summary>
/// This function retrieves the default container tissue mapping.
/// </summary>
public DataTable GetContainerTissueMapping()
{
if (_mappingContainerTissue == null)
{
_mappingContainerTissue = _databaseObject.CreateAndFillDataTable($"SELECT {QRY_CONTAINER_TISSUE_COLUMNS} FROM {QRY_CONTAINER_TISSUE}");
}
return(_mappingContainerTissue.Copy());
}
public void ClearCache()
{
_mappingContainerTissue = null;
_genderHints = null;
_tissueHints = null;
_healthStateHints = null;
_sampleSourceHints = null;
_unitHints = null;
_nameTypeHints = null;
}
/// <summary>
/// This function retrieves information for a tissue.
/// </summary>
public string GetTissueHint(string tissue)
{
if (_tissueHints == null)
{
_tissueHints = _databaseObject.CreateAndFillDataTable($"SELECT {QRY_TISSUE_HINT_COLUMNS} FROM {QRY_TISSUE_HINT}");
}
DataRow[] dr = (_tissueHints.Select($"TISSUE = '{tissue}'"));
if (dr.Length == 0)
{
return(string.Empty);
}
return((string)dr[0]["INFORMATION"]);
}
/// <summary>
/// This function retrieves information for a gender.
/// </summary>
public string GetGenderHint(string gender)
{
if (_genderHints == null)
{
_genderHints = _databaseObject.CreateAndFillDataTable($"SELECT {QRY_GENDER_HINT_COLUMNS} FROM {QRY_GENDER_HINT}");
}
DataRow[] dr = _genderHints.Select($"GENDER = '{gender}'");
if (dr.Length == 0)
{
return(string.Empty);
}
return((string)dr[0]["INFORMATION"]);
}
/// <summary>
/// This function retrieves information for a health state.
/// </summary>
public string GetHealthStateHint(string healthState)
{
if (_healthStateHints == null)
{
_healthStateHints =
_databaseObject.CreateAndFillDataTable($"SELECT {QRY_HEALTH_STATE_HINT_COLUMNS} FROM {QRY_HEALTH_STATE_HINT}");
}
DataRow[] dr = (_healthStateHints.Select($"HEALTH_STATE = '{healthState}'"));
if (dr.Length == 0)
{
return(string.Empty);
}
return((string)dr[0]["INFORMATION"]);
}
/// <summary>
/// This function retrieves information for a name type.
/// </summary>
public string GetNameTypeHint(string nameType)
{
if (_nameTypeHints == null)
{
_nameTypeHints =
_databaseObject.CreateAndFillDataTable($"SELECT {QRY_NAME_TYPE_HINT_COLUMNS} FROM {QRY_NAME_TYPE_HINT}");
}
DataRow[] dr = (_nameTypeHints.Select($"NAME_TYPE = '{nameType}'"));
if (dr.Length == 0)
{
return(string.Empty);
}
return((string)dr[0]["INFORMATION"]);
}
/// <summary>
/// This function retrieves information for a unit.
/// </summary>
public string GetUnitHint(string unit)
{
if (_unitHints == null)
{
_unitHints =
_databaseObject.CreateAndFillDataTable($"SELECT {QRY_UNIT_HINT_COLUMNS} FROM {QRY_UNIT_HINT}");
}
DataRow[] dr = (_unitHints.Select($"UNIT = '{unit}'"));
if (dr.Length == 0)
{
return(string.Empty);
}
return((string)dr[0]["INFORMATION"]);
}
/// <summary>
/// This function retrieves information for a sample source.
/// </summary>
public string GetSampleSourceHint(string sampleSource)
{
if (_sampleSourceHints == null)
{
_sampleSourceHints =
_databaseObject.CreateAndFillDataTable($"SELECT {QRY_SAMPLE_SOURCE_HINT_COLUMNS} FROM {QRY_SAMPLE_SOURCE_HINT}");
}
DataRow[] dr = (_sampleSourceHints.Select($"SAMPLE_SOURCE = '{sampleSource}'"));
if (dr.Length == 0)
{
return(string.Empty);
}
return((string)dr[0]["INFORMATION"]);
}
private DataTable executeStatementForDataTable(string selectStatement)
{
var dataTable = new DASDataTable(_databaseObject);
try
{
_databaseObject.FillDataTable(dataTable, selectStatement);
return(dataTable);
}
catch (Exception e)
{
var t = dataTable.GetErrors();
Console.WriteLine(e);
throw;
}
}
/// <summary>
/// This function retrieves expression data for a special protein.
/// </summary>
public DataTable GetExpressionDataByGeneId(int id)
{
DASDataTable retTable = null;
try
{
_databaseObject.AddParameter(P_ID, id, DAS.ParameterModes.PARM_IN,
DAS.ServerTypes.ST_NUMBER);
retTable = _databaseObject.CreateAndFillDataTable(
$"SELECT {QRY_EXPRESSION_DATA_COLUMNS} FROM {QRY_EXPRESSION_DATA_BY_ID} WHERE {P_ID} = @{P_ID}");
}
finally
{
_databaseObject.RemoveParameter(P_ID);
}
return(retTable);
}
/// <summary>
/// This function retrieves a list of found proteins fulfilling the search criteria.
/// </summary>
public DataTable GetProteinsByName(string name)
{
DASDataTable retTable = null;
try
{
_databaseObject.AddParameter(P_NAME, name, DAS.ParameterModes.PARM_IN,
DAS.ServerTypes.ST_VARCHAR2);
retTable = _databaseObject.CreateAndFillDataTable(
$"SELECT {QRY_FIND_PROTEINS_COLUMNS} FROM {QRY_PROTEINS_BY_NAME} WHERE {P_NAME} = @{P_NAME}");
}
finally
{
_databaseObject.RemoveParameter(P_NAME);
}
return(retTable);
}
/// <summary>
/// This function retrieves a list of information strings for a data base record id.
/// </summary>
public string[] GetDataBaseRecInfos(string database, string rec_id)
{
DASDataTable dt = null;
string[] ret;
try
{
_databaseObject.AddParameter(P_DATABASE, database, DAS.ParameterModes.PARM_IN,
DAS.ServerTypes.STRING);
_databaseObject.AddParameter(P_REC_ID, rec_id, DAS.ParameterModes.PARM_IN,
DAS.ServerTypes.STRING);
dt = _databaseObject.CreateAndFillDataTable(
$"SELECT {QRY_DATABASE_REC_INFO_COLUMNS} FROM {QRY_DATABASE_REC_INFO_BY_ID} WHERE {P_DATABASE} = @{P_DATABASE} AND {P_REC_ID} = @{P_REC_ID}");
ret = new string[dt.Columns.Count() - 2];
foreach (DASDataRow dr in dt.Rows)
{
for (int i = 2; i < dt.Columns.Count(); i++)
{
var colval = new DASColumnValue(dr, dt.Columns.ItemByIndex(i));
if (colval.DBNullValue)
{
continue;
}
if (colval.Value is DateTime)
{
ret[i - 2] += $"{dt.Columns.ItemByIndex(i).ColumnName}: {colval.GetValueAsString("yyyy-MM-dd")}";
}
else
{
ret[i - 2] += $"{dt.Columns.ItemByIndex(i).ColumnName}: {colval.GetValueAsString()}";
}
}
}
}
finally
{
_databaseObject.RemoveParameter(P_DATABASE);
_databaseObject.RemoveParameter(P_REC_ID);
}
return(ret);
}
public Task <T> LoadTemplateAsync <T>(Template template)
{
if (template.DatabaseType == TemplateDatabaseType.Remote)
{
return(_remoteTemplateRepository.LoadTemplateAsync <T>(template.DowncastTo <RemoteTemplate>()));
}
using (establishConnection(template.DatabaseType))
{
var connection = databaseConnection();
try
{
addTemplateNameParameter(template.Name, connection);
var sqlQuery = $"SELECT t.{TemplateTable.Columns.XML} FROM {TemplateTable.NAME} t WHERE t.{TemplateTable.Columns.TEMPLATE_TYPE} IN ({typeFrom(template.Type)}) AND t.{TemplateTable.Columns.NAME} = {_pName}";
var table = new DASDataTable(connection);
connection.FillDataTable(table, sqlQuery);
if (table.Rows.Count() > 0)
{
var serializationString = table.Rows.ItemByIndex(0)[TemplateTable.Columns.XML].ToString();
var objectFromTemplate = _stringSerializer.Deserialize <T>(serializationString);
_objectIdResetter.ResetIdFor(objectFromTemplate);
//Rename the template according to the template name as the template might have been renamed
var withName = objectFromTemplate as IWithName;
if (withName != null)
{
withName.Name = template.Name;
}
return(Task.FromResult(objectFromTemplate));
}
}
finally
{
removeNameParameter(connection);
}
return(default);
private void addReferencesToTemplate(Template template, DAS connection, List <string> loadedReferenceNames = null)
{
DASDataTable dataTable = null;
loadedReferenceNames = loadedReferenceNames ?? new List <string>();
try
{
addTemplateNameParameter(template.Name, connection);
var sqlQuery = string.Format("SELECT ref.{2}, ref.{3} FROM {4} ref WHERE ref.{0} = '{5}' AND ref.{1}={6}",
TemplateReferenceTable.Columns.TEMPLATE_TYPE, TemplateReferenceTable.Columns.NAME,
TemplateReferenceTable.Columns.REFERENCE_TEMPLATE_TYPE, TemplateReferenceTable.Columns.REFERENCE_NAME,
TemplateReferenceTable.NAME, template.TemplateType, _pName);
dataTable = connection.ExecuteQueryForDataTable(sqlQuery);
}
finally
{
removeNameParameter(connection);
}
foreach (DASDataRow row in dataTable)
{
var reference = loadTemplateBy(template.DatabaseType, row.StringAt(TemplateReferenceTable.Columns.REFERENCE_NAME), row.StringAt(TemplateReferenceTable.Columns.REFERENCE_TEMPLATE_TYPE), connection);
template.References.Add(reference);
if (loadedReferenceNames.Contains(reference.Name))
{
continue;
}
loadedReferenceNames.Add(reference.Name);
addReferencesToTemplate(reference, connection, loadedReferenceNames);
}
}
