Jun 15, 2007

SQL notifications do not work with SqlDependency

Maybe somebody can hint me why I do net get update change notifications from my SQL Server 2005:


private void SetDependancy()

{

    SqlClientPermission perm = new SqlClientPermission(PermissionState.Unrestricted);

    perm.Demand();

 

    bool result = SqlDependency.Start(this.ConnectionString);

 

    SqlConnection sqlConn = this.CreateWriterConnection();

    SqlCommand cmd = new SqlCommand("SELECT [ID], [CorrelationID], [Sender], [Receiver], [Message], [TimeStamp] FROM [dbo].[tWcfDatabaseChannel]", sqlConn);

    dependency = new SqlDependency(cmd);

    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

 

    try

    {

        sqlConn.Open();

        object res = cmd.ExecuteScalar();

    }

    catch

    {

        // ...

    }

    finally

    {

        sqlConn.Close();

    }

}

 

void dependency_OnChange(object sender, SqlNotificationEventArgs e)

{

    // bubble up this event

    if (this.OnChange != null)

        this.OnChange(sender, e);

 

    SetDependancy();

}



With the following database:

-- Creating a database
CREATE DATABASE [Test]
GO

-- Ensuring that Service Broker is enabled
ALTER DATABASE [Test] SET ENABLE_BROKER
GO

-- Switching to our database
use [Test]

/****** Object: Table [dbo].[tWcfDatabaseChannel] Script Date: 06/15/2007 09:06:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tWcfDatabaseChannel](
[ID] [uniqueidentifier] NOT NULL,
[CorrelationID] [uniqueidentifier] NULL,
[Sender] [nvarchar](200) COLLATE Latin1_General_CI_AS NULL,
[Receiver] [nvarchar](200) COLLATE Latin1_General_CI_AS NULL,
[Message] [ntext] COLLATE Latin1_General_CI_AS NULL,
[TimeStamp] [datetime] NOT NULL,
CONSTRAINT [PK_tWcfDatabaseChannel] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

No comments:

Post a Comment