protected void btnGetCounts_Click(object sender, EventArgs e) { UpdatePanel_O_CheckBoxes p = (UpdatePanel_O_CheckBoxes)panelChk.FindControl("study1"); string studies = p.GetChecked_as_CSV(); SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("datebeg", txtD1.Text, "date")); ps.Add(sql.CreateParam("dateend", txtD2.Text, "date")); ps.Add(sql.CreateParam("studies", studies, "text")); DataTable dt = sql.DataTable_from_ProcName("spTracking_Sessions_Counts", ps); gv.DataSource = dt; gv.DataBind(); if (studies.Contains("1076") | studies.Contains("1077")) { List <SqlParameter> psTadpole = new List <SqlParameter>(); psTadpole.Add(sql.CreateParam("datebeg", txtD1.Text, "date")); psTadpole.Add(sql.CreateParam("dateend", txtD2.Text, "date")); DataTable dtTadpole = sql.DataTable_from_ProcName("spTracking_Sessions_Counts_TadpoleTX_hrs", psTadpole); gvTadpole.DataSource = dtTadpole; gvTadpole.DataBind(); } }
/// <summary> /// New from Jeff - incorporating the Default StudyID /// </summary> public void GetCurrentDefaultStudyID() { //DataTable dt = oDB.GetProc("spSEC_Get_Default_StudyID_for_User"); DataTable dt = oSQL.DataTable_from_ProcName("spSEC_Get_Default_StudyID_for_User"); //2del //SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd); //sqlAdapter.Fill(dt); if (dt.HasRows()) { foreach (DataRow row in dt.Rows) { master_studyID = Convert.ToInt16(row["defaultstudyID"]); master_studyname = Convert.ToString(row["studyname"]); master_studyIDfull = Convert.ToInt32(row["studyIDfull"]); } lblDefaultStudy.Text = master_studyname; lblMasterStudyID.Text = master_studyID.ToString(); lblMasterStudyIDfull.Text = master_studyIDfull.ToString(); Session["master_studyID"] = master_studyID.ToString(); string theme = oSQL.StringScalar_from_SQLstring("exec spSEC_Get_Default_theme_for_User"); Session["master_theme"] = theme.ToString(); } else { Session["master_studyID"] = "0"; Session["master_theme"] = "Glass"; } }
protected void btnGetAddresses_Click(object sender, EventArgs e) { UpdatePanel_O_CheckBoxes p = (UpdatePanel_O_CheckBoxes)panelChk.FindControl("study1"); string studies = p.GetChecked_as_CSV(); SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("studies", studies, "text")); DataTable dt = sql.DataTable_from_ProcName("spTracking_Subj_AddressList", ps); dt.TableName = "Subject Addresses"; string datenow = DateTime.Now.Year.ToString() + ((DateTime.Now.Month < 10) ? "0" : "") + DateTime.Now.Month.ToString() + ((DateTime.Now.Day < 10) ? "0" : "") + DateTime.Now.Day.ToString(); string hrminnow = ((DateTime.Now.Hour < 10) ? "0" : "") + DateTime.Now.Hour.ToString() + ((DateTime.Now.Minute < 10) ? "0" : "") + DateTime.Now.Minute.ToString(); string filename = "UWAC_Subject_Addresses_" + datenow + "_" + hrminnow; //TODO SpreadsheetGearUtils.SaveDataTableToExcel(dt, filename, "xlsx"); }
protected void Refresh_Gridview_EditStaff(int staffID) { LoadUWAC_ADinfo(staffID); //populate the staff details if (oConn.State == ConnectionState.Closed) { oConn.ConnectionString = ConfigurationManager.ConnectionStrings["TRACKING_CONN_STRING"].ToString(); oConn.Open(); } SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("labID", int.Parse(DDL_SelectLabID.SelectedItem.Value).ToString(), "int")); ps.Add(sql.CreateParam("staffID", staffID.ToString(), "int")); DataTable dtStaffDetails = sql.DataTable_from_ProcName("spSEC_LabGroup_Staff_details_for_EDIT", ps); Gridview_EditStaff.DataSource = dtStaffDetails; Gridview_EditStaff.DataBind(); Panel_EditStaff.Visible = true; Gridview_EditStaff.Visible = true; }
protected void getTimepointDates_by_Subject(string subjID) { SQL_utils sql = new SQL_utils(); string IDtxt = ddlNewID.SelectedItem.Text; List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("studyIDfull", Master.Master_studyIDfull.ToString(), "int")); ps.Add(sql.CreateParam("subjID", subjID, "int")); DataTable dt = sql.DataTable_from_ProcName("def.spTimepointDates_by_Subject", ps); if (dt.Rows.Count > 0) { ogrid.DataSource = dt; ogrid.DataBind(); panelGrid.Visible = true; } //if (ds.Tables[1].Rows.Count > 0) { // ogrid2.DataSource = ds.Tables[1]; // ogrid2.DataBind(); // ogrid2.Visible = true; //} sql.Close(); }
protected void GetData(string mo, string mode) { SQL_utils sql = new SQL_utils("FinMart"); string txtimportDate = sql.StringScalar_from_SQLstring("select convert(varchar,min(imported_from_EDW),0) from fin.tblEDWData "); importDate.Text = String.Format("Imported on {0}", txtimportDate); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("yrmo", mo, "text")); ps.Add(sql.CreateParam("mode", mode, "text")); DataTable dt = sql.DataTable_from_ProcName("fin.spGetTabularMonth", ps); string tblinfo = (dt.HasRows()) ? "" : " - No data returned."; Label l = new Label(); l.Text = String.Format("<br/><br/>{0} {1}", mo, tblinfo); l.Font.Size = 12; if (dt == null) { if (mode == "core") { panel1.Controls.Add(l); } else if (mode == "gift") { panel2.Controls.Add(l); } } else { GridView gv = new GridView(); gv.RowCreated += new GridViewRowEventHandler(grid_RowCreated); gv.RowDataBound += new GridViewRowEventHandler(grid_RowDataBound); gv.DataSource = dt; gv.DataBind(); if (mode == "core") { panel1.Controls.Add(l); panel1.Controls.Add(gv); } else if (mode == "gift") { panel2.Controls.Add(l); panel2.Controls.Add(gv); } } sql.Close(); }
protected void RefreshGrid(Grid grid, string procname, string hhID) { SQL_utils sql = new SQL_utils("backend"); DataTable dt = sql.DataTable_from_ProcName(procname, sql.CreateParam("householdID", hhID, "int")); LoadGrid(grid, dt); sql.Close(); }
public static DataTable NDAR_DSE_with_Matching_UWFlds(string shortName) { SQL_utils sql = new SQL_utils("data"); DataTable dt = sql.DataTable_from_ProcName("def.spNDAR_MatchedFlds", sql.CreateParam("shortname", shortName, "text")); return(dt); }
private void Page_Load(object sender, System.EventArgs e) { sql = new SQL_utils("backend"); int nPersonID = Convert.ToInt32(Request.QueryString["personID"].ToString()); DataTable dt = sql.DataTable_from_ProcName("spGetPersonInfoByPersonID", sql.CreateParam("personID", nPersonID.ToString(), "int")); string sJSOutput = ""; if (IsPostBack) { // MovePersonIntoStudy(); } if (dt != null) { DataRow row = dt.Rows[0]; string fullname = row["FullName"].ToString(); //string fullname = dt.AsEnumerable().Select(f => f.Field<string>("FullName")).ToString(); lblName.Text = fullname; tdTitle.InnerHtml = "Create Subject: " + fullname; //txtPersonName.Value = fullname; hidPersonID.Value = nPersonID.ToString(); } else { //oReader.Close(); tblMaster.Rows.Clear(); HtmlTableRow oTr = new HtmlTableRow(); HtmlTableCell oTd = new HtmlTableCell(); tblMaster.Attributes["class"] = ""; oTr.Cells.Add(oTd); tblMaster.Rows.Add(oTr); oTd.InnerHtml = "The Person ID you supplied is not valid."; } LoadMaxIDs(); // LoadOtherIDs(nPersonID); //if (!IsPostBack) //{ // LoadGroups(); //} //DropDownList ddlSite = gridOtherID.Templates[1].Container.FindControl("ddlSite") as DropDownList; //ddlSite.DataSource = sql_Site; //ddlSite.DataBind(); sql.Close(); }
protected void getDATADates_by_Subject(string subjID, bool showIntHx, string title) { SQL_utils sql = new SQL_utils(); string IDtxt = ddlNewID.SelectedItem.Text; List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("studyIDfull", Master.Master_studyIDfull.ToString(), "int")); ps.Add(sql.CreateParam("subjID", subjID, "int")); DataTable dt = sql.DataTable_from_ProcName("def.spDATADates_by_Subject", ps); //see this for why it wasn't working in production: //http://stackoverflow.com/questions/7933830/asp-net-charting-control-not-working-on-production-server //I set the ChartImageHandler value in ISS to: //storage=memory;timeout=20;deleteAfterServicing=true; //System.Web.UI.DataVisualization.Charting.Chart chart = //utilMSCharts.MSGeom("point", dt, "datedone", "rnk", "measname", //"date", "numeric", // "chartname", "Subject DATA Dates: " + IDtxt, "Date", "Measure", -999, -999, -999, -999, 1100, 800, "mytooltip"); //if (chart != null) //{ // panelCharts.Controls.Add(chart); //} //else //{ // //there was an error //} string chartname = "chart" + subjID.ToString(); string lchart = ""; lchart = utilCharts.Highcharts_Line(chartname, dt, "datedone", "rnk", "measname", "date", "Subject DATA Dates: " + title, "Admin Date", "Measure", 900, 500, "delink"); Literal lit = new Literal(); lit.Text = lchart; panelCharts.Controls.Add(lit); string ID = sql.StringScalar_from_SQLstring("select ID from uwautism_research_backend..tblSubject where subjID=" + subjID); if (showIntHx) { DisplayIntHxCharts(Master.Master_studyID, ID); } sql.Close(); }
protected void LoadStudies() { SQL_utils sql = new SQL_utils("backend"); DataTable dt = sql.DataTable_from_ProcName("spSEC_GetStudies_CanView_OR_Acess_by_User__Short__with_SelectStudy"); lstStudies.DataSource = dt; lstStudies.DataTextField = "studyname"; lstStudies.DataValueField = "studyID"; lstStudies.DataBind(); }
protected void LoadStudyIDs() { SQL_utils sql = new SQL_utils("backend"); DataTable dt = sql.DataTable_from_ProcName("spSEC_AllowedStudies_By_User"); UpdatePanel_O_CheckBoxes p = new UpdatePanel_O_CheckBoxes(dt, "study1", "Select studies", "studyname", "studyID", "int", "0"); panelChk.Controls.Add(p); sql.Close(); }
//protected override void Render(HtmlTextWriter writer) { // Button btn = (Button)panelSubjectInfo.ContentTemplateContainer.FindControlRecursive("btn1"); // Page.ClientScript.RegisterForEventValidation(btn.UniqueID); // base.Render(writer); //} protected void LoadIDs() { SQL_utils sql = new SQL_utils("backend"); DataTable dt = sql.DataTable_from_ProcName("spSEC_AllowedIDs_By_Study_User", sql.CreateParam("studyID", Master.Master_studyID.ToString(), "int")); ddlID.DataSource = dt; ddlID.DataTextField = "IDlabel"; ddlID.DataValueField = "subjID"; ddlID.DataBind(); sql.Close(); }
protected DataTable GetAddressData() { SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("studyID", Master.Master_studyID.ToString(), "int")); ps.Add(sql.CreateParam("type", "2", "int")); //1=all subjects, 2=main contacts ps.Add(sql.CreateParam("include", "1", "int")); DataTable dt = sql.DataTable_from_ProcName("spSubject_Addresses", ps); return(dt); }
protected void FindStorageUnits(object sender, System.EventArgs e) { //SqlCommand oCmd = new SqlCommand(); //SqlDataAdapter oAdapter = new SqlDataAdapter(oCmd); //oTable = new DataTable(); //oCmd.Connection = Master.SqlConn; //oCmd.CommandTimeout = 90; //oCmd.CommandType = CommandType.StoredProcedure; //oCmd.CommandText = "spSearchStorageUnits"; //oCmd.Parameters.AddWithValue("@StudyID", selStudyID.SelectedValue); //oCmd.Parameters.AddWithValue("@ID", txtSubjID.Text.Trim()); //oCmd.Parameters.AddWithValue("@StorageLocationID", selStorageLocationID.SelectedValue); //oCmd.Parameters.AddWithValue("@StorageContentID", selStorageContent.SelectedValue); //if (oCmd.Parameters["@ID"].Value.ToString() == "") {oCmd.Parameters["@ID"].Value = DBNull.Value;} //if (oCmd.Parameters["@StorageLocationID"].Value.ToString() == "-1") {oCmd.Parameters["@StorageLocationID"].Value = DBNull.Value;} //if (oCmd.Parameters["@StorageContentID"].Value.ToString() == "-1") {oCmd.Parameters["@StorageContentID"].Value = DBNull.Value;} //oAdapter.Fill(oTable); SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("StudyID", selStudyID.SelectedValue, "int")); ps.Add(sql.CreateParam("ID", txtSubjID.Text.Trim(), "text")); ps.Add(sql.CreateParam("StorageLocationID", selStorageLocationID.SelectedValue, "int")); ps.Add(sql.CreateParam("StorageContentID", selStorageContent.SelectedValue, "int")); DataTable oTable = sql.DataTable_from_ProcName("spSearchStorageUnits", ps); if (oTable.Rows.Count > 0) { tblSearchResults.DataSource = oTable; tblSearchResults.DataBind(); tblSearchResults.Style["display"] = "inline"; tdErrMsg.Style["display"] = "none"; } else { tblSearchResults.Style["display"] = "none"; tdErrMsg.Style["display"] = "inline"; tdErrMsg.Style["color"] = "#ff0000"; tdErrMsg.InnerHtml = "There were no results that matched your search."; } }
//private SqlDataReader GetPersonInfo(int nPersonID) //{ // SqlCommand oCmd = new SqlCommand(); // SqlDataReader oReader; // oCmd.Connection = Master.SqlConn; // oCmd.CommandText = "spGetPersonInfoByPersonID"; // oCmd.CommandTimeout = 90; // oCmd.CommandType = CommandType.StoredProcedure; // oCmd.Parameters.Add(new SqlParameter("@PersonID", SqlDbType.VarChar, 100, ParameterDirection.Input, true, 0, 0 , "", DataRowVersion.Current, nPersonID)); // oReader = oCmd.ExecuteReader(); // return oReader; //} #endregion protected void LoadGroups(object sender, EventArgs e) { if (cboStudyID.Value.ToString() != "-1") { SQL_utils sql = new SQL_utils("backend"); DataTable dt = sql.DataTable_from_ProcName("spSEC_GetGroups_by_StudyID", sql.CreateParam("studyID", cboStudyID.Value.ToString(), "int")); cboGroupID.DataSource = dt; cboGroupID.DataBind(); cboGroupID.ValueField = "groupID"; cboGroupID.TextField = "GroupName"; cboGroupID.SelectedIndex = 0; } }
protected DataTable GetCompvars(DataTable data, int pk) { //Need to calculate Z-scores if needed SQL_utils sql = new SQL_utils("data"); DataTable dtcompvars = sql.DataTable_from_ProcName("dp.spCompvars", sql.CreateParam("dataproj_pk", pk.ToString(), "int")); if (dtcompvars != null) { dtcompvars.Columns.Add("ref_M", Nullable.GetUnderlyingType(typeof(double)) ?? typeof(double)); dtcompvars.Columns.Add("ref_SD", Nullable.GetUnderlyingType(typeof(double)) ?? typeof(double)); dtcompvars.Columns.Add("ref_N", Nullable.GetUnderlyingType(typeof(int)) ?? typeof(int)); #region Z values //Get new values foreach (DataRow row in dtcompvars.Rows) { //Calculate Z string curr_Variable = row.Field <string>("varname").ToString(); string ref_timept = row.Field <string>("ref_timept").ToString(); if (data.Columns.Contains(curr_Variable)) { //remove NULL values var currvar_data = data.AsEnumerable() .Where(r => !r.IsNull(curr_Variable)) .Where(r => r["timept"].ToString() == ref_timept) .Select(x => Convert.ToDouble(x[curr_Variable])); if (currvar_data.Count() > 0) { double?ref_M = currvar_data.Average(); double?ref_SD = currvar_data.StandardDeviation(); int ref_N = currvar_data.Count(); row["ref_M"] = ref_M; row["ref_SD"] = ref_SD; row["ref_N"] = ref_N; } } } #endregion } sql.Close(); return(dtcompvars); }
protected void loadList() { SQL_utils sql = new SQL_utils(); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("studyID", _content_studyID.ToString(), "int")); //Fill the data table DataTable dt = sql.DataTable_from_ProcName("spSCORING_ERRORS_GetViews_by_studyID", ps); sql.Close(); HideAllPanels(); load_dupl_studymeasID(); for (int i = 0; i < dt.Rows.Count; i++) { DataRow row = dt.Rows[i]; string tbl = row["tbl"].ToString(); string vw = row["vw"].ToString(); string gv_name = "gv_" + tbl; string up_name = "UpdatePanel_" + tbl; //lblInfo.Text += up_name + " " + gv_name + " " + tbl + "<br/>"; UpdatePanel up = (UpdatePanel)FindControlRecursive(this.Page, up_name); if (up != null) { // lblInfo.Text += "..." + up.ID + "<br/>"; loadScoringErrors(up, gv_name, vw); up.Visible = true; } else { // lblInfo.Text += "...NULL<br/>" ; } // } }
protected void GetInfo(string infotype, string sqlproc) { SQL_utils sql = new SQL_utils("backend"); // Create a DataTable of the selected studies DataTable dt = new DataTable(); dt.Columns.Add("fld", typeof(string)); dt.Columns.Add("value", typeof(int)); int counter = 0; foreach (ListItem li in lstStudies.Items) { counter++; lblInfo.Text += ", " + counter.ToString(); if (li.Selected) { DataRow r = dt.NewRow(); r["fld"] = "studyID"; r["value"] = Convert.ToInt16(li.Value); dt.Rows.Add(r); lblInfo.Text += "SELECTED"; } } ViewState["SelectedStudies"] = dt; if (dt.Rows.Count > 0) { SqlParameter p = sql.CreateParam("studies", dt); DataTable info = sql.DataTable_from_ProcName(infotype, p); //ShowSelectedStudies(mailing); SpreadsheetGearUtils.SaveDataTableToExcel(info, "mailing", true, "xlsx"); } else { lblInfo.Text = "0 records in data table."; } }
protected DataTable GetTrackingData(string mode) { SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); //Get Info DataSet ds = LoadSelectedItems_into_DataSet(); foreach (DataTable dt in ds.Tables) { ps.Add(sql.CreateParam(dt.TableName, dt)); } DataTable dtX = sql.DataTable_from_ProcName("spTracking_GetInfo_" + mode, ps); sql.Close(); return(dtX); }
protected void btnGetIDList_Click(object sender, EventArgs e) { int labID = Convert.ToInt32(ddlLab.SelectedValue.ToString()); if (labID > 0) { SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("labID", labID.ToString(), "int")); ps.Add(sql.CreateParam("study_csv", "all", "text")); DataTable dt = sql.DataTable_from_ProcName("spCode6_person_by_Lab", ps); string filename = "code6_IDs_" + SpreadsheetGearUtils.DateTime_for_filename(); SpreadsheetGearUtils.SaveDataTableToExcel(dt, filename, true, "csv"); sql.Close(); } }
protected void Populate_IDdelete_DDL(int studymeasID) { SQL_utils sql = new SQL_utils("data"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("studymeasID", ddl_studymeasID.SelectedValue, "int")); ps.Add(sql.CreateParam("clin", ddl_Clin.SelectedValue, "text")); ps.Add(sql.CreateParam("mode", "0,1,4", "text")); DataTable dtSM = sql.DataTable_from_ProcName("spID_and_Indexnum_by_smID", ps); sql.Close(); ddl_IDdelete.DataSource = dtSM; ddl_IDdelete.DataTextField = "IDindex"; ddl_IDdelete.DataValueField = "pk"; ddl_IDdelete.DataBind(); UpdatePanel01.Update(); }
protected void Populate_StudyMeas_New_DDL(int studymeasID) { SQL_utils sql = new SQL_utils("data"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("studymeasID", studymeasID.ToString(), "int")); DataTable dtSM = sql.DataTable_from_ProcName("spDEF__Potential_Movable_Studymeas", ps); sql.Close(); ddl_Studymeas_New.DataSource = dtSM; ddl_Studymeas_New.DataTextField = "studymeasname"; ddl_Studymeas_New.DataValueField = "studymeasID"; ddl_Studymeas_New.DataBind(); UpdatePanel01.Update(); }
protected void ShowCode6_Single() { int labID = Convert.ToInt32(ddlLab.SelectedValue.ToString()); if (labID > 0) { SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("labID", labID.ToString(), "int")); ps.Add(sql.CreateParam("code6", txtCode6.Text, "text")); DataTable dt = sql.DataTable_from_ProcName("spCode6_all_IDs_for_single_code6", ps); gvSingle.DataSource = dt; gvSingle.DataBind(); gvSingle.Visible = true; sql.Close(); } }
public static string GetSQL_for_NDAR_studymeas_to_include(string shortName, int studyID, int tp) { SQL_utils sql = new SQL_utils("data"); List <SqlParameter> p = new List <SqlParameter>(); p.Add(sql.CreateParam("shortName", shortName, "string")); p.Add(sql.CreateParam("studyID", studyID.ToString(), "int")); p.Add(sql.CreateParam("tp", tp.ToString(), "int")); DataTable dt = sql.DataTable_from_ProcName("spNDAR_get_studymeasID_to_include", p); string sql_for_studymeasIDs = ""; if (dt != null) { List <string> myIds = new List <string>(); foreach (DataRow dr in dt.Rows) { myIds.Add(Convert.ToString(dr["studymeasID"])); } sql_for_studymeasIDs = string.Join(",", myIds); } //var idlist = dt.AsEnumerable().Select(r => r.Field<string>("studymeasID")).ToArray(); //string sql_for_studymeasIDs = string.Join(",", idlist); //string sql_for_studymeasIDs = dt.AsEnumerable() // .Select(row => row["studymeasID"].ToString()) // .Aggregate((s1, s2) => String.Concat(s1, "," + s2)); // List<string> list_sql = dt.AsEnumerable().Select(r => r.Field<string>("studymeasID")).ToList<string>(); //string sql_for_studymeasIDs = String.Join(" ", (from row In dt.AsEnumerable() select row("studymeasID")).ToList<string>); sql.Close(); return(sql_for_studymeasIDs); }
protected void Load_gv_ScoreProc(int tblpk) { try { SQL_utils sql = new SQL_utils("data"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("tblpk", tblpk.ToString(), "int")); DataTable dt = sql.DataTable_from_ProcName("spDEF__spScore_by_tblpk", ps); sql.Close(); int i = 0; foreach (DataRow row in dt.Rows) { if (i == 0) { lblTableName.Text = Convert.ToString(row["tblname"]); lblspName.Text = Convert.ToString(row["spName"]); lblLength.Text = Convert.ToString(row["spTextHTML_Length"]); foo.Text = "<pre class=\"prettyprint\">" + Convert.ToString(row["spTextHTML"]) + "</pre>"; } i++; } //gv_ScoreProc.DataSource = dt; //gv_ScoreProc.DataBind(); } catch (SqlException) { } }
protected void Load_LabStaff() { int labID = int.Parse(DDL_SelectLabID.SelectedValue.ToString()); int studyactive = int.Parse(DDL_StudyActive.SelectedValue.ToString()); if (labID > 0) { SQL_utils sql = new SQL_utils("backend"); List <SqlParameter> p = new List <SqlParameter>(); p.Add(sql.CreateParam("labID", labID.ToString(), "int")); p.Add(sql.CreateParam("studyactive", studyactive.ToString(), "int")); p.Add(sql.CreateParam("single_staffID", "0", "int")); DataTable dt = sql.DataTable_from_ProcName("spSEC_Lab_and_AllStaffInLab_by_Studies", p); string[] keepCols = new string[] { "staffID", "StaffName", "LastLogon", "LabName", "N_Labs" }; DataTable dt_pivot = utilsPivot.Pivot(dt, keepCols, "StudyName", "color_allowed_groups", "StaffName", "StudyName DESC"); sql.Close(); Gridview_LabGroup_Staff2.DataSource = dt_pivot; Gridview_LabGroup_Staff2.DataBind(); Gridview_LabGroup_Staff2.Visible = true; Panel_AddNewStaff.Visible = false; btnShow_AddNew.Visible = true; Panel_MultLabs.Visible = true; Panel_EditStaff.Visible = false; Panel_UWAC_ADinfo.Visible = false; } }
public static string GetActigraphProperty(string id, int studymeasID, string propname) { SQL_utils sql = new SQL_utils("data"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("id", id, "text")); ps.Add(sql.CreateParam("smID_epoch", studymeasID.ToString(), "int")); DataTable dt_actprops = sql.DataTable_from_ProcName("spActigraphProps", ps); sql.Close(); string act_propvalue; try { act_propvalue = dt_actprops.AsEnumerable().Where(f => f.Field <string>("ap_param") == propname) .Select(f => f.Field <string>("ap_value")).First().ToString(); } catch (Exception ex) { act_propvalue = "Not Found!"; } return(act_propvalue); }
protected void Export_IBIS_Vineland() { SQL_utils sql = new SQL_utils("data"); List <SqlParameter> ps = new List <SqlParameter>(); string ID = ddlID2.SelectedValue.ToString(); string studymeasID = ddlStudymeasID.SelectedValue.ToString(); string dccID_1 = sql.StringScalar_from_SQLstring("select dbo.fnGetIBIS_DCC_ID('" + ID + "')"); string subjecttype = sql.StringScalar_from_SQLstring("select dbo.fnGetIBIS_DCC_subjecttype('" + ID + "')"); int tp = -1; if (studymeasID == "2671") { tp = 3; // 2671 IBIS: 03_Vineland-II (surv intvw) } if (studymeasID == "1811") { tp = 6; // 1811 IBIS: 06_Vineland-II (intvw) } if (studymeasID == "1837") { tp = 12; // 1837 IBIS: 12_Vineland-II (intvw) } if (studymeasID == "2037") { tp = 18; // 2037 IBIS: 18_Vineland-II (surv intvw) } if (studymeasID == "1863") { tp = 24; // 1863 IBIS: 24_Vineland-II (intvw) } if (studymeasID == "2293") { tp = 36; // 2293 IBIS: 36_Vineland-II (surv intvw) } if (studymeasID == "2750") { tp = 3; // 2750 IBIS-2: 03_Vineland-II (surv intvw) } if (studymeasID == "2740") { tp = 3; // 2740 IBIS-2: 03_Vineland-II (surv intvw)_Proband } if (studymeasID == "2760") { tp = 6; // 2760 IBIS-2: 06_Vineland-II (surv intvw) } if (studymeasID == "2755") { tp = 6; // 2755 IBIS-2: 06_Vineland-II (surv intvw)_Proband } if (studymeasID == "2773") { tp = 9; // 2773 IBIS-2: 09_Vineland-II (surv intvw) } if (studymeasID == "2784") { tp = 12; // 2784 IBIS-2: 12_Vineland-II (surv intvw) } if (studymeasID == "2798") { tp = 15; // 2798 IBIS-2: 15_Vineland-II (surv intvw) } if (studymeasID == "2810") { tp = 24; // 2810 IBIS-2: 24_Vineland-II (surv intvw) } if (studymeasID == "2827") { tp = 36; // 2827 IBIS-2: 36_Vineland-II (surv intvw) } if (studymeasID == "3678") { tp = 0; // 3678 IBIS-2: PROBAND_Vineland-II (surv intvw) } string tp_text = ""; if (tp == 0) { tp_text = "Screening"; } if (tp == 3) { tp_text = "v03"; } if (tp == 6) { tp_text = "v06"; } if (tp == 9) { tp_text = "v09"; } if (tp == 12) { tp_text = "v12"; } if (tp == 15) { tp_text = "v15"; } if (tp == 18) { tp_text = "v18"; } if (tp == 24) { tp_text = "v24"; } if (tp == 36) { tp_text = "v36"; } if (tp_text != "") { ps.Add(sql.CreateParam("ID", ID, "text")); ps.Add(sql.CreateParam("studymeasID", studymeasID, "int")); DataTable dt = sql.DataTable_from_ProcName("spIBIS2_VinelandIISurvey_Recode_for_IBIS_DCC_upload", ps); string id_nopr = ID.Replace("pr", "").Replace("PR", ""); if (dt.HasRows()) { string filename = "vineland_" + subjecttype + "_" + dccID_1 + "_" + id_nopr + "_" + tp_text + ".txt"; SpreadsheetGearUtils.SaveDataTableToFile(dt, filename, "IBISVine"); lblNoData.Text = "Data saved to ??? R:\\IBIS-2\\Vineland_Uploads<br/>" + filename; lblNoData.ForeColor = Color.Blue; } else { lblNoData.Text = "No data returned"; lblNoData.ForeColor = Color.Red; } } sql.Close(); }
protected void Load_gv_ScoreProc(int tblpk) { try { SQL_utils sql = new SQL_utils("data"); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("tblpk", tblpk.ToString(), "int")); DataTable dt = sql.DataTable_from_ProcName("spDEF__spScore_by_tblpk", ps); int n1 = dt.Rows.Count; string proccode = ""; string objcode = ""; int i = 0; foreach (DataRow row in dt.Rows) { if (i == 0) { //get referenced objects //string refobj_code = String.Format("exec spDEF_ReferencedObj_in_ScoringProc '{0}'", row["spName"].ToString()); string refobj_code = "SELECT o.name AS ReferencingObject, o.type_desc ReferencingObject_type, sd.referenced_entity_name AS ReferencedObject, o2.type_desc ReferencedObject_type " + Environment.NewLine + " FROM sys.sql_expression_dependencies AS sd " + Environment.NewLine + " INNER JOIN sys.objects AS o ON o.object_id = sd.referencing_id " + Environment.NewLine + " INNER JOIN sys.objects AS o2 ON o2.object_id = sd.referenced_id " + Environment.NewLine + " WHERE o.name = '" + row["spName"].ToString() + "'" + Environment.NewLine + " and(o2.type_desc like '%function%' or o2.type_desc like '%procedure%') " + Environment.NewLine + " and sd.referenced_entity_name != 'systemuser' "; Debug.WriteLine(refobj_code); Debug.WriteLine("[" + row["spName"].ToString() + "]"); DataTable dt_refobj = sql.DataTable_from_SQLstring(refobj_code); lblTableName.Text = Convert.ToString(row["tblname"]); lblspName.Text = Convert.ToString(row["spName"]); lblLength.Text = Convert.ToString(row["spTextHTML_Length"]); int n = -1; if (dt_refobj != null) { n = dt_refobj.Rows.Count; } lblRefObj.Text = n.ToString(); // (dt_refobj != null) ? dt_refobj.Rows.Count.ToString() : "0"; proccode = Convert.ToString(row["spTextHTML"]); Literal litproc = new Literal(); litproc = FormatCodeOfObject(sql, row["spName"].ToString()); placehold_proc.Controls.Add(litproc); //display referenced objects foreach (DataRow obj in dt_refobj.Rows) { string refobjname = obj["ReferencedObject"].ToString(); Literal lit = new Literal(); lit = FormatCodeOfObject(sql, refobjname); placehold_obj.Controls.Add(lit); //string myobjcode = sql.StringScalar_from_SQLstring("exec spRefObj_FullSQLtext " + refobjname); //DataTable dt_objcode = sql.DataTable_from_SQLstring("exec sp_helptext '" + refobjname + "'"); //objcode = String.Format("<br/><br/><b><u>{0}</u></b><br/>", refobjname); //for(int c=0; c < dt_objcode.Rows.Count; c++) //{ // DataRow r = dt_objcode.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 = objcode; } } i++; } //lit_ProcCode.Text = "<pre class=\"prettyprint\">" + proccode + "</pre>"; //lit_ObjCode.Text = "<pre class=\"prettyprint\">" + objcode + "</pre>"; //lit_ProcCode.Text = proccode ; //lit_ObjCode.Text = objcode ; //gv_ScoreProc.DataSource = dt; //gv_ScoreProc.DataBind(); sql.Close(); } catch (SqlException) { } }