/// <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());
}
}
