/// <summary>
/// Gets user tables for project DataSet, can be used with ds.dt.Merge() to add to existing DataSet
/// </summary>
/// <returns>DataSet containing just project related User tables</returns>
public DataSet getUserDataSet()
{
DataSet ds_prj_usr = new DataSet("Users");
try
{
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_prj_usr, "tblUser",
$"select *, [LastName] + ', ' + [FirstName] as FullName " +
$"from [dbo].[tblUser] " +
$"where [ValidTo] is null " +
$"order by [LastName], [FirstName], [UserID]");
// Copies made of tblUser so that the can be referenced by LeadApplicant and
// PI fields of tblProjects via DataRelations without additional SQL Server hits
DataTable leadApp = ds_prj_usr.Tables["tblUser"].Copy();
leadApp.TableName = "tlkLeadApplicant";
ds_prj_usr.Tables.Add(leadApp);
DataTable PI = ds_prj_usr.Tables["tblUser"].Copy();
PI.TableName = "tlkPI";
ds_prj_usr.Tables.Add(PI);
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to populate ds_prj_usr DataSet" + Environment.NewLine + Environment.NewLine + ex.Message);
}
return(ds_prj_usr);
}
private void btn_ProjectDocAdd_click(object sender, EventArgs e)
{
using (frm_ProjectDocAdd addProjectDoc = new frm_ProjectDocAdd(projectNumber, ds_Projects, documentType))
{
addProjectDoc.ShowDialog();
try
{
ds_Projects.Tables["tblProjectDocument"].Clear();
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_Projects, "tblProjectDocument",
$"select * from [dbo].[tblProjectDocument]" +
$"where [ValidTo] is null");
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to refresh tblProjectDocument" + Environment.NewLine + Environment.NewLine + ex.Message);
}
setProjectDocHistory(projectNumber, ds_Projects, documentType);
}
}
public DataSet GetAssetsHistoryDataSet()
{
DataSet ds_io = new DataSet("AssetsHistory");
SqlConnection conn = new SqlConnection
{
ConnectionString = SQL_Stuff.conString,
Credential = SQL_Stuff.credential
};
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_io, "tblTransferRequests",
@"SELECT RequestID, Project, VreNumber, RequestType, RequestedBy, RequesterNotes, ReviewedBy, ReviewDate, ReviewNotes
FROM dbo.tblTransferRequests
ORDER BY ReviewDate DESC");
SQL_Stuff.getDataTable(conn, null, ds_io, "tblAssetsChangeLog",
@"SELECT ChangeID, RequestID, FileID, TransferMethod, TransferFrom, TransferTo, DsaReviewed, ChangeAccepted, RejectionNotes
FROM dbo.tblAssetsChangeLog");
SQL_Stuff.getDataTable(conn, null, ds_io, "tlkTransferRequestTypes",
"SELECT RequestTypeID, RequestTypeLabel FROM dbo.tlkTransferRequestTypes");
SQL_Stuff.getDataTable(conn, null, ds_io, "tlkFileTransferMethods",
"SELECT MethodID, MethodLabel FROM dbo.tlkFileTransferMethods");
SQL_Stuff.getDataTable(conn, null, ds_io, "tblProject",
@"SELECT * FROM dbo.tblProject WHERE ValidTo IS NULL");
SQL_Stuff.getDataTable(conn, null, ds_io, "tblProjectPlatformInfo",
@"SELECT * FROM dbo.tblProjectPlatformInfo WHERE ValidTo IS NULL");
GetAssetGroups(ds_io, conn, null);
GetAssetsRegister(ds_io, conn, null);
GetDsasData(ds_io, conn, null);
GetUsersData(ds_io, conn, null);
}
return(ds_io);
}
public DataSet GetDsaData()
{
DataSet ds = new DataSet("DSAs");
SqlConnection conn = new SqlConnection
{
ConnectionString = SQL_Stuff.conString,
Credential = SQL_Stuff.credential
};
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds, "tblDsas",
@"SELECT DsaID, DocumentID, DataOwner, AmendmentOf, DsaName, DsaFileLoc, StartDate, ExpiryDate,
DataDestructionDate, AgreementOwnerEmail, DSPT, ISO27001, RequiresEncryption,
NoRemoteAccess, ValidFrom, ValidTo, Deprecated
FROM dbo.tblDsas
WHERE ValidTo IS NULL
ORDER BY DocumentID");
SQL_Stuff.getDataTable(conn, null, ds, "tblDsaNotes",
@"SELECT dnID, Dsa, Note, Created, CreatedBy FROM dbo.tblDsaNotes");
SQL_Stuff.getDataTable(conn, null, ds, "tblDsasProjects",
@"SELECT dpID, DocumentID, Project, ValidFrom, ValidTo FROM dbo.tblDsasProjects WHERE ValidTo IS NULL");
SQL_Stuff.getDataTable(conn, null, ds, "tblDsaDataOwners",
@"SELECT doID, DataOwnerName, RebrandOf, DataOwnerEmail FROM dbo.tblDsaDataOwners");
SQL_Stuff.getDataTable(conn, null, ds, "tblProject",
@"SELECT * FROM dbo.tblProject WHERE ValidTo IS NULL");
}
return(ds);
}
public void GetDsasData(DataSet ds, SqlConnection cn, SqlTransaction tr)
{
SQL_Stuff.getDataTable(cn, tr, ds, "tblDsas",
@"SELECT DsaID, DocumentID, DataOwner, AmendmentOf, DsaName, DsaFileLoc, StartDate, ExpiryDate,
DataDestructionDate, AgreementOwnerEmail, DSPT, ISO27001, RequiresEncryption,
NoRemoteAccess, ValidFrom, ValidTo, Deprecated
FROM dbo.tblDsas
WHERE ValidTo IS NULL
ORDER BY DocumentID");
SQL_Stuff.getDataTable(cn, tr, ds, "tblDsasProjects", "SELECT * FROM dbo.tblDsasProjects WHERE ValidTo IS NULL");
SQL_Stuff.getDataTable(cn, tr, ds, "tblDsaDataOwners", "SELECT doID, DataOwnerName FROM dbo.tblDsaDataOwners");
}
/// <summary>
/// Method to return a DataSet (ds_usr) with content of SQL table dbo.tblUser, dbo.tblUserNotes
/// and other related lookup tables.
/// Creates DataRelations so that dimension tables can be linked to values in the measures table.
/// </summary>
/// <returns></returns>
public DataSet getUsersDataSet()
{
DataSet ds_usr = new DataSet("Users");
try
{
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
//use method from GetDB to create, fill and add DataTables to class DataSet
SQL_Stuff.getDataTable(conn, null, ds_usr, "tblUser",
$"select *, [LastName] + ', ' + [FirstName] as FullName " +
$"from [dbo].[tblUser] " +
$"where [ValidTo] is null " +
$"order by [LastName], [FirstName], [UserID]");
SQL_Stuff.getDataTable(conn, null, ds_usr, "tlkUserStatus",
$"select * from [dbo].[tlkUserStatus] ");
SQL_Stuff.getDataTable(conn, null, ds_usr, "tlkTitle",
$"select * from [dbo].[tlkTitle] ");
SQL_Stuff.getDataTable(conn, null, ds_usr, "tblUserNotes",
$"select * from [dbo].[tblUserNotes]");
SQL_Stuff.getDataTable(conn, null, ds_usr, "tblUserProject",
$"select * from [dbo].[tblUserProject] " +
$"where [ValidTo] is null");
SQL_Stuff.getDataTable(conn, null, ds_usr, "tblProjects",
$"select * from [dbo].[tblProject] " +
$"where [ValidTo] is null " +
$"order by [ProjectNumber], [pID]");
//create a DataRelations to join dimensions to measures
ds_usr.Relations.Add("User_UserStatus"
, ds_usr.Tables["tlkUserStatus"].Columns["StatusID"] //parent
, ds_usr.Tables["tblUser"].Columns["Status"]); //child
ds_usr.Relations.Add("User_Title"
, ds_usr.Tables["tlkTitle"].Columns["TitleID"]
, ds_usr.Tables["tblUser"].Columns["Title"]);
ds_usr.Relations.Add("UserProject_Project"
, ds_usr.Tables["tblProjects"].Columns["ProjectNumber"]
, ds_usr.Tables["tblUserProject"].Columns["ProjectNumber"]);
}
}
catch (Exception e)
{
MessageBox.Show("Failed to populate ds_usr DataSet" + Environment.NewLine + Environment.NewLine + e);
//throw;
}
//return DataSet (ds_usr) as the output of this method
return(ds_usr);
}
/// <summary>
/// Method to return a DataSet (ds_prj) with content of SQL table dbo.tblProjects, dbo.tblProjectNotes,
/// dbo.tblUsers and other related lookup tables.
/// Creates DataRelations so that dimension tables can be linked to values in the measures table.
/// </summary>
/// <returns>
/// Full DataSet containing tables of all currently valid project records and asociated user/lookup tables.
/// </returns>
public DataSet getKristalDataSet()
{
DataSet ds_krs = new DataSet("Kristal");
try
{
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_krs, "tblKristal",
$"select * from [dbo].[tblKristal] " +
$"where [ValidTo] is null " +
$"order by [KristalRef], [KristalID]");
SQL_Stuff.getDataTable(conn, null, ds_krs, "tlkGrantStage",
$"select * from [dbo].[tlkGrantStage] order by StageNumber asc");
SQL_Stuff.getDataTable(conn, null, ds_krs, "tblProjectKristal",
$"select * from [dbo].[tblProjectKristal] " +
$"where [ValidTo] is null " +
$"order by [KristalRef], [ProjectNumber]");
SQL_Stuff.getDataTable(conn, null, ds_krs, "vw_AllProjects",
$"select * from [dbo].[vw_AllProjects]" +
$"order by [ProjectNumber]");
SQL_Stuff.getDataTable(conn, null, ds_krs, "tblKristalNotes",
$"select * from [dbo].[tblKristalNotes]");
ds_krs.Relations.Add("Kristal_GrantStage"
, ds_krs.Tables["tlkGrantStage"].Columns["GrantStageID"]
, ds_krs.Tables["tblKristal"].Columns["GrantStageID"]);
ds_krs.Relations.Add("Kristal_Project"
, ds_krs.Tables["vw_AllProjects"].Columns["ProjectNumber"]
, ds_krs.Tables["tblProjectKristal"].Columns["ProjectNumber"]);
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to populate ds_krs DataSet" + Environment.NewLine + Environment.NewLine + ex.Message);
}
return(ds_krs);
}
/// <summary>
/// Calls static method to populate static variable with SQL connection string.
/// Captures username and password from form controls to store in static variable (SQL_Stuff.credential).
/// </summary>
private void setConnection()
{
//set SQL connection string
SQL_Stuff.setString();
//set SQL Credentials
string usr = tb_UserName.Text;
SecureString pwd = new SecureString();
if (tb_Password.Text != "")
{
foreach (char c in tb_Password.Text)
{
pwd.AppendChar(c);
}
}
pwd.MakeReadOnly();
SQL_Stuff.credential = new SqlCredential(usr, pwd);
}
/// <summary>
/// Method to return a DataSet (ds_prj) with content of SQL table dbo.tblProjects, dbo.tblProjectNotes,
/// dbo.tblUsers and other related lookup tables.
/// Creates DataRelations so that dimension tables can be linked to values in the measures table.
/// </summary>
/// <returns>
/// Full DataSet containing tables of all currently valid project records and asociated user/lookup tables.
/// </returns>
public DataSet getProjectsDataSet()
{
DataSet ds_prj = new DataSet("Projects");
try
{
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblProjects",
$"select * from [dbo].[tblProject] " +
$"where [ValidTo] is null " +
$"order by [ProjectNumber], [pID]");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkStage",
$"select * from [dbo].[tlkStage] order by StageNumber asc");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkClassification",
$"select * from [dbo].[tlkClassification] ");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkRAG",
$"select * from [dbo].[tlkRAG] ");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkFaculty",
$"select * from [dbo].[tlkFaculty] ");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblProjectNotes",
$"select * from [dbo].[tblProjectNotes] " +
$"order by [ProjectNumber], [Created] desc");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblProjectPlatformInfo",
$"Select * from [dbo].[tblProjectPlatformInfo] " +
$"where [ValidTo] is null");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkPlatformInfo",
$"select * from [dbo].[tlkPlatformInfo]");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblUserProject",
$"select * from [dbo].[tblUserProject] " +
$"where [ValidTo] is null");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblProjectDocument",
$"select * from [dbo].[tblProjectDocument]" +
$"where [ValidTo] is null");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkDocuments",
$"select * from [dbo].[tlkDocuments]");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblDocsAccepted",
$"select tbl.ProjectNumber " +
$" , tlk.DocumentID " +
$" , tlk.DocumentDescription " +
$" , max(tbl.Accepted) as maxAccepted " +
$"from[dbo].[tlkDocuments] tlk " +
$" left join[dbo].[tblProjectDocument] tbl " +
$" on tlk.DocumentID = tbl.DocumentType " +
$"where tlk.ValidTo is null and tbl.ValidTo is null " +
$" and tbl.ProjectNumber is not null " +
$"group by tbl.ProjectNumber " +
$" , tlk.DocumentID " +
$" , tlk.DocumentDescription " +
$"order by tbl.ProjectNumber, tlk.DocumentID");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblDatHours",
$"select ProjectNumber " +
$" , sum(case when datediff(m, Created, getdate()) = 0 " +
$" then[DatHours] else 0 end) as ThisMonth " +
$" , sum(case when datediff(m, Created, getdate()) between 1 and 6 " +
$" then[DatHours] else 0 end) as Last6Month " +
$" , sum(case when datediff(m, Created, getdate()) between 1 and 12 " +
$" then[DatHours] else 0 end) as Last12Month " +
$" , sum([DatHours]) as AllTime " +
$"from [dbo].[tblProjectDatTime] " +
$"group by ProjectNumber ");
// get the user tables needed to link to project details and merge with project dataset
DataSet ds_prj_usr = getUserDataSet();
ds_prj.Merge(ds_prj_usr);
}
ds_prj.Relations.Add("Project_Stage"
, ds_prj.Tables["tlkStage"].Columns["StageID"] //parent
, ds_prj.Tables["tblProjects"].Columns["Stage"]); //child
ds_prj.Relations.Add("Project_Classification"
, ds_prj.Tables["tlkClassification"].Columns["classificationID"]
, ds_prj.Tables["tblProjects"].Columns["Classification"]);
ds_prj.Relations.Add("Project_DATRAG"
, ds_prj.Tables["tlkRAG"].Columns["ragID"]
, ds_prj.Tables["tblProjects"].Columns["DATRAG"]);
ds_prj.Relations.Add("Project_Faculty"
, ds_prj.Tables["tlkFaculty"].Columns["facultyID"]
, ds_prj.Tables["tblProjects"].Columns["Faculty"]);
ds_prj.Relations.Add("ProjectPlatformInfo_PlatformInfo"
, ds_prj.Tables["tlkPlatformInfo"].Columns["PlatformInfoID"]
, ds_prj.Tables["tblProjectPlatformInfo"].Columns["PlatformInfoID"]);
ds_prj.Relations.Add("ProjectDocument_Document"
, ds_prj.Tables["tlkDocuments"].Columns["DocumentID"]
, ds_prj.Tables["tblProjectDocument"].Columns["DocumentType"]);
ds_prj = addProjectUserDataRelations(ds_prj);
}
catch (Exception ex)
{
MessageBox.Show("Failed to populate ds_prj DataSet" + Environment.NewLine + Environment.NewLine + ex.Message);
}
//return DataSet (ds_prj) as the output of this method
return(ds_prj);
}
public void GetUsersData(DataSet ds, SqlConnection cn, SqlTransaction tr)
{
SQL_Stuff.getDataTable(cn, tr, ds, "tblUser", "SELECT * FROM dbo.tblUser WHERE ValidTo IS NULL");
SQL_Stuff.getDataTable(cn, tr, ds, "tblUserProject", "SELECT * FROM dbo.tblUserProject WHERE ValidTo IS NULL");
}
public void GetAssetsRegister(DataSet ds, SqlConnection cn, SqlTransaction tr)
{
SQL_Stuff.getDataTable(cn, tr, ds, "tblAssetsRegister",
@"SELECT FileID, Project, DataFileName, VreFilePath, DataRepoFilePath, AssetID
FROM dbo.tblAssetsRegister");
}
public void GetAssetGroups(DataSet ds, SqlConnection cn, SqlTransaction tr)
{
SQL_Stuff.getDataTable(cn, tr, ds, "tblAssetGroups", "SELECT * FROM dbo.tblAssetGroups");
}
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));
}
private void setProjectUserDocs(string pNumber, string pName)
{
lbl_ProjectNumber.Text = pNumber;
lbl_ProjectName.Text = pName;
DataSet ds_ProjectUserDocs = new DataSet();
try
{
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_ProjectUserDocs, "tblProjectUserDocs",
$"exec dbo.sp_UserDocStatusByProject '{pNumber}'");
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to populate ds_ProjectUserDocs DataSet" + Environment.NewLine + Environment.NewLine + ex.Message);
}
DataTable dt_dgv_ProjectUserDocs = new DataTable();
dt_dgv_ProjectUserDocs.Columns.Add("User Status");
dt_dgv_ProjectUserDocs.Columns.Add("Name");
dt_dgv_ProjectUserDocs.Columns.Add("SEED Confidentiality Agreement");
dt_dgv_ProjectUserDocs.Columns.Add("IRC User Agreement");
dt_dgv_ProjectUserDocs.Columns.Add("LASER User Agreement");
dt_dgv_ProjectUserDocs.Columns.Add("Information Security Essentials");
dt_dgv_ProjectUserDocs.Columns.Add("Information Security Advanced");
dt_dgv_ProjectUserDocs.Columns.Add("SAFE Researcher Training");
DataRow row;
foreach (DataRow upRow in ds_ProjectUserDocs.Tables["tblProjectUserDocs"].Rows)
{
row = dt_dgv_ProjectUserDocs.NewRow();
row["User Status"] = upRow["User Status"];
row["Name"] = upRow["Name"];
row["SEED Confidentiality Agreement"] = upRow["SEED Confidentiality Agreement"];
row["IRC User Agreement"] = upRow["IRC User Agreement"];
row["LASER User Agreement"] = upRow["LASER User Agreement"];
row["Information Security Essentials"] = upRow["Information Security Essentials"];
row["Information Security Advanced"] = upRow["Information Security Advanced"];
row["SAFE Researcher Training"] = upRow["SAFE Researcher Training"];
dt_dgv_ProjectUserDocs.Rows.Add(row);
}
dt_dgv_ProjectUserDocs.DefaultView.Sort = "Name Asc";
dt_dgv_ProjectUserDocs = dt_dgv_ProjectUserDocs.DefaultView.ToTable();
dgv_ProjectUserDocs.DataSource = dt_dgv_ProjectUserDocs;
dgv_ProjectUserDocs.Columns["User Status"].Width = 50;
dgv_ProjectUserDocs.Columns["Name"].Width = 150;
dgv_ProjectUserDocs.Columns["SEED Confidentiality Agreement"].Width = 100;
dgv_ProjectUserDocs.Columns["IRC User Agreement"].Width = 100;
dgv_ProjectUserDocs.Columns["LASER User Agreement"].Width = 100;
dgv_ProjectUserDocs.Columns["Information Security Essentials"].Width = 100;
dgv_ProjectUserDocs.Columns["Information Security Advanced"].Width = 100;
dgv_ProjectUserDocs.Columns["SAFE Researcher Training"].Width = 100;
}
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));
}
