/// <inheritdoc/> public void DeleteFromDatabase(IMapsDirectlyToDatabaseTable oTableWrapperObject) { //do not log information about access credentials if (!(oTableWrapperObject is IDataAccessCredentials)) { _logger.Debug("Deleted," + oTableWrapperObject.GetType().Name + "," + oTableWrapperObject.ID + "," + oTableWrapperObject); } lock (_oLockUpdateCommands) { //if the repository has obscure dependencies if (ObscureDependencyFinder != null) { ObscureDependencyFinder.ThrowIfDeleteDisallowed(oTableWrapperObject);//confirm that deleting the object is allowed by the dependencies } using (var con = GetConnection()) { using (DbCommand cmd = DatabaseCommandHelper.GetCommand( "DELETE FROM " + oTableWrapperObject.GetType().Name + " WHERE ID =@ID", con.Connection, con.Transaction)) { DatabaseCommandHelper.AddParameterWithValueToCommand("@ID", cmd, oTableWrapperObject.ID); cmd.ExecuteNonQuery(); } //likewise if there are obscure depenedency handlers let them handle cascading this delete into the mists of their obscure functionality (e.g. deleting a Catalogue in CatalogueRepository would delete all Evaluations of that Catalogue in the DQE repository because they would then be orphans) if (ObscureDependencyFinder != null) { ObscureDependencyFinder.HandleCascadeDeletesForDeletedObject(oTableWrapperObject); } } } }
/// <summary> /// Changes the location of the RSA private key file to a physical location on disk (which must exist) /// </summary> /// <param name="newLocation"></param> public void ChangeLocation(string newLocation) { ClearAllInjections(); if (!File.Exists(newLocation)) { throw new FileNotFoundException("Could not find key file at:" + newLocation); } //confirms that it is accessible and deserializable DeserializeFromLocation(newLocation); using (var con = _catalogueRepository.GetConnection()) { //Table can only ever have 1 record using (DbCommand cmd = DatabaseCommandHelper.GetCommand( @"if exists (select 1 from PasswordEncryptionKeyLocation) UPDATE PasswordEncryptionKeyLocation SET Path = @Path else INSERT INTO PasswordEncryptionKeyLocation(Path,Lock) VALUES (@Path,'X') ", con.Connection, con.Transaction)) { DatabaseCommandHelper.AddParameterWithValueToCommand("@Path", cmd, newLocation); cmd.ExecuteNonQuery(); } } ClearAllInjections(); }
/// <inheritdoc/> public IEnumerable <ICacheFetchFailure> FetchPage(int start, int batchSize) { List <int> toReturnIds = new List <int>(); using (var conn = ((CatalogueRepository)Repository).GetConnection()) { using (var cmd = DatabaseCommandHelper.GetCommand(@"SELECT ID FROM CacheFetchFailure WHERE CacheProgress_ID = @CacheProgressID AND ResolvedOn IS NULL ORDER BY FetchRequestStart OFFSET " + start + @" ROWS FETCH NEXT " + batchSize + @" ROWS ONLY", conn.Connection, conn.Transaction)) { DatabaseCommandHelper.AddParameterWithValueToCommand("@CacheProgressID", cmd, ID); using (var reader = cmd.ExecuteReader()) while (reader.Read()) { toReturnIds.Add(Convert.ToInt32(reader["ID"])); } } } return(Repository.GetAllObjectsInIDList <CacheFetchFailure>(toReturnIds)); }
/// <inheritdoc/> public void DeleteFromDatabase(IMapsDirectlyToDatabaseTable oTableWrapperObject) { _logger.Debug("Deleted," + oTableWrapperObject.GetType().Name + "," + oTableWrapperObject.ID + "," + oTableWrapperObject); lock (_oLockUpdateCommands) { //if the repository has obscure dependencies if (ObscureDependencyFinder != null) { ObscureDependencyFinder.ThrowIfDeleteDisallowed(oTableWrapperObject);//confirm that deleting the object is allowed by the dependencies } using (var con = GetConnection()) { DbCommand cmd = DatabaseCommandHelper.GetCommand("DELETE FROM " + oTableWrapperObject.GetType().Name + " WHERE ID =@ID", con.Connection, con.Transaction); DatabaseCommandHelper.AddParameterWithValueToCommand("@ID", cmd, oTableWrapperObject.ID); int affectedRows = cmd.ExecuteNonQuery(); if (affectedRows != 1) { throw new Exception("Attempted to delete object of type " + oTableWrapperObject.GetType().Name + " from table " + oTableWrapperObject.GetType().Name + " with ID " + oTableWrapperObject.ID + " but the DELETE command resulted in " + affectedRows + " affected rows"); } //likewise if there are obscure depenedency handlers let them handle cascading this delete into the mists of their obscure functionality (e.g. deleting a Catalogue in CatalogueRepository would delete all Evaluations of that Catalogue in the DQE repository because they would then be orphans) if (ObscureDependencyFinder != null) { ObscureDependencyFinder.HandleCascadeDeletesForDeletedObject(oTableWrapperObject); } } } }
public void Commit(Evaluation evaluation, string pivotCategory, DbConnection con, DbTransaction transaction) { if (IsCommitted) { throw new NotSupportedException("ColumnState was already committed"); } var sql = string.Format( "INSERT INTO [dbo].[ColumnState]([TargetProperty],[DataLoadRunID],[Evaluation_ID],[CountCorrect],[CountDBNull],[ItemValidatorXML],[CountMissing],[CountWrong],[CountInvalidatesRow],[PivotCategory])VALUES({0},{1},{2},{3},{4},{5},{6},{7},{8},{9})", "@TargetProperty", DataLoadRunID , evaluation.ID , CountCorrect , CountDBNull , "@ItemValidatorXML" , CountMissing , CountWrong , CountInvalidatesRow , "@PivotCategory" ); using (var cmd = DatabaseCommandHelper.GetCommand(sql, con, transaction)) { DatabaseCommandHelper.AddParameterWithValueToCommand("@ItemValidatorXML", cmd, ItemValidatorXML); DatabaseCommandHelper.AddParameterWithValueToCommand("@TargetProperty", cmd, TargetProperty); DatabaseCommandHelper.AddParameterWithValueToCommand("@PivotCategory", cmd, pivotCategory); cmd.ExecuteNonQuery(); } IsCommitted = true; }
public void Commit(Evaluation evaluation, string pivotCategory) { using (var con = evaluation.DQERepository.GetConnection()) { if (IsCommitted) { throw new NotSupportedException("PeriodicityState was already committed"); } string sql = string.Format( "INSERT INTO [dbo].[PeriodicityState]([Evaluation_ID],[Year],[Month],[CountOfRecords],[RowEvaluation],[PivotCategory])VALUES({0},{1},{2},{3},{4},{5})" , evaluation.ID , Year , Month , CountOfRecords , "@RowEvaluation", "@PivotCategory"); using (var cmd = DatabaseCommandHelper.GetCommand(sql, con.Connection, con.Transaction)) { DatabaseCommandHelper.AddParameterWithValueToCommand("@RowEvaluation", cmd, RowEvaluation); DatabaseCommandHelper.AddParameterWithValueToCommand("@PivotCategory", cmd, pivotCategory); cmd.ExecuteNonQuery(); } IsCommitted = true; } }
public RowState(Evaluation evaluation, int dataLoadRunID, int correct, int missing, int wrong, int invalid, string validatorXml, string pivotCategory, DbConnection con, DbTransaction transaction) { var sql = string.Format( "INSERT INTO [dbo].[RowState]([Evaluation_ID],[Correct],[Missing],[Wrong],[Invalid],[DataLoadRunID],[ValidatorXML],[PivotCategory])VALUES({0},{1},{2},{3},{4},{5},@validatorXML,{6})", evaluation.ID, correct, missing, wrong, invalid, dataLoadRunID, "@pivotCategory" ); var cmd = DatabaseCommandHelper.GetCommand(sql, con, transaction); DatabaseCommandHelper.AddParameterWithValueToCommand("@validatorXML", cmd, validatorXml); DatabaseCommandHelper.AddParameterWithValueToCommand("@pivotCategory", cmd, pivotCategory); cmd.ExecuteNonQuery(); Correct = correct; Missing = missing; Wrong = wrong; Invalid = invalid; ValidatorXML = validatorXml; DataLoadRunID = dataLoadRunID; }
/// <summary> /// Creates a new private RSA encryption key certificate at the given location and sets the catalogue repository to use it for encrypting passwords. /// This will make any existing serialized passwords iretrievable unless you restore and reset the original key file location. /// </summary> /// <param name="path"></param> /// <returns></returns> public FileInfo CreateNewKeyFile(string path) { ClearAllInjections(); string existingKey = GetKeyFileLocation(); if (existingKey != null) { throw new NotSupportedException("There is already a key file at location:" + existingKey); } RSACryptoServiceProvider provider = new RSACryptoServiceProvider(4096); RSAParameters p = provider.ExportParameters(true); var fi = new FileInfo(path); if (fi.Directory != null && !fi.Directory.Exists) { fi.Directory.Create(); } using (var stream = fi.Create()) { XmlSerializer SerializeXml = new XmlSerializer(typeof(RSAParameters)); SerializeXml.Serialize(stream, p); stream.Flush(); stream.Close(); } var fileInfo = new FileInfo(path); if (!fileInfo.Exists) { throw new Exception("Created file but somehow it didn't exist!?!"); } using (var con = _catalogueRepository.GetConnection()) { using (DbCommand cmd = DatabaseCommandHelper.GetCommand( "INSERT INTO PasswordEncryptionKeyLocation(Path,Lock) VALUES (@Path,'X')", con.Connection, con.Transaction)) { DatabaseCommandHelper.AddParameterWithValueToCommand("@Path", cmd, fileInfo.FullName); cmd.ExecuteNonQuery(); } } ClearAllInjections(); return(fileInfo); }
public bool IsLookupTable(ITableInfo tableInfo) { using (var con = GetConnection()) { DbCommand cmd = DatabaseCommandHelper.GetCommand( @"if exists (select 1 from Lookup join ColumnInfo on Lookup.Description_ID = ColumnInfo.ID where TableInfo_ID = @tableInfoID) select 1 else select 0", con.Connection, con.Transaction); DatabaseCommandHelper.AddParameterWithValueToCommand("@tableInfoID", cmd, tableInfo.ID); return(Convert.ToBoolean(cmd.ExecuteScalar())); } }
private int CountDefaults(PermissableDefaults type) { if (type == PermissableDefaults.None) { return(0); } using (var con = _repository.GetConnection()) { var cmd = DatabaseCommandHelper.GetCommand("SELECT COUNT(*) AS NumDefaults FROM ServerDefaults WHERE DefaultType=@DefaultType", con.Connection, con.Transaction); DatabaseCommandHelper.AddParameterWithValueToCommand("@DefaultType", cmd, StringExpansionDictionary[type]); var result = cmd.ExecuteScalar(); return(Convert.ToInt32(result)); } }
public UIDMapping[] LoadMappingsForProject(int projectNumber) { var table = _database.ExpectTable(_tableName); using (var conn = GetConnection()) { conn.Open(); var cmd = DatabaseCommandHelper.GetCommand( "SELECT * FROM " + table.GetFullyQualifiedName() + " WHERE ProjectNumber = @ProjectNumber", conn); DatabaseCommandHelper.AddParameterWithValueToCommand("@ProjectNumber", cmd, projectNumber); var reader = cmd.ExecuteReader(); var mappings = new List <UIDMapping>(); while (reader.Read()) { var mappingFromDatabase = HydrateMapping(reader); mappings.Add(mappingFromDatabase); } return(mappings.ToArray()); } }