/// <summary>
/// Select
/// </summary>
/// <param name="projId"></param>
/// <returns></returns>
public static List <AfsTarget> Select(int projId)
{
List <AfsTarget> l = new List <AfsTarget>();
string sql = @"
select *
from <mbs_owner>.afs_target
where afs_current = 1 and proj_id = " + projId +
" order by upper(target_name)";
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsTarget t = new AfsTarget();
t.ProjId = dao.GetIntByName("PROJ_ID");
t.TargetName = dao.GetStringByName("TARGET_NAME");
t.TargetType = dao.GetStringByName("TARGET_TYPE");
l.Add(t);
}
dao.CloseReader();
return(l);
}
/// <summary>
/// Select
/// </summary>
/// <param name="projId"></param>
/// <returns></returns>
public static List <AfsProjMeta> Select(int projId)
{
List <AfsProjMeta> l = new List <AfsProjMeta>();
string sql = @"
select *
from <mbs_owner>.afs_proj_meta
where afs_current = 1 and proj_id = " + projId +
" order by upper(category_name)";;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsProjMeta m = new AfsProjMeta();
m.ProjId = dao.GetIntByName("PROJ_ID");
m.CategoryName = dao.GetStringByName("CATEGORY_NAME");
m.CategoryValue = dao.GetStringByName("CATEGORY_VALUE");
l.Add(m);
}
dao.CloseReader();
return(l);
}
/// <summary>
/// Get dict of assay result types
/// </summary>
/// <param name="assayId"></param>
/// <returns></returns>
public static Dictionary <int, int> GetExpectedAssayResultTypes(int assayId)
{
Dictionary <int, int> dict = new Dictionary <int, int>();
DbCommandMx drd = new DbCommandMx();
string sql = AssayResultType.SelectExpectedResultTypesSqlTemplate;
drd.PrepareMultipleParameter(sql, 1); // (takes ~700 ms first time)
drd.ExecuteReader(assayId);
while (drd.Read())
{
int resultType = drd.GetIntByName("<resultTypeId>");
int secondaryResultType = drd.GetIntByName("<secondaryResultTypeId");
dict[secondaryResultType] = resultType;
}
drd.Dispose();
return(dict);
}
public static List <DbColumnMetadata> GetTableMetadataFromMySqlDictionary(
DbConnectionMx conn,
string schema,
string tableName)
{
string sql = String.Format(
@"SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
table_schema = '{0}'
AND table_name = '{1}'
ORDER BY ORDINAL_POSITION" ,
schema, tableName);
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
drd.PrepareUsingDefinedConnection(sql);
DbDataReader rdr = drd.ExecuteReader();
List <DbColumnMetadata> md = new List <DbColumnMetadata>();
while (rdr.Read())
{
DbColumnMetadata cmd = new DbColumnMetadata();
cmd.Name = drd.GetStringByName("column_name");
cmd.Type = drd.GetStringByName("data_type");
cmd.Length = drd.GetLongByName("character_maximum_length");
cmd.Precision = drd.GetIntByName("numeric_precision");
cmd.Scale = drd.GetIntByName("numeric_scale");
cmd.Nullable = drd.GetStringByName("is_nullable");
cmd.Comment = drd.GetStringByName("column_comment");
md.Add(cmd);
}
rdr.Close();
return(md);
}
/// <summary>
/// Get dict relating types between different assay databases
/// </summary>
static void BuildResultTypeDicts()
{
string sql = @"
select <columns>
from <tables>
order by lower(<orderColumns>)";
resultTypeDict1 = new Dictionary <int, AssayResultType>();
resultTypeDict2 = new Dictionary <int, AssayResultType>();
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (drd.Read())
{
AssayResultType r = new AssayResultType();
r.Name = drd.GetStringByName("rslt_typ_nm");
r.ResultTypeId1 = drd.GetIntByName("rslt_typ_id");
r.ResultTypeId2 = drd.GetIntByName("SECONDARY_RSLT_TYP_ID");
r.SummarizedMeanTypeCode = drd.GetStringByName("smrzd_mean_typ_cd");
r.DimensionTypeId = drd.GetIntByName("dmnsn_typ_id");
if (r.DimensionTypeId != 70) // dmnsn_typ_id: 70 = 'Result', 61 = 'Stat'
{
r.ResultTypeId2 = -r.ResultTypeId2;
}
resultTypeDict1[r.ResultTypeId1] = r;
resultTypeDict2[r.ResultTypeId2] = r;
}
drd.Dispose();
return;
}
/// <summary>
/// GetProjectMetaDataWithCriteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static AfsProject SelectMetaDataWithCriteria(string criteria)
{
string sql = @"
select *
from <mbs_owner>.afs_project
where afs_current = 1 and <criteria>" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
if (!dao.Read())
{
dao.CloseReader();
return(null);
}
AfsProject p = new AfsProject();
p.ProjId = dao.GetIntByName("PROJ_ID");
p.ProjectLabel = dao.GetStringByName("PROJ_NAME");
p.PlatformName = dao.GetStringByName("PLATFORM_NAME");
p.Description = dao.GetStringByName("DESCRIPTION");
p.ProjectFlowScheme = dao.GetClobByName("PROJ_FLOW_SCHEME");
p.MbsDhtFolderName = dao.GetStringByName("MBS_DHT_FOLDER_CODE");
p.MbsProjectName = dao.GetStringByName("MBS_PROJECT_CODE");
dao.CloseReader();
p.ProjMeta = AfsProjMeta.Select(p.ProjId);
p.Targets = AfsTarget.Select(p.ProjId);
p.Assays = AfsAssay.Select(p.ProjId);
p.Libraries = AfsLibrary.Select(p.ProjId);
return(p);
}
/// <summary>
/// GetTableMetadataFromOracleDictionary
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List <DbColumnMetadata> GetTableMetadataFromOracleDictionary(
DbConnectionMx conn,
string schemaName,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
DbColumnMetadata cmd;
List <DbColumnMetadata> cmdList = new List <DbColumnMetadata>();
string sql = "select column_name,data_type,data_length,data_precision,data_scale,nullable " +
"from sys.all_tab_columns where owner=:0 " +
"and table_name=:1 order by column_id";
if (conn == null)
{
throw new Exception("Connection not found for tableName: " + tableName);
}
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
int parmCount = 2;
drd.PrepareMultipleParameter(sql, parmCount);
string[] sa = tableName.Split('.');
if (sa.Length != 2)
{
throw new Exception("TableName not in owner.tableName form: " + tableName);
}
string creator = sa[0];
string tname = sa[1];
if (Lex.Eq(creator, "mbs_user")) // workaround to allow tables owned by dev mbs_owner
{
creator = "mbs_owner"; // to be accessed via synonyms defined on dev mbs_user
}
object[] p = new object[2];
p[0] = creator.ToUpper();
p[1] = tname.ToUpper();
drd.ExecuteReader(p);
while (drd.Read())
{
cmd = new DbColumnMetadata();
cmd.Name = drd.GetStringByName("column_name");
cmd.Type = drd.GetStringByName("data_type");
cmd.Length = drd.GetIntByName("data_length");
cmd.Precision = drd.GetIntByName("data_precision");
cmd.Scale = drd.GetIntByName("data_scale");
cmd.Nullable = drd.GetStringByName("nullable");
cmdList.Add(cmd);
}
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(cmdList);
}