public static string SelectString(
DbConnectionMx mxConn,
string sql)
{
string value;
DbDataReader dr = null;
DbCommandMx drd = new DbCommandMx();
drd.MxConn = mxConn; // set connection to use
drd.Prepare(sql);
dr = drd.ExecuteReader();
if (!dr.Read())
{
throw (new Exception("SelectString Read failed"));
}
if (dr.IsDBNull(0))
{
value = "";
}
else
{
value = dr.GetString(0);
}
dr.Close();
drd.Dispose();
return(value);
}
/// <summary>
/// Debug check for disallowed duplicate insert for a given method, cid, result group and result type
/// </summary>
/// <param name="voList"></param>
/// <returns></returns>
bool CheckForDuplicateInsert(List <AnnotationVo> voList)
{
AnnotationVo vo = voList[0];
string sql0 = // see if this result value already exists
"SELECT * " +
" FROM mbs_owner.MBS_ADW_RSLT " +
" WHERE mthd_vrsn_id = " + vo.mthd_vrsn_id +
" and ext_cmpnd_id_nbr = " + vo.ext_cmpnd_id_nbr +
" and rslt_grp_id = " + vo.rslt_grp_id +
" and rslt_typ_id = " + vo.rslt_typ_id +
" and sts_id = 1";
DbCommandMx d0 = new DbCommandMx();
d0.Prepare(sql0);
d0.ExecuteReader();
bool exists = d0.Read();
d0.Dispose();
if (exists)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Read a sample of a table for previewing
/// </summary>
/// <returns></returns>
List <string> GetPreviewSubset()
{
Dictionary <string, object> PreviewSubset = new Dictionary <string, object>();
MetaTable mt = Eqp.QueryTable.MetaTable;
string sql = "select /*+ first_rows */" + mt.KeyMetaColumn.ColumnMap + " from " +
GetSourceWithTableFilterCriteria(mt);
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (drd.Read())
{
if (drd.IsNull(0))
{
continue;
}
string id = drd.GetObject(0).ToString();
string cid = CompoundId.Normalize(id); // normalize cid adding prefix as needed
PreviewSubset[id] = null;
if (PreviewSubset.Count >= 100)
{
break;
}
}
drd.Dispose();
return(new List <string>(PreviewSubset.Keys));
}
/// <summary>
/// Insert the rows for a metatree node including its children into the metatree_nodes table
/// Called via command: Update MetatreeNodeTable
/// </summary>
/// <param name="toks"></param>
/// <param name="dao"></param>
public static int InsertMetatreeNodeRows(
MetaTreeNode mtn,
DbCommandMx dao)
{
int insCnt = 0;
foreach (MetaTreeNode cn in mtn.Nodes)
{
string names = "";
string values = "";
AddInsertColumn("parent_name", ref names, ref values, mtn.Name);
AddInsertColumn("parent_label", ref names, ref values, mtn.Label);
AddInsertColumn("parent_type", ref names, ref values, mtn.Type);
AddInsertColumn("child_name", ref names, ref values, cn.Name);
AddInsertColumn("child_label", ref names, ref values, cn.Label);
AddInsertColumn("child_type", ref names, ref values, cn.Type);
AddInsertColumn("child_size", ref names, ref values, cn.Size);
AddInsertColumn("child_update_dt", ref names, ref values, cn.UpdateDateTime);
string sql = "insert into " + MetatreeNodesTableName + " (" + names + ") " +
"values (" + values + ")";
dao.Prepare(sql);
dao.ExecuteNonReader();
insCnt++;
}
return(insCnt);
}
/// <summary>
/// Open select statement
/// </summary>
/// <param name="where"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
public static DbCommandMx Select(
string where,
string orderBy)
{
string sql = @"
select
obj_id,
obj_typ_id,
ownr_id,
obj_nm,
obj_desc_txt,
fldr_typ_id,
fldr_nm,
acs_lvl_id,
obj_itm_cnt,
obj_cntnt,
crt_dt,
upd_dt
from mbs_owner.mbs_log " + "\r\n" +
where + " \r\n" +
//" where obj_id between 9300232 and 9301232 " + // debug
orderBy;
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
return(drd);
}
/// <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>
/// Get image coordinates
/// </summary>
/// <param name="targetMapXDict"></param>
/// <param name="targetMapYDict"></param>
public static void GetImageCoords(
out Dictionary <int, double> targetMapXDict,
out Dictionary <int, double> targetMapYDict)
{
targetMapXDict = new Dictionary <int, double>();
targetMapYDict = new Dictionary <int, double>();
string sql = @"
select entrezgene_id, x, y
from mdbassay_owner.image_coord" ;
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (drd.Read())
{
int geneId = drd.GetInt(0);
double x = drd.GetDouble(1);
targetMapXDict[geneId] = x;
double y = drd.GetDouble(2);
targetMapYDict[geneId] = y;
}
drd.CloseReader();
drd.Dispose();
return;
}
/// <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>
/// Create the temp table for the list if needed
/// </summary>
/// <param name="schema"></param>
/// <param name="name"></param>
public static bool CreateListTableIfNeeded(
string dsName,
string listName)
{
DbCommandMx cmd = null;
bool created = true;
string fullTableName;
DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);
if (Lists.ContainsKey(fullTableName))
{
return(false);
}
string sql = @"
create global temporary table <schema.name> (
rowPos integer,
intKey integer,
stringKey varchar2(256))
on commit preserve rows" ;
sql = sql.Replace("<schema.name>", fullTableName);
try
{
DateTime t0 = DateTime.Now;
cmd = new DbCommandMx();
DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
cmd.MxConn = dbc;
cmd.Prepare(sql);
cmd.ExecuteNonReader();
created = true;
int tDelta = (int)TimeOfDay.Delta(t0);
//DebugLog.Message("Create Table time: " + tDelta);
}
catch (Exception ex)
{
if (!ex.Message.Contains("ORA-xxx"))
{
throw ex; // if other than already exists then throw exception
}
created = false; // already exists
}
finally { if (cmd != null)
{
cmd.Dispose();
}
}
TempDbList list = new TempDbList();
list.DataSource = ds;
list.Name = listName;
Lists[fullTableName] = list;
return(created);
}
/// <summary>
///
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static List <AfsAssay> SelectWithCriteria(string criteria)
{
MetaTreeNode mtn;
string sql = @"
select
p.proj_id,
p.mbs_project_code,
proj_name,
p.mbs_dht_folder_code,
dht_folder_name,
platform_name,
assay_id,
assay_name,
assay_db,
assay_use
from
<mbs_owner>.afs_project p,
<mbs_owner>.afs_assay a
where
p.afs_current = 1
and p.mbs_project_code is not null
and a.afs_current = 1
and a.proj_id = p.proj_id
and <criteria>
order by upper(dht_folder_name), upper(proj_name), upper(assay_name)
" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
List <AfsAssay> assays = new List <AfsAssay>();
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsAssay a = new AfsAssay();
a.ProjId = dao.GetInt(0);
a.MbsProjectName = dao.GetString(1).ToUpper();
a.ProjectLabel = dao.GetString(2);
a.MbsDhtFolderName = dao.GetString(3).ToUpper();
a.DhtFolderLabel = dao.GetString(4).ToUpper();
a.Platform = dao.GetString(5).ToUpper();
a.AssayId = dao.GetInt(6);
a.AssayLabel = dao.GetString(7);
a.AssayDb = dao.GetString(8).ToUpper();
a.AssayUse = dao.GetString(9).ToUpper();
assays.Add(a);
}
dao.CloseReader();
return(assays);
}
/// <summary>
/// Select basic project info with criteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static List <AfsProject> SelectWithCriteria(string criteria)
{
MetaTreeNode mtn;
string sql = @"
select
proj_id,
mbs_project_code,
proj_name,
mbs_dht_folder_code,
dht_folder_name,
platform_name
from
<mbs_owner>.afs_project p
where
afs_current = 1
and mbs_project_code is not null
and <criteria>
order by upper(dht_folder_name), upper(proj_name)
" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
List <AfsProject> projects = new List <AfsProject>();
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsProject p = new AfsProject();
p.ProjId = dao.GetInt(0);
p.MbsProjectName = dao.GetString(1).ToUpper();
p.ProjectLabel = dao.GetString(2);
if (Lex.IsNullOrEmpty(p.ProjectLabel))
{
p.ProjectLabel = p.MbsProjectName;
}
p.MbsDhtFolderName = dao.GetString(3).ToUpper();
p.DhtFolderLabel = dao.GetString(4).ToUpper();
if (Lex.IsNullOrEmpty(p.DhtFolderLabel))
{
p.DhtFolderLabel = p.MbsDhtFolderName;
}
p.PlatformName = dao.GetString(5).ToUpper();
projects.Add(p);
}
dao.CloseReader();
return(projects);
}
/// <summary>
/// Insert a row into the target-assay association table
/// </summary>
/// <param name="toks"></param>
/// <param name="dao"></param>
public static void InsertCommonAssayAttributes(
AssayAttributes taa,
DbCommandMx dao)
{
string names = "";
string values = "";
AddInsertColumn("id", ref names, ref values, taa.Id);
AddInsertColumn("assy_db", ref names, ref values, taa.AssayDatabase);
AddInsertColumn("assy_id2", ref names, ref values, taa.AssayId2);
AddInsertColumn("assy_id_nbr", ref names, ref values, taa.AssayIdNbr);
AddInsertColumn("assy_id_txt", ref names, ref values, taa.AssayIdTxt);
AddInsertColumn("assy_nm", ref names, ref values, taa.AssayName);
AddInsertColumn("assy_desc", ref names, ref values, taa.AssayDesc);
AddInsertColumn("assy_src", ref names, ref values, taa.AssaySource);
AddInsertColumn("assy_typ", ref names, ref values, taa.AssayType);
AddInsertColumn("assy_mode", ref names, ref values, taa.AssayMode);
AddInsertColumn("assy_sts", ref names, ref values, taa.AssayStatus);
AddInsertColumn("assy_sum_lvl", ref names, ref values, taa.SummarizedAvailable ? 1 : 0);
AddInsertColumn("assy_gene_cnt", ref names, ref values, taa.GeneCount);
AddInsertColumn("rslt_typ", ref names, ref values, taa.ResultTypeConcType);
AddInsertColumn("rslt_typ_nm", ref names, ref values, taa.ResultName);
AddInsertColumn("rslt_typ_id2", ref names, ref values, taa.ResultTypeId2);
AddInsertColumn("rslt_typ_id_nbr", ref names, ref values, taa.ResultTypeIdNbr);
AddInsertColumn("rslt_typ_id_txt", ref names, ref values, taa.ResultTypeIdTxt);
AddInsertColumn("rslt_uom", ref names, ref values, taa.ResultTypeUnits);
AddInsertColumn("conc_uom", ref names, ref values, taa.ResultTypeConcUnits);
AddInsertColumn("top_lvl_rslt", ref names, ref values, taa.TopLevelResult);
AddInsertColumn("remppd", ref names, ref values, taa.Remapped);
AddInsertColumn("mltplxd", ref names, ref values, taa.Multiplexed);
AddInsertColumn("rvwd", ref names, ref values, taa.Reviewed);
AddInsertColumn("cmpds_assyd", ref names, ref values, taa.CompoundsAssayed);
AddInsertColumn("prflng_assy", ref names, ref values, taa.ProfilingAssay);
AddInsertColumn("gene_id", ref names, ref values, taa.GeneId);
AddInsertColumn("gene_symbl", ref names, ref values, taa.GeneSymbol);
AddInsertColumn("gene_desc", ref names, ref values, taa.GeneDescription);
AddInsertColumn("gene_fmly", ref names, ref values, taa.GeneFamily);
AddInsertColumn("rslt_cnt", ref names, ref values, taa.ResultCount);
AddInsertColumn("assy_updt_dt", ref names, ref values, taa.AssayUpdateDate);
AddInsertColumn("assn_src", ref names, ref values, taa.AssociationSource);
AddInsertColumn("assn_cnflct", ref names, ref values, taa.AssociationConflict);
AddInsertColumn("x", ref names, ref values, taa.TargetMapX);
AddInsertColumn("y", ref names, ref values, taa.TargetMapY);
AddInsertColumn("z", ref names, ref values, taa.TargetMapZ);
string sql = "insert into mbs_owner.cmn_assy_atrbts (" + names + ") " +
"values (" + values + ")";
// sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);
dao.Prepare(sql);
dao.ExecuteNonReader();
return;
}
/// <summary>
/// Calculate & persist library stats
/// </summary>
/// <returns></returns>
public static string UpdateLibraryStatistics()
{
Dictionary <string, MetaTableStats> stats = new Dictionary <string, MetaTableStats>();
int libId;
long cnt;
DateTime dt;
string libName, txt;
try
{
DbCommandMx dao = new DbCommandMx();
string sql = // get count and date (must use crt_timestamp) for each library
@"
select
l.lib_id,
count(*),
l.crt_timestamp
from
corp_owner.corp_library l,
corp_owner.corp_library_substance ls
where ls.lib_id (+) = l.lib_id
group by l.lib_id, l.crt_timestamp
order by l.lib_id" ;
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
libId = dao.GetInt(0);
libName = "LIBRARY_" + libId;
if (!stats.ContainsKey(libName))
{
stats[libName] = new MetaTableStats();
}
cnt = dao.GetLong(1);
stats[libName].RowCount = cnt;
dt = dao.GetDateTime(2);
stats[libName].UpdateDateTime = dt;
}
dao.CloseReader();
dao.Dispose();
string fileName = MetaTableFactory.MetaTableXmlFolder + @"\LibraryStats";
MetaTableFactory.WriteMetaTableStats(stats, fileName);
}
catch (Exception ex)
{
throw ex;
}
return("Updated statistics for " + stats.Count + " libraries");
}
/// <summary>
/// Store Spotfire SQL and associated fields returning the sequence number of the row
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int InsertSpotfireSql(
string name,
int version,
string sqlStmt,
string keyColName,
string keys,
string owner)
{
int v0 = version;
if (version < 0)
{
version = SequenceDao.NextVal("dev_mbs_owner.mbs_spotfire_sql_seq");
if (v0 == -2) // append version to name
{
name += version;
}
}
else
{
DeleteSqlStatement(name, version); // delete if matching version
}
string sql = @"
insert into dev_mbs_owner.mbs_spotfire_sql
(name, version, sql, key_col_name, keys, ownr_id)
values (:0,:1,:2,:3,:4,:5)" ;
DbCommandMx drDao = new DbCommandMx();
OracleDbType[] pa = new OracleDbType[6];
pa[0] = OracleDbType.Varchar2;
pa[1] = OracleDbType.Int32;
pa[2] = OracleDbType.Clob;
pa[3] = OracleDbType.Varchar2;
pa[4] = OracleDbType.Varchar2;
pa[5] = OracleDbType.Varchar2;
drDao.Prepare(sql, pa);
object[] p = new object[6];
p[0] = name;
p[1] = version;
p[2] = sqlStmt;
p[3] = keyColName;
p[4] = keys;
p[5] = owner;
int count = drDao.ExecuteNonReader(p); // insert the row
drDao.Dispose();
return(version);
}
/// <summary>
/// Read dictionary from Oracle table
/// </summary>
/// <param name="dict"></param>
/// <returns></returns>
static DictionaryMx ReadDictionaryFromOracle(
DictionaryMx dict)
{
DbCommandMx drd = null;
int t0, t1, i1;
try
{
//if (Lex.Eq(dict.Name, "DIT_PERSON")) dict = dict; // debug
DbDataReader dr = null;
drd = new DbCommandMx();
drd.Prepare(dict.Sql);
dr = drd.ExecuteReader();
t0 = TimeOfDay.Milliseconds();
while (drd.Read())
{
if (!dr.IsDBNull(0))
{
string word = dr.GetValue(0).ToString();
if (Lex.IsNullOrEmpty(word))
{
continue;
}
string definition = null;
if (!dr.IsDBNull(1))
{
definition = dr.GetValue(1).ToString();
}
dict.Add(word, definition);
t1 = TimeOfDay.Milliseconds();
// if (t1-t0 > 2000) break; // limit time for development
}
}
drd.CloseReader();
drd.Dispose();
t1 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("ReadDictionaryFromOracle " + dict.Name + " Time: " + t1.ToString());
return(dict);
}
catch (Exception ex)
{
if (drd != null)
{
drd.Dispose();
}
return(null);
}
}
/// <summary>
/// Get dictionary of result types
/// </summary>
/// <returns></returns>
public static Dictionary <int, AssayDbResultType> GetResultTypeDict()
{
if (ResultTypeDict != null)
{
return(ResultTypeDict);
}
string sql = @"
select
rt.assay_rslt_typ_id,
rt.blgcl_rslt_typ_shrt_nm,
rt.sum_md_txt,
l.rslt_lvl_nm
from
metadata_owner.rslt_typ rt,
metadata_owner.rslt_lvl l
where
l.rslt_lvl_id = rt.rslt_lvl_id
and rt.sts_id = 1
and l.sts_id = 1";
Dictionary <int, AssayDbResultType> dict = new Dictionary <int, AssayDbResultType>();
ResultTypeDict = dict;
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (true)
{
if (!drd.Read())
{
break;
}
AssayDbResultType rt = new AssayDbResultType();
rt.RsltTypeId = drd.GetInt(0);
rt.Name = drd.GetString(1);
rt.SumMdTxt = drd.GetString(2);
rt.RsltLvl = drd.GetString(3);
dict[rt.RsltTypeId] = rt;
}
drd.CloseReader();
return(dict);
}
/// <summary>
/// /// <summary>
/// Copy PRD Annotation Table data to DEV reording and compressing the data by method
/// </summary>
/// <returns></returns>
public static string CopyPrdAnnotationTableDataToDev()
{
string mvidSql = @"
select mthd_vrsn_id, count from
(
select mthd_vrsn_id, count(mthd_vrsn_id) count
FROM MBS_OWNER.mbs_adw_rslt
WHERE mthd_vrsn_id > 0 /* = 708070 */
group by mthd_vrsn_id)
order by mthd_vrsn_id
";
Mobius.UAL.Progress.Show("Getting list of annotation table mtht_vrsn_ids ...");
DbCommandMx c1 = new DbCommandMx();
c1.Prepare(mvidSql);
c1.ExecuteReader();
DbCommandMx cmd = new DbCommandMx();
cmd.MxConn = DbConnectionMx.GetConnection("DEV857");
cmd.BeginTransaction();
long totalIns = 0;
int vmIdCnt = 0;
while (c1.Read())
{
long mvId = c1.GetLong(0);
string sql =
@"insert /*+ APPEND */ into mbs_owner.mbs_adw_rslt
select * from mbs_owner.mbs_adw_rslt
where mthd_vrsn_id = " + mvId + @"
order by ext_cmpnd_id_nbr, rslt_grp_id, rslt_id";
cmd.PrepareUsingDefinedConnection(sql);
int insCnt = cmd.ExecuteNonReader();
cmd.Commit();
totalIns += insCnt;
vmIdCnt++;
string msg = "Mthd_Vrsn_id: " + mvId + ", Vmids: " + vmIdCnt + ", Rows: " + totalIns;
Mobius.UAL.Progress.Show(msg);
DebugLog.Message(msg);
}
c1.Dispose();
cmd.Dispose();
return("Rows copied: " + totalIns);
}
/// <summary>
/// Get the next value for the sequence (Oracle)
/// </summary>
/// <param name="seqName"></param>
/// <returns></returns>
public static long NextValLongOracle(
string seqName)
{
string sql;
long nextVal;
SequenceDao seqDao = Lookup(seqName);
Queue <long> seqQueue = seqDao.Queue;
if (seqQueue.Count > 0)
{
nextVal = seqQueue.Dequeue();
return(nextVal);
}
if (seqDao.CacheSize <= 0)
{
sql = "select " + seqName + ".nextval from dual";
}
else
{
sql = "select /*+ first_rows */ " + seqName + ".nextval from sys.all_catalog where rownum <= " +
(seqDao.CacheSize + 1).ToString();
}
int t0 = TimeOfDay.Milliseconds();
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
if (!drd.Read())
{
throw (new Exception("SequenceDao.NextVal Read failed"));
}
nextVal = drd.GetLong(0); // return this one
while (drd.Read()) // enqueue the rest
{
seqQueue.Enqueue(drd.GetLong(0));
}
drd.CloseReader();
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("Read sequence, set size = " + seqQueue.Count.ToString() + ", Time(ms) = " + t0.ToString());
return(nextVal);
}
/// <summary>
/// Read the list of compounds for a library
/// </summary>
/// <param name="libId"></param>
/// <returns></returns>
public static CidList ReadLibrary(
int libId)
{
CidList l = new CidList();
DbCommandMx dao = new DbCommandMx();
string sql = @"
SELECT l.library_name, l.library_desc_text, s.corp_nbr
FROM corp_owner.corp_substance s,
corp_owner.corp_library_substance ls,
corp_owner.corp_library l
WHERE
l.lib_id = <libId> and
s.cpd_id = ls.cpd_id and
l.lib_id = ls.lib_id" ;
sql = Lex.Replace(sql, "<libId>", libId.ToString());
dao.Prepare(sql);
dao.ExecuteReader();
MetaTable rootMt = MetaTableCollection.Get(MetaTable.PrimaryRootTable);
while (dao.Read())
{
if (Lex.IsNullOrEmpty(l.UserObject.Name))
{
string name = dao.GetString(0);
if (Lex.IsNullOrEmpty(name) || Lex.IsInteger(name))
{
name = dao.GetString(1); // use desc if no name or just a number
}
if (Lex.IsNullOrEmpty(name))
{
name = "Library " + libId;
}
l.UserObject.Name = name;
}
int intCorpId = dao.GetInt(2);
string corpId = CompoundId.Normalize(intCorpId, rootMt);
l.Add(corpId);
}
dao.CloseReader();
dao.Dispose();
return(l);
}
/// <summary>
/// Read log object given the object id
/// </summary>
/// <param name="objectId"></param>
/// <returns></returns>
public static UserObject Read( // read object
int objectId) // id of item to read
{
string sql =
"select obj_id, obj_typ_id, ownr_id, obj_nm, obj_desc_txt, " +
"fldr_typ_id, fldr_nm, acs_lvl_id, obj_itm_cnt, obj_cntnt, crt_dt, upd_dt " +
"from mbs_owner.mbs_log where obj_id=:0";
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql, OracleDbType.Int32);
drd.ExecuteReader(objectId);
UserObject uo = UserObjectDao.FetchUserObject(drd);
drd.Dispose();
return(uo);
}
/// <summary>
/// Prepare query with string parameters
/// </summary>
/// <param name="drd"></param>
/// <param name="sql"></param>
/// <param name="stringParameterCount"></param>
public void PrepareMultipleParameterReader(
DbCommandMx drd,
string sql,
int stringParameterCount)
{
if (BuildSqlOnly)
{
Qe.AddSqlToSqlStatementList(this, sql);
}
else
{
DateTime t0 = DateTime.Now;
drd.Prepare(sql, OracleDbType.Varchar2, stringParameterCount);
double dt = TimeOfDay.Delta(t0);
ExecuteReaderTime += dt;
//ExecuteReaderCount++;
}
return;
}
/// <summary>
/// Get the number of non-null text, numeric or date values for a given annotation table.
/// </summary>
/// <returns></returns>
public int GetNonNullRsltCnt(int method_vrsn_id)
{
string sql = @"select
count(rslt_id) as rslt_cnt
from MBS_OWNER.mbs_adw_rslt
where
mthd_vrsn_id = :0
and sts_id = 1
and (rslt_val_nbr is not null
or rslt_val_txt is not null
or rslt_val_dt is not null)";
DbCommandMx cmd = new DbCommandMx();
cmd.Prepare(sql, Oracle.DataAccess.Client.OracleDbType.Int32);
cmd.ExecuteReader(method_vrsn_id);
cmd.Read();
return(cmd.GetInt(0));
}
/// <summary>
/// Simple prepare query with no parameters
/// </summary>
/// <param name="sql"></param>
public void PrepareReader(
DbCommandMx drd,
string sql)
{
if (BuildSqlOnly)
{
Qe.AddSqlToSqlStatementList(this, sql);
}
else
{
DateTime t0 = DateTime.Now;
drd.Prepare(sql, DbType.String, 0);
double dt = TimeOfDay.Delta(t0);
ExecuteReaderTime += dt;
//ExecuteReaderCount++;
}
return;
}
/// <summary>
/// Check that the specified calculated field expression is valid
/// </summary>
/// <param name="advExpr"></param>
/// <returns></returns>
public static string ValidateCalculatedFieldExpression(
string advExpr)
{
try
{
CalcFieldMetaBroker mb = new CalcFieldMetaBroker();
string sql = mb.BuildAdvancedExpressionSql(advExpr);
sql = "select * from (" + sql + ") where 1=2"; // wrap in some sql for rapid evaluation
DbCommandMx dao = new DbCommandMx();
DateTime t0 = DateTime.Now;
dao.Prepare(sql);
dao.ExecuteReader();
dao.Dispose();
double ms = TimeOfDay.Delta(t0);
return("");
}
catch (Exception ex)
{
return(ex.Message);
}
}
/// <summary>
/// Be sure the table is truncated
/// </summary>
/// <param name="schema"></param>
/// <param name="listName"></param>
public static void TruncateListTable(
string dsName,
string listName)
{
DbCommandMx cmd = null;
string fullTableName;
DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);
string sql = "truncate table " + fullTableName;
try
{
DateTime t0 = DateTime.Now;
cmd = new DbCommandMx();
DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
cmd.MxConn = dbc;
cmd.Prepare(sql);
cmd.ExecuteNonReader();
int tDelta = (int)TimeOfDay.Delta(t0);
//DebugLog.Message("Truncate Table time: " + tDelta);
}
catch (Exception ex) // if truncate failed see if need to create
{
if (!ex.Message.Contains("ORA-00942"))
{
throw ex; // if other than already exists then throw exception
}
bool created = CreateListTableIfNeeded(dsName, listName);
}
finally { if (cmd != null)
{
cmd.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>
/// DeleteSqlStatement
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static bool DeleteSqlStatement(
string name,
int version)
{
string sql = @"
delete from dev_mbs_owner.mbs_spotfire_sql
where name = '" + name + "'";
if (version >= 0) // specific version?
{
sql += " and version = " + version;
}
DbCommandMx cmd = new DbCommandMx();
cmd.Prepare(sql);
int count = cmd.ExecuteNonReader();
cmd.Dispose();
return(count > 0);
}
public static int SelectInt(
DbConnectionMx mxConn,
string sql)
{
int value;
DbDataReader dr = null;
DbCommandMx drd = new DbCommandMx();
drd.MxConn = mxConn; // set connection to use
drd.Prepare(sql);
dr = drd.ExecuteReader() as OracleDataReader;
if (!dr.Read())
{
throw (new Exception("SelectInt Read failed"));
}
value = drd.GetInt(0);
dr.Close();
drd.Dispose();
return(value);
}
/// <summary>
/// ReadSqlStatement
/// </summary>
/// <param name="name"></param>
/// <param name="id"></param>
/// <param name="sqlStmt"></param>
/// <param name="owner"></param>
/// <returns></returns>
public static bool ReadSqlStatement(
string name,
int version,
out string sqlStmt,
out string keyColName,
out string owner)
{
string sql = @"
select
sql,
key_col_name,
ownr_id
from dev_mbs_owner.mbs_spotfire_sql
where name = '" + name + "' and " +
"version = " + version;
DbCommandMx cmd = new DbCommandMx();
cmd.Prepare(sql);
cmd.ExecuteReader();
sqlStmt = keyColName = owner = null;
bool exists = cmd.Read();
if (exists)
{
sqlStmt = cmd.GetClob(0);
keyColName = cmd.GetString(1);
owner = cmd.GetString(2);
}
cmd.CloseReader();
cmd.Dispose();
return(exists);
}
/// <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;
}