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); }
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 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 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()); }
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(); }
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); 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); // Service broker supposed to be configured automatically with MASTER connection string. NotificationTest(10, connStr: MASTER_CONNECTION_STRING); }
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 }
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); }
public void Start() { listener.TableChanged += (o, e) => processing.RefreshCacheList(); listener.Start(); Thread t = new Thread(processing.ProcessFile); t.IsBackground = true; t.Start(); //t.Join(); }
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 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); }
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); }
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); }
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 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(); }
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(); }
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); }
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); }
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); }
public async Task Listen() { await _sqlDependency.Start(); }
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); }