CODEWITHSUNDEEP
c#.netsqldependency
Fanda
Fanda

Reputation: 3786

How to handle connection loss using .NET SqlDependency

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

Upvotes: 3

Views: 2890

Answers (1)

whoblitz
whoblitz

Reputation: 1065

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.

http://connect.microsoft.com/SQLServer/feedback/details/543921/sqldependency-incorrect-behaviour-after-sql-server-restarts

Upvotes: 4

Related Questions