Home / Questions / 793
Accepted
drielnox
Asked: 2020-12-10 05:27:50 +0800 CST 2020-12-10 05:27:50 +0800 CST

How do I notify my application if there was a change to a table in my SQL Server database?

  • 772

I would like to know how I can do this as I could implement a caching mechanism for a sample application written in C# . This implementation is intended to end up as a library.

I know that EntityFramework has a somewhat similar mechanism to Auto Refresh and that NHibernate has cache levels. But I have a vague idea of ​​the concept and no idea how they work internally.

From the little research I know it has something to do with SQL Server Broker Service , where the service sends messages to MSMQ and from my application I pool against MSMQ but I never saw an implementation example.

Update

Well, to contextualize where this applies to, it is an industry-wide Warehouse Management System . The system has about 15 services running at the same time and asking the database all the time about the movement of the products and containers that are in the respective storage areas.

For what reason are there 15 services running?

The warehouse that uses this management system has several lines with barcode scanners reading about 10 labels per minute at peak times, and each of these lines has a particular service because the logic varies in each line and in each logic makes a verification to different services and the existence of said barcode in the database.

In addition, it has 10 AGVs (Automated Guided Vehicles) that have services such as: Task issuer for AGV , Task Scheduler , etc. and these are constantly pooling against the database looking for new tasks to perform. The same happens with the terminals of forklift operators who constantly pool the database in search of new container movement orders.

It remains to add that reception, dispatch and storage are also automated and each of these has its own service(s).

Why doesn't the system have middleware ?

I must clarify that this system, of such magnitude, does not have middleware (which would be a glory because it would solve many architectural problems) since it is a system adapted to an old system from around 1995. and other systems where production was limited for those dates. Today, it had to be upgraded and we are having scalability issues to increase production.

To understand this, the BL and the DAL are on the same server and, therefore, it does not give me the possibility to distribute the data query load.

I clarify that the application has the client made in WinForms where they connect to the services (console application).

c#

3 Answers

  1. Best Answer

    A solution could be using " Query Notifications " that allow, from .NET, to receive notifications of changes made to SQL Server data. In this MSDN article you can see a simple example of how to detect changes and receive events.

    It may also be interesting to tie this in with SignalR so that the client applications are not the ones that connect directly to SQL Server. In CodeProject there is an article " Real Time Notifications using SignalR and SQL Dependency " which is very interesting.

    I do not know if this solution will provide the performance that is needed or if on the contrary the solution could be things like SQL Server R Services , a ServiceBus , ...

    • 9

  2. Unless your system is heavily centralized in SQL Server (and from what you say it doesn't seem so) I wouldn't recommend SSBS.

    My recommendation is that it be your middleware (where the logical origin of the event occurs) that triggers X process (eg enqueue a message).

    In the particular case of a cache, by definition it should not be updated in a distributed environment (eg farm of servers that cache queries to the database locally).

    If you want to keep a cached value as fresh as possible, then you should consider using schemes like Memcached or similar (redis, ...). Basically a single cache is maintained shared by all consumers of it.

    If it's a practice, implementing your own memcache can be fun and interesting.

    On the other hand, you may want to notify clients of certain changes, in which case there are several strategies, the most appropriate in your case might be SignalR, but you would have to see your specific case to see which form of scaling is the most appropriate.

    In any case, I repeat that, except for a specific reason, you place control over the flow of that communication in the logic (be it a cache on an updated value, an event that you propagate to the client, etc...).

    From what little I did research

    You are not describing any caching system (you are describing an event notification system). SSBS can send messages to a queue and then you dequeue from the application, it's that simple, but that scheme is very rigid and coupled to the database, you need the queue manager and also, your client has to pull (explicitly ask to the queue manager if there are new messages). Although you do not expose the specific problem, it does not seem the most appropriate (or there seem to be better alternatives).

    • 7

  3. As part of a perhaps less robust solution, you can have a DDL TRIGGER that inserts into a table if there have been changes to the other tables, and then queries it from your application, like so:

    CREATE TABLE TRG_TEST
(
ID INT,
NOMBRE_TABLA VARCHAR(50)
)

CREATE TRIGGER TRG_TABLES
ON DATABASE 
AFTER 
    ALTER_TABLE
AS 
    BEGIN
    SET NOCOUNT ON

    DECLARE @TABLE_NAME SYSNAME

    SELECT 
        @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')

    IF  @TABLE_NAME <> 'TRG_TEST'
    BEGIN
        INSERT INTO TRG_TEST VALUES (1,@TABLE_NAME)
    END

END
GO

    Surely there is a better option to avoid committing the change to the table you use to push the changes, something like SCHEMABINDING or similar.

    • 6