public static DataTable LoadFullDataStructureList(string source, string whereclause, bool uwonly, int studyID)
{
DataTable dt = new System.Data.DataTable();
if (source == "localDB")
{
SQL_utils sql = new SQL_utils("data");
if (uwonly & studyID > 0)
{
// See below where the view 'vwALL_VinelandIISurvey_withMBI' is swapped in.
// NDAR now requires the MBI variables, this view pulls these in from the 'ALL_VinelandIISurvey_MBI' table.
//dt = sql.DataTable_from_SQLstring("select * from vwNDAR_DS where uwtable is not null and " +
// " (shortname like '%" + whereclause + "%' or title like '%" + whereclause + "%')" +
// " and uwtable in (select (case when name = 'ALL_VinelandIISurvey' then 'vwALL_VinelandIISurvey_withMBI' else name end) name from datTable a " +
// " join datTable_Measure b ON a.tableID = b.tableID " +
// " where measureID in " +
// " (select measureID from uwautism_research_backend..tblstudymeas where studyID = " + studyID.ToString() + "))");
string code = String.Format("select * from vwNDAR_DS where uwtable is not null and " +
" (shortname like '%{0}%' or title like '%{0}%')" +
" and uwtable in (select tblname from def.Tbl " +
" where measureID in " +
" (select measureID from uwautism_research_backend..tblstudymeas where studyID = {1}))"
, whereclause, studyID);
dt = sql.DataTable_from_SQLstring(code);
}
else if (uwonly & studyID == 0)
{
dt = sql.DataTable_from_SQLstring("select * from vwNDAR_DS where uwtable is not null and " +
" (shortname like '%" + whereclause + "%' or title like '%" + whereclause + "%')");
}
else if (whereclause != "")
{
dt = sql.DataTable_from_SQLstring("select * from vwNDAR_DS where " +
" (shortname like '%" + whereclause + "%' or title like '%" + whereclause + "%')");
}
else
{
dt = sql.DataTable_from_ViewName("vwNDAR_DS");
}
sql.Close();
}
else if (source == "NDAR")
{
dt = LoadFullDataStructureList(source);
}
return(dt);
}
public static DataTable LoadFullDataStructureList(string source)
{
DataTable dt = new System.Data.DataTable();
if (source == "NDAR")
{
WebClient wc = new WebClient();
string ds_string = wc.DownloadString(@"https://ndar.nih.gov/api/datadictionary/v2/datastructure");
List <NDAR_DataStructure> dsList = JsonConvert.DeserializeObject <List <NDAR_DataStructure> >(ds_string,
new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore,
DateFormatHandling = DateFormatHandling.IsoDateFormat
});
//Create the data table for the dataElements
dt = NDAR_dataStructures();
foreach (NDAR_DataStructure ds in dsList)
{
DataRow row = dt.NewRow();
row["shortName"] = ds.shortName;
row["title"] = ds.title;
row["datatype"] = ds.dataType;
row["status"] = ds.status;
row["publicStatus"] = ds.publicStatus;
if (ds.publishDate.ToString() != "")
{
row["publishDate"] = Convert.ToInt64(ds.publishDate);
}
if (ds.modifiedDate.ToString() != "")
{
row["modifiedDate"] = Convert.ToInt64(ds.modifiedDate);
}
dt.Rows.Add(row);
}
}
else if (source == "localDB")
{
SQL_utils sql = new SQL_utils();
dt = sql.DataTable_from_ViewName("vwNDAR_DS");
}
return(dt);
}