how to moitor table in sql server 2008 from c# 3.0 application

Question

I have an application which will work as a service for somthing use TCP IP connection
my application or service(because i will convert it to windows service)
need to get some data form sql server 2008
the issue here is new records to this table will be added by other applications and i need my application knows when there is new records added. I don't think its good way to open a connection and keep pulling the database for new records becuase the application must be online with no delay than 30 second. so when a record added the application must know immediately.

Answer 1

see this pages :

1. Change Notification with Sql Server 2008

2. http://msdn.microsoft.com/en-us/library/ms171581.aspx

Answer 2

Another alternative is to use the Service Broker which you can read about here:
http://www.code-magazine.com/article.aspx?quickid=0605061&page=1

I recently did this in a program that receives faxes and allows multiple clients to receive notification, print, and file the faxes. Service Broker isn't recommended for more than 10 clients, but in your case you have just one (your service), so it's perfect for you.

I'll post both the SQL and the C# code that I wrote in the next three threads (it's a bit large). There's a bit of extraneous stuff in there, but you should be able to get an idea of how it works.

Answer 3

Here is the SQL to create a notification system with Service Broker:

CREATE MESSAGE TYPE [DmlNotification] validation = WELL_FORMED_XML;
CREATE CONTRACT [DmlNotification] ([DmlNotification] sent BY initiator);
go

CREATE QUEUE [DmlNotificationSender];
CREATE SERVICE [DmlNotificationSender] ON QUEUE [DmlNotificationSender];
go

CREATE TABLE [DmlNotificationReceivers]
(
	Ident UNIQUEIDENTIFIER PRIMARY KEY,
	DialogHandle UNIQUEIDENTIFIER,
	Expires DATETIME
)
go

CREATE PROCEDURE [BeginDmlConversation]
(
	@ident UNIQUEIDENTIFIER,
	@conversationTimeout int,
	@fullName NVARCHAR(55) OUT
)
AS
BEGIN
	SET @fullName = cast($(IdentPrefix) as NVARCHAR(55)) + cast(@ident as NVARCHAR(55))
	DECLARE @dialogHandle UNIQUEIDENTIFIER
	SELECT @dialogHandle = DialogHandle FROM [DmlNotificationReceivers] WHERE Ident = @ident
	IF @dialogHandle is not null
		UPDATE [DmlNotificationReceivers] SET Expires = DATEADD(SECOND, @conversationTimeout, GETUTCDATE()) WHERE Ident = @ident
	ELSE
	BEGIN
		BEGIN TRY
			BEGIN TRANSACTION
				EXECUTE ('CREATE QUEUE [' + @fullName + ']')
				EXECUTE ('CREATE SERVICE [' + @fullName + '] ON QUEUE [' + @fullName + '] ([DmlNotification])')
				BEGIN DIALOG CONVERSATION @dialogHandle
					FROM SERVICE [DmlNotificationSender] TO SERVICE @fullName ON CONTRACT [DmlNotification] WITH ENCRYPTION = OFF;
				INSERT INTO [DmlNotificationReceivers] VALUES (@ident, @dialogHandle, DATEADD(second, @conversationTimeout, GETUTCDATE()) );
			COMMIT TRANSACTION
			--PRINT 'Created queue and service ' + @fullName
			--PRINT 'Started dialog conversation ' + cast(@dialogHandle as NVARCHAR(55))
		END TRY
		BEGIN CATCH
			ROLLBACK TRANSACTION;
			EXECUTE [dbo].[LogError];
			DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT
			SELECT  @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
			RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
		END CATCH;
	END
END
go

CREATE PROCEDURE [RemoveExpiredDmlConversations]
AS
BEGIN
	DECLARE @nowDate DATETIME = GETUTCDATE()
	DECLARE @count INT
	SELECT @count = count(*) FROM [DmlNotificationReceivers] WHERE Expires <= @nowDate
	IF @count > 0 BEGIN
		--PRINT 'No expired conversations to remove'
	--END
	--ELSE BEGIN
		DECLARE @ident UNIQUEIDENTIFIER
		DECLARE @dialogHandle UNIQUEIDENTIFIER
		DECLARE enumCursor CURSOR FAST_FORWARD FOR
			SELECT Ident, DialogHandle FROM [DmlNotificationReceivers] WHERE Expires <= @nowDate
		OPEN enumCursor
		FETCH NEXT FROM enumCursor INTO @ident, @dialogHandle
		WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE @fullName NVARCHAR(55)
			SET @fullName = cast($(IdentPrefix) as NVARCHAR(55)) + cast(@ident as NVARCHAR(55))
			--PRINT 'Removing expired conversation ' + @fullName
			END CONVERSATION @dialogHandle
			EXECUTE ('DROP SERVICE [' + @fullName + ']')
			EXECUTE ('DROP QUEUE [' + @fullName + ']')
			FETCH NEXT FROM enumCursor INTO @ident, @dialogHandle
		END
		CLOSE enumCursor
		DEALLOCATE enumCursor

		--PRINT 'Deleting expired conversation(s)';
		DELETE FROM [DmlNotificationReceivers] WHERE Expires <= @nowDate
	END
END
go

CREATE PROCEDURE [SendDmlUpdate]
(
	@messageBody XML
)
AS
BEGIN
	-- Remove expired conversations
	EXEC [RemoveExpiredDmlConversations]
	-- Send message to each conversation in [DmlNotificationReceivers]
	--PRINT 'Sending message: ' + cast(@messageBody as nvarchar(max));
	DECLARE @dialogHandle UNIQUEIDENTIFIER;
	DECLARE enumCursor CURSOR FAST_FORWARD FOR SELECT DialogHandle from [DmlNotificationReceivers]
	OPEN enumCursor
	FETCH NEXT FROM enumCursor into @dialogHandle
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--PRINT 'Sending on conversation ' + cast(@dialogHandle as NVARCHAR(55));
		SEND ON CONVERSATION @dialogHandle MESSAGE TYPE [DmlNotification](@messageBody);
		FETCH NEXT FROM enumCursor INTO @dialogHandle
	END
	CLOSE enumCursor
	DEALLOCATE enumCursor
END
go

Answer 4

Here is some SQL showing how to connect notification to a table:

CREATE TABLE TestNotification (ID int, Value int)
go

CREATE TRIGGER TestNotificationDmlNotificationInsert
ON TestNotification FOR INSERT
AS
BEGIN
	DECLARE @messageBody XML
	SELECT @messageBody = (SELECT ID FROM inserted FOR XML PATH ('TestNotificationInserted'))
	EXEC [SendDmlUpdate] @messageBody
END
go

CREATE TRIGGER TestNotificationDmlNotificationUpdate
ON TestNotification FOR UPDATE
AS
BEGIN
	DECLARE @messageBody XML
	SELECT @messageBody = (SELECT ID, Value FROM inserted FOR XML PATH ('TestNotificationUpdated'))
	EXEC [SendDmlUpdate] @messageBody
END
go

CREATE TRIGGER TestNotificationDmlNotificationDelete
ON TestNotification FOR DELETE
AS
BEGIN
	DECLARE @messageBody XML
	SELECT @messageBody = (SELECT ID FROM deleted FOR XML PATH ('TestNotificationDeleted'))
	EXEC [SendDmlUpdate] @messageBody
END
go

Answer 5

Here is a DataManager class I wrote to read two tables and monitor them for changes:

public class DataManager
{
    public DataManager()
    {
        _conversationTimeout = 600;
    }

    public event EventHandler DataReady;
    public event ThreadExceptionEventHandler ThreadException;
    
    public string ConnectionStringText
    {
        get { return ConnectionStringBuilder.ConnectionString; }
        set { ConnectionStringBuilder = new SqlConnectionStringBuilder(value); }
    }

    public SqlConnectionStringBuilder ConnectionStringBuilder
    {
        get;
        private set;
    }

    public int ConversationTimeout
    {
        get { return _conversationTimeout; }
        set
        {
            _conversationTimeout = value;
        }
    }

    // All updating of the tables will be performed in this context.
    public SynchronizationContext SynchronizationContext
    {
        get { return context; }
        set
        {
            if (thread != null)
                throw new InvalidOperationException("Cannot change SynchronizationContext after calling BeginMonitor");
            context = value;
        }
    }

    public void BeginMonitor()
    {
        // If no context was provided, use the current one
        if (context == null)
            context = SynchronizationContext.Current;

        thread = new Thread(MonitorThreadProc);
        thread.IsBackground = true;
        thread.Name = "DataManager Monitor";
        thread.Start();
    }

    public DataTable FaxDataTable
    {
        get
        {
            if (faxDataTable == null)
                throw new InvalidOperationException("Cannot retrieve the DataTable until after the DataReady event fires");
            return faxDataTable;
        }
    }

    public DataTable FolderDataTable
    {
        get
        {
            if (folderDataTable == null)
                throw new InvalidOperationException("Cannot retrieve the DataTable until after the DataReady event fires");
            return folderDataTable;
        }
    }

    private Thread thread;
    private int _conversationTimeout;
    private DataTable faxDataTable = null;
    private DataTable folderDataTable = null;
    private SynchronizationContext context;

    protected void OnDataReady(EventArgs e)
    {
        if (DataReady != null)
            DataReady(this, e);
    }

    protected void OnThreadException(ThreadExceptionEventArgs e)
    {
        if (ThreadException != null)
            ThreadException(this, e);
    }

    private void MonitorThreadProc()
    {
        try
        {
            string ident = Guid.NewGuid().ToString();

            using (var connection = new SqlConnection(ConnectionStringBuilder.ConnectionString))
            {
                connection.InfoMessage += (object sender, SqlInfoMessageEventArgs e) =>
                {
                    if (e.Message != null)
                        Console.WriteLine(e.Message);
                };
                connection.Open();

                // Retreive faxes table
                faxDataTable = new DataTable();
                using (var adapter = new SqlDataAdapter("SELECT [Index], FaxId, CallerId, CSID, TSID, Pages, Size, ReceivedOn, FolderId, WasRead, WasPrinted, Filename FROM Faxes ORDER BY [Index]", connection))
                    adapter.Fill(faxDataTable);
                faxDataTable.PrimaryKey = new DataColumn[] { faxDataTable.Columns["FaxId"] };

                // Retreive folders table
                folderDataTable = new DataTable();
                using (var adapter = new SqlDataAdapter("SELECT FolderId, ParentFolderId, Description, IsSystemFolder, IsVisibleFolder FROM Folders ORDER BY FolderId", connection))
                    adapter.Fill(folderDataTable);
                folderDataTable.PrimaryKey = new DataColumn[] { folderDataTable.Columns["FolderId"] };

                // Notify listeners that data is ready
                if (context != null)
                    context.Post((object state) => OnDataReady(EventArgs.Empty), null);
                else
                    OnDataReady(EventArgs.Empty);

                while (true)
                {
                    int conversationTimeout = ConversationTimeout; // atomic pre-read of property value

                    var command = new SqlCommand { CommandText = "dbo.BeginDmlConversation", Connection = connection, CommandType = CommandType.StoredProcedure };
                    command.Parameters.AddWithValue("@ident", ident);
                    command.Parameters.AddWithValue("@conversationTimeout", conversationTimeout + 60);
                    command.Parameters.Add(new SqlParameter { ParameterName = "@fullName", SqlDbType = SqlDbType.NVarChar, Size = 55, Direction = ParameterDirection.Output, Value = String.Empty });
                    command.ExecuteNonQuery();
                    string fullName = (string)command.Parameters[2].Value;
                    string commandText = String.Format("WAITFOR ( receive cast(message_body as xml) FROM [dbo].[{0}]) ", fullName);
                    string notificationOptions = String.Format("Service=[dbo].[{0}]; Local Database={1}", fullName, ConnectionStringBuilder.InitialCatalog);
                    
                    command = new SqlCommand(commandText, connection);
                    var notificationRequest = new SqlNotificationRequest();
                    notificationRequest.UserData = ident;
                    notificationRequest.Options = notificationOptions;
                    notificationRequest.Timeout = conversationTimeout;
                    command.Notification = notificationRequest;
                    command.CommandTimeout = conversationTimeout + 15;

                    var notifications = new List<SqlXml>();

                    try
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                            while (reader != null && reader.Read())
                                notifications.Add(reader.GetSqlXml(0));
                    }
                    catch (SqlException ex)
                    {
                        // If it's not a timeout error, throw it
                        // The -2 Number is my best guess as I don't know if it means anything else
                        if (ex.Errors.Count != 1 || ex.Number != -2)
                            throw;

                        // TODO test state of connection to determain if we should try and re-open it
                    }

                    if (context != null)
                        context.Post(ParseNotifications, notifications);
                    else
                        ParseNotifications(notifications);
                }
            }
        }
        catch (Exception ex)
        {
            ThreadExceptionEventArgs e = new ThreadExceptionEventArgs(ex);
            if (context != null)
                context.Post((object state) => OnThreadException((ThreadExceptionEventArgs)state), e);
            else
                OnThreadException(e);
        }
    }

    private void ParseNotifications(object state)
    {
        foreach (SqlXml xml in (List<SqlXml>)state)
        {
            Console.WriteLine("NOTIFICATION: {0}", xml.Value);
            XmlReader reader = xml.CreateReader();
            while (reader.Read())
            {
                if (reader.NodeType == XmlNodeType.Element)
                {
                    switch (reader.Name)
                    {
                        case "FaxesInserted":
                            {
                                reader.Read();
                                object[] values = new object[12];
                                values[0] = reader.ReadElementContentAsInt();
                                values[1] = reader.ReadElementContentAsString();
                                values[2] = reader.ReadElementContentAsString();
                                values[3] = reader.ReadElementContentAsString();
                                values[4] = reader.ReadElementContentAsString();
                                values[5] = reader.ReadElementContentAsInt();
                                values[6] = reader.ReadElementContentAsInt();
                                values[7] = reader.ReadElementContentAsDateTime();
                                values[8] = reader.ReadElementContentAsInt();
                                values[9] = false;
                                values[10] = false;
                                values[11] = reader.ReadElementContentAsString();
                                FaxDataTable.Rows.Add(values);
                            }
                            break;
                        case "FaxesUpdated":
                            {
                                reader.Read();
                                string faxId = reader.ReadElementContentAsString();

                                DataRow row = FaxDataTable.Rows.Find(faxId);
                                row["FolderId"] = reader.ReadElementContentAsInt();
                                row["WasRead"] = reader.ReadElementContentAsBoolean();
                                row["WasPrinted"] = reader.ReadElementContentAsBoolean();
                            }
                            break;
                        case "FaxesDeleted":
                            {
                                reader.Read();
                                string faxId = reader.ReadElementContentAsString();

                                DataRow row = FaxDataTable.Rows.Find(faxId);
                                row.Delete();
                            }
                            break;
                        case "FoldersInserted":
                            {
                                reader.Read();
                                object[] values = new object[5];
                                values[0] = reader.ReadElementContentAsInt();
                                values[1] = reader.ReadElementContentAsInt();
                                values[2] = reader.ReadElementContentAsString();
                                values[3] = reader.ReadElementContentAsBoolean();
                                values[4] = reader.ReadElementContentAsBoolean();
                                FolderDataTable.Rows.Add(values);
                            }
                            break;
                        case "FoldersUpdated":
                            {
                                reader.Read();
                                int folderId = reader.ReadElementContentAsInt();

                                DataRow row = FolderDataTable.Rows.Find(folderId);
                                row["ParentFolderId"] = reader.ReadElementContentAsInt();
                                row["Description"] = reader.ReadElementContentAsString();
                            }
                            break;
                        case "FoldersDeleted":
                            {
                                reader.Read();
                                int folderId = reader.ReadElementContentAsInt();

                                DataRow row = FolderDataTable.Rows.Find(folderId);
                                row.Delete();
                            }
                            break;
                    }
                }
            }
        }
    }

    public void AddFolder(string folderName, int parentFolderId)
    {
        var command = new SqlCommand("INSERT INTO Folders values(@ParentFolderId, @Description, 0, 1)");
        command.Parameters.AddWithValue("@ParentFolderId", parentFolderId);
        command.Parameters.AddWithValue("@Description", folderName);
        ThreadPool.QueueUserWorkItem(ExecuteCommand, command);
    }

    public void RenameFolder(int folderId, string newFolderName)
    {
        var command = new SqlCommand("UPDATE Folders SET Description = @Description WHERE FolderId = @FolderId");
        command.Parameters.AddWithValue("@Description", newFolderName);
        command.Parameters.AddWithValue("@FolderId", folderId);
        ThreadPool.QueueUserWorkItem(ExecuteCommand, command);
    }

    public void DeleteEmptyFolder(int folderId)
    {
        var command = new SqlCommand("DELETE FROM Folders WHERE FolderId = @FolderId");
        command.Parameters.AddWithValue("@FolderId", folderId);
        ThreadPool.QueueUserWorkItem(ExecuteCommand, command);
    }

    public void MoveFaxToFolder(string faxId, int folderId)
    {
        var command = new SqlCommand("UPDATE Faxes SET FolderId = @FolderId WHERE FaxId = @FaxId");
        command.Parameters.AddWithValue("@FolderId", folderId);
        command.Parameters.AddWithValue("@FaxId", faxId);
        ThreadPool.QueueUserWorkItem(ExecuteCommand, command);
    }

    public void MarkFaxRead(string faxId)
    {
        var row = FaxDataTable.Rows.Find(faxId);
        if (row != null && (bool)row["WasRead"] == false)
        {
            var command = new SqlCommand("UPDATE Faxes SET WasRead = 1 WHERE FaxId = @FaxId");
            command.Parameters.AddWithValue("@FaxId", faxId);
            ThreadPool.QueueUserWorkItem(ExecuteCommand, command);
        }
    }

    public void MarkFaxPrinted(string faxId)
    {
        var row = FaxDataTable.Rows.Find(faxId);
        if (row != null && (bool)row["WasPrinted"] == false)
        {
            var command = new SqlCommand("UPDATE Faxes SET WasPrinted = 1 WHERE FaxId = @FaxId");
            command.Parameters.AddWithValue("@FaxId", faxId);
            ThreadPool.QueueUserWorkItem(ExecuteCommand, command);
        }
    }

    private void ExecuteCommand(object state)
    {
        SqlCommand command = (SqlCommand)state;
        try
        {
            using (var connection = new SqlConnection(ConnectionStringBuilder.ConnectionString))
            {
                connection.InfoMessage += (object sender, SqlInfoMessageEventArgs e) =>
                {
                    if (e.Message != null)
                        Console.WriteLine(e.Message);
                };
                connection.Open();
                command.Connection = connection;
                command.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            if (ThreadException != null)
                ThreadException(this, new ThreadExceptionEventArgs(ex));
        }
    }
}

Answer 6

Qasemt ,Tergiver  Thanks for your fast and good replay .
Tergiver the Service Broker and Notifications serivce seems to be good but as i read in Qasmet first link that the Notifications serivce   is now deprecated
i will use System.Data.SqlClient.SqlDependency with Service Broker as Tergiver solution 
i need a confirmation if this way is  deprecated ??
Tergiver  i want to ask if you face any drawback for this method?

thanks all again.

Answer 7

cast

 

($(IdentPrefix) as NVARCHAR(55))

its return an error here what is IdentPrefix ? exactly???.
thanks in advance

Answer 8

Sorry, I just copied my code and pasted it. $(IdentPrefix) is a SQLCMD macro. I build my databases with .Sql files invoked with SqlCmd.exe through a .Bat file.

sqlcmd -b -S %SQLSERVER% %SQLLOGIN% -d %SQLDATABASENAME% -i CreateNotifications.sql -v IdentPrefix='DMLN'

The "-v IdentPrefix='DMLN'" produces the substitution in the script.

I have encountered no drawbacks at all. The problem with notification is that you only know that something changed, not what. The benefit of Service Broker is that you don't even have to re-query the database. If you do it similar to how I did (sending XML message), you get all of the info in the change notification.

It is limited to no more than 10 clients (by recommendation), so it has somewhat limited use, but for my purpose (6 clients) and yours (1 client), it's perfect.

The only issue I have, is that one //TODO line in the C# code. If the client machine goes to sleep and wakes up, an exception occurs in DataManager (because the db connection was dropped I suppose). I need to deal with that issue, but have not gotten around to it yet. In the production environment where this code is used, the net result is that the client program has to be restarted and this has so far not been a big issue for them. All but one of their machines stay powered up all day and are shut down at night; Hense my lack of urgency to fix it.

Answer 9

thank you Tergiver you are great .
i execute the code on sql managment studio as a query and every thing is great but it gives this

The module 'BeginDmlConversation' depends on the missing object 'dbo.LogError'. The module will still be created; however, it cannot run successfully until the object exists.

i think this is not an issue because it is in the catch statment and used as i think for logging. i will continue working to complete my system and many thanks to you.

Answer 10

hello again Tergiver
thanks for helping me in this issue i applied your code but i have a little problem
i can see the service and the queue and every thing seems to be good
and i worte a c# code very close to your code
but the problem it's not working
after a little debuging
i can see that

WAITFOR

 

( receive cast(message_body as xml) FROM [dbo].[DMLND76AF0B5-C0B2-430E-A47F-5B7789C1DE1A])
i tried to execute it on sql server 2008 query analyizer and it

remains waiting for message even if i insert a new record  also even if i tried to execute send

SendDmlUpdate

 

manually in   query analyizer
its still waiting and nothing recieved
please help me how can i debug it how to know where is the error
??

manually in   query analyizer
its still waiting and nothing recieved
please help me how can i debug it how to know where is the error
??

 

Answer 11

thank you Tergiver you are great .
i execute the code on sql managment studio as a query and every thing is great but it gives this

The module 'BeginDmlConversation' depends on the missing object 'dbo.LogError'. The module will still be created; however, it cannot run successfully until the object exists.

i think this is not an issue because it is in the catch statment and used as i think for logging. i will continue working to complete my system and many thanks to you.

Just remove the line "EXECUTE [dbo].[LogError]. That is a SP that I use for database error logging. It wasn't really intended that you copy and paste this code, but read it and get an understanding of what's involved.

Answer 12

You have to enable Service Broker on the database:

ALTER DATABASE [DatabaseName] SET ENABLE_BROKER

Create the TestNotification table (above). Execute this SQL:

declare @ident as UNIQUEIDENTIFIER
set @ident = 'D76AF0B5-C0B2-430E-A47F-5B7789C1DE1A'
declare @fullName as nvarchar(55)
exec dbo.begindmlconversation @ident, 120, @fullName output 
exec ('WAITFOR ( receive cast(message_body as xml) FROM [dbo].[' + @fullName + '] )')

Then in another query window execute:

insert into TestNotification values (1, 2)

Answer 13

btw, the TestNotificationDmlNotificationInsert trigger should be altered:

CREATE TRIGGER TestNotificationDmlNotificationInsert
ON TestNotification FOR INSERT
AS
BEGIN
	DECLARE @messageBody XML
	SELECT @messageBody = (SELECT ID, Value FROM inserted FOR XML PATH ('TestNotificationInserted'))
	EXEC [SendDmlUpdate] @messageBody
END
go

Notice I added ", Value" to the SELECT statement there so that the insert notification includes the value column.

Answer 14

I've been reading my code and discovered several issues (and a bug or two). Most are non-issues for my case, but you may encounter problems, so I'll describe those I think you might encounter.

In my DataManager I am reading the tables (fully) and then opening a dialog conversation. There is a chance (extremely small in my case) that an insert/update/delete can occur between the read and the opening of a conversation. Since a conversation object doesn't exist until BeginDmlConversation is executed, any changes occurring between the read and that SP being executed will not be logged to the conversation queue.

The other issue is with BEGIN DIALOG CONVERSATION. A dialog conversation object in the database has a lifetime. If that lifetime expires, a query against that conversation (WAITFOR) will result in an error. By default, the lifetime is int.Max seconds (roughly 4,085 years). In my case a new conversation (new GUID) is created everytime BeginMonitor is called (every time the client is started up), and there is exactly zero chance that a client will run continuously for 4,085 years.

Since you only have one client, you don't need to have a client list (DmlNotificationReceivers table). In order to make your service as bullet-proof as possible (and potentially long-lived), you might consider the following sequence of events:

1) Open connection
2) Begin a transaction
4) Drop the existing queue if it exists and create a new queue
5) Drop the existing service if it exists and create a new service
3) Read table(s)
6) Begin dialog conversation specifying a long lifetime value, maybe 24 hours
7) End transaction
8) Issue Waitfor command with the timeout just a little shy of the one above

9a) If a notification is received (i.e. command.ExecuteReader returns without exception) parse the notifications and return to step #8
9b) If an exception occurs during ExecuteReader return to step #1. The exception should either be a timeout of the command or a dropped connection issue (assuming no bugs in the queries or code that parses the notifications).

Note that the reason you want to drop the queue and create a new one is that you don't want any 'old' notifications being sent (where 'old' means notifications that might have been added while you were restarting at step #1). Can you use an existing service without creating/dropping it? I don't know the answer to that one, so I would do it anyhow.

Answer 15

I'm still thinking about this. Once you create a queue and dialog conversation object on the database, the db will continue to queue up notifications, even when you're not connected. As long as your copy of the table(s) exist in memory (i.e. your service has not been restarted), you don't have to re-query for them. Your steps could look like this:

// When service starts up
Open connection
End any existing conversation, drop any existing queue and service.
Create queue and service
Read table(s)
Begin dialog conversation with default timeout (4k years)
Close connection

// Begin monitoring
1) Open connection
2) Issue WaitFor command with infinate timeout
3a) If notification is received parse notifications and return to step #2
3b) If SqlException occurs return to step #1 (use finally block to close connection)

Note that you're going to use the same object names for queue, service, and conversation, even between start/stop of service (not sure if that was clear in my last post).

Answer 16

Thanks very much Tergiver you are great i worked on my code and it works fine with me i am reading your notes and i will take them inconsideration
thanks you again.