public mdl_Dsas CollectDsasForInsert(DataSet ds, int dataOwner, bool isAmendment, DataGridView dgvAmendment, string fileName,
string filePath, DateTime?startDate, DateTime?expiryDate, DateTime?destroyDate,
string ownerEmail, bool dspt, bool iso27001, bool encryption, bool remote)
{
int?amendmentOfID = null;
if (isAmendment)
{
amendmentOfID = (int?)dgvAmendment.SelectedRows[0].Cells["DocumentID"].Value;
}
mdl_Dsas newDsa = new mdl_Dsas
{
DataOwner = dataOwner,
AmendmentOf = amendmentOfID,
DsaName = fileName,
DsaFileLoc = filePath,
StartDate = startDate,
ExpiryDate = expiryDate,
DataDestructionDate = destroyDate,
AgreementOwnerEmail = ownerEmail,
DSPT = dspt,
ISO27001 = iso27001,
RequiresEncryption = encryption,
NoRemoteAccess = remote
};
return(newDsa);
}
public mdl_Dsas GetDsaRecord(DataSet ds, int id)
{
mdl_Dsas dr = ds.Tables["tblDsas"].AsEnumerable()
.Where(x => x.Field <int>("DocumentID") == id)
.Select(x => new mdl_Dsas
{
DsaID = x.Field <int>("DsaID"),
ID = x.Field <int>("DocumentID"),
DataOwner = x.Field <int>("DataOwner"),
AmendmentOf = x.Field <int?>("AmendmentOf"),
DsaName = x.Field <string>("DsaName"),
DsaFileLoc = x.Field <string>("DsaFileLoc"),
StartDate = x.Field <DateTime?>("StartDate"),
ExpiryDate = x.Field <DateTime?>("ExpiryDate"),
DataDestructionDate = x.Field <DateTime?>("DataDestructionDate"),
AgreementOwnerEmail = x.Field <string>("AgreementOwnerEmail"),
DSPT = x.Field <bool?>("DSPT"),
ISO27001 = x.Field <bool?>("ISO27001"),
RequiresEncryption = x.Field <bool?>("RequiresEncryption"),
NoRemoteAccess = x.Field <bool?>("NoRemoteAccess"),
ValidFrom = x.Field <DateTime?>("ValidFrom"),
ValidTo = x.Field <DateTime?>("ValidTo"),
Deprecated = x.Field <bool>("Deprecated")
})
.FirstOrDefault();
return(dr);
}
public List <mdl_DsasProjects> CollectDsaProjectsForInsert(DataSet ds, mdl_Dsas rcrd, IEnumerable <string> projects)
{
List <mdl_DsasProjects> newDsaProject = projects.Select(prj => new mdl_DsasProjects {
Project = prj
}).ToList();
// If this is a DSA update, not DSA create, omit existing DSA-Project links from the insert
if (rcrd != null && rcrd.ID > 0)
{
List <string> currentPrjLinks = GetDsaProjectsList(ds, rcrd.ID);
newDsaProject = newDsaProject.Where(x => !currentPrjLinks.Contains(x.Project)).ToList();
}
return(newDsaProject);
}
public List <mdl_DsaNotes> CollectDsaNotesForInsert(DataGridView dgvNotes, DataSet ds, mdl_Dsas rcrd)
{
List <mdl_DsaNotes> currentDsaNotes = GetDsaNotes(ds, rcrd.ID);
List <string> existingNotes = currentDsaNotes.Select(x => x.Note).ToList();
List <DateTime?> existingCreateds = currentDsaNotes.Select(x => x.Created).ToList();
List <mdl_DsaNotes> newDsaNotes = new List <mdl_DsaNotes>();
foreach (DataGridViewRow dr in dgvNotes.Rows)
{
string created = dr.Cells["Created"].Value.ToString().NullIfEmpty();
DateTime? date = created == null ? (DateTime?)null : DateTime.Parse(created);
string note = dr.Cells["Notes"].Value.ToString();
mdl_DsaNotes noteRow = new mdl_DsaNotes {
Note = note, Created = date
};
bool skip = false;
foreach (mdl_DsaNotes n in currentDsaNotes)
{
skip = noteRow == n;
if (skip)
{
break;
}
}
if (!skip)
{
newDsaNotes.Add(new mdl_DsaNotes {
Note = note, Created = date
});
}
}
return(newDsaNotes);
}
public bool PutDsaData(mdl_Dsas inDsa, List <mdl_DsaNotes> inDsaNotes, List <mdl_DsasProjects> inDsaProjects,
mdl_Dsas rcrd)
{
if (rcrd.ID > 0 && rcrd == inDsa && inDsaNotes.Count == 0 && inDsaProjects.Count == 0)
{
MessageBox.Show("No changes to DSA record, nothing to update.\n", "DSA Not Updated", MessageBoxButtons.OK);
return(false);
}
bool[] success = new bool[3];
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
// If DSA record already exists and new data is equal to old, do not perform insert
if (rcrd.ID > 0 && rcrd == inDsa)
{
success[0] = true;
}
else
{
// tblDsas insert
string qryDsas = @"
INSERT INTO dbo.tblDsas (DocumentID, DataOwner, AmendmentOf, DsaName,
DsaFileLoc, StartDate, ExpiryDate, DataDestructionDate, AgreementOwnerEmail,
DSPT, ISO27001, RequiresEncryption, NoRemoteAccess)
OUTPUT INSERTED.DsaID
VALUES (@DocumentID, @DataOwner, @AmendmentOf, @DsaName, @DsaFileLoc, @StartDate,
@ExpiryDate, @DataDestructionDate, @AgreementOwnerEmail, @DSPT, @ISO27001,
@RequiresEncryption, @NoRemoteAccess)";
using (SqlCommand cmd = new SqlCommand(cmdText: qryDsas, connection: conn, transaction: trans))
{
cmd.Parameters.Add("@DocumentID", SqlDbType.Int).Value = inDsa.ID;
cmd.Parameters.Add("@DataOwner", SqlDbType.Int).Value = inDsa.DataOwner;
cmd.Parameters.Add("@AmendmentOf", SqlDbType.Int).Value = inDsa?.AmendmentOf ?? (object)DBNull.Value;
cmd.Parameters.Add("@DsaName", SqlDbType.VarChar, 100).Value = inDsa.DsaName;
cmd.Parameters.Add("@DsaFileLoc", SqlDbType.VarChar, 200).Value = inDsa.DsaFileLoc;
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value =
inDsa.StartDate.HasValue ? inDsa.StartDate.Value.Date : (object)DBNull.Value;
cmd.Parameters.Add("@ExpiryDate", SqlDbType.DateTime).Value =
inDsa.ExpiryDate.HasValue ? inDsa.ExpiryDate.Value.Date : (object)DBNull.Value;
cmd.Parameters.Add("@DataDestructionDate", SqlDbType.DateTime).Value =
inDsa.DataDestructionDate.HasValue ? inDsa.DataDestructionDate.Value.Date : (object)DBNull.Value;
cmd.Parameters.Add("@AgreementOwnerEmail", SqlDbType.VarChar, 50).Value = inDsa.AgreementOwnerEmail;
cmd.Parameters.Add("@DSPT", SqlDbType.Bit).Value = inDsa.DSPT;
cmd.Parameters.Add("@ISO27001", SqlDbType.Bit).Value = inDsa.ISO27001;
cmd.Parameters.Add("@RequiresEncryption", SqlDbType.Bit).Value = inDsa.RequiresEncryption;
cmd.Parameters.Add("@NoRemoteAccess", SqlDbType.Bit).Value = inDsa.NoRemoteAccess;
inDsa.DsaID = (int)cmd.ExecuteScalar();
}
success[0] = inDsa.DsaID > 0;
// If new DSA record, update DocumentID to be new dsa ID --> inDsa.DsaID
if (inDsa.ID == 0 && rcrd.ID == 0)
{
string setDocID = @"UPDATE dbo.tblDsas SET DocumentID = @ID WHERE DsaID = @ID";
using (SqlCommand cmd = new SqlCommand(cmdText: setDocID, connection: conn, transaction: trans))
{
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = inDsa.DsaID;
cmd.ExecuteNonQuery();
inDsa.ID = inDsa.DsaID;
}
}
else if (inDsa.DsaID != rcrd.DsaID) // If this is a DSA update, logical delete previous record
{
string setValidTo = @"UPDATE dbo.tblDsas SET ValidTo = @NOW WHERE DsaID = @DsaID";
using (SqlCommand cmd = new SqlCommand(cmdText: setValidTo, connection: conn, transaction: trans))
{
DateTime timestamp = DateTime.Now;
cmd.Parameters.Add("@NOW", SqlDbType.DateTime).Value = timestamp;
cmd.Parameters.Add("@DsaID", SqlDbType.Int).Value = rcrd.DsaID;
cmd.ExecuteNonQuery();
rcrd.ValidTo = timestamp;
}
}
}
// Add new DSA identity to tblDsaNotes insert, then bulk insert
foreach (mdl_DsaNotes note in inDsaNotes)
{
note.Dsa = inDsa.ID;
}
DataTable tblDsaNotes = inDsaNotes.ToDataTable();
int notesRows = SQL_Stuff.insertBulk(tblDsaNotes, "dbo.tblDsaNotes", conn, trans);
success[1] = notesRows == tblDsaNotes.Rows.Count;
// Add new DSA identity to tblDsasProjects insert, then bulk insert
foreach (mdl_DsasProjects prj in inDsaProjects)
{
prj.DocumentID = inDsa.ID;
}
DataTable tblDsasProjects = inDsaProjects.ToDataTable();
int prjRows = SQL_Stuff.insertBulk(tblDsasProjects, "dbo.tblDsasProjects", conn, trans);
success[2] = prjRows == tblDsasProjects.Rows.Count;
trans.Commit();
}
catch (Exception ex)
{
MessageBox.Show(
"Failed to add new DSA record:" + ex.GetType() + "\n\n" +
ex.Message + "\n\n" +
ex.StackTrace
);
try
{
trans.Rollback();
}
catch (Exception ex2)
{
MessageBox.Show(
"Failed to roll back transaction:" + ex2.GetType() + "\n\n" +
ex2.Message + "\n\n" +
ex2.StackTrace
);
}
}
}
return(!success.Contains(false));
}
private void btn_OK_Click(object sender, EventArgs e)
{
bool hasRequiredInputs = dsa.ValidateInputs(
fileName: tb_FileName.Text,
filePath: tb_FilePath.Text,
dataOwner: cb_ExistingDataOwner.SelectedItem.ToString()
);
if (!hasRequiredInputs)
{
return;
}
if (dsaRecord.ID == 0 && ConfirmationMsg() == DialogResult.Cancel)
{
return;
}
dsasInsertData = dsa.CollectDsasForInsert(
ds: ds,
dataOwner: (int)cb_ExistingDataOwner.SelectedValue,
isAmendment: dgv_AmendmentOf.Rows.Count == 1,
dgvAmendment: dgv_AmendmentOf,
fileName: tb_FileName.Text,
filePath: tb_FilePath.Text,
startDate: dtp_StartDate.Checked ? dtp_StartDate?.Value.Date : null,
expiryDate: dtp_ExpiryDate.Checked ? dtp_ExpiryDate?.Value.Date : null,
destroyDate: dtp_DestroyDate.Checked ? dtp_DestroyDate?.Value.Date : null,
ownerEmail: tb_OwnerEmail.Text,
dspt: chkb_DSPT.Checked,
iso27001: chkb_ISO27001.Checked,
encryption: chkb_Encryption.Checked,
remote: chkb_NoRemoteAccess.Checked
);
if (dsaRecord != null && dsaRecord.ID > 0)
{
dsasInsertData.ID = dsaRecord.ID;
}
dsaNotesInsertData = dsa.CollectDsaNotesForInsert(
dgvNotes: dgv_AddNote,
ds: ds,
rcrd: dsaRecord
);
dsasProjectsInsertData = dsa.CollectDsaProjectsForInsert(
ds: ds,
rcrd: dsaRecord,
projects: dgv_DsasProjects.Rows.OfType <DataGridViewRow>()
.Select(r => r.Cells["Project"])
.Select(c => c.Value.ToString())
);
insertSuccessful = dsa.PutDsaData(dsasInsertData, dsaNotesInsertData, dsasProjectsInsertData, dsaRecord);
if (insertSuccessful)
{
MessageBox.Show("Successfully added new DSA record.\n", "DSA Added", MessageBoxButtons.OK);
this.Close();
}
}
public void InputDsaInfo(int id)
{
dsaRecord = dsa.GetDsaRecord(ds, id);
List <string> dsaPrjList = dsa.GetDsaProjectsList(ds, id);
List <mdl_DsaNotes> dsaNotesHistory = dsa.GetDsaNotes(ds, id);
int isRebranded = ds.Tables["tblDsaDataOwners"].AsEnumerable()
.Where(r => r.Field <int?>("RebrandOf") == dsaRecord.DataOwner)
.Select(x => x.Field <int?>("RebrandOf"))
.ToList()
.Count;
if (isRebranded > 0)
{
chkb_OldDataOwners.Checked = false;
FillDataOwnersList();
}
cb_ExistingDataOwner.SelectedValue = dsaRecord.DataOwner;
tb_OwnerEmail.Text = dsaRecord.AgreementOwnerEmail;
tb_FileName.Text = dsaRecord.DsaName;
tb_FilePath.Text = dsaRecord.DsaFileLoc;
if (dsaRecord.StartDate.HasValue)
{
dtp_StartDate.Checked = true;
dtp_StartDate.Value = dsaRecord.StartDate.HasValue ? (DateTime)dsaRecord.StartDate : DateTime.Now.Date;
}
if (dsaRecord.ExpiryDate.HasValue)
{
dtp_ExpiryDate.Checked = true;
dtp_ExpiryDate.Value = dsaRecord.ExpiryDate.HasValue ? (DateTime)dsaRecord.ExpiryDate : DateTime.Now.Date;
}
if (dsaRecord.DataDestructionDate.HasValue)
{
dtp_DestroyDate.Checked = true;
dtp_DestroyDate.Value = dsaRecord.DataDestructionDate.HasValue ? (DateTime)dsaRecord.DataDestructionDate : DateTime.Now.Date;
}
if (dsaRecord.AmendmentOf.HasValue)
{
dgv_AmendmentOf.DataSource = dsa.CreateDsasBasicView(ds).Where(x => x.DocumentID == dsaRecord.AmendmentOf).ToList();
dgv_AmendmentOf.Columns["DocumentID"].Width = 80;
dgv_AmendmentOf.Columns["DataOwner"].Width = 120;
dgv_AmendmentOf.Columns["StartDate"].Width = 85;
dgv_AmendmentOf.Columns["ExpiryDate"].Width = 85;
dgv_AmendmentOf.Columns["DataDestructionDate"].Width = 140;
dgv_AmendmentOf.Columns["DsaName"].Width = 140;
dgv_AmendmentOf.Columns["AmendmentOf"].Width = 140;
dgv_AmendmentOf.Columns["DSPT"].Width = 75;
dgv_AmendmentOf.Columns["ISO27001"].Width = 75;
dgv_AmendmentOf.Columns["RequiresEncryption"].Width = 140;
dgv_AmendmentOf.Columns["NoRemoteAccess"].Width = 125;
dgv_AmendmentOf.RowHeadersWidth = 15;
}
chkb_DSPT.Checked = dsaRecord.DSPT ?? false;
chkb_ISO27001.Checked = dsaRecord.ISO27001 ?? false;
chkb_Encryption.Checked = dsaRecord.RequiresEncryption ?? false;
chkb_NoRemoteAccess.Checked = dsaRecord.NoRemoteAccess ?? false;
foreach (string prj in dsaPrjList)
{
dgv_DsasProjects.Rows.Add(prj);
}
foreach (mdl_DsaNotes n in dsaNotesHistory)
{
//dsaNotes.Rows.Add(n.Note, n.Created, n.CreatedBy);
dsaNotes.Rows.Add(n.Note, n.Created);
}
}
