public bool Initialize(string userId, CancellationToken token)
{
try
{
return(Task.Run(() =>
{
var finalConnectionString = _adapter == null ?
connectionString :
_adapter.ConnectionStrings(this.connectionStringName);
Expression <Func <TTable, bool> > filterExpression = p => p.ExecutorId == userId;
ITableDependencyFilter whereCondition = new SqlTableDependencyFilter <TTable>(filterExpression);
Table = new SqlTableDependency <TTable>(
finalConnectionString,
TableName,
filter: whereCondition);
Table.OnChanged += OnChangeRaised;
Table.OnError += Table_OnError;
Table.Start();
return true;
}, token).Result);
}
catch (OperationCanceledException)
{
throw;
}
catch (Exception ex)
{
throw ex;
}
}
public void Test()
{
SqlTableDependency <TestDateTimeSqlServerModel> tableDependency = null;
string naming;
Expression <Func <TestDateTimeSqlServerModel, bool> > expression = p => p.Start >= _now;
ITableDependencyFilter filterExpression = new SqlTableDependencyFilter <TestDateTimeSqlServerModel>(expression);
try
{
tableDependency = new SqlTableDependency <TestDateTimeSqlServerModel>(ConnectionStringForTestUser, filter: filterExpression);
tableDependency.OnChanged += TableDependency_Changed;
tableDependency.Start();
naming = tableDependency.DataBaseObjectsNamingConvention;
var t = new Task(ModifyTableContent);
t.Start();
Thread.Sleep(1000 * 15 * 1);
}
finally
{
tableDependency?.Dispose();
}
Assert.AreEqual(_counter, 2);
Assert.AreEqual(1, _insertedId);
Assert.AreEqual(1, _deletedId);
Assert.IsTrue(base.AreAllDbObjectDisposed(naming));
Assert.IsTrue(base.CountConversationEndpoints(naming) == 0);
}
private static void Main()
{
// Get Connection string
var connectionString = ConfigurationManager.ConnectionStrings["SqlServer2008 Test_User"].ConnectionString;
// Because our model has a property that does not match table column name, we need a mapper C# Model PROPERTY <--> Database Table Column Name
var mapper = new ModelToTableMapper <Product>();
mapper.AddMapping(c => c.ItemsInStock, "Quantity");
// Define WHERE filter specifing the WHERE condition
// We also pass the mapper defined above as last contructor's parameter
Expression <Func <Product, bool> > expression = p => (p.CategoryId == (int)CategorysEnum.Food || p.CategoryId == (int)CategorysEnum.Drink) && p.ItemsInStock <= 10;
ITableDependencyFilter whereCondition = new SqlTableDependencyFilter <Product>(expression, mapper);
// Create SqlTableDependency and pass filter condition, as weel as mapper
using (var dep = new SqlTableDependency <Product>(connectionString, "Products", mapper: mapper, filter: whereCondition))
{
dep.OnChanged += Changed;
dep.OnError += OnError;
dep.Start();
Console.WriteLine("TableDependency, SqlTableDependency, SqlTableDependencyFilter");
Console.WriteLine("Copyright (c) 2015-2018 Christian Del Bianco.");
Console.WriteLine("All rights reserved." + Environment.NewLine);
Console.WriteLine();
Console.WriteLine("Waiting for receiving notifications...");
Console.WriteLine("Press a key to stop");
Console.ReadKey();
}
}
public void Test6()
{
SqlTableDependency <Issue66Model2> tableDependency = null;
string naming;
var mapper = new ModelToTableMapper <Issue66Model2>();
mapper.AddMapping(c => c.Surname, "Second Name");
Expression <Func <Issue66Model2, bool> > expression = p => p.Id == 1 && p.Surname == "DEL BIANCO";
ITableDependencyFilter whereCondition = new SqlTableDependencyFilter <Issue66Model2>(expression, mapper);
var updateOf = new UpdateOfModel <Issue66Model2>();
updateOf.Add(i => i.Surname);
updateOf.Add(i => i.City);
try
{
tableDependency = new SqlTableDependency <Issue66Model2>(
ConnectionStringForTestUser,
includeOldValues: true,
tableName: TableName + "2",
mapper: mapper,
updateOf: updateOf,
notifyOn: DmlTriggerType.Update,
filter: whereCondition);
tableDependency.OnChanged += this.TableDependency_Changed2;
tableDependency.Start();
naming = tableDependency.DataBaseObjectsNamingConvention;
var t = new Task(ModifyTableContent5);
t.Start();
Thread.Sleep(1000 * 15 * 1);
}
finally
{
tableDependency?.Dispose();
}
Assert.AreEqual(CheckValues2[ChangeType.Insert.ToString()].Count, 0);
Assert.AreEqual(CheckValues2[ChangeType.Update.ToString()].Count, 1);
Assert.AreEqual(CheckValues2[ChangeType.Update.ToString()][0].Id, 1);
Assert.AreEqual(CheckValues2[ChangeType.Update.ToString()][0].Surname, "DEL BIANCO");
Assert.AreEqual(CheckValues2[ChangeType.Update.ToString()][0].Name, "CHRISTIAN");
Assert.AreEqual(CheckValues2[ChangeType.Update.ToString()][0].City, "BAAR");
Assert.AreEqual(CheckValuesOld2[ChangeType.Update.ToString()][0].Id, 1);
Assert.AreEqual(CheckValuesOld2[ChangeType.Update.ToString()][0].Surname, "DELBIANCO");
Assert.AreEqual(CheckValuesOld2[ChangeType.Update.ToString()][0].Name, "CHRISTIAN");
Assert.AreEqual(CheckValuesOld2[ChangeType.Update.ToString()][0].City, "LAVENA PONTE TRESA");
Assert.AreEqual(CheckValues2[ChangeType.Delete.ToString()].Count, 0);
Assert.IsTrue(base.AreAllDbObjectDisposed(naming));
Assert.IsTrue(base.CountConversationEndpoints(naming) == 0);
}
public void TestWithOldValues()
{
SqlTableDependency <WithMapperTestModel> tableDependency = null;
string naming;
var mapper = new ModelToTableMapper <WithMapperTestModel>();
mapper.AddMapping(c => c.Surname, "Second Name");
mapper.AddMapping(c => c.Identificator, "Id");
Expression <Func <WithMapperTestModel, bool> > expression = p => p.Identificator == _id;
ITableDependencyFilter filterExpression = new SqlTableDependencyFilter <WithMapperTestModel>(expression, mapper);
try
{
tableDependency = new SqlTableDependency <WithMapperTestModel>(
ConnectionStringForTestUser,
includeOldValues: true,
tableName: TableName,
mapper: mapper,
filter: filterExpression);
tableDependency.OnChanged += TableDependency_Changed;
tableDependency.Start();
naming = tableDependency.DataBaseObjectsNamingConvention;
var t = new Task(ModifyTableContent);
t.Start();
Thread.Sleep(1000 * 15 * 1);
}
finally
{
tableDependency?.Dispose();
}
Assert.AreEqual(_counter, 3);
Assert.AreEqual(CheckValues[ChangeType.Insert.ToString()].Item2.Identificator, CheckValues[ChangeType.Insert.ToString()].Item1.Identificator);
Assert.AreEqual(CheckValues[ChangeType.Insert.ToString()].Item2.Name, CheckValues[ChangeType.Insert.ToString()].Item1.Name);
Assert.AreEqual(CheckValues[ChangeType.Insert.ToString()].Item2.Surname, CheckValues[ChangeType.Insert.ToString()].Item1.Surname);
Assert.IsNull(CheckValuesOld[ChangeType.Insert.ToString()]);
Assert.AreEqual(CheckValues[ChangeType.Update.ToString()].Item2.Identificator, CheckValues[ChangeType.Update.ToString()].Item1.Identificator);
Assert.AreEqual(CheckValues[ChangeType.Update.ToString()].Item2.Name, CheckValues[ChangeType.Update.ToString()].Item1.Name);
Assert.AreEqual(CheckValues[ChangeType.Update.ToString()].Item2.Surname, CheckValues[ChangeType.Update.ToString()].Item1.Surname);
Assert.AreEqual(CheckValuesOld[ChangeType.Update.ToString()].Item2.Identificator, CheckValues[ChangeType.Insert.ToString()].Item2.Identificator);
Assert.AreEqual(CheckValuesOld[ChangeType.Update.ToString()].Item2.Name, CheckValues[ChangeType.Insert.ToString()].Item2.Name);
Assert.AreEqual(CheckValuesOld[ChangeType.Update.ToString()].Item2.Surname, CheckValues[ChangeType.Insert.ToString()].Item2.Surname);
Assert.AreEqual(CheckValues[ChangeType.Delete.ToString()].Item2.Identificator, CheckValues[ChangeType.Delete.ToString()].Item1.Identificator);
Assert.AreEqual(CheckValues[ChangeType.Delete.ToString()].Item2.Name, CheckValues[ChangeType.Delete.ToString()].Item1.Name);
Assert.AreEqual(CheckValues[ChangeType.Delete.ToString()].Item2.Surname, CheckValues[ChangeType.Delete.ToString()].Item1.Surname);
Assert.IsNull(CheckValuesOld[ChangeType.Delete.ToString()]);
Assert.IsTrue(AreAllDbObjectDisposed(naming));
Assert.IsTrue(CountConversationEndpoints(naming) == 0);
}
public void MethodsChain2()
{
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code.Trim().ToUpper().Substring(0, 3).Contains("WWW");
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("SUBSTRING(UPPER(LTRIM(RTRIM([Code]))), 0, 3) LIKE '%WWW%'", where);
}
public void ToStringWithInvariantCulture1()
{
// Arrange
Expression <Func <Product, bool> > expression = p => p.Price.ToString(CultureInfo.InvariantCulture) == "123.4";
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("(CONVERT(varchar(MAX), [Price]) = '123.4')", where);
}
public void ExceptionExpected1()
{
// Arrange
Expression <Func <Product, bool> > expression = p => p.Category.Description == "Pasta";
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("(UPPER([Code]) = '123')", where);
}
public void ExpressionOnBothSides1()
{
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code.Substring(1, 3).ToLower() == p.Id.ToString().ToLower();
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("(LOWER(SUBSTRING([Code], 1, 3)) = LOWER(CONVERT(varchar(MAX), [Id])))", where);
}
public void StringContains2()
{
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code.Contains("123");
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("[Code] LIKE '%123%'", where);
}
public void EndsOrStartsWith2()
{
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code.StartsWith("123") || p.Code.EndsWith("123");
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("([Code] LIKE '123%' OR [Code] LIKE '%123')", where);
}
public void ToStringOnStringField1()
{
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code.ToString() == "123.4";
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("(CONVERT(varchar(MAX), [Code]) = '123.4')", where);
}
public void SubstringTests1()
{
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code.Substring(0, 3) == "123";
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("(SUBSTRING([Code], 0, 3) = '123')", where);
}
public void ToUpper1()
{
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code.ToUpper() == "123";
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("(UPPER([Code]) = '123')", where);
}
public void RLTrim1()
{
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code.TrimEnd() == "123" && p.Code.TrimStart() == "123";
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("((RTRIM([Code]) = '123') AND (LTRIM([Code]) = '123'))", where);
}
public void UnitTestParameters4()
{
var par1 = "123";
// Arrange
Expression <Func <Product, bool> > expression = p => p.Id >= Int32.Parse(par1);
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
}
public void MoreConditions1()
{
var ids = new[] { 1, 2, 3 };
// Arrange
Expression <Func <Product, bool> > expression = p => ids.Contains(p.Id) && p.Code.Trim().Substring(0, 3).Equals("WWW");
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("([Id] IN (1,2,3) AND SUBSTRING(LTRIM(RTRIM([Code])), 0, 3) = 'WWW')", where);
}
public void UnitTestParameters4()
{
var par1 = "WWW";
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code.Trim().ToUpper().Substring(0, 3).Equals(par1);
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("SUBSTRING(UPPER(LTRIM(RTRIM([Code]))), 0, 3) = 'WWW'", where);
}
public void ColumnContainsFloats()
{
var prices = new[] { 123.45f, 432.10f };
// Arrange
Expression <Func <Product, bool> > expression = p => prices.Contains(p.ExcangeRate);
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual($"[ExcangeRate] IN ({123.45},{432.1})", where);
}
public void ColumnContainsStrings()
{
var codes = new[] { "one", "two" };
// Arrange
Expression <Func <Product, bool> > expression = p => codes.Contains(p.Code);
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("[Code] IN ('one','two')", where);
}
public void ColumnContainsNumbers()
{
var ids = new[] { 1, 2, 3 };
// Arrange
Expression <Func <Product, bool> > expression = p => ids.Contains(p.Id);
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("[Id] IN (1,2,3)", where);
}
public void UnitTestParameters6()
{
var par1 = "WWW";
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code.Trim().ToLower().Substring(0, 3) == par1.ToLower();
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("(SUBSTRING(LOWER(LTRIM(RTRIM([Code]))), 0, 3) = LOWER('WWW'))", where);
}
public void ColumnContainsDecimals()
{
var prices = new[] { 123.45M, 432.10M };
// Arrange
Expression <Func <Product, bool> > expression = p => prices.Contains(p.Price);
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("[Price] IN (123.45,432.10)", where);
}
public void UnitTestParameters2()
{
var par1 = 123;
// Arrange
Expression <Func <Product, bool> > expression = p => p.Id >= par1;
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("([Id] >= 123)", where);
}
private void iniciar2()
{
Expression <Func <ContenedorSimple, bool> > expression = p => p.REGIMEN == "EXPO";
ITableDependencyFilter CONDICION = new SqlTableDependencyFilter <ContenedorSimple>(expression);
_sqlTableDependency2 = new SqlTableDependency <ContenedorSimple>(_connectionString, tableName: "TEMPORAL2", filter: CONDICION);
_sqlTableDependency2.OnChanged += TableDependency2_Changed;
_sqlTableDependency2.OnError += _sqlTableDependency2_OnError;
_sqlTableDependency2.OnStatusChanged += _sqlTableDependency2_OnStatusChanged;
_sqlTableDependency2.Start(watchDogTimeOut: 28800);
while (!(_sqlTableDependency2.Status == TableDependency.SqlClient.Base.Enums.TableDependencyStatus.WaitingForNotification))
{
}
Console.WriteLine(@"ESPERANDO NOTIFICACIONES CONTENEDORESsimple 2");
}
public void Mapping2()
{
var mapper = new ModelToTableMapper <Product>();
mapper.AddMapping(c => c.Code, "[BarCode]");
// Arrange
Expression <Func <Product, bool> > expression = p => p.Code == "042100005264";
// Act
var where = new SqlTableDependencyFilter <Product>(expression, mapper).Translate();
// Assert
Assert.AreEqual("([BarCode] = '042100005264')", where);
}
public void MoreConditions3()
{
var ids = new[] { 1 };
// Arrange
Expression <Func <Product, bool> > expression = p =>
ids.Contains(p.Id) ||
(p.Code.Equals("WWW") && p.Code.Substring(0, 3) == "22");
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("([Id] IN (1) OR ([Code] = 'WWW' AND (SUBSTRING([Code], 0, 3) = '22')))", where);
}
private void iniciar1()
{
_connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
Expression <Func <Contenedor, bool> > expression = p => p.REGIMEN == "EXPO";
ITableDependencyFilter CONDICION = new SqlTableDependencyFilter <Contenedor>(expression);
_sqlTableDependency = new SqlTableDependency <Contenedor>(_connectionString, tableName: "temporal", filter: CONDICION);
_sqlTableDependency.OnChanged += TableDependency_Changed;
_sqlTableDependency.OnError += _sqlTableDependency_OnError;
_sqlTableDependency.OnStatusChanged += _sqlTableDependency_OnStatusChanged;
_sqlTableDependency.Start(watchDogTimeOut: 28800);
while (!(_sqlTableDependency.Status == TableDependency.SqlClient.Base.Enums.TableDependencyStatus.WaitingForNotification))
{
}
Console.WriteLine(@"ESPERANDO NOTIFICACIONES CONTENEDORES 1");
}
public void ColumnContainsDates()
{
var codes = new[] {
DateTime.ParseExact("2010-05-18 14:40:52,531", "yyyy-MM-dd HH:mm:ss,fff", System.Globalization.CultureInfo.InvariantCulture),
DateTime.ParseExact("2009-05-18 14:40:52,531", "yyyy-MM-dd HH:mm:ss,fff", System.Globalization.CultureInfo.InvariantCulture)
};
// Arrange
Expression <Func <Product, bool> > expression = p => codes.Contains(p.ExpireDateTime);
// Act
var where = new SqlTableDependencyFilter <Product>(expression).Translate();
// Assert
Assert.AreEqual("[ExpireDateTime] IN ('2010-05-18T14:40:52','2009-05-18T14:40:52')", where);
}
public void Configure(string ConnectionString)
{
// Define WHERE filter specifing the WHERE condition
// We also pass the mapper defined above as last contructor's parameter
Expression <Func <lmpTbl, bool> > expression = p =>
p.node_id == "PSEG";
ITableDependencyFilter whereCondition = new SqlTableDependencyFilter <lmpTbl>(
expression);
_LMPtableDependency = new SqlTableDependency <lmpTbl>(ConnectionString,
filter: whereCondition, executeUserPermissionCheck: false);
_LMPtableDependency.OnChanged += Changed;
_LMPtableDependency.OnError += TableDependency_OnError;
_LMPtableDependency.Start();
// Define WHERE filter specifing the WHERE condition
Expression <Func <loadTbl, bool> > Loadexpression = p =>
p.Area == "PJM RTO";
ITableDependencyFilter whereLoadCondition = new SqlTableDependencyFilter <loadTbl>(
Loadexpression);
_LoadTableDependency = new SqlTableDependency <loadTbl>(ConnectionString,
filter: whereLoadCondition, executeUserPermissionCheck: false);
_LoadTableDependency.OnChanged += LoadChanged;
_LoadTableDependency.OnError += TableDependency_OnError;
_LoadTableDependency.Start();
_GenFueltableDependency = new SqlTableDependency <GenFuelTbl>(ConnectionString,
executeUserPermissionCheck: false);
_GenFueltableDependency.OnChanged += GenFueltableDependency;
_GenFueltableDependency.OnError += TableDependency_OnError;
_GenFueltableDependency.Start();
_MeterTableDependency = new SqlTableDependency <MeterTbl>(ConnectionString,
executeUserPermissionCheck: false);
_MeterTableDependency.OnChanged += MeterChanged;
_MeterTableDependency.OnError += TableDependency_OnError;
_MeterTableDependency.Start();
Console.WriteLine("Waiting for receiving notifications...");
}