471,320 Members | 2,068 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,320 software developers and data experts.

SqlDependency Event Handler Getting Fired continously - SQL Notification Service

Hello all,
I am using the SqlDependency to subscribe to any new inserts into
a database table, I enabled the DB to be borker ready and subscrbed to
Query notifications on the database.

My C# Windows service has a simple query that checks if a new entry is
made into a table,
select * from Cast_Member where isMajor = '1'

When I insert a new record into this table with isMajor = 1, then the
event handler gets fired, but doesn't stop there, it continously gets
fired on the same record. Also if I already have a OLD record in the
table with the isMajor = 1, then the event handler gets fired as soon
as I start the windows service - repeatedly.

Here's the code,

protected override void OnStart(string[] args)

private bool CanRequestNotifications()
SqlClientPermission permit = new
SqlClientPermission(System.Security.Permissions.Pe rmissionState.Unrestricted);
return true;
catch (System.Exception exc)
return false;

private void NotificationListener()
string mailSQL;
SqlConnection sqlConn;
this.connectionString =
mailSQL = "select * from Cast_Member where isMajor =


sqlConn = new SqlConnection(this.connectionString);
sqlCmd = new SqlCommand(mailSQL, sqlConn);
catch (Exception e)
// handle exception

private void GetNotificationData()
DataSet myDataSet = new DataSet();
sqlCmd.Notification = null;

dependency = new SqlDependency(sqlCmd);
dependency.OnChange += new
using (SqlDataAdapter adapter = new
adapter.Fill(myDataSet, "CAST");
DataTable mailLogTbl = (DataTable)myDataSet.Tables[0];

foreach (DataRow dataRow in mailLogTbl.Rows)
int castID = (int)dataRow[0];
logFile.WriteLine("New Entry into CAST_MEMBER table: " +

private void dependency_OnChange(object sender,
SqlNotificationEventArgs e)

SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
protected override void OnStop()
dependency.OnChange -= dependency_OnChange;

If anyone sees any problems with this code please point it out. As you
can see for every new entry into the table I write it into a log file,
so my log file has the same exact information multiple times for just
one new inserted record into the table.

thanks in advance. (btw, I copied the above code from MSDN, so its not
mine except for writing it to log)

Jul 30 '07 #1
0 2581

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Andy Read | last post: by
18 posts views Thread by Christopher W. Douglas | last post: by
2 posts views Thread by Stefan | last post: by
2 posts views Thread by Deepesh | last post: by
5 posts views Thread by Richard Grant | last post: by
5 posts views Thread by ShaunO | last post: by
reply views Thread by erbilkonuk | last post: by
1 post views Thread by jan.loucka | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.