protected DataTable GetDetailDataTable(string schema, string master_tbl, string master_pk, string detail_tbl, string detail_pk, string linktable)
{
SQL_utils sql = new SQL_utils("backend");
string cols0 = sql.GetAllColumnsInTable("uwautism_research_backend", master_tbl, schema, true);
string cols1 = sql.GetAllColumnsInTable("uwautism_research_backend", detail_tbl, schema, true);
string cols2 = sql.GetAllColumnsInTable("uwautism_research_backend", linktable, schema, true);
string cols = cols0 + cols1 + cols2;
cols = cols.Replace(master_pk, "");
cols = cols.Replace(detail_pk, "");
cols = cols.Replace(", ,", ",");
cols = cols.Replace(",,", ",");
string where_clause = " where 1=1 ";
string sqlcode = "select a." + master_pk + " as master_pk, b." + detail_pk + " as detail_pk, " + cols + " from " + schema + "." + master_tbl + " a " +
" join " + schema + "." + linktable + " b ON a." + master_pk + " = b." + master_pk +
" join " + schema + "." + detail_tbl + " c ON b." + detail_pk + " = c." + detail_pk + where_clause;
sqlcode = sqlcode.Replace(", ,", ",");
sqlcode = sqlcode.Replace(",,", ",");
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
sql.Close();
return(dt);
}
protected void UpdateData(string db, string tbl, string schema)
{
string cols = sql.GetAllColumnsInTable(db, tbl, "dbo");
string pk = sql.GetPKForTable(db, tbl, "dbo");
string insSQL = "INSERT INTO uwacdb." + schema + "." + tbl + " (" + cols + ") " +
" select " + cols + " from " + db + ".." + tbl + " where " + pk + " not in " +
" (select " + pk + " from uwacdb." + schema + "." + tbl + " )";
string delSQL = "DELETE FROM uwacdb." + schema + "." + tbl +
" where " + pk + " not in " +
" (select " + pk + " from " + db + ".dbo." + tbl + " )";
string updateCols = sql.GetUpdateSQLForAllColumnsInTable(db, tbl, "dbo", pk);
string updatePKs = sql.GetPKsOfUnequalRecords(db, tbl, schema);
string updSQL = "UPDATE uwacdb." + schema + "." + tbl + " set " +
updateCols + " from " + db + ".dbo." + tbl + " b" +
" where uwacdb." + schema + "." + tbl + "." + pk + " = b." + pk +
" AND b." + pk + " IN (" + updatePKs + ")";
sql.NonQuery_from_SQLstring("SET IDENTITY_INSERT uwacdb." + schema + "." + tbl + " ON");
sql.NonQuery_from_SQLstring(insSQL);
sql.NonQuery_from_SQLstring("SET IDENTITY_INSERT uwacdb." + schema + "." + tbl + " OFF");
sql.NonQuery_from_SQLstring(delSQL);
sql.NonQuery_from_SQLstring("DISABLE TRIGGER ALL ON " + schema + "." + tbl);
sql.NonQuery_from_SQLstring(updSQL);
sql.NonQuery_from_SQLstring("ENABLE TRIGGER ALL ON " + schema + "." + tbl);
// lblInfo.Text = updateCols;
}
protected void LoadMaster(string schema, string master_tbl, string master_pk, bool usestudy)
{
SQL_utils sql = new SQL_utils("backend");
string cols = sql.GetAllColumnsInTable("uwautism_research_backend", master_tbl, schema, true);
cols = cols.Replace(master_pk + ",", "");
string where_clause = " where 1=1 ";
string study_clause = "";
if (usestudy)
{
study_clause = " and studyID = " + Master.Master_studyID.ToString();
}
string sqlcode = "select " + master_pk + " as master_pk, " + cols + " from " + schema + "." + master_tbl + where_clause + study_clause;
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
ogrid.Columns.Clear();
ogrid.DataSource = dt;
ogrid.DataBind();
lblEntity.Text = master_tbl;
sql.Close();
}
protected DataTable GetDetailDataTable(string schema, string master_tbl, string master_pk, string detail_tbl, string detail_pk, int master_pkvalue, bool usestudy)
{
SQL_utils sql = new SQL_utils("backend");
string cols = sql.GetAllColumnsInTable("uwautism_research_backend", detail_tbl, schema, true);
cols = cols.Replace(master_pk + ",", "");
cols = cols.Replace(detail_pk + ",", "");
string where_clause = " where 1=1 ";
string study_clause = "";
//if (usestudy)
//{
// study_clause = " and studyID = " + Master.Master_studyID.ToString();
//}
if (master_pkvalue > 0)
{
where_clause += " and " + master_pk + " = " + master_pkvalue.ToString();
}
string sqlcode = "select " + master_pk + " as master_pk, " + detail_pk + " as detail_pk, " + cols +
" from " + schema + "." + detail_tbl + where_clause + study_clause;
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
sql.Close();
return(dt);
}
protected void LoadDetail(string schema, string master_tbl, string master_pk, string detail_tbl, string detail_pk, int master_pkvalue, bool usestudy)
{
int nselected = 0;
if (ogrid.SelectedRecords != null)
{
nselected = ogrid.SelectedRecords.Count;
}
SQL_utils sql = new SQL_utils("backend");
string cols = sql.GetAllColumnsInTable("uwautism_research_backend", detail_tbl, schema, true);
cols = cols.Replace(master_pk + ",", "");
cols = cols.Replace(detail_pk + ",", "");
string where_clause = " where 1=1 ";
string study_clause = "";
//if (usestudy)
//{
// study_clause = " and studyID = " + Master.Master_studyID.ToString();
//}
if (master_pkvalue > 0)
{
where_clause += " and " + master_pk + " = " + master_pkvalue.ToString();
}
string sqlcode = "select " + master_pk + " as master_pk, " + detail_pk + " as detail_pk, " + cols + " from " + schema + "." + detail_tbl + where_clause + study_clause;
DataTable dt = sql.DataTable_from_SQLstring(sqlcode);
ogrid_detail.Columns.Clear();
ogrid_detail.DataSource = dt;
ogrid_detail.DataBind();
lblEntity_detail.Text = detail_tbl; // +" master_pk = " + master_pkvalue.ToString();
lblOutput_detail.Text = "items for: " + master_pkvalue.ToString();
ogrid_detail.Visible = true;
sql.Close();
}