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);
}
}