public void AdminPermissionExceptionCheckTest()
{
const string ScriptDisableBroker = @"
ALTER DATABASE [TestDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [TestDatabase] SET DISABLE_BROKER;
ALTER DATABASE [TestDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE";
// It is impossible to start notification without configured service broker.
ExecuteNonQuery(ScriptDisableBroker, MASTER_CONNECTION_STRING);
bool errorReceived = false;
try
{
using (SqlDependencyEx <TestClass> test = new SqlDependencyEx <TestClass>(new NullLogger <SqlDependencyEx <TestClass> >(),
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp")) test.Start(false);
}
catch (SqlException) { errorReceived = true; }
Assert.AreEqual(true, errorReceived);
// Service broker supposed to be configured automatically with MASTER connection string.
NotificationTest(10, connStr: MASTER_CONNECTION_STRING);
}
private void NoDetailsTest(
int changesCount,
int changesDelayInSec = 0,
string connStr = TEST_CONNECTION_STRING)
{
int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
connStr,
TEST_DATABASE_NAME,
TEST_TABLE_NAME, "temp", receiveDetails: false))
{
sqlDependency.TableChanged += (o, e) =>
{
Assert.AreEqual(SqlDependencyEx.NotificationTypes.None, e.NotificationType);
Assert.AreEqual(0, e.Data.Elements().Count());
changesReceived++;
};
sqlDependency.Start();
Thread.Sleep(changesDelayInSec * 1000);
MakeTableInsertDeleteChanges(changesCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(changesCount, changesReceived);
}
private void NotificationTest(
int changesCount,
int changesDelayInSec = 0,
string connStr = TEST_CONNECTION_STRING)
{
var changesReceived = 0;
using (var sqlDependency = new SqlDependencyEx(
new SqlDependencyExOptions
{
ConnectionString = connStr,
DatabaseName = TEST_DATABASE_NAME,
TableName = TEST_TABLE_NAME,
SchemaName = "temp"
}))
{
sqlDependency.TableChanged += (o, e) => changesReceived++;
sqlDependency.Start();
Thread.Sleep(changesDelayInSec * 1000);
MakeTableInsertDeleteChanges(changesCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(changesCount, changesReceived);
}
public void GetActiveDbListenersTest()
{
Func <int> getDbDepCount =
() =>
SqlDependencyEx.GetDependencyDbIdentities(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME).Length;
var options = new SqlDependencyExOptions
{
ConnectionString = TEST_CONNECTION_STRING,
DatabaseName = TEST_DATABASE_NAME,
TableName = TEST_TABLE_NAME,
SchemaName = "temp"
};
using (var dep1 = new SqlDependencyEx(options, identity: 4))
using (var dep2 = new SqlDependencyEx(options, identity: 5))
{
dep1.Start();
// Make sure db has been got 1 dependency object.
Assert.AreEqual(1, getDbDepCount());
dep2.Start();
// Make sure db has been got 2 dependency object.
Assert.AreEqual(2, getDbDepCount());
}
// Make sure db has no any dependency objects.
Assert.AreEqual(0, getDbDepCount());
}
private DataNotificator()
{
_sqlDependency = new SqlDependencyEx(_connectionString, _databaseName, _tableName,
listenerType: SqlDependencyEx.NotificationTypes.Insert,
identity: _identity);
_sqlDependency.TableChanged += SqlDependencyOnTableChanged;
}
private void SQLDependancyMonitoring()
{
string connectionString = ""; // your Connection string
int table_1_Identey = 1;
int table_2_Identiy = 2;
// you must add unique identity for each table motioning
SqlDependencyEx listener1 = new SqlDependencyEx(connectionString, "Database name", "your Table Name", identity: table_1_Identey);
SqlDependencyEx listener2 = new SqlDependencyEx(connectionString, "your Database name", "your table name", identity: table_1_Identey);
listener1.TableChanged += (o, ee) =>
{
// Here changes in table 1
// you got the rows has been added/ updated/ deleted
// you got row as as XML format string
string UpdatedRow = ee.Data.ToString();
};
// e.Data contains actual changed data in the XML format
listener2.TableChanged += (o, ee) =>
{
// Here changes in table 2
// you got the rows has been added/ updated/ deleted
// you got row as as XML format string
string UpdatedRow = ee.Data.ToString();
};
listener1.Start(); // starting Listing for table 1
listener2.Start(); // starting Listing for table 2
// YOU ,MUST STOP LISTING ON END OF YOUR APPLICATION
listener1.Stop(); // STOP LISTENIG FOR TABLE 1
listener2.Stop(); // STOP LISTENIG FOR TABLE 2
}
public void GetActiveDbListenersTest()
{
Func <int> getDbDepCount =
() =>
SqlDependencyEx.GetDependencyDbIdentities(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME).Length;
using (var dep1 = new SqlDependencyEx(TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME, "temp"
, identity: 4))
using (var dep2 = new SqlDependencyEx(TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME, "temp"
, identity: 5))
{
dep1.Start();
// Make sure db has been got 1 dependency object.
Assert.AreEqual(1, getDbDepCount());
dep2.Start();
// Make sure db has been got 2 dependency object.
Assert.AreEqual(2, getDbDepCount());
}
// Make sure db has no any dependency objects.
Assert.AreEqual(0, getDbDepCount());
}
private Observer(string connectionString, string databaseName, string tableName)
{
Console.WriteLine("Creating Observer Instance! {0}", DateTime.Now);
_Dependency = new SqlDependencyEx(connectionString, databaseName, tableName);
_Dependency.TableChanged += _Dependency_TableChanged;
_Dependency.Start();
}
private void NotificationTest(
int changesCount,
int changesDelayInSec = 0,
string connStr = TEST_CONNECTION_STRING)
{
int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
connStr,
TEST_DATABASE_NAME,
TEST_TABLE_NAME, "temp"))
{
sqlDependency.TableChanged += async(o, e) => await Task.Factory.StartNew(() => changesReceived ++);
sqlDependency.Start();
Thread.Sleep(changesDelayInSec * 1000);
MakeTableInsertDeleteChanges(changesCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(changesCount, changesReceived);
}
public async Task ClearDatabaseTest()
{
Func <Task <int> > getDbDepCount = async() => (await SqlDependencyEx.GetDependencyDbIdentities(TEST_CONNECTION_STRING, TEST_DATABASE_NAME)).Length;
var dep1 = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp",
identity: 4);
var dep2 = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp",
identity: 5);
dep1.Start();
// Make sure db has been got 1 dependency object.
Assert.AreEqual(1, getDbDepCount());
dep2.Start();
// Make sure db has been got 2 dependency object.
Assert.AreEqual(2, getDbDepCount());
// Forced db cleaning
await SqlDependencyEx.CleanDatabase(TEST_CONNECTION_STRING, TEST_DATABASE_NAME);
// Make sure db has no any dependency objects.
Assert.AreEqual(0, getDbDepCount());
dep1.Dispose();
dep2.Dispose();
}
public void MainPermissionExceptionCheckTest()
{
ExecuteNonQuery("USE [TestDatabase] DENY CREATE PROCEDURE TO [TempUser];", MASTER_CONNECTION_STRING);
var errorReceived = false;
try
{
using (var test = new SqlDependencyEx(
new SqlDependencyExOptions
{
ConnectionString = TEST_CONNECTION_STRING,
DatabaseName = TEST_DATABASE_NAME,
TableName = TEST_TABLE_NAME,
SchemaName = "temp"
})) test.Start();
}
catch (SqlException)
{
errorReceived = true;
}
Assert.AreEqual(true, errorReceived);
// It is impossible to start notification without CREATE PROCEDURE permission.
ExecuteNonQuery("USE [TestDatabase] GRANT CREATE PROCEDURE TO [TempUser];", MASTER_CONNECTION_STRING);
errorReceived = false;
try
{
using (var test = new SqlDependencyEx(
new SqlDependencyExOptions
{
ConnectionString = TEST_CONNECTION_STRING,
DatabaseName = TEST_DATABASE_NAME,
TableName = TEST_TABLE_NAME,
SchemaName = "temp"
})) test.Start();
}
catch (SqlException)
{
errorReceived = true;
}
Assert.AreEqual(false, errorReceived);
// There is supposed to be no exceptions with admin rights.
using (var test = new SqlDependencyEx(
new SqlDependencyExOptions
{
ConnectionString = MASTER_CONNECTION_STRING,
DatabaseName = TEST_DATABASE_NAME,
TableName = TEST_TABLE_NAME,
SchemaName = "temp"
})) test.Start();
}
public void ResourcesReleasabilityTest(int changesCount)
{
using (var sqlConnection = new SqlConnection(ADMIN_TEST_CONNECTION_STRING))
{
sqlConnection.Open();
var sqlConversationEndpointsCount = sqlConnection.GetUnclosedConversationEndpointsCount();
var sqlConversationGroupsCount = sqlConnection.GetConversationGroupsCount();
var sqlServiceQueuesCount = sqlConnection.GetServiceQueuesCount();
var sqlServicesCount = sqlConnection.GetServicesCount();
var sqlTriggersCount = sqlConnection.GetTriggersCount();
var sqlProceduresCount = sqlConnection.GetProceduresCount();
using (var sqlDependency = new SqlDependencyEx(
new SqlDependencyExOptions
{
ConnectionString = TEST_CONNECTION_STRING,
DatabaseName = TEST_DATABASE_NAME,
TableName = TEST_TABLE_NAME,
SchemaName = "temp"
}))
{
sqlDependency.Start();
// Make sure we've created one queue, sevice, trigger and two procedures.
Assert.AreEqual(sqlServicesCount + 1, sqlConnection.GetServicesCount());
Assert.AreEqual(
sqlServiceQueuesCount + 1,
sqlConnection.GetServiceQueuesCount());
Assert.AreEqual(sqlTriggersCount + 1, sqlConnection.GetTriggersCount());
Assert.AreEqual(sqlProceduresCount + 2, sqlConnection.GetProceduresCount());
MakeTableInsertDeleteChanges(changesCount);
// Wait a little bit to process all changes.
Thread.Sleep(1000);
}
// Make sure we've released all resources.
Assert.AreEqual(sqlServicesCount, sqlConnection.GetServicesCount());
Assert.AreEqual(
sqlConversationGroupsCount,
sqlConnection.GetConversationGroupsCount());
Assert.AreEqual(
sqlServiceQueuesCount,
sqlConnection.GetServiceQueuesCount());
Assert.AreEqual(
sqlConversationEndpointsCount,
sqlConnection.GetUnclosedConversationEndpointsCount());
Assert.AreEqual(sqlTriggersCount, sqlConnection.GetTriggersCount());
Assert.AreEqual(sqlProceduresCount, sqlConnection.GetProceduresCount());
}
}
public void SpecialFieldNameWithoutSquareBracketsTest()
{
var table1InsertsReceived = 0;
var table1DeletesReceived = 0;
var table1TotalNotifications = 0;
var table1TotalDeleted = 0;
using (var sqlDependencyFirstTable = new SqlDependencyEx(
new SqlDependencyExOptions
{
ConnectionString = TEST_CONNECTION_STRING,
DatabaseName = "TestDatabase",
TableName = "Order2",
SchemaName = "temp"
},
NotificationTypes.Insert,
true,
0))
{
sqlDependencyFirstTable.TableChanged += (sender, args) =>
{
if (args.NotificationType == NotificationTypes.Delete)
{
table1DeletesReceived++;
}
if (args.NotificationType == NotificationTypes.Insert)
{
table1InsertsReceived++;
}
table1TotalNotifications++;
};
if (!sqlDependencyFirstTable.Active)
{
sqlDependencyFirstTable.Start();
}
MakeNullCharacterInsert("[temp].[Order2]", "[Order]");
MakeNullCharacterInsert("[temp].[Order2]", "[Order]");
MakeNullCharacterInsert("[temp].[Order2]", "[Order]");
// Wait for notification to complete
Thread.Sleep(3000);
}
Assert.AreEqual(0, table1DeletesReceived);
Assert.AreEqual(3, table1InsertsReceived);
Assert.AreEqual(3, table1TotalNotifications);
Assert.AreEqual(0, table1TotalDeleted);
}
public void UnsupportedFieldTypeTest()
{
int table1InsertsReceived = 0;
int table1DeletesReceived = 0;
int table1TotalNotifications = 0;
int table1TotalDeleted = 0;
using (var sqlDependencyFirstTable = new SqlDependencyEx(
TEST_CONNECTION_STRING,
"TestDatabase",
"Order3",
"temp",
SqlDependencyEx.NotificationTypes.Insert,
true,
0))
{
sqlDependencyFirstTable.TableChanged += async(sender, args) =>
{
await Task.Factory.StartNew(() =>
{
if (args.NotificationType == SqlDependencyEx.NotificationTypes.Delete)
{
table1DeletesReceived++;
}
if (args.NotificationType == SqlDependencyEx.NotificationTypes.Insert)
{
table1InsertsReceived++;
}
table1TotalNotifications++;
});
};
if (!sqlDependencyFirstTable.Active)
{
sqlDependencyFirstTable.Start();
}
MakeNullCharacterInsert("[temp].[Order3]");
MakeNullCharacterInsert("[temp].[Order3]");
MakeNullCharacterInsert("[temp].[Order3]");
// Wait for notification to complete
Thread.Sleep(3000);
}
Assert.AreEqual(0, table1DeletesReceived);
Assert.AreEqual(3, table1InsertsReceived);
Assert.AreEqual(3, table1TotalNotifications);
Assert.AreEqual(0, table1TotalDeleted);
}
private static void Run(Options args)
{
var listener = new SqlDependencyEx(args.ConnectionString, args.Database, args.Table);
listener.TableChanged += ListenerOnTableChanged;
listener.Start();
DisplayPrompt(args);
do
{
Thread.Sleep(100);
} while (Console.ReadKey().KeyChar != 'x');
listener.Stop();
}
static void Main(string[] args)
{
SqlDependencyEx listener = new SqlDependencyEx(
@"Data Source=rutwik-c\sqlexpress;Initial Catalog=Student.Data.StudentContext;Integrated Security=True",
"Student.Data.StudentContext",
"Studs");
listener.TableChanged += TableChangedEvent;
listener.Start();
Console.WriteLine("Listening..");
Thread.Sleep(100000);
listener.Stop();
Console.WriteLine("Stopped!");
Console.ReadLine();
}
private static void DetailsTest(int insertsCount)
{
var elementsInDetailsCount = 0;
var changesReceived = 0;
using (var sqlDependency = new SqlDependencyEx(
new SqlDependencyExOptions
{
ConnectionString = TEST_CONNECTION_STRING,
DatabaseName = TEST_DATABASE_NAME,
TableName = TEST_TABLE_NAME,
SchemaName = "temp"
}))
{
sqlDependency.TableChanged += (o, e) =>
{
changesReceived++;
if (e.Data == null)
{
return;
}
var inserted = e.Data.Element("inserted");
var deleted = e.Data.Element("deleted");
elementsInDetailsCount += inserted != null
? inserted.Elements("row").Count()
: 0;
elementsInDetailsCount += deleted != null
? deleted.Elements("row").Count()
: 0;
};
sqlDependency.Start();
MakeChunkedInsertDeleteUpdate(insertsCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(insertsCount * 2, elementsInDetailsCount);
Assert.AreEqual(3, changesReceived);
}
static void Main(string[] args)
{
var listener = new SqlDependencyEx("user id=mssuser;password=M3r!7db#uk$@9wd;data source=172.27.137.181;initial catalog=MVC;Application Name=Campaign Manager;", "MVC", "mytable", listenerType: SqlDependencyEx.NotificationTypes.Update);
listener.TableChanged += (o, e) => Console.WriteLine("Your table was changed!:" + e.Data);
//listener.Stop();
listener.Start();
Console.WriteLine("started");
Console.ReadLine();
// listener.Stop();
}
private static void DetailsTest(int insertsCount)
{
int elementsInDetailsCount = 0;
int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME, "temp"))
{
sqlDependency.TableChanged += async(o, e) =>
{
await Task.Factory.StartNew(() =>
{
changesReceived++;
if (e.Data == null)
{
return;
}
var inserted = e.Data.Element("inserted");
var deleted = e.Data.Element("deleted");
elementsInDetailsCount += inserted != null
? inserted.Elements("row").Count()
: 0;
elementsInDetailsCount += deleted != null
? deleted.Elements("row").Count()
: 0;
});
};
sqlDependency.Start();
MakeChunkedInsertDeleteUpdate(insertsCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(insertsCount * 2, elementsInDetailsCount);
Assert.AreEqual(3, changesReceived);
}
private static SqlDependencyEx GetListener(
string connectionStringName,
string tableName,
EventHandler <SqlDependencyEx.TableChangedEventArgs> callbackMethod)
{
var connectionString = Configuration.GetConnectionString(connectionStringName);
var databaseName = new SqlConnection(connectionString).Database;
SqlDependencyEx listener = null;
try
{
listener = new SqlDependencyEx(connectionString, databaseName, tableName);
listener.TableChanged += callbackMethod;
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred while trying to create a listener on table {tableName} in database {databaseName}", ex.Message);
}
return(listener);
}
public void Listen()
{
string cmd = "select name_point from point where post=0;";
// See constructor optional parameters to configure it according to your needs
SqlDependencyEx listener =
new SqlDependencyEx
("Server=sql-001.base2base.com.ua,14332;Database=b2b-test;User Id=base2base-test-user;Password=bW3GtOUQ02o0;",
"b2b-test", "point");
// e.Data contains actual changed data in the XML format
listener.TableChanged += (o, e) => Console.WriteLine("Your table was changed!");
// After you call the Start method you will receive table notifications with
// the actual changed data in the XML format
listener.Start();
// ... Your code is here
// Don't forget to stop the listener somewhere!
listener.Stop();
}
public Task Start()
{
IEntityType[] syncEngineEntityTypes;
string databaseName;
using (var serviceScope = _serviceScopeFactory.CreateScope())
{
var dbContext = serviceScope.ServiceProvider.GetRequiredService <TDbContext>();
syncEngineEntityTypes = dbContext.Model.GetEntityTypes().Where(EntityTypeExtensions.IsSyncEngineEnabled).ToArray();
databaseName = dbContext.Database.GetDbConnection().Database;
}
SqlDependencyEx.CleanDatabase(_configuration.GetConnectionString("LegacyConnection"), databaseName);
_logger.LogInformation("Found {entityTrackingCount} Entities with Sync Engine enabled", syncEngineEntityTypes.Length);
foreach (var entityType in syncEngineEntityTypes)
{
var tableName = entityType.GetTableName();
Interlocked.Increment(ref _SqlDependencyIdentity);
var sqlTableDependency = new SqlDependencyEx(_configuration.GetConnectionString("LegacyConnection"), databaseName, tableName, identity: _SqlDependencyIdentity);
_logger.LogDebug("Create SqlDependency on table {tableName} with identity: {sqlDependencyId}", tableName, _SqlDependencyIdentity);
sqlTableDependency.TableChanged += async(object sender, SqlDependencyEx.TableChangedEventArgs e) => await ProcessChangeEvent(sender);
_sqlTableDependencies.Add(sqlTableDependency);
_logger.LogInformation("Sync Engine configured for Entity: {entityTypeName}", entityType.Name);
}
_sqlTableDependencies.ForEach(s => s.Start());
return(Task.CompletedTask);
}
public void ClearDatabaseTest()
{
Func <int> getDbDepCount =
() =>
SqlDependencyEx.GetDependencyDbIdentities(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME).Length;
var options = new SqlDependencyExOptions
{
ConnectionString = TEST_CONNECTION_STRING,
DatabaseName = TEST_DATABASE_NAME,
TableName = TEST_TABLE_NAME,
SchemaName = "temp"
};
var dep1 = new SqlDependencyEx(
options,
identity: 4);
var dep2 = new SqlDependencyEx(
options,
identity: 5);
dep1.Start();
// Make sure db has been got 1 dependency object.
Assert.AreEqual(1, getDbDepCount());
dep2.Start();
// Make sure db has been got 2 dependency object.
Assert.AreEqual(2, getDbDepCount());
// Forced db cleaning
SqlDependencyEx.CleanDatabase(TEST_CONNECTION_STRING, TEST_DATABASE_NAME);
// Make sure db has no any dependency objects.
Assert.AreEqual(0, getDbDepCount());
dep1.Dispose();
dep2.Dispose();
}
public void MainPermissionExceptionCheckTest()
{
ExecuteNonQuery("USE [TestDatabase] DENY CREATE PROCEDURE TO [TempUser];", MASTER_CONNECTION_STRING);
bool errorReceived = false;
try
{
using (SqlDependencyEx <TestClass> test = new SqlDependencyEx <TestClass>(new NullLogger <SqlDependencyEx <TestClass> >(),
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp")) test.Start(true);
}
catch (SqlException) { errorReceived = true; }
Assert.AreEqual(true, errorReceived);
// It is impossible to start notification without CREATE PROCEDURE permission.
ExecuteNonQuery("USE [TestDatabase] GRANT CREATE PROCEDURE TO [TempUser];", MASTER_CONNECTION_STRING);
errorReceived = false;
try
{
using (SqlDependencyEx <TestClass> test = new SqlDependencyEx <TestClass>(new NullLogger <SqlDependencyEx <TestClass> >(),
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp")) test.Start(false);
}
catch (SqlException) { errorReceived = true; }
Assert.AreEqual(false, errorReceived);
// There is supposed to be no exceptions with admin rights.
using (SqlDependencyEx <TestClass> test = new SqlDependencyEx <TestClass>(new NullLogger <SqlDependencyEx <TestClass> >(),
MASTER_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp")) test.Start(false);
}
static void Main(string[] args)
{
var listener = new SqlDependencyEx(ConfigurationManager.ConnectionStrings["ReconsileConnection"].ConnectionString, ConfigurationManager.AppSettings["DatabaseName"], ConfigurationManager.AppSettings["TableName"]);
ReconclieProcessing processing = new ReconclieProcessing();
listener.TableChanged += (o, e) => processing.RefreshCacheList();
listener.Start();
Thread t = new Thread(processing.ProcessFile);
t.IsBackground = true;
t.Start();
while (true)
{
var keyInfo = Console.ReadKey();
if (keyInfo.Key == ConsoleKey.C && keyInfo.Modifiers == ConsoleModifiers.Control)
{
processing.KeepGoing = false;
listener.Stop();
break;
}
}
t.Join();
}
private DataNotificator()
{
_sqlDependency = new SqlDependencyEx(_connectionString, _databaseName, _tableName);
_sqlDependency.TableChanged += SqlDependencyOnTableChanged;
}
public void TwoTablesNotificationsTest()
{
var table1InsertsReceived = 0;
var table1DeletesReceived = 0;
var table1TotalNotifications = 0;
var table1TotalDeleted = 0;
var table2InsertsReceived = 0;
var table2DeletesReceived = 0;
var table2TotalNotifications = 0;
var table2TotalInserted = 0;
using (var sqlDependencyFirstTable = new SqlDependencyEx(
new SqlDependencyExOptions
{
ConnectionString = TEST_CONNECTION_STRING,
DatabaseName = "TestDatabase",
TableName = "TestTable",
SchemaName = "temp"
},
NotificationTypes.Delete,
true,
0))
{
sqlDependencyFirstTable.TableChanged += (sender, args) =>
{
if (args.NotificationType == NotificationTypes.Delete)
{
table1DeletesReceived++;
table1TotalDeleted += args.Data.Element("deleted").Elements("row").Count();
}
if (args.NotificationType == NotificationTypes.Insert)
{
table1InsertsReceived++;
}
table1TotalNotifications++;
};
if (!sqlDependencyFirstTable.Active)
{
sqlDependencyFirstTable.Start();
}
using (var sqlDependencySecondTable = new SqlDependencyEx(
new SqlDependencyExOptions
{
ConnectionString = TEST_CONNECTION_STRING,
DatabaseName = "TestDatabase",
TableName = "TestTable",
SchemaName = "temp2"
},
NotificationTypes.Insert,
true,
1))
{
sqlDependencySecondTable.TableChanged += (sender, args) =>
{
if (args.NotificationType == NotificationTypes.Delete)
{
table2DeletesReceived++;
}
if (args.NotificationType == NotificationTypes.Insert)
{
table2InsertsReceived++;
table2TotalInserted += args.Data.Element("inserted").Elements("row").Count();
}
table2TotalNotifications++;
};
if (!sqlDependencySecondTable.Active)
{
sqlDependencySecondTable.Start();
}
MakeChunkedInsert(5, TEST_TABLE_1_FULL_NAME);
MakeChunkedInsert(3, TEST_TABLE_2_FULL_NAME);
MakeChunkedInsert(8, TEST_TABLE_2_FULL_NAME);
DeleteFirstRow(TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_2_FULL_NAME);
DeleteFirstRow(TEST_TABLE_2_FULL_NAME);
MakeChunkedInsert(1, TEST_TABLE_2_FULL_NAME);
MakeChunkedInsert(1, TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_2_FULL_NAME);
// Wait for notification to complete
Thread.Sleep(3000);
}
}
Assert.AreEqual(5, table1DeletesReceived);
Assert.AreEqual(0, table1InsertsReceived);
Assert.AreEqual(5, table1TotalNotifications);
Assert.AreEqual(5, table1TotalDeleted);
Assert.AreEqual(3, table2InsertsReceived);
Assert.AreEqual(0, table2DeletesReceived);
Assert.AreEqual(3, table2TotalNotifications);
Assert.AreEqual(12, table2TotalInserted);
}
private static void NotificationTypeTest(int insertsCount, SqlDependencyEx.NotificationTypes testType)
{
int elementsInDetailsCount = 0;
int changesReceived = 0;
int expectedElementsInDetails = 0;
var notificationTypes = GetMembers(testType);
foreach (var temp in notificationTypes)
{
switch (temp)
{
case SqlDependencyEx.NotificationTypes.Insert:
expectedElementsInDetails += insertsCount / 2;
break;
case SqlDependencyEx.NotificationTypes.Update:
expectedElementsInDetails += insertsCount;
break;
case SqlDependencyEx.NotificationTypes.Delete:
expectedElementsInDetails += insertsCount / 2;
break;
}
}
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME, "temp", testType))
{
sqlDependency.TableChanged += (o, e) =>
{
changesReceived++;
if (e.Data == null)
{
return;
}
var inserted = e.Data.Element("inserted");
var deleted = e.Data.Element("deleted");
elementsInDetailsCount += inserted != null
? inserted.Elements("row").Count()
: 0;
elementsInDetailsCount += deleted != null
? deleted.Elements("row").Count()
: 0;
};
sqlDependency.Start();
MakeChunkedInsertDeleteUpdate(insertsCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(expectedElementsInDetails, elementsInDetailsCount);
Assert.AreEqual(notificationTypes.Length, changesReceived);
}
public void TwoTablesNotificationsTest()
{
int table1InsertsReceived = 0;
int table1DeletesReceived = 0;
int table1TotalNotifications = 0;
int table1TotalDeleted = 0;
int table2InsertsReceived = 0;
int table2DeletesReceived = 0;
int table2TotalNotifications = 0;
int table2TotalInserted = 0;
using (var sqlDependencyFirstTable = new SqlDependencyEx <TestClass>(new NullLogger <SqlDependencyEx <TestClass> >(),
TEST_CONNECTION_STRING,
"TestDatabase",
"TestTable",
"temp",
NotificationTypes.Delete,
true,
0))
{
sqlDependencyFirstTable.TableChanged += async(sender, args) =>
{
if (args.NotificationType == NotificationTypes.Delete)
{
table1DeletesReceived++;
table1TotalDeleted += args.Data.Deleted == null ? 0 : 1;
}
if (args.NotificationType == NotificationTypes.Insert)
{
table1InsertsReceived++;
}
table1TotalNotifications++;
};
if (!sqlDependencyFirstTable.Active)
{
sqlDependencyFirstTable.Start(false);
}
using (var sqlDependencySecondTable = new SqlDependencyEx <TestClass>(new NullLogger <SqlDependencyEx <TestClass> >(),
TEST_CONNECTION_STRING,
"TestDatabase",
"TestTable",
"temp2",
NotificationTypes.Insert,
true,
1))
{
sqlDependencySecondTable.TableChanged += async(sender, args) =>
{
if (args.NotificationType == NotificationTypes.Delete)
{
table2DeletesReceived++;
}
if (args.NotificationType == NotificationTypes.Insert)
{
table2InsertsReceived++;
table2TotalInserted += args.Data.Inserted == null ? 0 : 1;
}
table2TotalNotifications++;
};
if (!sqlDependencySecondTable.Active)
{
sqlDependencySecondTable.Start(false);
}
MakeChunkedInsert(5, TEST_TABLE_1_FULL_NAME);
MakeChunkedInsert(3, TEST_TABLE_2_FULL_NAME);
MakeChunkedInsert(8, TEST_TABLE_2_FULL_NAME);
DeleteFirstRow(TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_2_FULL_NAME);
DeleteFirstRow(TEST_TABLE_2_FULL_NAME);
MakeChunkedInsert(1, TEST_TABLE_2_FULL_NAME);
MakeChunkedInsert(1, TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_1_FULL_NAME);
DeleteFirstRow(TEST_TABLE_2_FULL_NAME);
// Wait for notification to complete
Thread.Sleep(3000);
}
}
Assert.AreEqual(5, table1DeletesReceived);
Assert.AreEqual(0, table1InsertsReceived);
Assert.AreEqual(5, table1TotalNotifications);
Assert.AreEqual(5, table1TotalDeleted);
Assert.AreEqual(3, table2InsertsReceived);
Assert.AreEqual(0, table2DeletesReceived);
Assert.AreEqual(3, table2TotalNotifications);
Assert.AreEqual(12, table2TotalInserted);
}
