protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { FST.Common.Business_Interface bi = new FST.Common.Business_Interface(); // load the races (or ethnicities) into the page DataTable dt = bi.GetRaces(); this.ddlRace.DataSource = dt; DataRow row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlRace.DataTextField = "FieldName"; this.ddlRace.DataValueField = "FieldValue"; this.ddlRace.DataBind(); this.ddlRace.Text = ""; dt.Clear(); // get the loci (for all lab kits) dt = bi.GetLocus(Guid.Empty); this.ddlLocus.DataSource = dt; row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlLocus.DataTextField = "FieldName"; this.ddlLocus.DataValueField = "FieldValue"; this.ddlLocus.DataBind(); this.ddlLocus.Text = ""; dt.Clear(); // get the alleles for the selected locus dt = bi.GetAlleles(this.ddlLocus.SelectedValue); this.ddlAlleleNo.DataSource = dt; row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlAlleleNo.DataTextField = "FieldName"; this.ddlAlleleNo.DataValueField = "FieldValue"; this.ddlAlleleNo.DataBind(); this.ddlAlleleNo.Text = ""; dt.Clear(); // get the frequencies dt = bi.GetFrequencyData(); this.gvFreqView.DataSource = dt; this.gvFreqView.DataBind(); } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { // load number of persons involved from DB and bind to gridview DataTable dt = bi.GetNoOfPersonsInvolved(); this.ddlNoOfPersons.DataSource = dt; this.ddlNoOfPersons.DataTextField = "FieldName"; this.ddlNoOfPersons.DataValueField = "FieldValue"; this.ddlNoOfPersons.DataBind(); this.ddlNoOfPersons.SelectedIndex = 0; dt.Clear(); // load lab kits from db and bind to gridview dt = bi.GetLabKits(); this.ddLabKit.DataSource = dt; this.ddLabKit.DataBind(); string firstLabKitID = dt.Rows[0]["FieldValue"].ToString(); dt.Clear(); // load loci for the lab kit from DB and bind to gridview dt = bi.GetLocus(Guid.Parse(firstLabKitID)); this.ddlLocus.DataSource = dt; DataRow row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlLocus.DataTextField = "FieldName"; this.ddlLocus.DataValueField = "FieldValue"; this.ddlLocus.DataBind(); this.ddlLocus.Text = ""; dt.Clear(); // set some defaults this.ddlDropOutType.Text = ""; this.ddlDeducible.Text = Convert.ToString("Yes", CultureInfo.CurrentCulture); this.txtDropOutOption.Text = Convert.ToString("6.25 pg", CultureInfo.CurrentCulture); // get dropout rates for page defaults from DB and databind to gridview dt = ReadDropoutRate(this.txtDropOutOption.Text, Convert.ToInt16(this.ddlNoOfPersons.Text, CultureInfo.CurrentCulture), this.ddlDeducible.Text, firstLabKitID); this.gvDropOut.DataSource = dt; this.gvDropOut.DataBind(); } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { // get options for number of persons involved from the database and bind to our dropdown DataTable dt = bi.GetNoOfPersonsInvolved(); this.ddlNoOfPersons.DataSource = dt; DataRow row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlNoOfPersons.DataTextField = "FieldName"; this.ddlNoOfPersons.DataValueField = "FieldValue"; this.ddlNoOfPersons.DataBind(); this.ddlNoOfPersons.Text = ""; dt.Clear(); // get dropout rate types from the database and bind to our dropdown (PHET1, PHET2, PHOM) dt = bi.GetDropOutTypes(); this.ddlDropOutType.DataSource = dt; row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlDropOutType.DataTextField = "FieldName"; this.ddlDropOutType.DataValueField = "FieldValue"; this.ddlDropOutType.DataBind(); this.ddlDropOutType.Text = ""; dt.Clear(); // get drop out options from the database and bind to our dropdown // these are the rate points, so 6.25pg, 12.5pg, 25pg, etc. and also refered to as DNA template amount in the UI dt = bi.getDropOutOptions(); this.ddlDropOutOption.DataSource = dt; row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlDropOutOption.DataTextField = "FieldName"; this.ddlDropOutOption.DataValueField = "FieldValue"; this.ddlDropOutOption.DataBind(); this.ddlDropOutOption.Text = ""; dt.Clear(); // get the labk kits from the database and bind to our dropdown dt = bi.GetLabKits(); this.gvLabKits.DataSource = dt; this.gvLabKits.DataBind(); dt.Clear(); // get the list of loci from the database and bind to our dropdown dt = bi.GetLocus(Guid.Empty); this.ddlLocus.DataSource = dt; row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlLocus.DataTextField = "FieldName"; this.ddlLocus.DataValueField = "FieldValue"; this.ddlLocus.DataBind(); this.ddlLocus.Text = ""; this.ddlDeducible.Text = ""; } }
public DataTable GetEvidenceFromFile(FileUpload fuPopulationUpload, string path) { string strErrMsg = string.Empty; DataTable dt = null; DataTable loci = bi.GetLocus(Guid.Parse(LabKitID)); try { string connString = string.Empty; string extension = string.Empty; // determine file type if (fuPopulationUpload.FileName.EndsWith(".csv")) { extension = ".csv"; } else if (fuPopulationUpload.FileName.EndsWith(".xlsx")) { extension = ".xlsx"; } else if (fuPopulationUpload.FileName.EndsWith(".xls")) { extension = ".xls"; } // save file string filename = DateTime.Now.Ticks.ToString() + extension; fuPopulationUpload.SaveAs(path + filename); // create connection string switch (extension) { case ".csv": connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='text;HDR=YES;FMT=CSVDelimited';"); break; case ".xlsx": connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + filename + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2';"); break; case ".xls": connString = string.Format("Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + path + filename + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"); break; } // open the file using (OleDbConnection cn = new OleDbConnection(connString)) { cn.Open(); string query = string.Empty; string sheetName = "Sheet1$"; DataTable dtTables = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); if (dtTables.Rows.Count == 1) { sheetName = (string)dtTables.Rows[0]["TABLE_NAME"]; } switch (extension) { case ".csv": query = "SELECT * FROM [" + filename + "]"; break; case ".xlsx": query = "SELECT * FROM [" + sheetName + "]"; break; case ".xls": query = "SELECT * FROM [" + sheetName + "]"; break; } // read file OleDbDataAdapter adapter = new OleDbDataAdapter(query, cn); dt = new DataTable(); adapter.Fill(dt); if (!dt.Columns.Contains("ID") || !dt.Columns.Contains("D8S1179") || !dt.Columns.Contains("D21S11") || !dt.Columns.Contains("D7S820") || !dt.Columns.Contains("CSF1PO") || !dt.Columns.Contains("D3S1358") || !dt.Columns.Contains("TH01") || !dt.Columns.Contains("D13S317") || !dt.Columns.Contains("D16S539") || !dt.Columns.Contains("D2S1338") || !dt.Columns.Contains("D19S433") || !dt.Columns.Contains("vWA") || !dt.Columns.Contains("TPOX") || !dt.Columns.Contains("D18S51") || !dt.Columns.Contains("D5S818") || !dt.Columns.Contains("FGA")) { MessageBox.Show("The uploaded file does not contain one of the required locus columns or the ID column or the ethnicity column."); return(null); } // remove blank rows foreach (DataRow dr in dt.Select("ID IS NULL")) { dt.Rows.Remove(dr); } // remove unneeded columns List <string> removeColumns = new List <string>(); foreach (DataColumn dc in dt.Columns) { if (dc.ColumnName.ToUpper() != "ID" && loci.Select("FieldName='" + dc.ColumnName + "'").Length == 0) { removeColumns.Add(dc.ColumnName); } } foreach (string colName in removeColumns) { dt.Columns.Remove(colName); } foreach (DataRow dr in dt.Rows) { // break on cells where there are no commas between values foreach (DataColumn dc in dt.Columns) { if (dc.ColumnName.ToUpper() != "ID") { string[] data = dr[dc.ColumnName].ToString().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); for (int i = 0; i < data.Length; i++) { data[i] = data[i].Trim(); } StringBuilder sb = new StringBuilder(); for (int i = 0; i < data.Length; i++) { sb.Append(data[i]); if (i != data.Length - 1) { sb.Append(","); } } dr[dc.ColumnName] = sb.ToString(); } } } // order the columns in the proper order dt.Columns["ID"].SetOrdinal(dt.Columns.Count - 1); foreach (DataRow dr in loci.Rows) { if (dt.Columns.Contains(dr["FieldName"].ToString())) { dt.Columns[dr["FieldName"].ToString()].SetOrdinal(Convert.ToInt32(dr["FieldValue"]) - 1); } } // uppercase all the columns except vWA which needs a special case foreach (DataColumn dc in dt.Columns) { if (dc.ColumnName.ToUpper() != "VWA") { dc.ColumnName = dc.ColumnName.ToUpper(); } else { dc.ColumnName = "vWA"; } } } } catch (Exception ex) { // handle errors MessageBox.Show("There was an error reading the uploaded file. Please try uploading an Excel or CSV file in the correct format."); return(null); } return(dt); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataTable dt = bi.GetNoOfPersonsInvolved(); this.ddlNoOfPersons.DataSource = dt; DataRow row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlNoOfPersons.DataTextField = "FieldName"; this.ddlNoOfPersons.DataValueField = "FieldValue"; this.ddlNoOfPersons.DataBind(); this.ddlNoOfPersons.Text = ""; dt.Clear(); dt = bi.GetDropOutTypes(); this.ddlDropOutType.DataSource = dt; row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlDropOutType.DataTextField = "FieldName"; this.ddlDropOutType.DataValueField = "FieldValue"; this.ddlDropOutType.DataBind(); this.ddlDropOutType.Text = ""; dt.Clear(); dt = bi.getDropOutOptions(); this.ddlDropOutOption.DataSource = dt; row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlDropOutOption.DataTextField = "FieldName"; this.ddlDropOutOption.DataValueField = "FieldValue"; this.ddlDropOutOption.DataBind(); this.ddlDropOutOption.Text = ""; dt.Clear(); dt = bi.GetLocus(Guid.Empty); this.ddlLocus.DataSource = dt; row = dt.NewRow(); row["FieldName"] = DBNull.Value; row["FieldValue"] = DBNull.Value; dt.Rows.Add(row); this.ddlLocus.DataTextField = "FieldName"; this.ddlLocus.DataValueField = "FieldValue"; this.ddlLocus.DataBind(); this.ddlLocus.Text = ""; this.ddlDeducible.Text = ""; this.ddlDegradationType.Text = ""; dt.Clear(); dt = bi.GetDegradedDropOutData(); this.gvDropOut.DataSource = dt; this.gvDropOut.DataBind(); } }