I need to register SqlDependency to watch new data on remote SQL Server (2005). Connection loss is probable, because connections are across cities and countries. How should I to handle connection loss? In normal mode I can use exception on .Open(), but using SqlDependency connection is still opened.
Thank you for your tips.
SqlCommand command = new SqlCommand("SELECT id FROM dbo.batches WHERE terminalId = @terminalId", msConnection);
command.Parameters.AddWithValue("@terminalId", SqlDbType.Int);
command.Parameters["@terminalId"].Value = terminalId;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(NewBatchHandler);
SqlDependency.Start(connectionString);
command.ExecuteNonQuery();
You can use an overloaded constructor to decrease the timeout, at least reducing the frequency of this issue. This is an issue MS knows about, and unfortunately has no fix I am aware of.
Microsoft party line is they know it's an issue and aren't going to fix it. In the past, I have rolled my own solutions based on polling when this became a real issue with a production database.