public void lnkDownload_Click(object sender, EventArgs e) { string strErrMsg = string.Empty; string connString = string.Empty; string extension = ".xlsx"; // save file string filename = DateTime.Now.Ticks.ToString() + extension; string sheetName = "Profiles$"; File.Copy(Server.MapPath("~/Reports/ProfileDownloadBlank.xlsx"), Server.MapPath("~/Admin/Upload/" + filename)); // create connection string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/Admin/Upload/") + filename + ";Extended Properties='Excel 12.0;HDR=No';"); DataTable dt = bi.GetKnown_Profile("Lab Types", Guid.Empty); dt.Columns["ID"].SetOrdinal(0); // open the file using (OleDbConnection cn = new OleDbConnection(connString)) { cn.Open(); string query = string.Empty; DataTable dtTables = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); if (dtTables.Rows.Count == 1) { sheetName = (string)dtTables.Rows[0]["TABLE_NAME"]; } query = "SELECT * FROM [" + sheetName + "]"; OleDbDataAdapter adapter = new OleDbDataAdapter(query, cn); DataTable dt2 = new DataTable(); adapter.Fill(dt2); dt2.Columns["ID"].SetOrdinal(1); int x = 1; foreach (DataColumn dc in dt.Columns) { query = "UPDATE [" + sheetName + "] SET F" + x.ToString() + " = '" + dc.ColumnName + "'"; OleDbCommand cmd = new OleDbCommand(query, cn); cmd.ExecuteNonQuery(); x++; } foreach (DataRow dr in dt.Rows) { OleDbCommand cmd = new OleDbCommand(); string cmdText = "INSERT INTO [" + sheetName + "] ("; for (int i = 1; i <= dt.Columns.Count; i++) { cmdText += "F" + i.ToString() + ","; } cmdText = cmdText.Substring(0, cmdText.Length - 1); cmdText += ") VALUES ("; int j = 1; foreach (DataColumn dc in dt.Columns) { cmdText += "@F" + j.ToString() + ","; j++; cmd.Parameters.AddWithValue("@F" + j.ToString(), dr[dc.ColumnName].ToString()); } cmdText = cmdText.Substring(0, cmdText.Length - 1); cmdText += ")"; cmd.CommandText = cmdText; cmd.Connection = cn; cmd.ExecuteNonQuery(); } // read file query = "SELECT * FROM [" + sheetName + "]"; adapter = new OleDbDataAdapter(query, cn); dt = new DataTable(); adapter.Fill(dt); cn.Close(); GC.Collect(); } // we check for whether we can send out the file by seeing if we can write to the file while (IsFileLocked(new FileInfo(Server.MapPath("~/Admin/Upload/" + filename)))) { GC.Collect(); // do not remove this, the ACE OLEDB has GC issues and invoking the collector causes it to flush its buffer to disk Thread.Sleep(1000); } Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("Content-Disposition", "attachment; filename=LabTypes.xlsx"); Response.BinaryWrite(File.ReadAllBytes(Server.MapPath("~/Admin/Upload/" + filename))); Response.End(); File.Delete(filename); }
private void StartBulkSearch() { // special string signifying a test bool isTest = comparisonData.Comparison == "\tes\t"; string testId = isTest ? comparisonData.FB1 : string.Empty; if (!isTest) { Log.Info(strUserId, comparisonData.ToString(), "Comparison Start", strHp_Head + " / " + strHd_Head); } // frequencies cache DataTable dtFrequencies = new Database().GetFrequencyData(); // create denominator cache Dictionary <string, Dictionary <string, double> > perRaceLocusDenominatorCache = new Dictionary <string, Dictionary <string, double> >(); foreach (DataRow drEth in new Database().getAllEthnics().Rows) { perRaceLocusDenominatorCache.Add(drEth["EthnicName"].ToString(), new Dictionary <string, double>()); } foreach (string key in perRaceLocusDenominatorCache.Keys) { foreach (DataRow drLoc in bi.GetLocusInOrder(comparisonData.LabKitID).Rows) { perRaceLocusDenominatorCache[key].Add(drLoc["LocusName"].ToString().ToUpper(), double.NaN); } } // end create denominator cache // dropout cache Dictionary <int, Dictionary <string, Dictionary <string, float> > > perReplicateDropOutCache = new Dictionary <int, Dictionary <string, Dictionary <string, float> > >(); for (int i = 0; i < 3; i++) { foreach (DataRow drLoc in bi.GetLocusInOrder(comparisonData.LabKitID).Rows) { if (!perReplicateDropOutCache.ContainsKey(i)) { perReplicateDropOutCache.Add(i, new Dictionary <string, Dictionary <string, float> >()); } perReplicateDropOutCache[i].Add(drLoc["LocusName"].ToString().ToUpper(), new Dictionary <string, float>()); } } // end dropout cache // start frequency cache Dictionary <string, Dictionary <string, Dictionary <string, float> > > perRaceLocusFrequencyCache = new Dictionary <string, Dictionary <string, Dictionary <string, float> > >(); foreach (DataRow drEth in new Database().getAllEthnics().Rows) { perRaceLocusFrequencyCache.Add(drEth["EthnicName"].ToString(), new Dictionary <string, Dictionary <string, float> >()); } foreach (string key in perRaceLocusDenominatorCache.Keys) { foreach (DataRow drLoc in bi.GetLocusInOrder(comparisonData.LabKitID).Rows) { perRaceLocusFrequencyCache[key].Add(drLoc["LocusName"].ToString().ToUpper(), new Dictionary <string, float>()); } } // end frequency cache // start bulk permutation caches // all we need to do is send these in as not null Dictionary <string, Dictionary <int, List <Comparison.AllelesPair> > > numeratorPermutationCache = new Dictionary <string, Dictionary <int, List <Comparison.AllelesPair> > >(); Dictionary <string, Dictionary <int, List <Comparison.AllelesPair> > > denominatorPermutationCache = new Dictionary <string, Dictionary <int, List <Comparison.AllelesPair> > >(); // end bulk permutation cache Dictionary <string, Dictionary <string, float> > Results = new Dictionary <string, Dictionary <string, float> >(); // gets "lab types," "population," or "from file" comparison list from the database DataTable dtKnownProfile = bi.GetKnown_Profile(strLab_Popultn_Type, comparisonData.FromFileGuid); for (int i = 0; i < dtKnownProfile.Rows.Count; i++) { try { // reads datarow to a dictionary comparisonData.ComparisonAlleles = ReadBulkComparison(dtKnownProfile, i); FST.Common.Comparison comparison = new Comparison(comparisonData); Dictionary <string, float> Result = comparison.DoCompare( // caches dtFrequencies, perRaceLocusDenominatorCache, perRaceLocusFrequencyCache, perReplicateDropOutCache, numeratorPermutationCache, denominatorPermutationCache ); // if we're testing, write test results. otherwise, add results to the Results dictionary (gets passed to the bulk printer below) if (isTest) { bi.WriteTestResults(testId, dtKnownProfile.Rows[i]["ID"].ToString(), Result["Asian"].ToString(), Result["Black"].ToString(), Result["Caucasian"].ToString(), Result["Hispanic"].ToString()); } else { Results.Add(dtKnownProfile.Rows[i]["ID"].ToString(), Result); } } catch { continue; } } if (!isTest) { strReportFilePath = FSTService.Instance.BulkPrinter.Print(Results, dtKnownProfile, comparisonData); } if (!isTest) { Log.Info(strUserId, comparisonData.ToString(), "Comparison End", strHp_Head + " / " + strHd_Head); } }