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 string GetDDLcontents_A(string ctlname, string actiontypeID, string actionstatusID) { SQL_utils sql = new SQL_utils("backend"); string data = ""; DataTable dt = new DataTable(); if (ctlname == "actionstatusID") { dt = sql.DataTable_from_SQLstring("select '--select status--^-1' as textvalue " + " union select * from (select top 100 percent actionstatus +'^'+ cast(a.actionstatusID as varchar) textvalue " + " from tblactionstatus_lkup a" + " join tblActionTypeStatus b ON a.actionstatusID = b.actionstatusID " + " where actiontypeID = " + actiontypeID + " order by b.sortorder ) x "); } data = String.Join(";", dt.AsEnumerable().Select(r => r.Field <string>("textvalue")).ToArray()); if (actionstatusID == "") { actionstatusID = "-1"; } Control ctl = (Control)gridM.Templates[0].Container.FindControl("ddl_" + ctlname); data += "|" + actionstatusID.ToString() + "|ddl_" + ctlname; return(data); }
protected void btnLoadStaff_OnClick(object sender, EventArgs e) { int labID = Convert.ToInt32(cboLab.Value); SQL_utils sql = new SQL_utils("backend"); string studyIDs_csv = uwac.trk.dataops.GetCSV(gridStudies.GridView.GetSelectedFieldValues("studyID")); //string sqlcode = String.Format("select * from vwSEC_AllowedGroups_By_StaffID where studyID in ({0}) and StaffName not in ('{1}','{2}')", studyIDs_csv, "aut user", "autsys TestAcct"); string sqlcode = String.Format("select * from vwSEC_AllowedGroups_By_StaffID where labID in ({0}) and StaffName not in ('{1}','{2}')", labID, "aut user", "autsys TestAcct"); DataTable dt = sql.DataTable_from_SQLstring(sqlcode); Session["labstaff"] = dt; pivotStaff.DataSource = dt; pivotStaff.DataBind(); pivotStaff.Visible = true; sql.Close(); }
private void LoadDoc(string filepath) { if (docversid > 0) { SQL_utils sql = new SQL_utils("backend"); DataTable dt_docversinfo = sql.DataTable_from_SQLstring(String.Format("select * from vwdocvers where docversid={0}", docversid)); if (dt_docversinfo.HasRows()) { if (dt_docversinfo.Rows.Count == 1) { DataRow row = dt_docversinfo.Rows[0]; _origfilename = row["origfilename"].ToString(); _fileext = row["fileext"].ToString(); _doctypeid = Convert.ToInt32(row["doctypeid"].ToString()); _docfilename = row["docfilename"].ToString(); } if (_fileext.ToLower() == ".xlsx" | _fileext.ToLower() == ".xls") { ReadExcelFile(filepath, _docfilename); } else if (_fileext.ToLower() == ".csv") { ReadExcelFile(filepath, _docfilename); } } else { processing_results.Log(String.Format("ERROR: docversID {0} not in DB.", docversid)); } } }
// protected void Load_ddlSSD() //{ // SQL_utils sql = new SQL_utils("backend"); // string sqlcmd = "select -1 as subjstatusdetailID, '-- update status detail--' subjstatusdetail union select a.subjstatusdetailID, subjstatusdetail from uwautism_research_backend..tblsubjstatusdetail_lkup a " + // "join tblsubjstatusanddetail b ON a.subjstatusdetailID = b.subjstatusdetailID " + // "where b.subjstatusID = " + ddlSS.SelectedValue.ToString(); // DataTable dt = sql.DataTable_from_SQLstring(sqlcmd); // List<string> ssdvals = dt.AsEnumerable().Select(f => f.Field<int>("subjstatusdetailID").ToString()).ToList(); // Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "CallMyFunction", "ClearOptionSSD();", true); // foreach(string s in ssdvals) // { // Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "CallMyFunction", "AddOptionSSD(" + s + "," + s + ");", true); // //foreach(ListItem itm in ddlSSD.Items) // //{ // // if (ssdvals.Contains(itm.Value)) // // { // // itm.Enabled = true; // // } // // else{ // // ddlSSD.options. // // itm.Enabled = false; // // } // //} // } // //ddlSSD.DataValueField = "subjstatusdetailID"; // //ddlSSD.DataTextField = "subjstatusdetail"; // //ddlSSD.DataSource = dt; // //ddlSSD.DataBind(); // // updatepanelSSD.Update(); // sql.Close(); //} public string loadMeasstatusdetail(string measstatusID, string measstatusdetailID) { SQL_utils sql = new SQL_utils("backend"); DataTable dt = sql.DataTable_from_SQLstring("select '--select detail--,-1' as ms_msd union select measstatusdetail +','+ cast(measstatusdetailID as varchar) ms_msd from tblMeasstatusdetail_lkup" + " where measstatusdetailID in (select measstatusdetailID from tblmeasstatusanddetail where measstatusID=" + measstatusID + ")"); DataTable dt2 = sql.DataTable_from_SQLstring("select '--select detail--' measstatusdetail,'-1' as measstatusdetailID union select measstatusdetail , cast(measstatusdetailID as varchar) measstatusdetailID from tblMeasstatusdetail_lkup" + " where measstatusdetailID in (select measstatusdetailID from tblmeasstatusanddetail where measstatusID=" + measstatusID + ")"); string msd = String.Join(";", dt.AsEnumerable().Select(r => r.Field <string>("ms_msd")).ToArray()); if (measstatusdetailID == null | measstatusdetailID == "") { measstatusdetailID = "-1"; } Control ctrl = gridM.Templates[0].Container.FindControl("ddl_measstatusdetailID"); string ctlID = ctrl.ID; string ctl_clientID = ctrl.ClientID; msd += "|" + measstatusdetailID + "|" + ctlID; //try to just lookup the control name without the full clientID sql.Close(); return(msd); }
public static void SqlCommand_NonQuery(SqlCommand sqlcmd) { //New Feb 2019 SQL_utils sql = new SQL_utils("data"); SqlParameter[] newps = new SqlParameter[sqlcmd.Parameters.Count]; List <SqlParameter> ps = new List <SqlParameter>(); foreach (SqlParameter p in sqlcmd.Parameters) { SqlParameter newp = new SqlParameter(); newp.ParameterName = p.ParameterName; newp.Value = p.Value; newp.SqlValue = p.SqlValue; newp.SqlDbType = p.SqlDbType; ps.Add(newp); } string sqlproc = sqlcmd.CommandText; sql.NonQuery_from_SQLstring(sqlproc, ps); sql.Close(); }
protected Literal FormatCodeOfObject(SQL_utils sql, string refobjname) { Literal lit = new Literal(); //string refobjname = obj["ReferencedObject"].ToString(); ////string myobjcode = sql.StringScalar_from_SQLstring("exec spRefObj_FullSQLtext " + refobjname); DataTable dt = sql.DataTable_from_SQLstring("exec sp_helptext '" + refobjname + "'"); string objcode = String.Format("<br/><br/><b><u>{0}</u></b><br/>", refobjname); for (int c = 0; c < dt.Rows.Count; c++) { DataRow r = dt.Rows[c]; string newline = String.Format("<span style=\"color:silver\">{0}.</span> {1}<br/>", c + 1, r["Text"]); objcode += newline; } //objcode = String.Format("<b><u>{0}</u></b><br/>{1}<br/><br/>", refobjname, myobjcode); //lit.Text = "<pre class=\"prettyprint\">" + objcode + "</pre>"; lit.Text = objcode; return(lit); }
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(); } } }
protected void GetRptData(string d1, string d2, Panel panel) { panel.Controls.Clear(); SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("d1", d1, "date")); ps.Add(sql.CreateParam("d2", d2, "date")); DataSet ds = sql.DataSet_from_ProcName("ac.spValant_CHDD_annual_report", ps); foreach (DataTable dt in ds.Tables) { GridView gv = new GridView(); //Grid gv = new Grid(); gv.RowDataBound += new GridViewRowEventHandler(gv_RowDataBound); gv.DataSource = dt; gv.DataBind(); panel.Controls.Add(gv); Literal lit = new Literal(); lit.Text = "<br/><br/>"; panel.Controls.Add(lit); } }
protected void btnUpdateSMS_OnClick(object sender, EventArgs e) { int numselected = ogridSMS.SelectedRecords.Count; ArrayList sel = ogridSMS.SelectedRecords; List <string> pks = new List <string>(); if (numselected > 0) { for (int i = 0; i < numselected; i++) { Hashtable vals = (Hashtable)sel[i]; pks.Add(vals["studymeassubjID"].ToString()); } } string sqlcode = String.Format("update tblstudymeassubj set measstatusID={0}, measstatusdetailID={1}, date='{2}' where studymeassubjID in ({3})", ddlMS.SelectedValue.ToString(), ddlMSD.SelectedValue.ToString(), txtSMSdate.Text, String.Join(",", pks.AsEnumerable())); SQL_utils sql = new SQL_utils("backend"); sql.NonQuery_from_SQLstring(sqlcode); LoadSMS(sql, Request.QueryString["id"]); sql.Close(); }
protected void LoadOverview_by_study() { if (print_jminfo) { jminfo.Text += "LoadOverview_by_study..<br/>"; } //lbl_StudyName.Text = _content_studyname; lbl_StudyName.Text = Master.Master_studyname; SQL_utils sql = new SQL_utils("data"); string sqlcode = "exec spRELv2_GET_RelTracking_Overview_by_study " + Master.Master_studyID.ToString(); DataTable dt2 = sql.DataTable_from_SQLstring(sqlcode); sql.Close(); GridView gv_measures = (GridView)UpdatePanel_Overview.FindControl("gv_measures"); gv_measures.DataSource = dt2; gv_measures.DataBind(); UpdatePanel_Overview.Update(); }
//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 void SaveToDB(int dataproj_pk) { byte[] result; using (var stream = new MemoryStream()) { var ser = new BinaryFormatter(); ser.Serialize(stream, this); stream.Flush(); result = stream.ToArray(); SQL_utils sql = new SQL_utils("data"); //sql.SaveChartOrder(dataproj_pk, result); sql.Close(); } Debug.WriteLine(String.Format("This order is {0} bytes long", result.Length)); DxChartOrder neworderC = new DxChartOrder(); using (MemoryStream ms = new MemoryStream(result)) { var ser2 = new BinaryFormatter(); neworderC = (DxChartOrder)(ser2.Deserialize(ms)); } }
//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); }
private void InsertREDCapData() { DataTable dtready = _dtRC.AsEnumerable().Where(f => f.Field <string>("isready") == "ready").CopyToDataTable(); DataColumn col = new DataColumn("verified", typeof(int)); col.DefaultValue = 0; dtready.Columns.Add(col); SQL_utils sql = new SQL_utils("data"); resultslog.Log("INFO: Attempting to match data types"); DataTable dt_dest = sql.DataTable_from_SQLstring(String.Format("select * from {0} where 1=2", tblname)); dtready.MatchColumnDataTypes(dt_dest); try { string insertresults = sql.BulkInsert(dtready, tblname); resultslog.Log(insertresults); //resultslog.Log(String.Format("SUCCESS: inserted {0} rows of data into {1}", dtready.Rows.Count, tblname)); if (!insertresults.Contains("Error")) { UpdateTrackingDB(dtready); } } catch (Exception ex) { resultslog.Log(String.Format("FAILED to insert data into {0} (# rows = {1})", tblname, dtready.Rows.Count)); } sql.Close(); }
protected void SetTitles() { string title = ConfigurationManager.AppSettings["MasterTitle"]; string subtitle = ConfigurationManager.AppSettings["MasterSubtitle"]; //title += " [backend=" + ConfigurationManager.ConnectionStrings["TRACKING_CONN_STRING"] + "]"; SQL_utils sql = new SQL_utils("backend"); title = (Master_studyID > 0) ? sql.StringScalar_from_SQLstring("select coalesce(mastertitle, 'UWAC DB') mastertitle from vwStudyMastertitle where studyID=" + Master_studyID.ToString()) : "UWAC DB (no default study selected)"; placeholderMasterTitle.Controls.Clear(); Literal litTitle = new Literal { Text = title }; Label lblSubtitle = new Label { Text = String.Format(" {0}", subtitle), ForeColor = System.Drawing.Color.Gray }; placeholderMasterTitle.Controls.Add(litTitle); placeholderMasterTitle.Controls.Add(lblSubtitle); sql.Close(); }
public static DataTable SqlCommand_returns_DataTable(SqlCommand sqlcmd) { //New Feb 2019 SQL_utils sql = new SQL_utils("data"); SqlParameter[] newps = new SqlParameter[sqlcmd.Parameters.Count]; List <SqlParameter> ps = new List <SqlParameter>(); foreach (SqlParameter p in sqlcmd.Parameters) { SqlParameter newp = new SqlParameter(); newp.ParameterName = p.ParameterName; newp.Value = p.Value; newp.SqlValue = p.SqlValue; newp.SqlDbType = p.SqlDbType; ps.Add(newp); } string sqlproc = sqlcmd.CommandText; DataTable dt = sql.DataTable_from_SQLstring(sqlproc, ps); sql.Close(); return(dt); }
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 callbackPanel_Callback(object source, DevExpress.Web.CallbackEventArgsBase e) { SQL_utils sql = new SQL_utils(); string x = sql.StringScalar_from_SQLstring(String.Format("select processing_results from tblDocVers_ProcessingResults where docversid={0}", e.Parameter.ToString())); litText.Text = x; }
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 int UpdateSubjset() { var subj_csv = uwac.trk.dataops.GetCSV(gridLkupSubj.GridView.GetSelectedFieldValues(gridLkupSubj.KeyFieldName)); if (subj_csv != null) { string subjset_pk = (Request.QueryString["subjset_pk"] != null) ? Request.QueryString["subjset_pk"] : "0"; string subjset = txtSubjset.Text; string subjsetdetails = txtSubjsetdetails.Text; SQL_utils sql = new SQL_utils("data"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("subjset_pk", subjset_pk, "int")); ps.Add(sql.CreateParam("studyID", Master.Master_studyID.ToString(), "int")); ps.Add(sql.CreateParam("subjset", subjset, "text")); ps.Add(sql.CreateParam("subjsetdetails", subjsetdetails, "text")); ps.Add(sql.CreateParam("sel_subj", subj_csv, "text")); ps.Add(sql.CreateParam("out", "", "int", "output")); int newsubjset_pk = sql.NonQuery_from_ProcName("dp.spSubjSet_UPDATE", ps, "out"); sql.Close(); return(newsubjset_pk); } else { return(0); } }
protected void gridMeasNoVarsCallback_OnCallback(object sender, EventArgs e) { Debug.Print(" $$ gridMeasCallback_OnCallback " + System.DateTime.Now.ToString()); List <int> selmeasID = dataops.GetListInt(gridSelMeas.GetSelectedFieldValues("measureID")).Distinct().ToList(); List <int> measID_of_selvars = dataops.GetListInt(gridSelVars.GetSelectedFieldValues("measureID")).Distinct().ToList(); List <int> MeasNoVars = selmeasID.Except(measID_of_selvars).ToList(); if (MeasNoVars.Count > 0) { SQL_utils sql = new SQL_utils("backend"); string code = String.Format("select measname from tblMeasure where measureID in ({0})", String.Join(",", MeasNoVars)); DataTable dt_MeasNoVar = sql.DataTable_from_SQLstring(code); sql.Close(); gridMeasWithNoVars.DataSource = dt_MeasNoVar; gridMeasWithNoVars.DataBind(); } //RefreshSession_selinthx(); //PivotSelIntHx.DataSource = (DataTable)Session["proj_inthx"]; //PivotSelIntHx.DataBind(); //var selinthx = gridSelIntHx.GetSelectedFieldValues(gridSelIntHx.KeyFieldName); //selected_inthxlist = selinthx.ConvertAll(x => Convert.ToString(x)); }
protected void LoadSubjStatus_options() { SQL_utils sql = new SQL_utils("backend"); DataTable dt = sql.DataTable_from_SQLstring("select * from tblSubjectStatus_Lkup where subjstatusID in " + " (select subjstatusID from tblStudy_SubjStatus where studyID = "+ Master.Master_studyID.ToString() + ")"); }
protected void LoadDatatypes() { SQL_utils sql = new SQL_utils("backend"); string sqlcode = "select studymeasID, studymeasname from tblmeasure a join tblstudymeas b ON a.measureID=b.measureID " + Environment.NewLine + " where a.measureID in (select measureID from uwautism_research_data.def.tbl where measureID>0 and importfiletype is not null and importfiletype != 7) " + Environment.NewLine + " and studyID = " + Master.Master_studyID.ToString() + Environment.NewLine + " order by b.timepointID, studymeasname "; DataTable dt = sql.DataTable_from_SQLstring(sqlcode); cboStudymeas.DataSource = dt; cboStudymeas.ValueField = "studymeasID"; cboStudymeas.TextField = "studymeasname"; cboStudymeas.DataBind(); if (dt.Rows.Count > 0) { cboStudymeas.NullText = "select measure"; } else { cboStudymeas.NullText = "none configured for import"; } sql.Close(); }
//Pass in the ctlname and filtervalue to get the data to populate a DDL //with a subset of relevant options. This string is then passed back to a //Javascript function that loads the ddl. public string GetDDLcontents(string ctlname, string measstatusID, string measstatusdetailID) { SQL_utils sql = new SQL_utils("backend"); string data = ""; DataTable dt = new DataTable(); if (ctlname == "measstatusdetailID") { dt = sql.DataTable_from_SQLstring("select '--select detail--^-1' as textvalue union select measstatusdetail +'^'+ cast(measstatusdetailID as varchar) textvalue from tblMeasstatusdetail_lkup" + " where measstatusdetailID in (select measstatusdetailID from tblmeasstatusanddetail where measstatusID=" + measstatusID.ToString() + ")"); } data = String.Join(";", dt.AsEnumerable().Select(r => r.Field <string>("textvalue")).ToArray()); if (measstatusdetailID == "") { measstatusdetailID = "-1"; } //Control ctl = (Control)gridM.Templates[0].Container.FindControl("ddl_" + ctlname); data += "|" + measstatusdetailID.ToString() + "|ddl_" + ctlname; sql.Close(); return(data); }
protected DataTable getDataforCombobox(string sqlcode) { SQL_utils sql = new SQL_utils("backend"); DataTable dt = sql.DataTable_from_SQLstring(sqlcode); return(dt); }
protected DataTable GetStaffToEdit(int staffID) { SQL_utils sql = new SQL_utils("backend"); //int staffID = Convert.ToInt32(cboStaffInLab.Value.ToString()); int labID = Convert.ToInt32(cboLab.Value.ToString()); string studyIDs_csv = uwac.trk.dataops.GetCSV(gridStudies.GridView.GetSelectedFieldValues("studyID")); string studyID_whereclause = (studyIDs_csv == null) ? " > 0 " : String.Format(" in ({0}) ", studyIDs_csv); string code1 = String.Format("select coalesce(labgroup_staffID, -1 * a.labgroupID) labgroup_staffID, studyname, groupname, a.labgroupID, a.groupID, staffID, coalesce(b.dbroleID, 0) dbroleID, coalesce(dbrole, '*NONE*') dbrole " + " from (select * from tbllabgroup where labID = {2}) a " + " join tblgroup a1 ON a.groupID=a1.groupID " + " join tblstudy a2 ON a1.studyID = a2.studyID " + " left join (select * from tblLabgroup_staff where staffID = {1}) b ON a.labgroupID = b.labgroupID " + " left join tblDBrole_lkup c ON b.dbroleID = c.dbroleID " + " where a.labID = {2} order by studyname, groupname ", studyID_whereclause, staffID, labID); DataTable dt1 = sql.DataTable_from_SQLstring(code1); sql.Close(); int n1 = dt1.Rows.Count; return(dt1); }
protected DataTable GetIDs(int studyID) { DataTable dt = new DataTable(); SQL_utils sql = new SQL_utils(); if (studyID == 1011 | studyID == 1015 | studyID == 1022 | studyID == 1030) { dt = sql.DataTable_from_SQLstring("select ID, txgrp + '...' + ID + '...' + subjstatus as details " + " from uwautism_research_backend..vwmasterstatus_S_txgrp where txgrp<>'' and ID in " + " (select tap_id from uwautism_research_backend..tbl_TxRandomAssign where tap_id not like '%x') and studyID=" + studyID.ToString() + " order by txgrp, ID"); } else { string sortexpr = "ID"; dt = sql.DataTable_from_SQLstring(String.Format("select ID, txgrp + '...' + ID + '...' + subjstatus as details " + " from uwautism_research_backend..vwmasterstatus_S_txgrp where txgrp<>'' and studyID=" + studyID.ToString() + " order by {0} ", sortexpr)); } sql.Close(); return(dt); }
//protected void logConfirmation(string email, string usergroup) //{ // string textToShow = "Confirmation successful, thank you!"; // email = email ?? ""; // usergroup = usergroup ?? ""; // if (email.Length < 5 || usergroup.Length < 5) textToShow = "Confirmation failed, please email [email protected]."; // SqlCommand sqlCmd = new SqlCommand // { // Connection = oConn, // CommandType = System.Data.CommandType.Text, // CommandText = "INSERT into confirmations ([email], [usergroup], [datetime]) VALUES ( '" + email + "', '" + usergroup + "', '" + DateTime.Now + "')" // }; // try // { // sqlCmd.ExecuteNonQuery(); // } // catch(SqlException exc) // { // textToShow = "Confirmation failed, please email [email protected]." + exc.Message; // } // lblInfo.Text = textToShow; //} protected void logConfirmation(string email, string usergroup) { string textToShow = "Confirmation successful, thank you!"; email = email ?? ""; usergroup = usergroup ?? ""; if (email.Length < 5 || usergroup.Length < 5) { textToShow = "Confirmation failed, please email [email protected]."; } else { SQL_utils sql = new SQL_utils("backend"); string sqlcode = String.Format("INSERT into confirmations ([email], [usergroup], [datetime]) VALUES ( '{0}', '{1}', '{2}')", email, usergroup, DateTime.Now.ToString()); try { sql.NonQuery_from_SQLstring(sqlcode); } catch (SqlException exc) { textToShow = "Confirmation failed, please email [email protected]." + exc.Message; } } lblInfo.Text = textToShow; }
protected void btnFindID_Click(object sender, EventArgs e) { if (txtIDToFind.Text.Length > 0) { SQL_utils sql = new SQL_utils("backend"); string s = "select a.* from gev.vwSubjects_in_UW_studies_with_Names a " + "where personID in " + "(select personID from gev.vwSubjects_in_UW_studies where ID like '%" + txtIDToFind.Text + "%' )"; DataTable dt = sql.DataTable_from_SQLstring(s); gv.DataSource = dt; gv.DataBind(); lblInfo.Text = ""; } else { lblInfo.Text = ""; //Nothing to search } }