/// <summary> /// Handles the upload button event. We save the file to disk, attempt to read a Lab Kit from it, and attempt to save it to the DB /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnLabKitUpload_Click(object sender, EventArgs e) { DataTable dt = null; List <int> labKitLoci = new List <int>(); // make sure we have a lab kit name if (string.IsNullOrEmpty(txtLabKitName.Text)) { MessageBox.Show("Please enter a name for the Lab Kit."); return; } // make sure a file has been uploaded with this post if (string.IsNullOrEmpty(FileUpload1.FileName)) { MessageBox.Show("Please select a Lab Kit file."); return; } try { string connString = string.Empty; string extension = string.Empty; // determine file type if (FileUpload1.FileName.EndsWith(".xlsx")) { extension = ".xlsx"; } else if (FileUpload1.FileName.EndsWith(".xls")) { extension = ".xls"; } // save file string filename = DateTime.Now.Ticks.ToString() + extension; FileUpload1.SaveAs(Server.MapPath("~/Admin/Upload/" + filename)); // create connection string switch (extension) { case ".xlsx": connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/Admin/Upload/") + filename + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1';"); break; case ".xls": connString = string.Format("Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + Server.MapPath("~/Admin/Upload/") + filename + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1';"); break; } // create a dropout rate table DataTable dtDropouts = new DataTable(); dtDropouts.Columns.Add(new DataColumn("LabKitID", typeof(Guid))); dtDropouts.Columns.Add(new DataColumn("LocusID", typeof(int))); dtDropouts.Columns.Add(new DataColumn("TypeID", typeof(int))); dtDropouts.Columns.Add(new DataColumn("DropOptionID", typeof(int))); dtDropouts.Columns.Add(new DataColumn("DropoutRate", typeof(double))); dtDropouts.Columns.Add(new DataColumn("NoOfPersonsInvolvd", typeof(int))); dtDropouts.Columns.Add(new DataColumn("Deducible", typeof(string))); DataTable dtLocus = bi.GetLocus(Guid.Empty); // get drop out rates // for 1 through 4 people, obviously for (int i = 1; i <= 4; i++) { // open the file and read the data from the sheet which we are working (named "1 person" and so on) using (OleDbConnection cn = new OleDbConnection(connString)) { cn.Open(); string query = string.Empty; switch (extension) { case ".xlsx": query = "SELECT * FROM [" + i + " person$]"; break; case ".xls": query = "SELECT * FROM [" + i + " person$]"; break; } // read file OleDbDataAdapter adapter = new OleDbDataAdapter(query, cn); dt = new DataTable(); adapter.Fill(dt); } string deducible = string.Empty; List <DropOutRow> vals = new List <DropOutRow>(); // get loci int rowIdx = 2, colIdx = 2; List <string> locusNames = new List <string>(); while (colIdx < dt.Columns.Count && dt.Rows[rowIdx][colIdx].ToString().Trim() != string.Empty) { locusNames.Add(dt.Rows[rowIdx][colIdx++].ToString().Trim()); } Dictionary <string, int> locusIDMap = new Dictionary <string, int>(); foreach (DataRow dr in dtLocus.Rows) { if (locusNames.Contains(dr["FieldName"].ToString(), StringComparer.Create(CultureInfo.CurrentCulture, true))) { locusIDMap.Add(dr["FieldName"].ToString().ToUpper(), Convert.ToInt32(dr["FieldValue"].ToString())); } } locusNames.Clear(); foreach (string locus in locusIDMap.Keys) { locusNames.Add(locus); } // set up the lab kit loci. we will be sending these to the database later if (labKitLoci.Count == 0) { foreach (string locus in locusIDMap.Keys) { labKitLoci.Add(locusIDMap[locus]); } } // get the deducible, PHET1, for persons i (which will be 1 through 4, see above) int firstRow = 3, firstCol = 2, type = 1, persons = i; deducible = "Yes"; vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons)); // get the deducible, PHET2, for persons i (which will be 1 through 4, see above) firstRow = 14; firstCol = 2; type = 2; persons = i; deducible = "Yes"; vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons)); // get the deducible, PHOM1, for persons i (which will be 1 through 4, see above) firstRow = 25; firstCol = 2; type = 3; persons = i; deducible = "Yes"; vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons)); // if we're doing more than one person, then get the non-deducible too if (i > 1) { // get the non-deducible, PHET1, for persons i (which will be 1 through 4, see above) firstRow = 38; firstCol = 2; type = 1; persons = i; deducible = "No"; vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons)); // get the non-deducible, PHET2, for persons i (which will be 1 through 4, see above) firstRow = 49; firstCol = 2; type = 2; persons = i; deducible = "No"; vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons)); // get the non-deducible, PHOM1, for persons i (which will be 1 through 4, see above) firstRow = 60; firstCol = 2; type = 3; persons = i; deducible = "No"; vals.AddRange(CreateRows(dt, deducible, locusNames, locusIDMap, firstRow, firstCol, type, persons)); } // go through our DropOutRows we created from the Excel file and add them to our table foreach (DropOutRow drow in vals) { DataRow dr = dtDropouts.NewRow(); dr["LocusID"] = drow.LocusID; dr["TypeID"] = drow.TypeID; dr["DropOptionID"] = drow.DropOptionID; dr["DropoutRate"] = drow.DropoutRate; dr["NoOfPersonsInvolvd"] = drow.NoOfPersonsInvolvd; dr["Deducible"] = drow.Deducible; dtDropouts.Rows.Add(dr); } vals.Clear(); } // create a dropins table DataTable dtDropins = new DataTable(); dtDropins.Columns.Add(new DataColumn("LabKitID", typeof(Guid))); dtDropins.Columns.Add(new DataColumn("DropInRateID", typeof(string))); dtDropins.Columns.Add(new DataColumn("Type", typeof(string))); dtDropins.Columns.Add(new DataColumn("DropInRate", typeof(double))); // get drop in rates // open the file and get the data from the dropins table using (OleDbConnection cn = new OleDbConnection(connString)) { cn.Open(); string query = string.Empty; switch (extension) { case ".xlsx": query = "SELECT * FROM [Drop-in$]"; break; case ".xls": query = "SELECT * FROM [Drop-in$]"; break; } // read file OleDbDataAdapter adapter = new OleDbDataAdapter(query, cn); dt = new DataTable(); adapter.Fill(dt); } // go through the specific areas in the file for (int i = 2; i <= 7; i++) { DataRow dr = dtDropins.NewRow(); dr["DropinRateID"] = dt.Rows[i][0]; dr["Type"] = dt.Rows[i][1]; dr["DropInRate"] = dt.Rows[i][2]; dtDropins.Rows.Add(dr); } // save the lab kit name and get back a GUID for it string guid = bi.SaveLabKit(txtLabKitName.Text, ""); Log.Info(Context.User.Identity.Name, Request.FilePath, Session, "Uploaded Lab Kit", guid); // add that GUID to the tables of drop-ins and drop-outs foreach (DataRow dr in dtDropouts.Rows) { dr["LabKitID"] = guid; } foreach (DataRow dr in dtDropins.Rows) { dr["LabKitID"] = guid; } // save the drop-ins and drop-outs to the database bi.SaveLabKitData(guid, dtDropouts, dtDropins, labKitLoci); // refresh the list of lab kits (should now include the new one) DataTable dt2 = bi.GetLabKits(); this.gvLabKits.DataSource = dt2; this.gvLabKits.DataBind(); txtLabKitName.Text = string.Empty; } catch (Exception ex) { // handle errors Log.Error(Context.User.Identity.Name, Request.FilePath, Session, "Error Adding Lab Kit", "File Parsing Error", ex); MessageBox.Show("There was an error reading the uploaded file. Please try uploading an Excel file in the correct format."); return; } }