public bool PutTransferRecords(DataSet ds, string prj, string vre, string rt, string rq, string rqn,
string rv, DateTime rd, string rvn,
DataGridView assets, DataGridView files, string vreDir, string repoDir,
string tm, string tf, string tt, string dsa, DataGridView rej)
{
bool[] success = new bool[4];
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
// tblTransferRequests insert
mdl_TransferRequests tr = CollectTransferRequestInsert(ds, prj, vre, rt, rq, rqn, rv, rd, rvn);
string trQry = @"
INSERT INTO dbo.tblTransferRequests (Project, VreNumber, RequestType, RequestedBy, RequesterNotes,
ReviewedBy, ReviewDate, ReviewNotes)
OUTPUT INSERTED.RequestID
VALUES (@Project, @VreNumber, @RequestType, @RequestedBy, @RequesterNotes, @ReviewedBy,
@ReviewDate, @ReviewNotes)";
using (SqlCommand cmd = new SqlCommand(cmdText: trQry, connection: conn, transaction: trans))
{
cmd.Parameters.Add("@Project", SqlDbType.VarChar, 5).Value = tr.Project;
cmd.Parameters.Add("@VreNumber", SqlDbType.VarChar, 5).Value = tr.VreNumber;
cmd.Parameters.Add("@RequestType", SqlDbType.Int).Value = tr.RequestType;
cmd.Parameters.Add("@RequestedBy", SqlDbType.Int).Value = tr.RequestedBy;
cmd.Parameters.Add("@RequesterNotes", SqlDbType.VarChar, int.MaxValue).Value = tr?.RequesterNotes ?? (object)DBNull.Value;
cmd.Parameters.Add("@ReviewedBy", SqlDbType.Int).Value = tr.ReviewedBy;
cmd.Parameters.Add("@ReviewDate", SqlDbType.DateTime).Value =
tr.ReviewDate.HasValue ? tr.ReviewDate.Value.Date : (object)DBNull.Value;
cmd.Parameters.Add("@ReviewNotes", SqlDbType.VarChar, int.MaxValue).Value = tr?.ReviewNotes ?? (object)DBNull.Value;
// Execute insert and get the newly created ID
tr.RequestID = (int)cmd.ExecuteScalar();
}
success[0] = tr.RequestID > 0;
// tblAssetGroups insert
List <mdl_AssetGroups> ag = CollectAssetGroupsInsert(ds, prj, assets);
if (ag.Any())
{
int agRows = SQL_Stuff.insertBulk(ag.ToDataTable(), "dbo.tblAssetGroups", conn, trans);
success[1] = agRows == ag.Count();
// Query tblAssetGroups again to get all new IDs for use in further inserts
GetAssetGroups(ds, conn, trans);
}
else
{
success[1] = true;
}
// tblAssetsRegister insert
List <mdl_AssetsRegister> ar = CollectAssetsRegisterInsert(prj, files, vreDir, repoDir);
// If there are any assets, join asset ID to ar before doing bulk insert
if (ag.Any())
{
// Join asset IDs to the assets DGV
var assetsWithID = from a1 in assets.Rows.OfType <DataGridViewRow>()
join a2 in ds.Tables["tblAssetGroups"].AsEnumerable()
on a1.Cells["AssetName"].Value.ToString() equals a2.Field <string>("AssetName")
select new
{
FileName = a1.Cells["FileName"].Value.ToString(),
AssetName = a1.Cells["AssetName"].Value.ToString(),
AssetID = a2.Field <int>("AssetID")
};
// Join assets data to the new assets register records
IEnumerable <mdl_AssetsRegister> arTmp =
from a1 in ar
join a2 in assetsWithID on a1.DataFileName equals a2.FileName into FilesWithAssetInfo
from a3 in FilesWithAssetInfo.DefaultIfEmpty()
select new mdl_AssetsRegister
{
Project = a1.Project,
DataFileName = a1.DataFileName,
VreFilePath = a1.VreFilePath,
DataRepoFilePath = a1.DataRepoFilePath,
AssetID = a3?.AssetID
};
//ar = arTmp.GroupBy(x => x.DataFileName, (key, grp) => grp.First()).ToList();
ar = arTmp.ToList();
}
int arRows = SQL_Stuff.insertBulk(ar.ToDataTable(), "dbo.tblAssetsRegister", conn, trans);
success[2] = arRows == ar.Count();
// Get new FileIDs by re-querying the tbl
GetAssetsRegister(ds, conn, trans);
// tblAssetsChangeLog insert
List <mdl_AssetsChangeLog> cl = CollectAssetsChangeLogInsert(ds, tr.RequestID, files, rej, tm, tf, tt, dsa);
int clRows = SQL_Stuff.insertBulk(cl.ToDataTable(), "dbo.tblAssetsChangeLog", conn, trans);
success[3] = clRows == cl.Count();
trans.Commit();
}
catch (Exception ex)
{
MessageBox.Show(
"Failed to add new file transfer record(s):" + 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));
}
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));
}