protected void ddl_ChangeGroup_SelectedIndexChanged(object sender, EventArgs e) { int subjID = Convert.ToInt32(ddl_ChangeGroupID.SelectedValue.ToString()); if (subjID > 0) { int newgroup1 = Convert.ToInt32(ddl_newGroup1.SelectedValue.ToString()); int newgroup2 = Convert.ToInt32(ddl_newGroup2.SelectedValue.ToString()); SQL_utils sql = new SQL_utils("backend"); int oldgroup = sql.IntScalar_from_SQLstring(String.Format("select groupID from tblsubject where subjID={0}", subjID)); if (newgroup1 == newgroup2 && newgroup1 > 0 && newgroup1 != oldgroup) { string sqlcode = String.Format("select groupID from tblsubject where subjID = {0}", subjID); int oldgroupID = sql.IntScalar_from_SQLstring(sqlcode); if (newgroup1 != oldgroupID) { btn_ChangeGroup.Visible = true; } } else { btn_ChangeGroup.Visible = false; } sql.Close(); } }
public void SaveToDB() { SQL_utils sql = new SQL_utils("data"); int current_maxpk = sql.IntScalar_from_SQLstring("select max(datauploadPK) maxPK from def.DataUpload"); string sqlcode = String.Format("insert into def.DataUpload (filename, studymeasID, ID, isMultSubj, uploaded, uploadedby) values ({0},{1},{2},{3},getdate(), sec.systemuser())" , filename, studymeasID, ID, isMultSubj); sql.NonQuery_from_SQLstring(sqlcode); string sqlcode2 = String.Format("select datauploadPK from def.DataUpload where filename='{0}' and uploadedby=sec.systemuser() and datauploadPK > {1} ", filename, current_maxpk); datauploadPK = sql.IntScalar_from_SQLstring(sqlcode2); sql.Close(); if (datauploadPK > 0) { results.Add(String.Format("Added datauploadPK {0}", datauploadPK)); } else { results.Add(String.Format("DataUpload Not Logged for '{0}'!", filename)); } }
protected void btnInsertToken_Click(object sender, EventArgs e) { string url = "https://redcap.iths.org/api/"; SQL_utils sql = new SQL_utils("data"); //Make sure token is unique int numtokens = sql.IntScalar_from_SQLstring(String.Format("select count(*) from def.redcaptoken where token='{0}'", txtToken.Text)); if (numtokens > 0) { lblRESULTS.ForeColor = Color.Red; lblRESULTS.Text = "This token has already been entered into the DB. Try again."; } else { int newtokenid = sql.IntScalar_from_SQLstring(String.Format("select max(tokenid)+1 from def.REDCapToken ")); string sql_newtoken = String.Format("insert into def.REDCapToken(token, project, url, idfld, tokenid) Values('{0}','{1}','{2}','{3}',{4})", txtToken.Text, txtProjectname.Text, url, txtIDfld.Text, newtokenid); sql.NonQuery_from_SQLstring(sql_newtoken); sql.NonQuery_from_SQLstring(String.Format("insert into def.REDCapToken_study(tokenid, studyid) values({0},{1})", newtokenid, Master.Master_studyID)); lblRESULTS.ForeColor = Color.ForestGreen; lblRESULTS.Text = "Token added."; } sql.Close(); gv_tokens.DataBind(); }
protected void btnSubmitMissing_Click(object sender, EventArgs e) { bool isInvalid = string.IsNullOrEmpty(cboDataUploadType.Value.ToString()); if (!isInvalid) { int doctypeID = Convert.ToInt32(cboDataUploadType.Value); int entitytypeid = Convert.ToInt32(Request.QueryString["entitytypeid"]); int entityid = Convert.ToInt32(Request.QueryString["entityid"]); SQL_utils sql = new SQL_utils(); int docid = sql.IntScalar_from_SQLstring("select max(docid)+1 from tbldoc"); int docversid = sql.IntScalar_from_SQLstring("select max(docversid)+1 from tbldocvers"); if (docid > 0 & docversid > 0) { string code1 = String.Format("insert into tbldoc(docid, doctitle, doctypeid, docstatusid) values({0}, 'MISSING', {1}, 5)", docid, doctypeID); sql.NonQuery_from_SQLstring(code1); string code2 = String.Format("insert into tbldocvers(docid, docversid, docexists) values({0}, {1}, 0)", docid, docversid); sql.NonQuery_from_SQLstring(code2); string code3 = String.Format("insert into tbldocfk(docid, entitytypeid, entityid) values({0}, {1}, {2})", docid, entitytypeid, entityid); sql.NonQuery_from_SQLstring(code3); btnSubmitMissing.Visible = false; lblMissingLogged.Visible = true; } } }
public string SaveVarsToDB(SQL_utils sql, int extractionmodeID) { string info = ""; int num_vars = 0; if (vars.Count > 0) { try { foreach (uwac.Variable v in vars) { //Check that var is not already defined int var_in_flds = sql.IntScalar_from_SQLstring(String.Format("select count(*) from def.fld where tblpk={0} and fldname='{1}'", tblpk, v.varname)); if (var_in_flds == 0) { string sqlcode; if (extractionmodeID >= 0) { sqlcode = String.Format("insert into def.fld(tblpk, ord_pos, fldname, FieldDataType, FieldLabel, FieldValueSetID, fldextractionmode) " + " values({0},{1},'{2}','{3}','{4}',{5},{6})" , tblpk, num_vars, v.varname, v.datatype, v.varlabel, v.fieldvaluesetid, extractionmodeID); } else { sqlcode = String.Format("insert into def.fld(tblpk, ord_pos, fldname, FieldDataType, FieldLabel, FieldValueSetID) " + " values({0},{1},'{2}','{3}','{4}',{5})" , tblpk, num_vars, v.varname, v.datatype, v.varlabel, v.fieldvaluesetid); } sql.NonQuery_from_SQLstring(sqlcode); int newfldpk = sql.IntScalar_from_SQLstring( String.Format("select fldpk from def.fld where tblpk={0} and fldname='{1}'", tblpk, v.varname)); if (newfldpk > 0) { num_vars++; } } } if (num_vars > 0) { info += String.Format(" {0} variables added.", num_vars); } return(info); } catch (Exception ex) { return(String.Format("ERROR. {0}", ex.Message)); } } else { return("No variables defined."); } }
public int LogUpload_Doc() { OrderedDictionary newvalues = new OrderedDictionary(); //newvalues.Add("docID", docID); //newvalues.Add("docversID", docversID); newvalues.Add("docfilename", docfilename); newvalues.Add("doctitle", doctitle); newvalues.Add("docdesc", docdesc); newvalues.Add("doctypeID", doctypeID); newvalues.Add("docstatusID", docstatusID); newvalues.Add("fileext", fileext); newvalues.Add("origfilename", origfilename); //Insert Doc docID = DxDbOps.InsertSqlCode_ReturnPK(newvalues, "tblDoc", "backend", "dbo", "DocID"); if (docID > 0) { newvalues.Add("DocID", docID); //Insert DocVers docversID = DxDbOps.InsertSqlCode_ReturnPK(newvalues, "tblDocVers", "backend", "dbo", "DocVersID"); //Insert entity link - we link this DocID to this MeasureID SQL_utils sql = new SQL_utils("backend"); string sql_doc = String.Format("select docID from tbldoc where doctitle='{0}' and created > dateadd(minute, -3, getdate())", doctitle); string sql_docvers = String.Format("select docversID from tbldocvers where origfilename='{0}' and docID={1} and created > dateadd(minute, -3, getdate())" , origfilename, docID); int thisDocID = sql.IntScalar_from_SQLstring(sql_doc); int thisDocVersID = sql.IntScalar_from_SQLstring(sql_docvers); string fk_code = String.Format("insert into tblDocFK(DocID, EntitytypeID, EntityID) values({0}, {1}, {2})" , thisDocID, (int)linked_entitytype, linked_entityID); if (thisDocID == docID & thisDocVersID == docversID) { docfilename = String.Format("DocVersID_{0}{1}", docversID, fileext); sql.NonQuery_from_SQLstring(fk_code); sql.Close(); return(docversID); } else { sql.Close(); return(-1); } } return(-1); }
protected void ValidateNewID(object sender, EventArgs e) { string id1 = newID1.Text; string id2 = newID2.Text; string oldsubjid = ddl_newID.SelectedValue; SQL_utils sql = new SQL_utils("data"); string oldid = sql.StringScalar_from_SQLstring(String.Format("select id from uwautism_research_backend..tblsubject where subjID={0}", oldsubjid)); if (oldsubjid != "-1") { if (id1 == id2 && !String.IsNullOrEmpty(id1)) { int n_newid = sql.IntScalar_from_SQLstring(String.Format("select coalesce(count(*),0) n from uwautism_research_backend..vwMasterStatus_S where studyID={0} and ID='{1}'" , Master.Master_studyID, id1)); if (n_newid > 0) { error_label.Text = String.Format("The ID '{0}' is already taken.", id1); } else { //new ID is unique string sql_ndata = String.Format("select count(*) n from uwautism_research_data..datData where ID='{0}' and studymeasID in (select studymeasID from uwautism_research_backend..tblstudymeas where studyID={1})" , oldid, Master.Master_studyID); int ndata = sql.IntScalar_from_SQLstring(sql_ndata); List <string> dataupdateresult = new List <string>(); if (ndata > 0) { error_label.Text = String.Format("'{0}' is available. However, there are {1} entered data records in which ID='{2}'.<br/>Check the box if you want to update the entered data to this new ID '{0}'.<br/>Do not check the box if you want the {1} entered data records to remain as ID '{2}'.", id1, ndata, oldid); chkDataToo.Visible = true; } else { error_label.Text = String.Format("'{0}' is available. There is no entered data with the original ID '{1}'.", id1, oldid); //txtOVERRIDE.Text = "Enter 'fix' here to update entered data with the new ID."; } btnNewID.Visible = true; btnCancelNewID.Visible = true; btnValidateNewID.Visible = false; } } else { error_label.Text = "New ID values must match."; } sql.Close(); } }
protected void btnNew_OnCLick(object sender, EventArgs e) { SQL_utils sql = new SQL_utils("backend"); try { //string code = String.Format(String.Format("exec spSEC_StudyDesign__BeginNewStudy '{0}', '{1}' ", txtName.Value, txtNameFull.Value)); int newstudyID = sql.IntScalar_from_SQLstring("select max(studyID) + 1 from tblStudy"); int newgroupID = sql.IntScalar_from_SQLstring("select max(groupID) + 1 from tblGroup"); //int labID = sql.IntScalar_from_SQLstring("select coalesce(defaultlabid, 1) from tblstaff where staffID = sec.systemuser_staffID()"); int labID = Convert.ToInt32(cboLab.SelectedItem.Value.ToString()); string studyname = txtName.Value.ToString(); string studyfullname = txtNameFull.Value.ToString(); if (newstudyID > 0 & labID > 0) { string code0 = String.Format("insert into dbo.tblstudy(studyID, studyname, studyfullname, active, labID) values({0}, '{1}', '{2}', 1, {3});", newstudyID, studyname, studyfullname, labID); string code1 = String.Format("insert into dbo.tblgroup(groupID, studyID, groupname, groupabbr, goaln, siteID, sortorder) values({0}, {1}, 'Group 1', 'Grp1', 0, 3, 1);", newgroupID, newstudyID); string code2 = String.Format("insert into dbo.tblLabGroup(groupid, labid, labgroup_enabled) values({0}, {1}, 1 )", newgroupID, labID); string code3 = String.Format("insert into dbo.tblLabGroup_staff(staffid, dbroleid, labgroupid) select sec.systemuser_staffID(), 1, max(labgroupID) from tbllabgroup where groupID={0};", newgroupID); string code4 = String.Format("insert into dbo.tblTimepoint(studyID, timepoint, timepoint_text) values ({0}, 1, 'T1');", newstudyID); string code = String.Format("{0} {1} {2} {3} {4}", code0, code1, code2, code3, code4); var result1 = sql.NonQuery_from_SQLstring_withRollback(code); if (result1 == "success") { try { sql.NonQuery_from_SQLstring(String.Format("update tblStaff set defaultstudyID={0}", newstudyID)); Response.Redirect("StudyDesign.aspx"); } catch (Exception ex) { } } } } catch (Exception ex) { } sql.Close(); }
public static string SaveToDB(string shortname) { NDAR.NDAR_DataStructure ds = NDAR.GetNDARDataStructure(shortname); DataTable dse = NDAR.NDARDataStructureElements_to_DataTable(ds); string results = ""; //lblInfo.Text = "nrow = {" + dse.Rows.Count.ToString() + "}"; SQL_utils sql = new SQL_utils(); //Check is NDAR_DS exists int n_ds = sql.IntScalar_from_SQLstring( String.Format("select count(*) from NDAR_DS where shortname='{0}'", shortname)); if (n_ds == 0) { // then insert DSE string code = String.Format("insert into ndar_ds (shortname, title, datatype, status ) values ('{0}', '{1}','{2}','{3}')" , ds.shortName, ds.title, ds.dataType, ds.status); sql.NonQuery_from_SQLstring(code); } //Check is NDAR_DS exists int n_dse = sql.IntScalar_from_SQLstring( String.Format("select count(*) from NDAR_DSE where shortname='{0}'", shortname)); if (n_dse > 0) { sql.NonQuery_from_SQLstring(String.Format("delete from NDAR_DSE where shortname='{0}'", shortname)); } SqlParameter p = sql.CreateParam("NDAR_DSE", dse); try { sql.NonQuery_from_ProcName("spNDAR_Insert_DSE", p); results = "Successfully imported " + shortname; } catch (Exception ex) { results = "Error: " + ex.Message; } sql.Close(); return(results); }
protected void btnHousehold_Click(object sender, EventArgs e) { SQL_utils sql = new SQL_utils("backend"); if (Request.QueryString["ID"] != null) { int hhid = sql.IntScalar_from_SQLstring("select householdID from vwMasterStatus_S where ID='" + Request.QueryString["ID"] + "' and studyID=" + Master.Master_studyID.ToString()); Response.Redirect("~/Tracking/Household2.aspx?hhID=" + hhid.ToString()); } else if (Request.QueryString["subjid"] != null) { int hhid = sql.IntScalar_from_SQLstring("select householdID from vwMasterStatus_S where subjID='" + Request.QueryString["subjid"] + "' and studyID=" + Master.Master_studyID.ToString()); Response.Redirect("~/Tracking/Household2.aspx?hhID=" + hhid.ToString()); } }
protected void btnCreateNew_Click(object sender, EventArgs e) { SQL_utils sql = new SQL_utils("kawdata"); int docid = sql.IntScalar_from_SQLstring(String.Format("select docid from tblDocVers where docversid={0}", Request.QueryString["dvID"])); int entitytypeid = sql.IntScalar_from_SQLstring(String.Format("select entitytypeid from vwDocVers where docversid={0}", Request.QueryString["dvID"])); int entityid = Convert.ToInt32(cbo_NewEntity.Value.ToString()); string sqlcode = String.Format("insert into tbldocfk (docid, entitytypeid, entityid) values({0},{1},{2})" , docid, entitytypeid, entityid); sql.NonQuery_from_SQLstring(sqlcode); sql.Close(); }
protected void SetDataChk(string subjID) { SQL_utils sql = new SQL_utils("backend"); int datachkID = sql.IntScalar_from_SQLstring("select datachkID from uwautism_research_backend..tblSubject where subjID= " + subjID); string str_datachkID = datachkID.ToString(); //foreach(ListItem itm in ddlDataChk.Items) //{ // if(datachkID == itm.Value) itm.Selected=true; //} // ddlDataChk.SelectedValue = ddlDataChk.Items.FindByValue(datachkID.ToString()).ToString(); ddlDataChk.DataBind(); //ddlDataChk.ClearSelection(); ddlDataChk.Items.FindByValue(str_datachkID).Selected = true; btnUpdateDataChk.Visible = true; ddlDataChk.Visible = true; panelGrid.Visible = true; sql.Close(); }
public Datadictionary(DataTable dt_meta, List <string> formnames, int studyID) { Initialize(); CreateDatadictionaryFromREDCapMeta(dt_meta, formnames); if (formnames.Count == 1) { SQL_utils sql = new SQL_utils("data"); measureid = sql.IntScalar_from_SQLstring(String.Format("select measureid from def.REDCap_form where form_name='{0}' and tokenid in (select tokenid from def.REDCapToken_study where studyid={1})", formnames[0], studyID)); measname = sql.StringScalar_from_SQLstring(String.Format("select measname from uwautism_research_backend..tblmeasure where measureid = {0}", measureid)); tblname = sql.StringScalar_from_SQLstring(String.Format("select tblname from def.tbl where measureid={0}", measureid)); tblpk = sql.IntScalar_from_SQLstring(String.Format("select tblpk from def.tbl where measureid={0}", measureid)); sql.Close(); } }
public static int CreateValueSet(SQL_utils sql, int mID, uwac.Valueset vset) { int max = sql.IntScalar_from_SQLstring("select max(fieldvaluesetID) from datfieldvalueset"); string measname = sql.StringScalar_from_SQLstring("select measname from uwautism_research_backend..tblmeasure where measureID=" + mID.ToString()); int newmax = (max > 0) ? max + 1 : 0; if (newmax > 0) { try { string setname = String.Format("{0} set {1}", measname, vset.localvaluesetid.ToString()); string code = String.Format("insert into datfieldvalueset (fieldvaluesetID, fieldvaluesetdesc) values({0},'{1}')" , newmax, setname); sql.NonQuery_from_SQLstring(code); foreach (Valuesetitem itm in vset.valitems) { AddValueSetItem(sql, newmax, Convert.ToInt32(itm.value), itm.label); } return(newmax); } catch (Exception ex) { return(-1); } } else { return(-1); } }
//protected void ValidateChangeGroup(object sender, EventArgs e) //{ // int subjID = Convert.ToInt32(ddl_ChangeGroupID.SelectedValue.ToString()); // int newgroupID1 = Convert.ToInt32(ddl_newGroup1.SelectedValue.ToString()); // int newgroupID2 = Convert.ToInt32(ddl_newGroup2.SelectedValue.ToString()); // if(newgroupID1 == newgroupID2 && newgroupID1 > 0 && subjID > 0) // { // btn_ChangeGroup.Visible = true; // } //} protected void ChangeGroup(object sender, EventArgs e) { int subjID = Convert.ToInt32(ddl_ChangeGroupID.SelectedValue.ToString()); int newgroupID1 = Convert.ToInt32(ddl_newGroup1.SelectedValue.ToString()); int newgroupID2 = Convert.ToInt32(ddl_newGroup2.SelectedValue.ToString()); SQL_utils sql = new SQL_utils("backend"); int oldgroup = sql.IntScalar_from_SQLstring(String.Format("select groupID from tblsubject where subjID={0}", subjID)); if (newgroupID1 == newgroupID2 && newgroupID1 > 0 && subjID > 0 && newgroupID1 != oldgroup) { try { string sqlcode = String.Format(" update tblsubject set groupID = {0} where subjID = {1};", newgroupID1, subjID); string sqlAddActions = String.Format("exec spUpdate_SA_SM_for_Subj_with_new_group {0}", subjID); string sqllogchange = String.Format("insert into uwautism_research_backend..AuditChanges(pk, pkfldname, tblname, fldname, oldvalue, newvalue, updated, updatedBy) values({0},'subjID','tblSubject', 'groupID','{1}','{2}',getdate(),sec.systemuser());" , subjID, oldgroup, newgroupID1); string sql_all = String.Format("{0} {1} {2}", sqlcode, sqlAddActions, sqllogchange); sql.NonQuery_from_SQLstring_withRollback(sql_all); error_label.Text = String.Format("Group changed and actions and measures updated."); btn_ChangeGroup.Visible = false; } catch (Exception ex) { } } sql.Close(); }
//private DataRow PopulateRow(DataTable dt, DataRow source_row, List<string> source_colnames) //{ // DataRow row = dt.NewRow(); // try // { // for(int i=0; i < row.ItemArray.Length; i++) // //foreach (Importfield fld in settings.fields) // { // fld.type = dt.Columns[fld.field].DataType.ToString(); // fld.basedate = basedate; // if (fld.ConvertFromLabelToValue) // { // fld.valueset = settings.valuesets.Where(v => v.fieldvaluesetid == fld.fieldvaluesetID).First(); // } // fld.AssignImportValueToProcess(source_row); // string rowfield = fld.field; // row[rowfield] = fld.ProcessedValue(); // } // return row; // } // catch (Exception ex) // { // //some error // return null; // } //} //public void AddRow(bool has_id, DataTable dt, DataRow row) //{ // if (row != null) // { // if (has_id) // { // string row_id = row["id"].ToString(); // if (String.IsNullOrEmpty(row_id)) // { // row["id"] = settings.ID; // } // } // Debug.WriteLine(row["id"].ToString()); // dt.Rows.Add(row); // } //} public int SaveRowToDB(SQL_utils sql, DataRow row) { int studymeasid = Convert.ToInt32(row["studymeasid"]); string tblname = sql.StringScalar_from_SQLstring(String.Format("select coalesce(tblname,'none') from def.tbl where measureid = (select measureid from uwautism_research_backend..tblstudymeas where studymeasid={0})", studymeasid)); int x = 0; int n = CountValuesInRow(row); if (studymeasid > 0 & tblname != "none") { string code = String.Format("select count(*) from {0} where studymeasid={1} and id='{2}'", tblname, studymeasid, row["record_id"]); int nrows = sql.IntScalar_from_SQLstring(code); string msg; if (nrows > 0) { msg = String.Format(" YES ROW nrows:{0} studymeasid={1} and id='{2}' n_values: {3}", nrows, studymeasid, row["record_id"], n); } else { msg = String.Format(" NO ROW nrows:{0} studymeasid={1} and id='{2}' n_values: {3}", nrows, studymeasid, row["record_id"], n); } Debug.WriteLine(msg); } return(x); }
protected string ProcessDictionary(string filename) { string insertresults = ""; SQL_utils sql = new SQL_utils("data"); //Check for tblpk int tblpk = sql.IntScalar_from_SQLstring("select coalesce(tblpk,-1) from def.tbl where measureID=" + Request.QueryString["mID"]); if (tblpk > 0) { DataSet ds = SpreadsheetGearUtils.GetDataSet(filename, false); DataTable dt = ds.Tables[0]; // Add tblpk DataColumn col = new DataColumn("tblpk", typeof(int)); col.DefaultValue = tblpk; // Add fldextractionmode - and thus allow this measure to be imported DataColumn col2 = new DataColumn("fldextractionmode", typeof(int)); int mode = (int)FieldExtractionMode.matchFldname; col2.DefaultValue = mode; dt.Columns.AddRange(new DataColumn[2] { col, col2 }); insertresults = sql.BulkInsert(dt, "fld", "def"); } sql.Close(); return(insertresults); }
protected void dxMenu_OnItemDataBound(object sender, MenuItemEventArgs e) { var itm = e.Item; SQL_utils sql = new SQL_utils("backend"); int mastertitleID = sql.IntScalar_from_SQLstring("select mastertitleID from vwStudyMastertitle where studyID = " + Master_studyID.ToString()); sql.Close(); List <string> nodes_to_hide = new List <string>(); if (mastertitleID != 1) { nodes_to_hide.AddRange(new List <string> { "Clinic", "Calendar", "Other ID's", "Bernier Lab", "Tadpole", "PATH", "IBIS", "ESS" , "Sleep Pilot", "(old) Data Projects", "IntHx Data Summary", "IntHx Tx Types & Categories", "Reliability Tracking" }); } if (mastertitleID != 2) { nodes_to_hide.Add("AVH"); } itm.Visible = (nodes_to_hide.Contains(itm.Text)) ? false : true; }
protected void gridSets_RowDeleting(object sender, DevExpress.Web.Data.ASPxDataDeletingEventArgs e) { var keys = e.Keys; ICollection valueCollection = e.Keys.Values; int[] vals = new int[e.Keys.Count]; valueCollection.CopyTo(vals, 0); int subjset_pk = Convert.ToInt32(vals[0]); int i = gridSets.FindVisibleIndexByKeyValue(e.Keys[gridSets.KeyFieldName]); Control c = gridSets.FindDetailRowTemplateControl(i, "gridSets"); e.Cancel = true; SQL_utils sql = new SQL_utils("data"); int n = sql.IntScalar_from_SQLstring("select count(*) from dp.dataproject where subjset_pk = " + subjset_pk.ToString()); if (n == 0) { string sql1 = "delete from dp.Subj where subjset_pk = " + subjset_pk; sql.NonQuery_from_SQLstring(sql1); string sql2 = "delete from dp.SubjSet where subjset_pk = " + subjset_pk; sql.NonQuery_from_SQLstring(sql2); } DataTable dt = GetSubjSets(); gridSets.DataSource = dt; gridSets.DataBind(); sql.Close(); }
public string SaveToDB() { string result = ""; if (rptnum > 0 & dataproj_pk > 0) { SQL_utils sql = new SQL_utils("data"); string sqlinsert = String.Format("insert into dp.Report (dataproj_pk, rptnum, rpttitle, rptdesc, rptfilename, created, createdBy) " + " values({0},{1},'{2}','{3}', '{4}',getdate(), sec.systemuser())" , dataproj_pk, rptnum, rpttitle, rptdesc, RptFilename()); sql.NonQuery_from_SQLstring(sqlinsert); int rptpk = sql.IntScalar_from_SQLstring(String.Format("select rptpk from dp.Report where dataproj_pk={0} and rptnum={1}", dataproj_pk, rptnum)); string saveorders_msg = SaveOrdersToDB(sql, rptpk); result = String.Format("Report saved (with {0}).", saveorders_msg); sql.Close(); } return(result); }
protected void CreateHousehold() { //Continue with your logic SQL_utils sql = new SQL_utils("backend"); int hhID = sql.IntScalar_from_SQLstring("exec hh.spHousehold_INSERT '" + NameTextBox.Text + "'"); sql.Close(); if (hhID > 0) { Response.Redirect("Household2.aspx?hhID=" + hhID.ToString()); } //lblEnterName.Visible = false; //if (txtNewHHname.Text == "New Household Name") //{ // lblEnterName.Visible = true; // //ExecBeforeLoad("alert('some value')"); ShowAlert("Enter a name for // //the new household."); //} //else if (txtNewHHname.Text != "" ) //{ //} }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { panelDups.Visible = false; SQL_utils sql = new SQL_utils("backend"); DataTable dt = sql.DataTable_from_SQLstring("select -1 labID, '--select lab--' labname union " + " select a.labID, labname from fn_AllowedLabs_by_User() a join tblLab b ON a.labID = b.labID"); ddlLab.DataSource = dt; ddlLab.DataBind(); int numfail = sql.IntScalar_from_SQLstring("select coalesce(count(*),0) from tblOtherID_failed"); lblNumFailed.Text = String.Format("# of failed otherID records: {0}", numfail); sql.Close(); } else { if (Session["labstudies"] != null) { gridStudy.DataSource = (DataTable)Session["labstudies"]; gridStudy.DataBind(); } } }
//0 #region 1) Load data for display protected void PopulateGV(string id) { int getall = 1; //adjust this if needed. SQL_utils sql = new SQL_utils(); DataTable dt = sql.DataTable_from_SQLstring("exec spALL_MIND_IntHx_vers2_stacked_2018_for_display " + Master.Master_studyID.ToString() + ",'" + id + "'," + getall.ToString()); sql.Close(); ViewState["dt"] = dt; gvRawData.DataSource = dt; gvRawData.DataBind(); lblRawData_nrecs.Text = dt.Rows.Count.ToString() + " records."; SQL_utils sql1 = new SQL_utils("backend"); string groupname = sql1.StringScalar_from_SQLstring("select groupname from vwMasterStatus_S where studyID=" + Master.Master_studyID.ToString() + " and ID='" + id + "'"); lblGroup.Text = groupname; int hasscan = sql1.IntScalar_from_SQLstring("exec uwautism_research_data.dbo.spHaveUWmethodRawScan '" + id + "'"); if (hasscan == 1) { linkRawData.NavigateUrl = "~/dataentryforms/common/MIND_IntHx_v2stack/scans/UW_method_" + id + "_reduced.pdf"; linkRawData.Visible = true; } LoadSMS(sql1, id); sql1.Close(); }
//public string CreateValueSet (List<string> vals, string valuesetname) //{ // SQL_utils sql = new SQL_utils("data"); // string code = String.Format("insert into datFieldValueSet {0}") //} //public int ConvertToFieldValue() public static int ProcessValueSet(string fldname, string valuesetname, bool createNewSetIfNeeded, List <string> valuelabels) { SQL_utils sql = new SQL_utils("data"); string code = String.Format("select fieldvaluesetID from def.fld where fldname = '{0}'", fldname); int fvsID = sql.IntScalar_from_SQLstring(code, true); //No set yet, so create one if (fvsID == -98765) { bool addDummyItem = false; fvsID = CreateValueSet(sql, valuesetname, addDummyItem, valuelabels); } if (fvsID > 0) { if (valuelabels.Count > 0) { for (int i = 0; i < valuelabels.Count; i++) { AddValueSetItem(sql, fvsID, null, valuelabels[i]); //Add the label } } } return(fvsID); }
public string SaveFieldsToDB() { string result = ""; string result2 = ""; if (dt_metadata.HasRows()) { SQL_utils sql = new SQL_utils("data"); string first_tokenid = dt_metadata.AsEnumerable().Select(f => f.Field <int>("tokenid")).First().ToString(); if (first_tokenid != "") { string sql_n = String.Format("select count(*) from def.REDCap_fields where tokenid = {0}", first_tokenid); int nflds = sql.IntScalar_from_SQLstring(sql_n); if (nflds > 0) { string sql_del = String.Format("delete from def.REDCap_fields where tokenid = {0}", first_tokenid); sql.NonQuery_from_SQLstring(sql_del); result = String.Format("{0} records deleted from [redcap_fields]. ", nflds); } result2 = sql.BulkInsert(dt_metadata, "redcap_fields", "def"); } } return(String.Format("{0} {1}", result, result2)); }
public static int Get_Count_NDAR_DSE_from_DB(string shortName) { SQL_utils sql = new SQL_utils("data"); int n = sql.IntScalar_from_SQLstring("select coalesce(count(*),0) from NDAR_DSE where shortname = '" + shortName + "'"); return(n); }
private int NextDocID() { SQL_utils sql = new SQL_utils("backend"); int id = sql.IntScalar_from_SQLstring("select max(docID) + 1 from tbldoc"); sql.Close(); return(id); }
protected void Page_Load(object sender, EventArgs e) { //pivotM.FieldValueTemplate = new FieldValueTemplate(); //pivotM.CellTemplate = new CellTemplate(); string filter = ""; bool isCallback = IsCallback; bool isPostback = IsPostBack; if (Request.QueryString["studymeasID"] != null) { var smID = Request.QueryString["studymeasID"]; SQL_utils sql = new SQL_utils("backend"); int measureID = sql.IntScalar_from_SQLstring(String.Format("select measureID from tblstudymeas where studymeasID={0}", smID)); int timept = sql.IntScalar_from_SQLstring(String.Format("select timepointID from tblstudymeas where studymeasID={0}", smID)); sql.Close(); //gridLkupMeas.GridView.Selection.SelectRowByKey(measureID); filter = String.Format("[StudyMeasID] = {0}", smID); } if (!IsCallback && !IsPostBack) { gvM.Visible = true; SQL_utils sql = new SQL_utils("backend"); string sqlcode = String.Format("select count(*) n from uwautism_research_backend..tblstudymeas where studyID = {0} and reliabilityStudymeasID > 0", Master.Master_studyID); int numrel = sql.IntScalar_from_SQLstring(sqlcode); rblREL.Visible = (numrel > 0) ? true : false; sql.Close(); // LoadEntities(); } if (IsPostBack && Session["M_data"] != null) { } sliderValue.Value = Master.Colorlevel.ToString(); txtColorLevel.Text = Master.Colorlevel.ToString(); //Still to fix //if(filter != "") gvM.FilterExpression = filter; }
private int GetRptnum() { SQL_utils sql = new SQL_utils("data"); int nextrptnum = sql.IntScalar_from_SQLstring("select coalesce(max(rptnum),0)+1 from dp.Report where dataproj_pk=" + dataproj_pk.ToString()); sql.Close(); return(nextrptnum); }
protected void btnDuplicate_click(Object sender, EventArgs e) { SQL_utils sql = new SQL_utils("data"); DataEntryController dec = (DataEntryController)this.DataEntryController1; string tblname = dec.DatabaseTable; var smID = dec.StudyMeasID; var pkfld = dec.PrimaryKeyField; int pkval = dec.PrimaryKeyVal; string id = dec.StudySubjectIDString; int indexnum = dec.StudyMeasureIndexnum; DataTable dtflds = sql.DataTable_from_SQLstring("select fldname from def.fld where tblpk = " + Environment.NewLine + " (select tblpk from def.tbl where tblname='" + tblname + "') and fldname not in ('" + pkfld + "')"); int maxindexnum = sql.IntScalar_from_SQLstring("select max(indexnum) from " + tblname + Environment.NewLine + " where id = '" + id + "' and studymeasID=" + smID.ToString()); int nextindexnum = maxindexnum + 1; List <string> flds = dtflds.AsEnumerable().Select(f => f.Field <string>("fldname")).ToList(); string flds_csv = String.Join(",", flds).ToLower(); string newflds_csv = flds_csv.Replace(",studymeasid,", ",4984 as studymeasid,"); newflds_csv = newflds_csv.Replace(",indexnum,", "," + nextindexnum.ToString() + " as indexnum,"); string sqlcode = String.Format("insert into all_currchecklist({0}) " + Environment.NewLine + " select {1} " + Environment.NewLine + " from all_currchecklist where ccl_pk={2}" , flds_csv, newflds_csv, pkval); sql.NonQuery_from_SQLstring(sqlcode); int maxindexnum2 = sql.IntScalar_from_SQLstring("select max(indexnum) from " + tblname + Environment.NewLine + " where id = '" + id + "' and studymeasID=" + smID.ToString()); if (maxindexnum2 > maxindexnum) { lblNewrec.Text = String.Format("indexnum={0} inserted", maxindexnum2); } sql.Close(); }