client informs client
-
Not sure it belongs in this forum, I hope it does. I have a SQL Server Express application with a few clients; some are running app1 (.NET WinForms), others app2 (also .NET WinForms). When one of the app1 clients changes some data in the database, all of the app2 clients (who may be viewing the same data) need to be notified about the data change within a few seconds. How would you implement this? I could have all clients refresh their views all the time (no thanks), implement a direct client-to-client notification based on some interprocess comm scheme (I'd rather not), or maybe the database itself might be helping out. Suggestions are welcome. TIA
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Not sure it belongs in this forum, I hope it does. I have a SQL Server Express application with a few clients; some are running app1 (.NET WinForms), others app2 (also .NET WinForms). When one of the app1 clients changes some data in the database, all of the app2 clients (who may be viewing the same data) need to be notified about the data change within a few seconds. How would you implement this? I could have all clients refresh their views all the time (no thanks), implement a direct client-to-client notification based on some interprocess comm scheme (I'd rather not), or maybe the database itself might be helping out. Suggestions are welcome. TIA
Luc Pattyn [My Articles] Nil Volentibus Arduum
;P What is the world coming to? Luc asking questions!!!! ;P I've done similar things in the past, but not using SQL Server. I don't know what IPC capabilities are in your server or could be easily grafted onto it. Given these two points, I'd say: 1. Make the server do it. Client to client gets way too messy when you consider clients appearing/disappearing on the fly. At least the server has a reasonably current idea of who's out there. 2. How I did it was to use sockets with the following simple protocol:
client sends requests at most one outstanding first request of a session is a logon server sends responses to requests OR asynchronous notifications any socket error => forced logoff (therefore unexpected logon at server => kill old session, start new)HTH Peter
Software rusts. Simon Stephenson, ca 1994.
-
Not sure it belongs in this forum, I hope it does. I have a SQL Server Express application with a few clients; some are running app1 (.NET WinForms), others app2 (also .NET WinForms). When one of the app1 clients changes some data in the database, all of the app2 clients (who may be viewing the same data) need to be notified about the data change within a few seconds. How would you implement this? I could have all clients refresh their views all the time (no thanks), implement a direct client-to-client notification based on some interprocess comm scheme (I'd rather not), or maybe the database itself might be helping out. Suggestions are welcome. TIA
Luc Pattyn [My Articles] Nil Volentibus Arduum
I believe SQL Server has an event notification process that can inform a client of data change. That is as much as I know, thankfully I have never had to implement such a horrible scenario, to date I have always managed to talk the client out of this based on the cost and perceived (by me) fragility of the solution. I have implemented a polling solution a long time ago (SQL Server 6.5) but was never really happy with the results. [edit] have a look through Mika's[^] stuff I think he had some info on it at some stage [/edit]
Never underestimate the power of human stupidity RAH
-
;P What is the world coming to? Luc asking questions!!!! ;P I've done similar things in the past, but not using SQL Server. I don't know what IPC capabilities are in your server or could be easily grafted onto it. Given these two points, I'd say: 1. Make the server do it. Client to client gets way too messy when you consider clients appearing/disappearing on the fly. At least the server has a reasonably current idea of who's out there. 2. How I did it was to use sockets with the following simple protocol:
client sends requests at most one outstanding first request of a session is a logon server sends responses to requests OR asynchronous notifications any socket error => forced logoff (therefore unexpected logon at server => kill old session, start new)HTH Peter
Software rusts. Simon Stephenson, ca 1994.
Thanks Peter, yes I need to earn some Enquirer points too you know. :laugh: I'm currently considering a UDP multicast, where producing clients just throw their "new critical data" message up in the air, and consuming clients react on it. I was hoping SQL Server itself would have some built-in mechanism, I need to read up on it I guess. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
I believe SQL Server has an event notification process that can inform a client of data change. That is as much as I know, thankfully I have never had to implement such a horrible scenario, to date I have always managed to talk the client out of this based on the cost and perceived (by me) fragility of the solution. I have implemented a polling solution a long time ago (SQL Server 6.5) but was never really happy with the results. [edit] have a look through Mika's[^] stuff I think he had some info on it at some stage [/edit]
Never underestimate the power of human stupidity RAH
Hi Mycroft, thanks for the info. I'm gonna read An Introduction to SQL Server Notification Services[^] first, then look into some of Mika's stuff. Or I might simply use a UDP multicast scheme, all I really need is a signal on important data changes. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Thanks Peter, yes I need to earn some Enquirer points too you know. :laugh: I'm currently considering a UDP multicast, where producing clients just throw their "new critical data" message up in the air, and consuming clients react on it. I was hoping SQL Server itself would have some built-in mechanism, I need to read up on it I guess. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Sounds like your app is a little less demanding of accuracy. Mine was a multi-terminal raffle selling setup - server in the back room, one or two fixed PCs and some mobiles (J2ME phones with bluetooth-speaking battery-powered printers) wandering around amongst the patrons. Request/response were the obvious ticket sales (or not), logon/off, operator cash management, etc; the notifications were things like raffle closed, new raffle opening (i.e. clients, come and get the details) I'd be tempted to write it up - there are some interesting design considerations - except that a lot of the code is Hall of Shame material. :-O Cheers, Peter
Software rusts. Simon Stephenson, ca 1994.
-
Sounds like your app is a little less demanding of accuracy. Mine was a multi-terminal raffle selling setup - server in the back room, one or two fixed PCs and some mobiles (J2ME phones with bluetooth-speaking battery-powered printers) wandering around amongst the patrons. Request/response were the obvious ticket sales (or not), logon/off, operator cash management, etc; the notifications were things like raffle closed, new raffle opening (i.e. clients, come and get the details) I'd be tempted to write it up - there are some interesting design considerations - except that a lot of the code is Hall of Shame material. :-O Cheers, Peter
Software rusts. Simon Stephenson, ca 1994.
I don't mind reading an article with substandard code in it, as long as it says so while also offering valuable insight in concepts and/or design. And if the code really is horrible, we can always turn it into a "guess who wrote this code" competition in the appropriate forum... :-D
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Not sure it belongs in this forum, I hope it does. I have a SQL Server Express application with a few clients; some are running app1 (.NET WinForms), others app2 (also .NET WinForms). When one of the app1 clients changes some data in the database, all of the app2 clients (who may be viewing the same data) need to be notified about the data change within a few seconds. How would you implement this? I could have all clients refresh their views all the time (no thanks), implement a direct client-to-client notification based on some interprocess comm scheme (I'd rather not), or maybe the database itself might be helping out. Suggestions are welcome. TIA
Luc Pattyn [My Articles] Nil Volentibus Arduum
Luc Pattyn wrote:
How would you implement this?
Depends on the full specs, sensei. If there were no additional concerns, I'd simply have an update-trigger log the primary key and a datestamp in a table, and have the clients poll the table's count every second. That would also enable you to limit the update-logging to specific fields. Something like an RSS idea for SQL :)
Bastard Programmer from Hell :suss:
-
Luc Pattyn wrote:
How would you implement this?
Depends on the full specs, sensei. If there were no additional concerns, I'd simply have an update-trigger log the primary key and a datestamp in a table, and have the clients poll the table's count every second. That would also enable you to limit the update-logging to specific fields. Something like an RSS idea for SQL :)
Bastard Programmer from Hell :suss:
Thanks Eddy. I was hoping for a solution that doesn't poll, something event driven. Maybe an SQL statement that actually blocks until a specified row/field gets modified or added (Yes I could spend an entire connection!). :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Thanks Eddy. I was hoping for a solution that doesn't poll, something event driven. Maybe an SQL statement that actually blocks until a specified row/field gets modified or added (Yes I could spend an entire connection!). :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
My pleasure :) An event upon data change still sounds like a trigger. Is CLR Integration on? You could write a trigger[^] in C#. ..and if you can spend a connection, you probably can spend a thread; monitoring a custom lightweight trace might be an option too.
Bastard Programmer from Hell :suss:
-
My pleasure :) An event upon data change still sounds like a trigger. Is CLR Integration on? You could write a trigger[^] in C#. ..and if you can spend a connection, you probably can spend a thread; monitoring a custom lightweight trace might be an option too.
Bastard Programmer from Hell :suss:
Hmm, I'm not familiar with triggers; the way I understand it, it causes some action to occur in the database, however I need other clients to get a notification (not just the one client that is causing the trigger to fire). A simple scenario would be: client1 is looking at a customers list; client2 (different PC, different user) is looking at the same list; client1 adds/modifies a customer record, client2 should get his display updated, or at least get a warning the data is stale. Of course polling can do that easily, I hope for an event driven solution though. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Hmm, I'm not familiar with triggers; the way I understand it, it causes some action to occur in the database, however I need other clients to get a notification (not just the one client that is causing the trigger to fire). A simple scenario would be: client1 is looking at a customers list; client2 (different PC, different user) is looking at the same list; client1 adds/modifies a customer record, client2 should get his display updated, or at least get a warning the data is stale. Of course polling can do that easily, I hope for an event driven solution though. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Luc Pattyn wrote:
Hmm, I'm not familiar with triggers; the way I understand it, it causes some action to occur in the database, however I need other clients to get a notification (not just the one client that is causing the trigger to fire).
A trigger, traditionally, executes a piece of SQL when data is inserted/updated/deleted from a table. You can check *which* fields change in the trigger. MSDN has an example on a CLR Trigger[^] that uses pipes to communicate with the outside world. I'm not sure whether you can launch a socket from there, but once you can catch the change, you can inform your clients - one way or the other. My client would then typically use the
INotifyPropertyChangedinterface to propagate changes.Bastard Programmer from Hell :suss:
-
Luc Pattyn wrote:
Hmm, I'm not familiar with triggers; the way I understand it, it causes some action to occur in the database, however I need other clients to get a notification (not just the one client that is causing the trigger to fire).
A trigger, traditionally, executes a piece of SQL when data is inserted/updated/deleted from a table. You can check *which* fields change in the trigger. MSDN has an example on a CLR Trigger[^] that uses pipes to communicate with the outside world. I'm not sure whether you can launch a socket from there, but once you can catch the change, you can inform your clients - one way or the other. My client would then typically use the
INotifyPropertyChangedinterface to propagate changes.Bastard Programmer from Hell :suss:
OK, I'll investigate how that works and fits with what I have already. Thanks again. :beer:
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
OK, I'll investigate how that works and fits with what I have already. Thanks again. :beer:
Luc Pattyn [My Articles] Nil Volentibus Arduum