public static void AddErrorTypes(SqlServerValidationToolkitContext ctx)
{
if (ctx.Errortypes.Any())
{
return;
}
ctx.Errortypes.AddRange(new List <ErrorType>()
{
new ErrorType(MinMaxRule.TooLowErrorTypeCode, "MinMax", "Too low"),
new ErrorType(MinMaxRule.TooHighErrorTypeCode, "MinMax", "Too high"),
new ErrorType(MinMaxRule.TooEarlyErrorTypeCode, "MinMax", "Too early"),
new ErrorType(MinMaxRule.TooLateErrorTypeCode, "MinMax", "Too late"),
new ErrorType(ValidationRule.NotEnteredErrorTypeCode, "Common", "not entered"),
new ErrorType(ComparisonRule.GreaterThanErrorType, "Comparison", "greater than"),
new ErrorType(ComparisonRule.SmallerThanErrorTypeCode, "Comparison", "smaller than"),
new ErrorType(ComparisonRule.LaterThanErrorTypeCode, "Comparison", "later than"),
new ErrorType(ComparisonRule.EarlierThanErrorTypeCode, "Comparison", "earlier than"),
new ErrorType(ComparisonRule.GreaterThanOrEqualsErrorTypeCode, "Comparison", "greater than or equals"),
new ErrorType(ComparisonRule.SmallerThanOrEqualsErrorTypeCode, "Comparison", "smaller than or equals"),
new ErrorType(ComparisonRule.LaterThanOrEqualsErrorTypeCode, "Comparison", "later than or at the same time as"),
new ErrorType(ComparisonRule.EarlierThanOrEqualsErrorTypeCode, "Comparison", "earlier than or at the same time as"),
new ErrorType(ComparisonRule.NotEqualsErrorTypeCode, "Comparison", "not equals"),
new ErrorType(ComparisonRule.EqualsErrorTypeCode, "Comparison", "equals"),
new ErrorType(LikeRule.NotLikeErrorTypeCode, "Like", "not like"),
});
ctx.SaveChanges();
}
public void ExecuteValidation()
{
using (var ctxSqlServer = SqlServerValidationToolkitContext.Create(_connectionStringSqlServer))
{
_ctxLocalDb.Validate(ctxSqlServer);
}
}
public void TestCustomConnectionString()
{
using (SqlServerValidationToolkitContext entities = SqlServerValidationToolkitContext.Create("data source=localhost;initial catalog=ValidationToolkit_UnitTest;integrated security=True"))
{
entities.Columns.ToList().ForEach(c => Console.WriteLine(c.Column_id));
}
}
private static void AddBirthDateColumn(Source source, SqlServerValidationToolkitContext ctx)
{
var cBirthDate = new Column()
{
Name = "Birth_date",
Description = "The birthdate of the child",
Type = "datetime",
};
string customQueryCode = "BirthDateRightAfterHospitalEntry";
var errorType = new ErrorType(customQueryCode,
"CustomQuery",
"The birthdate must be less than two days after the Hospital Entry");
ctx.Errortypes.Add(errorType);
ctx.SaveChanges();
var birthDateValidationRule = ValidationRuleFactory.CreateCustomQueryRule(string.Format(@"SELECT [BabyID], '{0}' AS ErrorType_code
FROM [dbo].[Babies]
WHERE DATEDIFF(day,Hospital_entry,Birth_date)>1", customQueryCode));
birthDateValidationRule.Errortypes.Add(errorType);
cBirthDate.ValidationRules.Add(birthDateValidationRule);
source.Columns.Add(cBirthDate);
}
public void CorrectEntry_SetItToNull_NullIsNotAllowed_Like()
{
string columnName = "Email";
using (var ctx = SqlServerValidationToolkitContext.Create())
{
var column = ctx.Columns.Single(c => c.Name == columnName);
var vr = column.ValidationRules.Single();
vr.NullValueTreatment = NullValueTreatment.InterpretAsError;
vr.CompiledQuery = vr.Query;
ctx.SaveChanges();
}
int babyId;
using (var ctx = TestDatabaseContext.Create(Settings.Default.ConnectionString))
{
var baby = ctx.Babies.First(b => b.Email != null);
baby.Email = null;
ctx.SaveChanges();
babyId = baby.BabyID;
}
using (var ctx = SqlServerValidationToolkitContext.Create())
{
bool babyEntryExisted = WrongBabyEntryExists(columnName, babyId, ctx);
ctx.Validate(SqlServerValidationToolkitContext.Create(Settings.Default.ConnectionString));
bool babyEntryExists = WrongBabyEntryExists(columnName, babyId, ctx);
Assert.IsFalse(babyEntryExisted);
Assert.IsTrue(babyEntryExists);
}
}
public void AddValidation(SqlServerValidationToolkitContext ctx)
{
SqlServerValidationToolkit.Model.Entities.Database database = new Model.Entities.Database()
{
Name = ctx.Database.Connection.Database,
};
ctx.Databases.Add(database);
ValidationRuleFactory.LoadErrorTypes(ctx);
var source = new Source()
{
DatabaseName = ctx.Database.Connection.Database,
Name = "Babies",
Schema = "dbo",
IdColumnName = "BabyID",
Description = "",
Database = database
};
source.UpdateNameForSqlQuery();
AddLenghtColumn(source);
AddWeightColumn(source);
AddEmailColumn(source);
AddHospitalEntryColumn(source);
AddBirthDateColumn(source, ctx);
ctx.Sources.Add(source);
//set the compiled query for each validation rule
ctx.ValidationRules.Local.ToList().ForEach(vr => vr.CompiledQuery = vr.Query);
Save(ctx);
}
/// <summary>
/// Validates all columns
/// </summary>
public void Validate(System.Data.Common.DbConnection connection, SqlServerValidationToolkitContext ctx)
{
foreach (var column in this.Columns)
{
column.Validate(connection, ctx);
}
}
public void Initialize()
{
Initializer initializer = new Initializer();
string databaseFileName = "SqlServerValidationToolkit.Model.Context.SqlServerValidationToolkitContext.sdf";
File.Delete(databaseFileName);
using (var ctx = SqlServerValidationToolkitContext.Create())
{
//DatabaseInitializer.AddErrorTypes(ctx);
initializer.AddValidation(ctx);
ctx.Database.Initialize(false);
//var ctxBabies = new TestDatabaseContext(Settings.Default.ConnectionString);
//ctxBabies.Database.Initialize(false);
ctx.Database.Connection.Close();
ctx.Validate(SqlServerValidationToolkitContext.Create(Settings.Default.ConnectionString));
}
}
/// <summary>
/// Fills the wrongValues-table with all wrong values from the column's rules
/// </summary>
public void Validate(System.Data.Common.DbConnection connection, SqlServerValidationToolkitContext ctx)
{
foreach (var rule in this.ValidationRules)
{
rule.Validate(connection, ctx);
}
}
/// <summary>
/// Loads the errorTypes-list
/// </summary>
public void LoadErrorTypes(SqlServerValidationToolkitContext ctx)
{
_errorTypes.Clear();
var errorTypes = ctx.Errortypes.ToList();
_errorTypes.AddRange(errorTypes);
}
/// <summary>
/// Fills the wrongValues-table with all wrong values from the rule
/// </summary>
public void Validate(System.Data.Common.DbConnection connection, SqlServerValidationToolkitContext ctx)
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
try
{
if (!IsActive)
{
SetAllWrongValuesToCorrected(connection);
}
else
{
UpdateWrongValues(connection, ctx);
}
}
catch (Exception e)
{
throw new Exception(string.Format("Exception occurred while executing '{0}'", CompiledQuery), e);
}
finally
{
connection.Close();
}
}
public void Init(string connectionString)
{
//TODO: Use own context-class
using (var connection = SqlServerValidationToolkitContext.CreateConnection(connectionString))
{
Tables = GetTables(connection).OrderBy(t => t.Name).ToList();
}
}
public Validator()
{
string decryptedConnectionString = GetDecryptedConnectionString();
_connectionStringSqlServer = decryptedConnectionString;
_ctxLocalDb = SqlServerValidationToolkitContext.Create();
LoadSources();
}
public ValidationRuleEditViewViewModel(ValidationRule rule, SqlServerValidationToolkitContext ctx = null)
{
_rule = rule;
_ctx = ctx;
AddNewErrorTypeCommand = new RelayCommand(() => AddNewErrorType(), () => _rule is CustomQueryRule);
DeleteSelectedErrorTypeCommand = new RelayCommand(() => DeleteSelectedErrorType(), () => _rule is CustomQueryRule);
}
public void Refresh()
{
_ctxLocalDb.Dispose();
_ctxLocalDb = SqlServerValidationToolkitContext.Create();
_ctxLocalDb.WrongValues.ToList();
_ctxLocalDb.Errortypes.ToList();
LoadSources();
}
private static void AssertWrongValueExists(long babyId, string columnName)
{
var ctx = SqlServerValidationToolkitContext.Create();
Assert.IsTrue(
ctx.WrongValues.Any(wrongValue =>
wrongValue.Validation_ValidationRule.Column.Name == columnName &&
wrongValue.Id == babyId), string.Format("No wrong value exists for baby {0} and column {1}", babyId, columnName));
}
private void UpdateWrongValues(System.Data.Common.DbConnection connection, SqlServerValidationToolkitContext ctx)
{
string q = CompiledQuery;
var c = connection.CreateCommand();
c.CommandText = q;
var reader = c.ExecuteReader();
List <WrongValue> correctedWrongValues = Validation_WrongValue.ToList();
while (reader.Read())
{
//the query returns the id of the invalid value and the errortype-id
int invalidValueId = reader.GetInt32(0);
string errorTypeCode = reader.GetString(1);
WrongValue existingWrongValue = Validation_WrongValue
.SingleOrDefault(wv =>
wv.Errortype.CodeForValidationQueries == errorTypeCode
&&
wv.Id == invalidValueId
);
string value = GetValue(invalidValueId, connection);
if (existingWrongValue == null)
{
ErrorType errorType = ctx.Errortypes.Where(et => et.CodeForValidationQueries == errorTypeCode).SingleOrDefault();
if (errorType == null) //errorType should exist
{
throw new Exception("ErrorType not found for code '" + errorTypeCode + "'");
}
WrongValue wrongValue = new WrongValue()
{
Errortype = errorType,
Id = invalidValueId,
Value = value
};
Validation_WrongValue.Add(wrongValue);
}
else
{
existingWrongValue.Value = value;
correctedWrongValues.Remove(existingWrongValue);
}
}
_log.Info(string.Format("{0} wrong values are corrected", correctedWrongValues.Count));
foreach (var wvCorrected in correctedWrongValues)
{
wvCorrected.Is_Corrected = true;
}
}
public void Uninstall()
{
string c = Resources.UninstallToolkit;
using (var ctxSqlServer = SqlServerValidationToolkitContext.Create(_connectionStringSqlServer))
{
ctxSqlServer.Database.ExecuteSqlCommand(c);
}
}
private void UpdateSources(SqlServerValidationToolkitContext ctx)
{
//TODO: Use own context-class
foreach (var table in Tables.Where(t => t.IsSelected))
{
var source = GetSource(ctx, table);
UpdateColumns(table, source);
}
}
private static void UpdateExistingDb(Database existingDb, string encryptedConnectionString)
{
using (var ctx = SqlServerValidationToolkitContext.Create())
{
var attachedDb = ctx.Databases.Attach(existingDb);
attachedDb.EncryptedConnectionString = encryptedConnectionString;
ctx.SaveChanges();
}
}
/// <summary>
/// Installs the required objects for the validation toolkit
/// </summary>
public void InstallValidationToolkit()
{
using (var ctx = SqlServerValidationToolkitContext.Create())
{
AddErrorTypes(ctx);
}
string createToolkit = Resources.ProceduresAndFunctions;
ExecuteSplitByGo(createToolkit);
}
public static Database GetExistingDatabase()
{
Database existingDb = null;
using (var ctx = SqlServerValidationToolkitContext.Create())
{
if (ctx.Databases.Any())
{
existingDb = ctx.Databases.Single();
}
}
return(existingDb);
}
public void CorrectMultipleValues()
{
var ctx = SqlServerValidationToolkitContext.Create();
Assert.IsTrue(WrongLengthEntriesExisting(ctx));
var ctxBabies = TestDatabaseContext.Create(Settings.Default.ConnectionString);
CorrectLength(ctxBabies);
ctx.Validate(SqlServerValidationToolkitContext.Create(Settings.Default.ConnectionString));
Assert.IsFalse(WrongLengthEntriesExisting(ctx));
}
private static void AddNewDb(string encryptedConnectionString)
{
var database = new Database()
{
Name = "db",
EncryptedConnectionString = encryptedConnectionString
};
using (var ctx = SqlServerValidationToolkitContext.Create())
{
ctx.Databases.Add(database);
ctx.SaveChanges();
}
}
public static string GetDecryptedConnectionString()
{
string decryptedConnectionString;
using (var ctx = SqlServerValidationToolkitContext.Create())
{
var database = ctx.Databases.Single();
using (var decryptedSecureString = database.EncryptedConnectionString.DecryptString())
{
decryptedConnectionString = decryptedSecureString.ToInsecureString();
}
}
return(decryptedConnectionString);
}
private void UpdateSources()
{
//TODO: Use own context-class
using (var ctx = SqlServerValidationToolkitContext.Create())
{
ctx.Database.Connection.Open();
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
UpdateSources(ctx);
ctx.SaveChanges();
scope.Complete();
}
ctx.Database.Connection.Close();
}
}
private static void AssertWrongValueIsCorrected(long babyId, string columnName)
{
var ctx = SqlServerValidationToolkitContext.Create();
var uncorrectedWrongValues = ctx.WrongValues.Where(wrongValue =>
wrongValue.Validation_ValidationRule.Column.Name == columnName &&
wrongValue.Id == babyId);
string n = "";
foreach (var wv in uncorrectedWrongValues)
{
n += wv;
}
Assert.IsFalse(
uncorrectedWrongValues.Any(), "uncorrected wrong values exist: " + n);
}
public void ChangeLikeValidationRule()
{
var babyId = ApplyCorrection(
(wrongValue) =>
wrongValue.Validation_ValidationRule.Column.Name == "Email",
(ctxB, baby) =>
{
using (var ctx = SqlServerValidationToolkitContext.Create())
{
var emailRule = ctx.ValidationRules.OfType <LikeRule>().Single(vr => vr.Column.Name == "Email");
emailRule.LikeExpression = baby.Email;
emailRule.CompiledQuery = emailRule.Query;
ctx.SaveChanges();
}
});
AssertWrongValueIsCorrected(babyId, "Email");
}
public void SetOnlyMaxInMinMaxRule()
{
using (var ctx = SqlServerValidationToolkitContext.Create())
{
int wrongValuesCountBefore = ctx.WrongValues.Count();
//set the minimal length to null, the -10 value should not be in the wrong-value set
var rule = ctx.ValidationRules.OfType <MinMaxRule>().Single(r => r.Column.Name == "Length");
rule.Minimum = null;
rule.CompiledQuery = rule.Query;
ctx.SaveChanges();
ctx.Validate(SqlServerValidationToolkitContext.Create(Settings.Default.ConnectionString));
int wrongValuesCountAfter = ctx.WrongValues.Count();
Assert.IsTrue(wrongValuesCountBefore > wrongValuesCountAfter);
}
}
//private static Initializer _initializer = new Initializer();
public Initializer()
{
string databaseName;
using (var ctx = SqlServerValidationToolkitContext.Create(Settings.Default.ConnectionString))
{
databaseName = ctx.Database.Connection.Database;
}
string deleteQueryFormat = @"
USE [master]
ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [{0}] ;";
string deleteQuery = string.Format(deleteQueryFormat, databaseName);
string encryptedConnectionString;
using (var secureConnectionString = Settings.Default.ConnectionString.ToSecureString())
{
encryptedConnectionString = secureConnectionString.EncryptString();
}
DatabaseInitializer initializer = new DatabaseInitializer(encryptedConnectionString);
initializer.ExecuteSplitByGo(deleteQuery);
System.Data.Entity.Database.SetInitializer <SqlServerValidationToolkitContext>(null);
using (var ctx = SqlServerValidationToolkitContext.Create(Settings.Default.ConnectionString))
{
ctx.Database.Create();
}
ValidationRuleFactory = new ValidationRuleFactory();
//initializer.InstallValidationToolkit();
System.Data.Entity.Database.SetInitializer <TestDatabaseContext>(null);
initializer.ExecuteSplitByGo(Resources.CREATE_and_fill_Validation_Test_Database);
}
