SQL Dependency OnChange event

[Sudip_inn]

here first i am sharing the code. SQL Dependency can be used in web and windows application. i have plant to use it in c# console application.

code taken from here Create a push notification system with SignalR | DotNet - awesome

public void RegisterNotification(DateTime currentTime)
{
string conStr = ConfigurationManager.ConnectionStrings["sqlConString"].ConnectionString;
string sqlCommand = @"SELECT [ContactID],[ContactName],[ContactNo] from [dbo].[Contacts] where [AddedOn] > @AddedOn";
//you can notice here I have added table name like this [dbo].[Contacts] with [dbo], its mendatory when you use Sql Dependency
using (SqlConnection con = new SqlConnection(conStr))
{
SqlCommand cmd = new SqlCommand(sqlCommand, con);
cmd.Parameters.AddWithValue("@AddedOn", currentTime);
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
cmd.Notification = null;
SqlDependency sqlDep = new SqlDependency(cmd);
sqlDep.OnChange += sqlDep_OnChange;
//we must have to execute the command here
using (SqlDataReader reader = cmd.ExecuteReader())
{
// nothing need to add here now
}
}
}

void sqlDep_OnChange(object sender, SqlNotificationEventArgs e)
{
//or you can also check => if (e.Info == SqlNotificationInfo.Insert) , if you want notification only for inserted record
if (e.Type == SqlNotificationType.Change)
{
SqlDependency sqlDep = sender as SqlDependency;
sqlDep.OnChange -= sqlDep_OnChange;

//from here we will send notification message to client
var notificationHub = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();
notificationHub.Clients.All.notify("added");
//re-register notification
RegisterNotification(DateTime.Now);
}
}

see the two above function. from first function this way event is attached sqlDep.OnChange += sqlDep_OnChange;

and from second function we are detaching the same event this way sqlDep.OnChange -= sqlDep_OnChange;

i have a question why we need to detach the OnChange event ?

please tell me reason why we need to detach this event ? if we do not detach then what bad things may happen ?

tell me best category to post question about SQL Dependency in MSDN forum or MS QA forum ?

thanks

Continue reading...