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)
