dyatchenko / servicebrokerlistener Goto Github PK

Msg 156, Niveau 15, État 1, Procédure tr_Listener_1, Ligne 21
Syntaxe incorrecte vers le mot clé 'Not'.

public partial class Default : System.Web.UI.Page, IDisposable
{
    SqlDependencyEx sqlDependency;

    public Default()
    {
        string DBConn = ConfigurationManager.ConnectionStrings["DBConn"].ToString();
        sqlDependency = new SqlDependencyEx(DBConn, "TestDB", "tbl_MessageDetail", "dbo");
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        RegisterNotification();
        /*outputResult is a DIV - this is shown*/
        outputResult.InnerHtml += ("Registered Notifier: " + DateTime.Now.ToShortTimeString() +  "<br/>");
    }

    private void RegisterNotification()
    {
        sqlDependency.Stop();
        sqlDependency.TableChanged += OnDataChange;
        Thread.Sleep(2000);
        sqlDependency.Start();
        /*outputResult is a DIV - this is shown*/
        outputResult.InnerHtml += ("SQL Dependency Started: " + DateTime.Now.ToShortTimeString() +  "<br/>");
    }

    private void UnregisterNotification()
    {
        sqlDependency.Stop();
        sqlDependency.TableChanged -= OnDataChange;
    }

    private void OnDataChange(object sender, SqlDependencyEx.TableChangedEventArgs e)
    {
        string result = e.Data.FirstNode.ToString();
        /*outputResult is a DIV - where the result is NOT showing on change*/
        outputResult.InnerHtml += result + "<br/>";
    }

    public override void Dispose()
    {
        // Clean up the resources!
        UnregisterNotification();
    }
}

            SqlDependencyExNew sqlDependency = new SqlDependencyExNew(
               connectionString: conx, databaseName: "db", tableName: "table", listenerType: NotificationTypes.Update, identity: 1);
            sqlDependency.TableChanged += (o, e) => OnChange(o, e);


            SqlDependencyExNew sqlDependency2 = new SqlDependencyExNew(
               connectionString: conx, databaseName: "db", tableName: "table", listenerType: NotificationTypes.Update, identity: 2);
            sqlDependency.TableChanged += (o, e) => OnChangeA(o, e);


            sqlDependency.Start();
            sqlDependency2.Start();
            Console.WriteLine("______________Escuchando_______________");

            Console.ReadKey();
            sqlDependency.Stop();
            sqlDependency2.Stop();
        }
        catch (Exception exp)
        {
            if (listener.Active == true)
            {
                listener.Stop();
            }
            if (listener2.Active == true)
            {
                listener2.Stop();
            }
            throw;
        }
    }

    private static async void OnChangeA(object o, SqlDependencyExNew.TableChangedEventArgs e)
    {
        await SOMETHING(o, e);
    }

    private static Task<XElement> SOMETHING(object o, TableChangedEventArgs e)
    {
        var xxx = e.Data;
        return Task.Run(() =>
        {
            return xxx;
        });
    }

    private static async void OnChange(object o, SqlDependencyExNew.TableChangedEventArgs e)
    {
        await SOMETHING(o, e);
    }

System.Data.SqlClient.SqlException was unhandled
  Class=16
  ErrorCode=-2146232060
  HResult=-2146232060
  LineNumber=29
  Message=User does not have permission to perform this action.
Changed database context to '<database>'.
  Number=15247
  Procedure=sp_InstallListenerNotification_1
  Server=<serverip>
  Source=.Net SqlClient Data Provider
  State=1
  StackTrace:
       à System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       à System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       à System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       à System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       à System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
       à System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
       à System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       à ServiceBrokerListener.Domain.SqlDependencyEx.ExecuteNonQuery(String commandText, String connectionString) dans C:\Users\bbigras\Documents\Visual Studio 2015\Projects\WindowsFormsApplication1\WindowsFormsApplication1\SqlDependencyEx.cs:ligne 632
       à ServiceBrokerListener.Domain.SqlDependencyEx.InstallNotification() dans C:\Users\bbigras\Documents\Visual Studio 2015\Projects\WindowsFormsApplication1\WindowsFormsApplication1\SqlDependencyEx.cs:ligne 751
       à ServiceBrokerListener.Domain.SqlDependencyEx.Start() dans C:\Users\bbigras\Documents\Visual Studio 2015\Projects\WindowsFormsApplication1\WindowsFormsApplication1\SqlDependencyEx.cs:ligne 532
       à WindowsFormsApplication1.YourClass.RegisterNotification() dans C:\Users\bbigras\Documents\Visual Studio 2015\Projects\WindowsFormsApplication1\WindowsFormsApplication1\YouClass.cs:ligne 33
       à WindowsFormsApplication1.YourClass.Go() dans C:\Users\bbigras\Documents\Visual Studio 2015\Projects\WindowsFormsApplication1\WindowsFormsApplication1\YouClass.cs:ligne 27
       à WindowsFormsApplication1.Form1..ctor() dans C:\Users\bbigras\Documents\Visual Studio 2015\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Form1.cs:ligne 85
       à WindowsFormsApplication1.Program.Main() dans C:\Users\bbigras\Documents\Visual Studio 2015\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Program.cs:ligne 28
       à System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       à System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       à Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       à System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       à System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       à System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

        string connectionString = ConfigurationManager.ConnectionStrings["EmployeeEntityModel"].ConnectionString;
       


        // See constructor optional parameters to configure it according to your needs
        var listener = new SqlDependencyEx(connectionString, "SignalRDemo", "Employee", listenerType: SqlDependencyEx.NotificationTypes.Update, receiveDetails: true, identity: 1);

        // e.Data contains actual changed data in the XML format
        listener.TableChanged += Listener_TableChanged;

        // After you call the Start method you will receive table notifications with 
        // the actual changed data in the XML format
        listener.Start();


        var listener1 = new SqlDependencyEx(connectionString, "SignalRDemo", "Employee", listenerType: SqlDependencyEx.NotificationTypes.Insert, receiveDetails: true, identity: 2);

        // e.Data contains actual changed data in the XML format
        listener1.TableChanged += Listener1_TableChanged;

        // After you call the Start method you will receive table notifications with 
        // the actual changed data in the XML format
        listener1.Start();
       
    }

    private static void Listener1_TableChanged(object sender, SqlDependencyEx.TableChangedEventArgs e)
    {
        throw new NotImplementedException();
    }

    private static void Listener_TableChanged(object sender, SqlDependencyEx.TableChangedEventArgs e)
    {
        throw new NotImplementedException();
    }

System.Data.SqlClient.SqlException: Cannot drop the service 'ListenerService_1', because it does not exist or you do not have permission.
Changed database context to 'TestDatabase'.
Errors: System.Data.SqlClient.SqlErrorCollection

USE [TestDatabase]
            IF OBJECT_ID ('temp.sp_UninstallListenerNotification_1', 'P') IS NOT NULL
                EXEC temp.sp_UninstallListenerNotification_1

    [TestFixture]
    public class SqlDependencyExTest
    {
        private const string MASTER_CONNECTION_STRING =
            "Data Source=localhost\\sql2005;Initial Catalog=master;Integrated Security=True";

        private const string TEST_CONNECTION_STRING =
            "Data Source=localhost\\sql2005;Initial Catalog=TestDatabase;User Id=TempLogin;Password=8fdKJl3$nlNv3049jsKK;";

        private const string ADMIN_TEST_CONNECTION_STRING =
            "Data Source=localhost\\sql2005;Initial Catalog=TestDatabase;Integrated Security=True";

        private const string INSERT_FORMAT =
            "USE [TestDatabase] INSERT INTO temp.[TestTable] (TestField) VALUES({0})";

        private const string REMOVE_FORMAT =
            "USE [TestDatabase] DELETE FROM temp.[TestTable] WHERE TestField = {0}";

        private const string TEST_DATABASE_NAME = "TestDatabase";

        private const string TEST_TABLE_NAME = "TestTable";

        private const string TEST_TABLE_1_FULL_NAME = "temp.TestTable";

        private const string TEST_TABLE_2_FULL_NAME = "temp2.TestTable";

        [SetUp]
        public void TestSetup()
        {
            const string CreateDatabaseScript = @"
                CREATE DATABASE TestDatabase;

                ALTER DATABASE [TestDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
                ALTER DATABASE [TestDatabase] SET ENABLE_BROKER; 
                ALTER DATABASE [TestDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE

                -- FOR SQL Express
                ALTER AUTHORIZATION ON DATABASE::[TestDatabase] TO [sa]
                ALTER DATABASE [TestDatabase] SET TRUSTWORTHY ON; ";
            const string CreateUserScript = @"
                CREATE LOGIN TempLogin 
                WITH PASSWORD = '8fdKJl3$nlNv3049jsKK', DEFAULT_DATABASE=TestDatabase;

                USE [TestDatabase];
                CREATE USER TempUser FOR LOGIN TempLogin;

                GRANT CREATE PROCEDURE TO [TempUser];
                GRANT CREATE SERVICE TO [TempUser];
                GRANT CREATE QUEUE  TO [TempUser];
                GRANT REFERENCES ON CONTRACT::[DEFAULT] TO [TempUser]
                GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [TempUser];
                GRANT CONTROL ON SCHEMA::[temp] TO [TempUser]
                GRANT CONTROL ON SCHEMA::[temp2] TO [TempUser];  
                ";
            const string CreateTable1Script = @"                
                CREATE SCHEMA Temp
                    CREATE TABLE TestTable (TestField int, StrField NVARCHAR(MAX));
                ";
            const string CreateTable2Script = @"
                CREATE SCHEMA Temp2
                    CREATE TABLE TestTable (TestField int, StrField NVARCHAR(MAX));";

            TestCleanup();

            ExecuteNonQuery(CreateDatabaseScript, MASTER_CONNECTION_STRING);
            ExecuteNonQuery(CreateTable1Script, ADMIN_TEST_CONNECTION_STRING);
            ExecuteNonQuery(CreateTable2Script, ADMIN_TEST_CONNECTION_STRING);
            ExecuteNonQuery(CreateUserScript, MASTER_CONNECTION_STRING);
        }

        [TearDown]
        public void TestCleanup()
        {
            const string DropTestDatabaseScript = @"
                IF (EXISTS(select * from sys.databases where name='TestDatabase'))
                BEGIN
                    ALTER DATABASE [TestDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                    DROP DATABASE [TestDatabase]
                END
                IF (EXISTS(select * from master.dbo.syslogins where name = 'TempLogin'))
                BEGIN
                    DECLARE @loginNameToDrop sysname
                    SET @loginNameToDrop = 'TempLogin';

                    DECLARE sessionsToKill CURSOR FAST_FORWARD FOR
                        SELECT session_id
                        FROM sys.dm_exec_sessions
                        WHERE login_name = @loginNameToDrop
                    OPEN sessionsToKill

                    DECLARE @sessionId INT
                    DECLARE @statement NVARCHAR(200)

                    FETCH NEXT FROM sessionsToKill INTO @sessionId

                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                        PRINT 'Killing session ' + CAST(@sessionId AS NVARCHAR(20)) + ' for login ' + @loginNameToDrop

                        SET @statement = 'KILL ' + CAST(@sessionId AS NVARCHAR(20))
                        EXEC sp_executesql @statement

                        FETCH NEXT FROM sessionsToKill INTO @sessionId
                    END

                    CLOSE sessionsToKill
                    DEALLOCATE sessionsToKill

                    PRINT 'Dropping login ' + @loginNameToDrop
                    SET @statement = 'DROP LOGIN [' + @loginNameToDrop + ']'
                    EXEC sp_executesql @statement
                END
                ";
            ExecuteNonQuery(DropTestDatabaseScript, MASTER_CONNECTION_STRING);
        }

        [Test]
        public void NotificationTestWith10ChangesAnd60SecDelay()
        {
            NotificationTest(10, 60);
        }

        [Test]
        public void NotificationTestWith10Changes()
        {
            NotificationTest(10);
        }

        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(10000);
            }

            Assert.AreEqual(changesCount, changesReceived);
        }

        private static void MakeTableInsertDeleteChanges(int changesCount)
        {
            for (int i = 0; i < changesCount / 2; i++)
            {
                ExecuteNonQuery(string.Format(INSERT_FORMAT, i), MASTER_CONNECTION_STRING);
                ExecuteNonQuery(string.Format(REMOVE_FORMAT, i), MASTER_CONNECTION_STRING);
            }
        }

        private static void ExecuteNonQuery(string commandText, string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand(commandText, conn))
            {
                conn.Open();
                command.CommandType = CommandType.Text;
                command.CommandTimeout = 60000;
                command.ExecuteNonQuery();
            }
        }

    }

private const string SQL_FORMAT_CREATE_INSTALLATION_PROCEDURE = @"
                USE [{0}]
                " + SQL_PERMISSIONS_INFO + @"
                IF OBJECT_ID ('{6}.{1}', 'P') IS NULL
                BEGIN
                    EXEC ('
                        CREATE PROCEDURE {6}.{1}
                        AS
                        BEGIN
                            -- Service Broker configuration statement.
                            {2}
                            -- Notification Trigger check statement.
                            {4}
                            -- Notification Trigger configuration statement.
                            DECLARE @triggerStatement NVARCHAR(MAX)
                            DECLARE @select NVARCHAR(MAX)
                            DECLARE @sqlInserted NVARCHAR(MAX)
                            DECLARE @sqlDeleted NVARCHAR(MAX)

                            SET @triggerStatement = N''{3}''

                            SET @select = STUFF((SELECT '','' + COLUMN_NAME
                                                 FROM INFORMATION_SCHEMA.COLUMNS
                                                 WHERE DATA_TYPE NOT IN  (''text'',''ntext'',''image'') AND TABLE_NAME = ''{5}'' AND TABLE_CATALOG = ''{0}''
                                                 FOR XML PATH ('''')
                                                 ), 1, 1, '''')
                            SET @sqlInserted = 
                                N''SET @retvalOUT = (SELECT '' + @select + N'' 
                                                     FROM INSERTED 
                                                     FOR XML PATH(''''row''''), ROOT (''''inserted''''))''
                            SET @sqlDeleted = 
                                N''SET @retvalOUT = (SELECT '' + @select + N'' 
                                                     FROM DELETED 
                                                     FOR XML PATH(''''row''''), ROOT (''''deleted''''))''                            
                            SET @triggerStatement = REPLACE(@triggerStatement
                                                     , ''%inserted_select_statement%'', @sqlInserted)
                            SET @triggerStatement = REPLACE(@triggerStatement
                                                     , ''%deleted_select_statement%'', @sqlDeleted)
                            EXEC sp_executeSql @triggerStatement
                        END
                        ')
                END
            ";

        using (SqlDependencyEx sqlDependency =  new SqlDependencyEx(ConfigurationManager.ConnectionStrings["dbConnectionstring"].ConnectionString, "EIS_BMT", "EIS_Notifications"))
        {


            sqlDependency.TableChanged += dependency_OnChange;
            // Make table changes.
            sqlDependency.Start();

            // Wait a little bit to receive all changes.
            Thread.Sleep(1000);


            IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();
            return context.Clients.All.RecieveNotification(totalNewMessages, Messages);
        }

private const string CONNECTION_STRING  = "Server=LFTCMCPTP83;Database=Database;Trusted_Connection=True;MultipleActiveResultSets=true;  Integrated Security=false;User ID=used_id;Password=password";
private const string DATABASE_NAME = "db_name";

private const string TABLE_NAME = "table_name";

private const string SCHEMA_NAME = "dbo";

private SqlDependencyEx sqlDependency = new SqlDependencyEx(CONNECTION_STRING, DATABASE_NAME,
    TABLE_NAME, SCHEMA_NAME);

private void RegisterNotification()
{
    sqlDependency.TableChanged += OnDataChange;
    sqlDependency.Start();
}

private void OnDataChange(object sender, SqlDependencyEx.TableChangedEventArgs e)
{
    //Code
}

public IActionResult Create(){
     RegisterNotification();
}`

private static readonly List<int> ActiveEntities = new List<int>(); 

private static readonly ConcurrentDictionary<int, byte> ActiveEntities = new ConcurrentDictionary<int, byte>();

lock (ActiveEntities)
            {
                if (ActiveEntities.Contains(this.Identity))
                    throw new InvalidOperationException("An object with the same identity has already been started.");
                ActiveEntities.Add(this.Identity);
            }

ActiveEntities.TryAdd(this.Identity, byte.MinValue);

lock (ActiveEntities)
                if (ActiveEntities.Contains(Identity)) ActiveEntities.Remove(Identity);

byte itemRemoved;
            ActiveEntities.TryRemove(Identity, out itemRemoved);

BEGIN TRY END CONVERSATION @ConvHandle; END TRY BEGIN CATCH END CATCH

SET @select = STUFF((SELECT '','' + COLUMN_NAME
                                                FROM INFORMATION_SCHEMA.COLUMNS
                                                WHERE TABLE_NAME = ''{5}'' AND TABLE_CATALOG = ''{0}''
                                                FOR XML PATH ('''')
                                                ), 1, 1, '''')

    private const string SQL_FORMAT_DROP_PROCEDURE = @"
            USE [{0}]
            IF OBJECT_ID ('{2}.{1}', 'P') IS NOT NULL
                DROP PROCEDURE temp.[{1}]
        ";

    private const string SQL_FORMAT_DROP_PROCEDURE = @"
            USE [{0}]
            IF OBJECT_ID ('{2}.{1}', 'P') IS NOT NULL
                DROP PROCEDURE {2}.[{1}]
        ";

The Service Broker in database "XXX" cannot be enabled because there is already an enabled Service Broker with the same ID.

ALTER DATABASE [{0}] SET ENABLE_BROKER; 

ALTER DATABASE [{0}] SET NEW_BROKER; 

this.sqlDependencyMailSent = new SqlDependencyEx(
                           ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString,
                           "costerdb",
                           "PLAN_AlarmRelayQueue",
                           "dbo",
                           SqlDependencyEx.NotificationTypes.Delete,true,0);
            this.sqlDependencyMailSent.TableChanged += onMailSent;
            this.enqueuedId = -1;
            if(!this.sqlDependencyMailSent.Active)
                this.sqlDependencyMailSent.Start();

            this.sqlDependencyMailAcked = new SqlDependencyEx(
                                               ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString,
                                               "costerdb",
                                               "PLAN_AlarmRelayAck",
                                               "dbo",
                                               SqlDependencyEx.NotificationTypes.Insert,true,1);

            this.sqlDependencyMailAcked.TableChanged += onMailNotified;
            if (!this.sqlDependencyMailAcked.Active)
                `this.sqlDependencyMailAcked.Start();

//sqlDependency.TableChanged -= OnDataChangeAsync;
sqlDependency.Stop();