private void dgv_KristalRef_CellDoubleClick(object sender, DataGridViewCellEventArgs e) { int r = e.RowIndex; if (r > -1) { try { Kristal kristal = new Kristal(); mdl_Kristal mdl_Kristal = new mdl_Kristal(); mdl_Kristal = kristal.fetchCurrentKristal(Convert.ToInt32(dgv_KristalRef.Rows[r].Cells["Kristal Ref"].Value)); DataTable tlkGrantStage = ds_Project.Tables["tlkGrantStage"]; using (frm_Kristal Kristal = new frm_Kristal(mdl_Kristal)) { Kristal.ShowDialog(); fillProjectsDataSet(); setProjectKristalRef(mdl_CurrentProject.ProjectNumber); } } catch (Exception ex) { MessageBox.Show("Please double click on a data row to see Kristal details." + Environment.NewLine + ex.Message); } } }
/// <summary> /// Takes a Kristal reference and queries [dbo].[tblKristal] for current values. /// </summary> /// <param name="KristalRef"></param> /// <returns>Returns populated mdl_Kristal if preseant, empty if not</returns> public mdl_Kristal fetchCurrentKristal(int KristalRef) { mdl_Kristal kristal = new mdl_Kristal(); try { SqlConnection conn = new SqlConnection(); conn.ConnectionString = SQL_Stuff.conString; conn.Credential = SQL_Stuff.credential; using (conn) { SqlCommand qryCheckKristal = new SqlCommand(); qryCheckKristal.Connection = conn; qryCheckKristal.CommandText = $"select * from [dbo].[tblKristal] where [KristalRef] = @KristalRef and ValidTo is null"; qryCheckKristal.Parameters.Add("@KristalRef", SqlDbType.Int).Value = KristalRef; conn.Open(); SqlDataReader reader = qryCheckKristal.ExecuteReader(); while (reader.Read()) { kristal.KristalID = Convert.ToInt32(reader["KristalID"].ToString()); kristal.KristalRef = Convert.ToInt32(reader["KristalRef"].ToString()); kristal.KristalName = reader["KristalName"].ToString(); kristal.GrantStageID = Convert.ToInt32(reader["GrantStageID"].ToString()); } } } catch (Exception ex) { MessageBox.Show("Failed to query database for Kristal ref " + Environment.NewLine + Environment.NewLine + ex.Message); } return(kristal); }
private bool addProjectKristal() { if (cb_GrantStage.SelectedIndex > -1) { mdl_Kristal newKristal = new mdl_Kristal(); newKristal.GrantStageID = (int)cb_GrantStage.SelectedValue; newKristal.KristalName = tb_KristalName.Text; int testRef; // if KristalRef is an integer if (int.TryParse(tb_KristalRef.Text, out testRef)) { newKristal.KristalRef = testRef; if (newKristal.KristalRef > 0) { Kristal kristal = new Kristal(); kristal.insertProjectKristalReference(projectNumber, newKristal); return(true); } } else { MessageBox.Show("Please enter a Kristal Reference."); } } else { MessageBox.Show("Please select an application stage."); } return(false); }
public frm_Kristal(mdl_Kristal mdl_Kristal) { InitializeComponent(); setTabIndex(); setKristal(mdl_Kristal); setKristalProjects(); setKristalNotes(); }
/// <summary> /// On successful insert of ProjectKristal record, inserts new Kristal record if required /// </summary> /// <param name="pNumber"></param> /// <param name="insKristal"></param> /// <returns>TRUE on successful insert of a tblProjectKrystal record, FALSE on a fail</returns> public bool insertProjectKristalReference(string pNumber, mdl_Kristal insKristal) { if (insertProjectKristal(pNumber, insKristal.KristalRef)) { insertKristal(insKristal); return(true); } else { return(false); } }
/// <summary> /// Inserts a new Kristal Reference to [dbo].[tblKristal] if not already present. /// Logically deletes and inserts new record if already present. /// </summary> /// <param name="insKristal"></param> /// <returns>TRUE on insert, FALSE on no insert</returns> public bool insertKristal(mdl_Kristal insKristal) { mdl_Kristal existingKristal = fetchCurrentKristal(insKristal.KristalRef); //if the kristal record already exists and there is no difference. if (existingKristal == insKristal) { return(false); } //if the kristal reference exists with different attributes it needs updating // logical delete before insert if (existingKristal.KristalRef == insKristal.KristalRef & existingKristal != insKristal) { deleteKristal(existingKristal.KristalID); } //insert a kristal reference if needs updating or if doesn't already exist try { SqlConnection conn = new SqlConnection(); conn.ConnectionString = SQL_Stuff.conString; conn.Credential = SQL_Stuff.credential; using (conn) { //create parameterised SQL query to insert a new record to tblProjectNotes SqlCommand qryInsertKristal = new SqlCommand(); qryInsertKristal.Connection = conn; qryInsertKristal.CommandText = $"insert into [dbo].[tblKristal] " + "([KristalRef], [KristalName], [GrantStageID]) values (@KristalRef, @KristalName, @GrantStageID)"; qryInsertKristal.Parameters.Add("@KristalRef", SqlDbType.Int).Value = insKristal.KristalRef; qryInsertKristal.Parameters.Add("@KristalName", SqlDbType.VarChar, 4000).Value = insKristal.KristalName; qryInsertKristal.Parameters.Add("@GrantStageID", SqlDbType.Int).Value = insKristal.GrantStageID; //open connection and execute insert conn.Open(); qryInsertKristal.ExecuteNonQuery(); } return(true); } catch (Exception ex) { MessageBox.Show("Failed to add new Kristal Ref to database" + Environment.NewLine + ex.Message); return(false); } }
private void dgv_KristalList_CellDoubleClick(object sender, DataGridViewCellEventArgs e) { int r = e.RowIndex; if (r > -1) { try { Kristal kristal = new Kristal(); mdl_Kristal mdl_Kristal = new mdl_Kristal(); mdl_Kristal = kristal.fetchCurrentKristal(Convert.ToInt32(dgv_KristalList.Rows[r].Cells["Kristal Ref"].Value)); frm_Kristal Kristal = new frm_Kristal(mdl_Kristal); Kristal.Show(); } catch (Exception ex) { MessageBox.Show("Please double click on a data row to see Kristal details." + Environment.NewLine + ex.Message); } } }
private bool addKristal() { if (cb_GrantStage.SelectedIndex > -1) { mdl_Kristal newKristal = new mdl_Kristal(); newKristal.GrantStageID = (int)cb_GrantStage.SelectedValue; newKristal.KristalName = tb_KristalName.Text; int testRef; if (int.TryParse(tb_KristalRef.Text, out testRef)) { newKristal.KristalRef = testRef; if (newKristal.KristalRef > 0) { Kristal kristal = new Kristal(); if (kristal.insertKristal(newKristal) == true) { MessageBox.Show("Item added"); newKristalRef = newKristal.KristalRef; return(true); } } } else { MessageBox.Show("Please enter a Kristal Reference."); } } else { MessageBox.Show("Please select an application stage."); } return(false); }
public bool updateKristal(mdl_Kristal currentKristal) { mdl_Kristal newKristal = new mdl_Kristal(); try { newKristal.KristalRef = currentKristal.KristalRef; newKristal.GrantStageID = (int)cb_GrantStage.SelectedValue; newKristal.KristalName = tb_KristalName.Text; } catch (Exception ex) { MessageBox.Show("Please enter valid details." + Environment.NewLine + Environment.NewLine + ex.Message); } //if details remain same, do nothing if (currentKristal == newKristal) { return(true); } //if details changed, update if (newKristal != currentKristal) { Kristal kristal = new Kristal(); //logically delete current record from dbo.tblKristal if (kristal.deleteKristal(current_Kristal.KristalID)) { //insert new record to dbo.tblKristal kristal.insertKristal(newKristal); current_Kristal = newKristal; return(true); } } return(false); }
public void setKristal(mdl_Kristal mdl_Kristal) { try { current_Kristal = mdl_Kristal; Kristal kristal = new Kristal(); ds_Kristal = kristal.getKristalDataSet(); lbl_KristalRefValue.Text = current_Kristal.KristalRef.ToString(); DataView GrantStages = new DataView(ds_Kristal.Tables["tlkGrantStage"]); GrantStages.RowFilter = "[ValidTo] is null"; cb_GrantStage.DataSource = GrantStages; cb_GrantStage.ValueMember = "GrantStageID"; cb_GrantStage.DisplayMember = "GrantStageDescription"; cb_GrantStage.SelectedValue = current_Kristal.GrantStageID; tb_KristalName.Text = current_Kristal.KristalName; } catch (Exception ex) { MessageBox.Show("Method setKristalEdit of class frm_ProjectKristalEdit has failed" + Environment.NewLine + Environment.NewLine + ex.Message); } }