/// <summary>
/// Returns the remote DataTable row held in <paramref name="externalSource"/> that describes all cohorts held in it (that have at least one identifier mapping).
/// </summary>
/// <param name="externalSource"></param>
/// <param name="displayMemberName"></param>
/// <param name="valueMemberName"></param>
/// <param name="versionMemberName"></param>
/// <param name="projectNumberMemberName"></param>
/// <returns></returns>
public static DataTable GetImportableCohortDefinitionsTable(ExternalCohortTable externalSource, out string displayMemberName, out string valueMemberName, out string versionMemberName, out string projectNumberMemberName)
{
var server = externalSource.Discover().Server;
var syntax = server.GetQuerySyntaxHelper();
using (var con = server.GetConnection())
{
con.Open();
string sql =
$@"Select
{syntax.EnsureWrapped("description")},
{syntax.EnsureWrapped("id")},
{syntax.EnsureWrapped("version")},
{syntax.EnsureWrapped("projectNumber")}
from {externalSource.DefinitionTableName}
where
exists (Select 1 from {externalSource.TableName} WHERE {externalSource.DefinitionTableForeignKeyField}=id)";
using (var da = server.GetDataAdapter(sql, con))
{
displayMemberName = "description";
valueMemberName = "id";
versionMemberName = "version";
projectNumberMemberName = "projectNumber";
DataTable toReturn = new DataTable();
da.Fill(toReturn);
return(toReturn);
}
}
}
/// <summary>
/// Fetches and returns project number, version etc listed in the remote cohort database for this cohort
/// </summary>
public IExternalCohortDefinitionData GetExternalData()
{
var db = ExternalCohortTable.Discover();
var syntax = db.Server.GetQuerySyntaxHelper();
string sql =
$@"Select
{syntax.EnsureWrapped("projectNumber")},
{syntax.EnsureWrapped("description")},
{syntax.EnsureWrapped("version")},
{syntax.EnsureWrapped("dtCreated")}
from {ExternalCohortTable.DefinitionTableName}
where
{syntax.EnsureWrapped("id")} = {OriginID}";
using (var con = db.Server.GetConnection())
{
con.Open();
using (var getDescription = db.Server.GetCommand(sql, con))
{
using (var r = getDescription.ExecuteReader())
{
if (!r.Read())
{
throw new Exception("No records returned for Cohort OriginID " + OriginID);
}
return(new ExternalCohortDefinitionData(r, ExternalCohortTable.Name));
}
}
}
}
/// <summary>
/// Returns the remote DataTable row held in <paramref name="externalSource"/> that describes all cohorts held in it (that have at least one identifier mapping).
/// </summary>
/// <param name="externalSource"></param>
/// <param name="displayMemberName"></param>
/// <param name="valueMemberName"></param>
/// <param name="versionMemberName"></param>
/// <param name="projectNumberMemberName"></param>
/// <returns></returns>
public static DataTable GetImportableCohortDefinitionsTable(ExternalCohortTable externalSource, out string displayMemberName, out string valueMemberName, out string versionMemberName, out string projectNumberMemberName)
{
var server = externalSource.Discover().Server;
using (var con = server.GetConnection())
{
con.Open();
string sql = string.Format(
"Select description,id,version,projectNumber from {0} where exists (Select 1 from {1} WHERE {2}=id)"
, externalSource.DefinitionTableName,
externalSource.TableName,
externalSource.DefinitionTableForeignKeyField);
var da = server.GetDataAdapter(sql, con);
displayMemberName = "description";
valueMemberName = "id";
versionMemberName = "version";
projectNumberMemberName = "projectNumber";
DataTable toReturn = new DataTable();
da.Fill(toReturn);
return(toReturn);
}
}
/// <inheritdoc/>
public string GetReleaseIdentifierDataType()
{
DiscoveredTable table = ExternalCohortTable.Discover().ExpectTable(ExternalCohortTable.TableName);
//discover the column
return(table.DiscoverColumn(GetReleaseIdentifier(true))
.DataType.SQLType); //and return it's datatype
}
/// <inheritdoc/>
public IExternalCohortDefinitionData GetExternalData(int timeoutInSeconds = -1)
{
var db = ExternalCohortTable.Discover();
var syntax = db.Server.GetQuerySyntaxHelper();
string sql =
$@"Select
{syntax.EnsureWrapped("projectNumber")},
{syntax.EnsureWrapped("description")},
{syntax.EnsureWrapped("version")},
{syntax.EnsureWrapped("dtCreated")}
from {ExternalCohortTable.DefinitionTableName}
where
{syntax.EnsureWrapped("id")} = {OriginID}";
if (timeoutInSeconds != -1)
{
db.Server.TestConnection(timeoutInSeconds * 1000);
}
using (var con = db.Server.GetConnection())
{
con.Open();
using (var getDescription = db.Server.GetCommand(sql, con))
{
if (timeoutInSeconds != -1)
{
getDescription.CommandTimeout = timeoutInSeconds;
}
using (var r = getDescription.ExecuteReader())
{
if (!r.Read())
{
return(ExternalCohortDefinitionData.Orphan);
}
return(new ExternalCohortDefinitionData(r, ExternalCohortTable.Name));
}
}
}
}
/// <summary>
/// Fetches and returns project number, version etc listed in the remote cohort database for this cohort
/// </summary>
public IExternalCohortDefinitionData GetExternalData()
{
string sql = @"select projectNumber, description,version,dtCreated from " + ExternalCohortTable.DefinitionTableName + " where id = " + OriginID;
var db = ExternalCohortTable.Discover();
using (var con = db.Server.GetConnection())
{
con.Open();
var getDescription = db.Server.GetCommand(sql, con);
var r = getDescription.ExecuteReader();
if (!r.Read())
{
throw new Exception("No records returned for Cohort OriginID " + OriginID);
}
return(new ExternalCohortDefinitionData(r, ExternalCohortTable.Name));
}
}
/// <inheritdoc/>
public DiscoveredDatabase GetDatabaseServer()
{
return(ExternalCohortTable.Discover());
}
