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

das
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)
{
CanRequestNotifications();
NotificationListener();

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

private void NotificationListener()
{
string mailSQL;
SqlConnection sqlConn;
try
{
this.connectionString =
ConfigurationManager.AppSettings["connectionString"];
mailSQL = "select * from Cast_Member where isMajor =
'1'";

SqlDependency.Stop(this.connectionString);
SqlDependency.Start(this.connectionString);

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

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

dependency = new SqlDependency(sqlCmd);
dependency.OnChange += new
OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new
SqlDataAdapter(sqlCmd))
{
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: " +
castID.ToString());
}

}
}
private void dependency_OnChange(object sender,
SqlNotificationEventArgs e)
{

SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
this.GetNotificationData();
}
protected override void OnStop()
{
dependency.OnChange -= dependency_OnChange;
SqlDependency.Stop(this.connectionString);
}
------------------------------------------------------------------------------------------------------------------------------------------------

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.