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 += (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);
        }
        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 += (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);
        }
        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 ResourcesReleasabilityTest(int changesCount)
        {
            using (var sqlConnection = new SqlConnection(ADMIN_TEST_CONNECTION_STRING))
            {
                sqlConnection.Open();

                int sqlConversationEndpointsCount = sqlConnection.GetUnclosedConversationEndpointsCount();
                int sqlConversationGroupsCount = sqlConnection.GetConversationGroupsCount();
                int sqlServiceQueuesCount = sqlConnection.GetServiceQueuesCount();
                int sqlServicesCount = sqlConnection.GetServicesCount();
                int sqlTriggersCount = sqlConnection.GetTriggersCount();
                int sqlProceduresCount = sqlConnection.GetProceduresCount();

                using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
                            TEST_CONNECTION_STRING,
                            TEST_DATABASE_NAME,
                            TEST_TABLE_NAME, "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 ClearDatabaseTest()
        {
            Func<int> getDbDepCount =
                () =>
                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
            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;

            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());
        }
        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 test = new SqlDependencyEx(
                        TEST_CONNECTION_STRING,
                        TEST_DATABASE_NAME,
                        TEST_TABLE_NAME,
                        "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);
        }
        public void MainPermissionExceptionCheckTest()
        {
            ExecuteNonQuery("USE [TestDatabase] DENY CREATE PROCEDURE TO [TempUser];", MASTER_CONNECTION_STRING);
            bool errorReceived = false;
            try
            {
                using (SqlDependencyEx test = new SqlDependencyEx(
                        TEST_CONNECTION_STRING,
                        TEST_DATABASE_NAME,
                        TEST_TABLE_NAME,
                        "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 (SqlDependencyEx test = new SqlDependencyEx(
                        TEST_CONNECTION_STRING,
                        TEST_DATABASE_NAME,
                        TEST_TABLE_NAME,
                        "temp")) test.Start();
            }
            catch (SqlException) { errorReceived = true; }

            Assert.AreEqual(false, errorReceived);

            // There is supposed to be no exceptions with admin rights.
            using (SqlDependencyEx test = new SqlDependencyEx(
                    MASTER_CONNECTION_STRING,
                    TEST_DATABASE_NAME,
                    TEST_TABLE_NAME,
                    "temp")) test.Start();
        }