private void PopulateUsersByProjectId(SqlConnection sqlConnection, DataManagementPlan dataManagementPlan, int projectId)
        {
            var urdmsUsers = new List<UrdmsUser>();
            var nonUrdmsUsers = new List<User>();
            
            if (sqlConnection.State == ConnectionState.Open)
            {
                var sqlCommand = new SqlCommand(string.Format(PartiesByProjectId, projectId, (int)AccessRole.Owners), _sqlConnection);
                var sqlReader = sqlCommand.ExecuteReader();
                while (sqlReader.Read())
                {
                    int role;
                    var userId = sqlReader["UserId"].ToString();
                    var fullName = sqlReader["FullName"].ToString();
                    int.TryParse(sqlReader["Role"].ToString(), out role);

                    if (!string.IsNullOrWhiteSpace(userId))
                    {
                        urdmsUsers.Add(new UrdmsUser {Id = userId, Name = fullName, Role = role});
                    }
                    else
                    {
                        nonUrdmsUsers.Add(new User {Name = fullName, Role = role});
                    }
                }
                sqlReader.Close();
            }
            dataManagementPlan.UrdmsUsers = urdmsUsers;
            dataManagementPlan.NonUrdmsUsers = nonUrdmsUsers;
        }
        public DataManagementPlan GetDataManagementPlanByProjectId(int id)
        {
            var dataManagementPlan = new DataManagementPlan();
            try
            {
                _sqlConnection.Open();
                using (var sqlCommand = new SqlCommand(string.Format(DmpSqlString, id), _sqlConnection))
                {
                    using (var sqlReader = sqlCommand.ExecuteReader())
                    {
                        while (sqlReader.Read())
                        {
                            dataManagementPlan.Id = sqlReader.GetValue<int>("Id");
                            // Project details
                            dataManagementPlan.ProjectTitle = sqlReader.GetStringValue("ProjectTitle");
                            dataManagementPlan.ProjectDescription = sqlReader.GetStringValue("ProjectDescription");
                            dataManagementPlan.CreationDate = sqlReader.GetValue<DateTime>("CreationDate");
                            // Backup
                            dataManagementPlan.BackupLocations = sqlReader.GetEnumValue<BackupLocations>("BackupLocations");
                            dataManagementPlan.BackupPolicyLocationsDescription = sqlReader.GetStringValue("BackupPolicyLocationsDescription");
                            dataManagementPlan.BackupPolicyResponsibilities = sqlReader.GetEnumValue<DataResponsibilities>("BackupPolicyResponsibilities");
                            dataManagementPlan.BackupPolicyResponsibilitiesDescription = sqlReader.GetStringValue("BackupPolicyResponsibilitiesDescription");
                            // Data documentation
                            dataManagementPlan.MetadataStandards = sqlReader.GetStringValue("MetadataStandards");
                            // Data organisation
                            dataManagementPlan.DirectoryStructure = sqlReader.GetStringValue("DirectoryStructure");
                            // Relationship between new and pre-existing data
                            dataManagementPlan.RelationshipBetweenExistingAndNewData = sqlReader.GetEnumValue<DataRelationship>("RelationshipBetweenExistingAndNewData");
                            // Data retention
                            dataManagementPlan.DataRetentionPeriod = sqlReader.GetEnumValue<DataRetentionPeriod>("DataRetentionPeriod");
                            dataManagementPlan.DataRetentionResponsibilities = sqlReader.GetEnumValue<DataResponsibilities>("DataRetentionResponsibilities");
                            dataManagementPlan.DataRetentionResponsibilitiesDescription = sqlReader.GetStringValue("DataRetentionResponsibilitiesDescription");
                            dataManagementPlan.DataRetentionLocations = sqlReader.GetEnumValue<DataRetentionLocations>("DataRetentionLocations");
                            dataManagementPlan.DataRetentionLocationsDescription = sqlReader.GetStringValue("DataRetentionLocationsDescription");
                            dataManagementPlan.DepositToRepository = sqlReader.GetNullableValue<bool>("DepositToRepository");
                            // Data sharing
                            dataManagementPlan.DataSharingAvailability = sqlReader.GetEnumValue<DataSharingAvailability>("DataSharingAvailability");
                            dataManagementPlan.ShareAccess = sqlReader.GetEnumValue<ShareAccess>("ShareAccess");
                            dataManagementPlan.ShareAccessDescription = sqlReader.GetStringValue("ShareAccessDescription");
                            dataManagementPlan.DataLicensingType = sqlReader.GetEnumValue<DataLicensingType>("DataLicensingType");
                            dataManagementPlan.ReuseByOrganisations = sqlReader.GetStringValue("ReuseByOrganisations");
                            dataManagementPlan.DataSharingAvailabilityDate = sqlReader.GetNullableValue<DateTime>("DataSharingAvailabilityDate");
                            // Ethical requirements
                            dataManagementPlan.EthicRequiresClearance = sqlReader.GetNullableValue<bool>("EthicRequiresClearance");
                            dataManagementPlan.EthicComments = sqlReader.GetStringValue("EthicComments");
                            // Pre-existing research data
                            dataManagementPlan.UseExistingData = sqlReader.GetNullableValue<bool>("UseExistingData");
                            dataManagementPlan.ExistingDataOwner = sqlReader.GetStringValue("ExistingDataOwner");
                            dataManagementPlan.ExistingDataAccessTypes = sqlReader.GetEnumValue<ExistingDataAccessTypes>("ExistingDataAccessTypes");
                            dataManagementPlan.AccessTypesDescription = sqlReader.GetStringValue("AccessTypesDescription");
                            // New research data
                            dataManagementPlan.ResearchDataDescription = sqlReader.GetStringValue("ResearchDataDescription");
                            dataManagementPlan.DataOwners = sqlReader.GetEnumValue<DataOwners>("DataOwners");
                            dataManagementPlan.DataOwnersDescription = sqlReader.GetStringValue("DataOwnersDescription");
                            dataManagementPlan.DataActionFrequency = sqlReader.GetEnumValue<DataActionFrequency>("DataActionFrequency");
                            dataManagementPlan.IsVersioned = sqlReader.GetNullableValue<bool>("IsVersioned");
                            // Data storage
                            dataManagementPlan.InstitutionalStorageTypes = sqlReader.GetEnumValue<InstitutionalStorageTypes>("InstitutionalStorageTypes");
                            dataManagementPlan.InstitutionalOtherTypeDescription = sqlReader.GetStringValue("InstitutionalOtherTypeDescription");
                            dataManagementPlan.ExternalStorageTypes = sqlReader.GetEnumValue<ExternalStorageTypes>("ExternalStorageTypes");
                            dataManagementPlan.ExternalOtherTypeDescription = sqlReader.GetStringValue("ExternalOtherTypeDescription");
                            dataManagementPlan.PersonalStorageTypes = sqlReader.GetEnumValue<PersonalStorageTypes>("PersonalStorageTypes");
                            dataManagementPlan.PersonalOtherTypeDescription = sqlReader.GetStringValue("PersonalOtherTypeDescription");
                            dataManagementPlan.MaxDataSize = sqlReader.GetEnumValue<MaxDataSize>("MaxDataSize");
                            dataManagementPlan.FileFormats = sqlReader.GetStringValue("FileFormats");
                            dataManagementPlan.VersionControl = sqlReader.GetEnumValue<VersionControl>("VersionControl");
                            dataManagementPlan.VersionControlDescription = sqlReader.GetStringValue("VersionControlDescription");
                            // Privacy and confidentiality requirements
                            dataManagementPlan.IsSensitive = sqlReader.GetNullableValue<bool>("IsSensitive");
                            dataManagementPlan.ConfidentialityComments = sqlReader.GetStringValue("ConfidentialityComments");
                        }
                        sqlReader.Close();
                    }
                }

                PopulatePrincipalInvestigatorByProjectId(_sqlConnection, dataManagementPlan, id);
                PopulateUsersByProjectId(_sqlConnection, dataManagementPlan, id);
                dataManagementPlan.DateModified = DateTime.Now;
            }
            finally
            {
                _sqlConnection.Close();
            }
            return dataManagementPlan;
        }
 private void PopulatePrincipalInvestigatorByProjectId(SqlConnection sqlConnection, DataManagementPlan dataManagementPlan, int projectId)
 {
     if (sqlConnection.State == ConnectionState.Open)
     {
         var sqlCommand = new SqlCommand(string.Format(PrincipalinvestigatorByProjectId, projectId, (int)ProjectRelationship.PrincipalInvestigator), _sqlConnection);
         var sqlReader = sqlCommand.ExecuteReader();
         while (sqlReader.Read())
         {
             dataManagementPlan.PricipalInvestigator= (string) sqlReader["FullName"];
         }
         sqlReader.Close();
     }
     
 }
 public void Save(string filePath, DataManagementPlan entity)
 {
     var doc = entity.ToXDocument();
     doc.Save(filePath);
 }