After a while of searching the internet, I found this piece of code that triggers upon a LINQ-based change to the Database. It trigger only once and doesn't mention or show what was changed/deleted/added, or what table was CRUDed.
static class GlobalNotifications
{
public static event OnChangeEventHandler OnChange;
public static void InitializeNotifications(string connectString)
{
// Initialize notifications
SqlDependency.Start(connectString);
// Create and register a new dependency
SqlDependency dependency = new SqlDependency();
dependency.OnChange += new OnChangeEventHandler(NotificationCallback);
System.Runtime.Remoting.Messaging.CallContext.SetData("MS.SqlDependencyCookie", dependency.Id);
}
internal static void NotificationCallback(object o, SqlNotificationEventArgs args)
{
OnChange.Invoke(o, args);
}
}
This is how I'm using it:
public partial class Nawa : Form
{
public Nawa()
{
InitializeComponent();
}
private void Nawa_Load(object sender, EventArgs e)
{
GlobalNotifications.InitializeNotifications("Server=GENISYSSERVER; Trusted_Connection=no;database=Maple_DBv1; user id=sa; password=Wc123Wc123");
GlobalNotifications.OnChange += new System.Data.SqlClient.OnChangeEventHandler(GlobalNotifications_OnChange);
}
void GlobalNotifications_OnChange(object sender, System.Data.SqlClient.SqlNotificationEventArgs e)
{
MessageBox.Show("Test");
}
private void button1_Click(object sender, EventArgs e)
{
using (DataClasses1DataContext dbcontext = new DataClasses1DataContext("Server=GENISYSSERVER; Trusted_Connection=no;database=Maple_DBv1; user id=sa; password=Wc123Wc123")) {
OrderFood random = dbcontext.OrderFoods.FirstOrDefault(id => id.ID == 10);
if (random != null) {
if (random.MenuID == 4)
random.MenuID = 1;
else
random.MenuID = 4;
dbcontext.SubmitChanges();
}
}
}
}
Can someone help in this regard? How to get more details of what was changed, type of change, Table(s) changed, and why does it fire only once. Also, how can it understand LINQ changes only? It doesn't trigger on direct changes etc.
Reference: Extemporaneous Mumblings
A SQLDependency will only fire once, you need to recreate the dependency after it has fired.
The OnChange event fires once and then gets consumed, so you need to hook up the event again after it fires.
I don't have any C# code to hand to demonstrate how to deal with this but I'm sure some VB.NET should work well enough for you to come up with your own solution.
Private _permissionsDependency As SqlDependency
Private Sub doSubscribe()
_permissionsDependency = New SqlDependency(cmd.InnerCommand)
RemoveHandler _permissionsDependency.OnChange, AddressOf User_OnChange
AddHandler _permissionsDependency.OnChange, AddressOf User_OnChange
End Sub
Private Sub User_OnChange(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlNotificationEventArgs)
If _permissionsDependency IsNot Nothing Then RemoveHandler _permissionsDependency .OnChange, AddressOf User_OnChange
Select Case e.Info
Case SqlNotificationInfo.Delete
RaiseEvent UserDeleted(Me)
Case SqlNotificationInfo.Update
populateUser()
RaiseEvent UserUpdated(Me)
Case Else
End Select
End Sub
As you can see you can find out what happened by looking at e.Info
, which will allow for you to know what happened (in my example I'm only looking for deletes and updates).