private void timer1_Tick(object sender, EventArgs e) { timer1.Enabled = false; SQLiteConnection cnn = GGKUtilLib.getDBConnection(); dataGridView1.Rows.Clear(); dataGridView2.Rows.Clear(); SQLiteCommand query = new SQLiteCommand(select_sql, cnn); SQLiteDataReader reader = query.ExecuteReader(); DataTable dt1 = null; DataTable dt2 = null; dt1 = new DataTable(); dt2 = new DataTable(); dt1.Load(reader); reader = query.ExecuteReader(); dt2.Load(reader); query.Dispose(); cnn.Dispose(); dataGridView1.DataSource = dt1; dataGridView2.DataSource = dt2; dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridView2.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridView2.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; }
private void bwDelete_DoWork(object sender, DoWorkEventArgs e) { DataGridView dgv = (DataGridView)e.Argument; SQLiteConnection conn = GGKUtilLib.getDBConnection(); List <DataGridViewRow> rows = new List <DataGridViewRow>(); using (SQLiteTransaction trans = conn.BeginTransaction()) { foreach (DataGridViewRow row in dgv.SelectedRows) { SQLiteCommand upCmd = new SQLiteCommand("DELETE FROM kit_master WHERE kit_no=@kit_no", conn); upCmd.Parameters.AddWithValue("@kit_no", row.Cells[0].Value.ToString()); upCmd.ExecuteNonQuery(); rows.Add(row); } // trans.Commit(); } this.Invoke(new MethodInvoker(delegate { foreach (DataGridViewRow row in rows) { dgvEditKit.Rows.Remove(row); } })); }
public void Save() { GGKUtilLib.setStatus("Saving ..."); SQLiteConnection conn = GGKUtilLib.getDBConnection(); string sex = ""; string location = ""; using (SQLiteTransaction trans = conn.BeginTransaction()) { foreach (DataGridViewRow row in dgvEditKit.Rows) { SQLiteCommand upCmd = new SQLiteCommand("UPDATE kit_master set name=@name, sex=@sex, disabled=@disabled, x=@x, y=@y WHERE kit_no=@kit_no", conn); upCmd.Parameters.AddWithValue("@name", row.Cells[1].Value.ToString()); sex = row.Cells[2].Value.ToString(); if (sex == "Unknown") { upCmd.Parameters.AddWithValue("@sex", "U"); } else if (sex == "Male") { upCmd.Parameters.AddWithValue("@sex", "M"); } else if (sex == "Female") { upCmd.Parameters.AddWithValue("@sex", "F"); } if (((bool)row.Cells[3].Value)) { upCmd.Parameters.AddWithValue("@disabled", "1"); } else { upCmd.Parameters.AddWithValue("@disabled", "0"); } location = row.Cells[4].Value.ToString(); if (location == "Unknown") { upCmd.Parameters.AddWithValue("@x", "0"); upCmd.Parameters.AddWithValue("@y", "0"); } else { upCmd.Parameters.AddWithValue("@x", location.Split(new char[] { ':' })[0]); upCmd.Parameters.AddWithValue("@y", location.Split(new char[] { ':' })[1]); } upCmd.Parameters.AddWithValue("@kit_no", row.Cells[0].Value.ToString()); upCmd.ExecuteNonQuery(); } // trans.Commit(); } GGKUtilLib.setStatus("Saved."); }
private void timer1_Tick(object sender, EventArgs e) { timer1.Enabled = false; DataGridViewCellStyle gray = new DataGridViewCellStyle(); gray.ForeColor = Color.LightGray; SQLiteConnection cnn = GGKUtilLib.getDBConnection(); dgvEditKit.Rows.Clear(); SQLiteCommand query = new SQLiteCommand(@"SELECT kit_no,name,sex,disabled,coalesce(x,0),coalesce(y,0),last_modified FROM kit_master WHERE reference=0 order by last_modified DESC", cnn); SQLiteDataReader reader = query.ExecuteReader(); string sex = "U"; string xy = null; while (reader.Read()) { int new_idx = dgvEditKit.Rows.Add(); DataGridViewRow row = dgvEditKit.Rows[new_idx]; row.Cells[0].Value = reader.GetString(0); row.Cells[1].Value = reader.GetString(1); sex = reader.GetString(2); if (sex == "U") { row.Cells[2].Value = "Unknown"; } else if (sex == "M") { row.Cells[2].Value = "Male"; } else if (sex == "F") { row.Cells[2].Value = "Female"; } if (reader.GetInt16(3) == 1) { row.Cells[3].Value = true; } else { row.Cells[3].Value = false; } xy = reader.GetInt16(4).ToString() + ":" + reader.GetInt16(5).ToString(); if (xy == "0:0") { xy = "Unknown"; } row.Cells[4].Value = xy; } query.Dispose(); cnn.Dispose(); }
public static void _addParameter(string key, string value, string desc, string read_only) { SQLiteConnection cnn = GGKUtilLib.getDBConnection(); SQLiteCommand upCmd = new SQLiteCommand(@"INSERT into ggk_settings(key,value,description,readonly) VALUES (@key,@value,@desc,@readonly)", cnn); upCmd.Parameters.AddWithValue("@key", key); upCmd.Parameters.AddWithValue("@value", value); upCmd.Parameters.AddWithValue("@desc", desc); upCmd.Parameters.AddWithValue("@readonly", read_only); upCmd.ExecuteNonQuery(); upCmd.Dispose(); }
public static void saveParameterValue(string key, string value) { SQLiteConnection cnn = GGKUtilLib.getDBConnection(); SQLiteCommand upCmd = new SQLiteCommand(@"UPDATE ggk_settings set value=@value WHERE key=@key", cnn); upCmd.Parameters.AddWithValue("@value", value); upCmd.Parameters.AddWithValue("@key", key); upCmd.ExecuteNonQuery(); upCmd.Dispose(); // settings[key][0] = value; }
private void timer1_Tick(object sender, EventArgs e) { timer1.Enabled = false; disabled.Clear(); DataGridViewCellStyle gray = new DataGridViewCellStyle(); gray.ForeColor = Color.LightGray; SQLiteConnection cnn = GGKUtilLib.getDBConnection(); dataGridViewOpenKit.Rows.Clear(); string hide = GGKSettings.getParameterValue("Admixture.ReferencePopulations.Hide"); SQLiteCommand query = new SQLiteCommand(select_sql, cnn); SQLiteDataReader reader = query.ExecuteReader(); while (reader.Read()) { int new_idx = dataGridViewOpenKit.Rows.Add(); DataGridViewRow row = dataGridViewOpenKit.Rows[new_idx]; row.Cells[0].Value = reader.GetString(0); row.Cells[1].Value = reader.GetString(1); row.Cells[2].Value = reader.GetString(3); if (reader.GetInt16(2) == 1) { row.DefaultCellStyle = gray; disabled.Add(reader.GetString(0)); } } query.Dispose(); cnn.Dispose(); if (dataGridViewOpenKit.Rows.Count > 0) { dataGridViewOpenKit.CurrentCell = dataGridViewOpenKit.Rows[0].Cells[0]; kitLbl.Text = dataGridViewOpenKit.SelectedRows[0].Cells[0].Value.ToString(); } if (dataGridViewOpenKit.Rows.Count == 0) { MessageBox.Show("There are no kits available to open.", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); this.Close(); } }
private void bwDelete_DoWork(object sender, DoWorkEventArgs e) { string kit_no = (string)e.Argument; SQLiteConnection cnn = GGKUtilLib.getDBConnection(); try { SQLiteCommand upCmd = new SQLiteCommand(@"DELETE from kit_master where kit_no=@kit_no", cnn); upCmd.Parameters.AddWithValue("@kit_no", kit_no); upCmd.ExecuteNonQuery(); this.Invoke(new MethodInvoker(delegate { this.Close(); })); } catch (Exception err) { this.Invoke(new MethodInvoker(delegate { MessageBox.Show("Unable to delete kit " + kit_no + "\r\nTechnical Error: " + err.Message); GGKUtilLib.setStatus("Unable to delete kit " + kit_no); })); } }
public void Disable() { GGKUtilLib.setStatus("Disabling ..."); SQLiteConnection conn = GGKUtilLib.getDBConnection(); using (SQLiteTransaction trans = conn.BeginTransaction()) { foreach (DataGridViewRow row in dgvEditKit.SelectedRows) { SQLiteCommand upCmd = new SQLiteCommand("UPDATE kit_master set disabled=@disabled WHERE kit_no=@kit_no", conn); upCmd.Parameters.AddWithValue("@name", row.Cells[1].Value.ToString()); upCmd.Parameters.AddWithValue("@disabled", "1"); upCmd.Parameters.AddWithValue("@kit_no", row.Cells[0].Value.ToString()); upCmd.ExecuteNonQuery(); row.Cells[3].Value = true; } // trans.Commit(); } GGKUtilLib.setStatus("Disabled."); }
public void Enable() { string kit_no = txtKit.Text; SQLiteConnection cnn = GGKUtilLib.getDBConnection(); using (SQLiteTransaction dbTrans = cnn.BeginTransaction()) { try { SQLiteCommand upCmd = new SQLiteCommand(@"UPDATE kit_master set disabled=0 where kit_no=@kit_no", cnn); upCmd.Parameters.AddWithValue("@kit_no", kit_no); upCmd.ExecuteNonQuery(); dbTrans.Commit(); doControlActivities(false); } catch (Exception err) { dbTrans.Rollback(); MessageBox.Show("Unable to enable kit " + kit_no + "\r\nTechnical Error: " + err.Message); GGKUtilLib.setStatus("Unable to enable kit " + kit_no); } } }
private void bwPhaseVisualizer_DoWork(object sender, DoWorkEventArgs e) { DataTable dt_existing = GGKUtilLib.QueryDB("select segment_image,segment_xml from cmp_phased where phased_kit='" + phased_kit + "' and match_kit='" + unphased_kit + "' and chromosome='" + chromosome + "' and start_position=" + start_position + " and end_position=" + end_position); if (dt_existing.Rows.Count > 0) { object[] o = dt_existing.Rows[0].ItemArray; string xml = o[1].ToString(); dt = new DataTable(); MemoryStream ms = new MemoryStream(Encoding.ASCII.GetBytes(xml)); dt.ReadXml(ms); this.Invoke(new MethodInvoker(delegate { dgvSegment.DataSource = dt; dgvSegment.Columns[0].HeaderText = "Position"; dgvSegment.Columns[1].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(unphased_kit)); dgvSegment.Columns[2].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(phased_kit)) + " (Paternal)"; dgvSegment.Columns[3].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(phased_kit)) + " (Maternal)"; dgvSegment.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvSegment.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvSegment.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvSegment.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvSegment.Columns[0].ReadOnly = true; dgvSegment.Columns[1].ReadOnly = true; dgvSegment.Columns[2].ReadOnly = true; dgvSegment.Columns[3].ReadOnly = true; })); byte[] image_array = (byte[])o[0]; Image img = GGKUtilLib.byteArrayToImage(image_array); this.Invoke(new MethodInvoker(delegate { original = new Bitmap(img, 600, 150); pbSegment.Image = original; })); } else { dt = GGKUtilLib.QueryDB("select a.position,a.genotype,p.paternal_genotype,p.maternal_genotype from kit_autosomal a,kit_phased p where a.kit_no='" + unphased_kit + "' and a.position>" + start_position + " and a.position<" + end_position + " and a.chromosome='" + chromosome + "' and p.rsid=a.rsid and p.kit_no='" + phased_kit + "' order by a.position"); if (dt.Rows.Count > 0) { if (this.IsHandleCreated) { this.Invoke(new MethodInvoker(delegate { dgvSegment.DataSource = dt; dgvSegment.Columns[0].HeaderText = "Position"; dgvSegment.Columns[1].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(unphased_kit)); dgvSegment.Columns[2].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(phased_kit)) + " (Paternal)"; dgvSegment.Columns[3].HeaderText = GGKUtilLib.sqlSafe(GGKUtilLib.getKitName(phased_kit)) + " (Maternal)"; dgvSegment.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvSegment.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvSegment.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvSegment.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvSegment.Columns[0].ReadOnly = true; dgvSegment.Columns[1].ReadOnly = true; dgvSegment.Columns[2].ReadOnly = true; dgvSegment.Columns[3].ReadOnly = true; })); Image img = GGKUtilLib.getPhasedSegmentImage(dt, chromosome); this.Invoke(new MethodInvoker(delegate { original = img; pbSegment.Image = img; })); } dt.TableName = "cmp_phased"; StringBuilder sb = new StringBuilder(); StringWriter w = new StringWriter(sb); dt.WriteXml(w, XmlWriteMode.WriteSchema); string segment_xml = sb.ToString(); SQLiteConnection conn = GGKUtilLib.getDBConnection(); SQLiteCommand cmd = new SQLiteCommand("INSERT INTO cmp_phased(phased_kit,match_kit,chromosome,start_position,end_position,segment_image,segment_xml) VALUES (@phased_kit,@match_kit,@chromosome,@start_position,@end_position,@segment_image,@segment_xml)", conn); cmd.Parameters.AddWithValue("@phased_kit", phased_kit); cmd.Parameters.AddWithValue("@match_kit", unphased_kit); cmd.Parameters.AddWithValue("@chromosome", chromosome); cmd.Parameters.AddWithValue("@start_position", start_position); cmd.Parameters.AddWithValue("@end_position", end_position); byte[] image_bytes = GGKUtilLib.imageToByteArray(original); cmd.Parameters.Add("@segment_image", DbType.Binary, image_bytes.Length).Value = image_bytes; cmd.Parameters.AddWithValue("@segment_xml", segment_xml); cmd.ExecuteNonQuery(); conn.Close(); } } }
private void bwPhaseVisualizer_DoWork(object sender, DoWorkEventArgs e) { DataTable dt2 = GGKUtilLib.QueryDB("select distinct kit_no from kit_phased"); string phased_kit = null; string unphased_kit = null; string chromosome = null; string start_position = null; string end_position = null; int percent = 0; foreach (DataRow row in dt2.Rows) { if (bwPhaseVisualizer.CancellationPending) { break; } phased_kit = row.ItemArray[0].ToString(); percent = dt2.Rows.IndexOf(row) * 100 / dt2.Rows.Count; bwPhaseVisualizer.ReportProgress(percent, "Phased Segments for kit #" + phased_kit + " (" + GGKUtilLib.getKitName(phased_kit) + ") - Processing ..."); DataTable dt3 = GGKUtilLib.QueryDB("select unphased_kit,chromosome,start_position,end_position FROM (select kit1'unphased_kit',chromosome,start_position,end_position from cmp_autosomal where kit2='" + phased_kit + "' UNION select kit2'unphased_kit',chromosome,start_position,end_position from cmp_autosomal where kit1='" + phased_kit + "') order by cast(chromosome as integer),start_position"); foreach (DataRow row3 in dt3.Rows) { if (bwPhaseVisualizer.CancellationPending) { break; } unphased_kit = row3.ItemArray[0].ToString(); chromosome = row3.ItemArray[1].ToString(); start_position = row3.ItemArray[2].ToString(); end_position = row3.ItemArray[3].ToString(); DataTable exists = GGKUtilLib.QueryDB("select * from cmp_phased where phased_kit='" + phased_kit + "' and match_kit='" + unphased_kit + "' and chromosome='" + chromosome + "' and start_position=" + start_position + " and end_position=" + end_position); if (exists.Rows.Count > 0) { //already exists... if (!redo_visual) { bwPhaseVisualizer.ReportProgress(percent, "Segment [" + GGKUtilLib.getKitName(phased_kit) + ":" + GGKUtilLib.getKitName(unphased_kit) + "] Chr " + chromosome + ": " + start_position + "-" + end_position + ", Already Processed. Skipping ..."); continue; } else { GGKUtilLib.UpdateDB("DELETE from cmp_phased where phased_kit='" + phased_kit + "'"); } } bwPhaseVisualizer.ReportProgress(percent, "Segment [" + GGKUtilLib.getKitName(phased_kit) + ":" + GGKUtilLib.getKitName(unphased_kit) + "] Chr " + chromosome + ": " + start_position + "-" + end_position + ", Processing ..."); DataTable dt = GGKUtilLib.QueryDB("select a.position,a.genotype,p.paternal_genotype,p.maternal_genotype from kit_autosomal a,kit_phased p where a.kit_no='" + unphased_kit + "' and a.position>" + start_position + " and a.position<" + end_position + " and a.chromosome='" + chromosome + "' and p.rsid=a.rsid and p.kit_no='" + phased_kit + "' order by a.position"); if (dt.Rows.Count > 0) { if (bwPhaseVisualizer.CancellationPending) { break; } Image img = GGKUtilLib.getPhasedSegmentImage(dt, chromosome); dt.TableName = "cmp_phased"; StringBuilder sb = new StringBuilder(); StringWriter w = new StringWriter(sb); dt.WriteXml(w, XmlWriteMode.WriteSchema); string segment_xml = sb.ToString(); SQLiteConnection conn = GGKUtilLib.getDBConnection(); SQLiteCommand cmd = new SQLiteCommand("INSERT INTO cmp_phased(phased_kit,match_kit,chromosome,start_position,end_position,segment_image,segment_xml) VALUES (@phased_kit,@match_kit,@chromosome,@start_position,@end_position,@segment_image,@segment_xml)", conn); cmd.Parameters.AddWithValue("@phased_kit", phased_kit); cmd.Parameters.AddWithValue("@match_kit", unphased_kit); cmd.Parameters.AddWithValue("@chromosome", chromosome); cmd.Parameters.AddWithValue("@start_position", start_position); cmd.Parameters.AddWithValue("@end_position", end_position); byte[] image_bytes = GGKUtilLib.imageToByteArray(img); cmd.Parameters.Add("@segment_image", DbType.Binary, image_bytes.Length).Value = image_bytes; cmd.Parameters.AddWithValue("@segment_xml", segment_xml); cmd.ExecuteNonQuery(); conn.Close(); } } } }
private void bwPhasing_DoWork(object sender, DoWorkEventArgs e) { if (father_kit != "Unknown" && mother_kit != "Unknown") { dt = GGKUtilLib.QueryDB("SELECT c.[rsid]'RSID',c.[chromosome]'Chromosome',c.[position]'Position',c.[genotype]\"Child\",COALESCE(f.[genotype],'--')\"Father\",COALESCE(m.[genotype],'--')\"Mother\",''\"Phased Paternal\",''\"Phased Maternal\" FROM kit_autosomal c left outer join kit_autosomal f,kit_autosomal m on f.rsid=c.rsid AND m.rsid=c.rsid WHERE c.kit_no='" + child_kit + "' AND f.kit_no='" + father_kit + "' AND m.[kit_no]='" + mother_kit + "' ORDER BY cast(c.chromosome as integer),c.position"); } else if (father_kit != "Unknown" && mother_kit == "Unknown") { dt = GGKUtilLib.QueryDB("SELECT c.[rsid]'RSID',c.[chromosome]'Chromosome',c.[position]'Position',c.[genotype]\"Child\",COALESCE(f.[genotype],'--')\"Father\",'--'\"Mother\",''\"Phased Paternal\",''\"Phased Maternal\" FROM kit_autosomal c left outer join kit_autosomal f on f.rsid=c.rsid WHERE c.kit_no='" + child_kit + "' AND f.kit_no='" + father_kit + "' ORDER BY cast(c.chromosome as integer),c.position"); } else if (father_kit == "Unknown" && mother_kit != "Unknown") { dt = GGKUtilLib.QueryDB("SELECT c.[rsid]'RSID',c.[chromosome]'Chromosome',c.[position]'Position',c.[genotype]\"Child\",'--'\"Father\",COALESCE(m.[genotype],'--')\"Mother\" ,''\"Phased Paternal\",''\"Phased Maternal\" FROM kit_autosomal c left outer join kit_autosomal m on m.rsid=c.rsid WHERE c.kit_no='" + child_kit + "' AND m.kit_no='" + mother_kit + "' ORDER BY cast(c.chromosome as integer),c.position"); } // after phasing... string child = null; string father = null; string mother = null; string phased_paternal = null; string phased_maternal = null; object[] o = null; string nc = "N"; bool amb = false; foreach (DataRow row in dt.Rows) { o = row.ItemArray; phased_paternal = ""; phased_maternal = ""; child = o[3].ToString(); father = o[4].ToString(); mother = o[5].ToString(); if (child.Length == 1) { child = child + child; } //check if ((father.Replace(child[0].ToString(), "").Replace(child[1].ToString(), "") == father || mother.Replace(child[0].ToString(), "").Replace(child[1].ToString(), "") == mother) && o[1].ToString() != "X" && father != "--" && mother != "--" && child != "--") { mutatedRow.Add(dt.Rows.IndexOf(row)); } amb = false; if (father == child && child[0] != child[1] && mother == "--") { amb = true; } else if (mother == child && child[0] != child[1] && father == "--") { amb = true; } else if (father == child && child[0] != child[1] && mother == child) { amb = true; } if (amb) { ambiguousRow.Add(dt.Rows.IndexOf(row)); nc = getNucleotideCode(child[0].ToString(), child[1].ToString()); row.SetField(6, nc); row.SetField(7, nc); phased_paternal = nc; phased_maternal = nc; continue; } if (child == "--" || child == "??") { if (father[0] == father[1] && father == mother && o[1].ToString() != "X") { row.SetField(6, father[0].ToString()); row.SetField(7, father[0].ToString()); phased_paternal = father[0].ToString(); phased_maternal = father[0].ToString(); continue; } } if (male && o[1].ToString() == "X") { child = child[0].ToString(); if (child == "-" && mother != "--") { row.SetField(6, ""); row.SetField(7, mother[0].ToString()); phased_paternal = ""; phased_maternal = mother[0].ToString(); continue; } } else { if (child[0] == child[1] && child[0] != '-' && child[0] != '?') { row.SetField(6, child[0].ToString()); row.SetField(7, child[0].ToString()); phased_paternal = child[0].ToString(); phased_maternal = child[0].ToString(); continue; } } if (o[1].ToString() != "X") { autosomalSingleSNPPhase(child, father, mother, row, ref phased_paternal, ref phased_maternal); } else if (o[1].ToString() == "X") { if (male) { row.SetField(3, child[0].ToString()); row.SetField(4, ""); row.SetField(6, ""); row.SetField(7, child[0].ToString()); phased_paternal = ""; phased_maternal = child[0].ToString(); } else { autosomalSingleSNPPhase(child, father, mother, row, ref phased_paternal, ref phased_maternal); } } if (phased_paternal == "" && phased_maternal != "") { phased_paternal = child.Replace(phased_maternal, ""); if (phased_paternal.Length > 0) { phased_paternal = phased_paternal[0].ToString(); } row.SetField(6, phased_paternal); } if (phased_maternal == "" && phased_paternal != "") { phased_maternal = child.Replace(phased_paternal, ""); if (phased_maternal.Length > 0) { phased_maternal = phased_maternal[0].ToString(); } row.SetField(7, phased_maternal); } } // save to kit_phased string rsid = null; string chromosome = null; string position = null; bwPhasing.ReportProgress(-1, "Saving Phased Kit " + child_kit + " ..."); SQLiteConnection conn = GGKUtilLib.getDBConnection(); SQLiteCommand cmd = new SQLiteCommand("DELETE FROM kit_phased where kit_no=@kit_no", conn); cmd.Parameters.AddWithValue("@kit_no", child_kit); cmd.ExecuteNonQuery(); using (SQLiteTransaction trans = conn.BeginTransaction()) { foreach (DataRow row in dt.Rows) { o = row.ItemArray; rsid = o[0].ToString(); chromosome = o[1].ToString(); position = o[2].ToString(); phased_paternal = o[6].ToString(); phased_maternal = o[7].ToString(); cmd = new SQLiteCommand("INSERT OR REPLACE INTO kit_phased(kit_no,rsid,chromosome,position,paternal_genotype,maternal_genotype,paternal_kit_no,maternal_kit_no) VALUES(@kit_no,@rsid,@chromosome,@position,@paternal_genotype,@maternal_genotype,@paternal_kit_no,@maternal_kit_no)", conn); cmd.Parameters.AddWithValue("@kit_no", child_kit); cmd.Parameters.AddWithValue("@rsid", rsid); cmd.Parameters.AddWithValue("@chromosome", chromosome); cmd.Parameters.AddWithValue("@position", position); cmd.Parameters.AddWithValue("@paternal_genotype", phased_paternal); cmd.Parameters.AddWithValue("@maternal_genotype", phased_maternal); if (father_kit == "Unknown") { cmd.Parameters.AddWithValue("@paternal_kit_no", ""); } else { cmd.Parameters.AddWithValue("@paternal_kit_no", father_kit); } if (mother_kit == "Unknown") { cmd.Parameters.AddWithValue("@maternal_kit_no", ""); } else { cmd.Parameters.AddWithValue("@maternal_kit_no", mother_kit); } cmd.ExecuteNonQuery(); } trans.Commit(); } }
private void bwPopuate_DoWork(object sender, DoWorkEventArgs e) { string kit = (string)e.Argument; SQLiteConnection cnn = GGKUtilLib.getDBConnection(); //kit master SQLiteCommand query = new SQLiteCommand(@"SELECT name, sex from kit_master where kit_no=@kit_no", cnn); query.Parameters.AddWithValue("@kit_no", kit); SQLiteDataReader reader = query.ExecuteReader(); string sex = "U"; if (reader.Read()) { this.Invoke(new MethodInvoker(delegate { txtName.Text = reader.GetString(0); sex = reader.GetString(1); if (sex == "U") { cbSex.SelectedIndex = 0; } else if (sex == "M") { cbSex.SelectedIndex = 1; } else if (sex == "F") { cbSex.SelectedIndex = 2; } })); } reader.Close(); query.Dispose(); // kit autosomal - RSID,Chromosome,Position,Genotypoe query = new SQLiteCommand(@"SELECT rsid 'RSID',chromosome 'Chromosome',position 'Position',genotype 'Genotype' from kit_autosomal where kit_no=@kit_no order by chromosome,position", cnn); query.Parameters.AddWithValue("@kit_no", kit); reader = query.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(reader); this.Invoke(new MethodInvoker(delegate { dataGridViewAutosomal.Rows.Clear(); dataGridViewAutosomal.Columns.Clear(); dataGridViewAutosomal.DataSource = dt; dataGridViewAutosomal.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridViewAutosomal.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridViewAutosomal.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridViewAutosomal.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; ((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[0]).MaxInputLength = 20; ((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[1]).MaxInputLength = 2; ((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[2]).MaxInputLength = 15; ((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[3]).MaxInputLength = 2; })); reader.Close(); query.Dispose(); // kit - ysnp query = new SQLiteCommand(@"SELECT ysnps from kit_ysnps where kit_no=@kit_no", cnn); query.Parameters.AddWithValue("@kit_no", kit); reader = query.ExecuteReader(); if (reader.Read()) { ysnps = reader.GetString(0); } reader.Close(); query.Dispose(); this.Invoke(new MethodInvoker(delegate { textBoxYDNA.Text = ysnps; })); // kit - ystr query = new SQLiteCommand(@"SELECT marker,value from kit_ystr where kit_no=@kit_no", cnn); query.Parameters.AddWithValue("@kit_no", kit); reader = query.ExecuteReader(); string marker = null; string value = null; Dictionary <string, string> ystr_dict = new Dictionary <string, string>(); while (reader.Read()) { marker = reader.GetString(0); value = reader.GetString(1); ystr_dict.Add(marker, value); } reader.Close(); query.Dispose(); this.Invoke(new MethodInvoker(delegate { // //YDNA12 for (int i = 0; i < ydna12.Length; i++) { if (ystr_dict.ContainsKey(ydna12[i])) { dgvy12.Rows[i].Cells[1].Value = ystr_dict[ydna12[i]]; ystr_dict.Remove(ydna12[i]); } } //YDNA25 for (int i = 0; i < ydna25.Length; i++) { if (ystr_dict.ContainsKey(ydna25[i])) { dgvy25.Rows[i].Cells[1].Value = ystr_dict[ydna25[i]]; ystr_dict.Remove(ydna25[i]); } } //YDNA37 for (int i = 0; i < ydna37.Length; i++) { if (ystr_dict.ContainsKey(ydna37[i])) { dgvy37.Rows[i].Cells[1].Value = ystr_dict[ydna37[i]]; ystr_dict.Remove(ydna37[i]); } } //YDNA67 for (int i = 0; i < ydna67.Length; i++) { if (ystr_dict.ContainsKey(ydna67[i])) { dgvy67.Rows[i].Cells[1].Value = ystr_dict[ydna67[i]]; ystr_dict.Remove(ydna67[i]); } } //YDNA111 for (int i = 0; i < ydna111.Length; i++) { if (ystr_dict.ContainsKey(ydna111[i])) { dgvy111.Rows[i].Cells[1].Value = ystr_dict[ydna111[i]]; ystr_dict.Remove(ydna111[i]); } } //YDNA MISC dgvymisc.Rows.Clear(); foreach (string str in ystr_dict.Keys) { dgvymisc.Rows.Add(new string[] { str, ystr_dict[str] }); } })); // kit - mtdna query = new SQLiteCommand(@"SELECT mutations,fasta from kit_mtdna where kit_no=@kit_no", cnn); query.Parameters.AddWithValue("@kit_no", kit); reader = query.ExecuteReader(); string fasta = null; if (reader.Read()) { this.Invoke(new MethodInvoker(delegate { textBoxMtDNA.Text = reader.GetString(0); fasta = reader.GetString(1); if (fasta != null) { tbFASTA.Text = fasta; } else { tbFASTA.Text = ""; } })); } reader.Close(); query.Dispose(); }
private void bwSave_DoWork(object sender, DoWorkEventArgs e) { save_success = false; Object[] args = (Object[])e.Argument; string kit_no = args[0].ToString(); string name = args[1].ToString(); DataGridViewRowCollection rows = (DataGridViewRowCollection)args[2]; string ysnps_list = args[3].ToString(); string mutations = args[4].ToString(); DataGridViewRowCollection[] dgvy_rows = (DataGridViewRowCollection[])args[5]; string sex = args[6].ToString(); string fasta = args[7].ToString(); SQLiteConnection cnn = GGKUtilLib.getDBConnection(); try { SQLiteCommand upCmd = null; //kit master string kit_name = GGKUtilLib.getKitName(kit_no); if (kit_name == "Unknown") { // new kit upCmd = new SQLiteCommand(@"INSERT OR REPLACE INTO kit_master(kit_no, name, sex)values(@kit_no,@name,@sex)", cnn); upCmd.Parameters.AddWithValue("@kit_no", kit_no); upCmd.Parameters.AddWithValue("@name", name); upCmd.Parameters.AddWithValue("@sex", sex[0].ToString()); upCmd.ExecuteNonQuery(); } else { // kit exists upCmd = new SQLiteCommand(@"UPDATE kit_master SET name=@name, sex=@sex WHERE kit_no=@kit_no", cnn); upCmd.Parameters.AddWithValue("@name", name); upCmd.Parameters.AddWithValue("@sex", sex[0].ToString()); upCmd.Parameters.AddWithValue("@kit_no", kit_no); upCmd.ExecuteNonQuery(); } upCmd.Dispose(); bwSave.ReportProgress(35, "Saving Autosomal data ..."); upCmd = new SQLiteCommand(@"DELETE from kit_autosomal where kit_no=@kit_no", cnn); upCmd.Parameters.AddWithValue("@kit_no", kit_no); upCmd.ExecuteNonQuery(); //kit autosomal upCmd = new SQLiteCommand(@"INSERT OR REPLACE INTO kit_autosomal(kit_no, rsid,chromosome,position,genotype)values(@kit_no,@rsid,@chromosome,@position,@genotype)", cnn); using (var transaction = cnn.BeginTransaction()) { bool incomplete = false; foreach (DataGridViewRow row in rows) { if (row.IsNewRow) { continue; } incomplete = false; for (int c = 0; c < row.Cells.Count; c++) { if (row.Cells[c].Value == null) { incomplete = true; break; } else if (row.Cells[c].Value.ToString().Trim() == "") { incomplete = true; break; } } if (incomplete) { continue; } upCmd.Parameters.AddWithValue("@kit_no", kit_no); upCmd.Parameters.AddWithValue("@rsid", row.Cells[0].Value.ToString()); upCmd.Parameters.AddWithValue("@chromosome", row.Cells[1].Value.ToString()); upCmd.Parameters.AddWithValue("@position", row.Cells[2].Value.ToString()); upCmd.Parameters.AddWithValue("@genotype", row.Cells[3].Value.ToString()); upCmd.ExecuteNonQuery(); } transaction.Commit(); } upCmd.Dispose(); bwSave.ReportProgress(75, "Saving Y-SNPs ..."); if (ysnps_list.Trim() != "") { //kit ysnps upCmd = new SQLiteCommand(@"INSERT OR REPLACE INTO kit_ysnps(kit_no, ysnps) values (@kit_no,@ysnps)", cnn); upCmd.Parameters.AddWithValue("@kit_no", kit_no); upCmd.Parameters.AddWithValue("@ysnps", ysnps_list); upCmd.ExecuteNonQuery(); upCmd.Dispose(); } //kit ystr bwSave.ReportProgress(80, "Saving Y-STR Values ..."); upCmd = new SQLiteCommand(@"DELETE from kit_ystr where kit_no=@kit_no", cnn); upCmd.Parameters.AddWithValue("@kit_no", kit_no); upCmd.ExecuteNonQuery(); upCmd = new SQLiteCommand(@"INSERT OR REPLACE INTO kit_ystr(kit_no, marker, value)values(@kit_no,@marker,@value)", cnn); using (var transaction = cnn.BeginTransaction()) { foreach (DataGridViewRowCollection row_collection in dgvy_rows) { foreach (DataGridViewRow row in row_collection) { if (row.IsNewRow) { continue; } if (row.Cells[1].Value.ToString().Trim() == "") { continue; } upCmd.Parameters.AddWithValue("@kit_no", kit_no); upCmd.Parameters.AddWithValue("@marker", row.Cells[0].Value.ToString()); upCmd.Parameters.AddWithValue("@value", row.Cells[1].Value.ToString()); upCmd.ExecuteNonQuery(); } } transaction.Commit(); } upCmd.Dispose(); //kit mtdna if (mutations.Trim() != "" || fasta.Trim() != "") { bwSave.ReportProgress(90, "Saving mtDNA mutations ..."); upCmd = new SQLiteCommand(@"INSERT OR REPLACE INTO kit_mtdna(kit_no, mutations,fasta)values(@kit_no,@mutations,@fasta)", cnn); upCmd.Parameters.AddWithValue("@kit_no", kit_no); upCmd.Parameters.AddWithValue("@mutations", mutations); upCmd.Parameters.AddWithValue("@fasta", fasta); upCmd.ExecuteNonQuery(); } bwSave.ReportProgress(100, "Saved"); save_success = true; } catch (Exception err) { bwSave.ReportProgress(-1, "Not Saved. Tech Details: " + err.Message); MessageBox.Show("Not Saved. Techical Details: " + err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } cnn.Dispose(); }