Exemple #1
0
        /// <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);
            }
        }
Exemple #3
0
        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);
        }
Exemple #4
0
        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);
        }
Exemple #5
0
 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");
 }
Exemple #6
0
        /// <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);
        }
Exemple #7
0
        /// <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);
        }
Exemple #8
0
        /// <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);
        }
Exemple #9
0
        /// <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);
        }
Exemple #10
0
 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");
 }
Exemple #11
0
 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");
 }
Exemple #12
0
 public void GetAssetGroups(DataSet ds, SqlConnection cn, SqlTransaction tr)
 {
     SQL_Stuff.getDataTable(cn, tr, ds, "tblAssetGroups", "SELECT * FROM dbo.tblAssetGroups");
 }
Exemple #13
0
        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));
        }
Exemple #14
0
        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;
        }
Exemple #15
0
        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));
        }