Question:
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 with Auto Refresh and that NHibernate has cache levels. But I have a vague idea of the concept and have no idea how they work internally.
From what little I did 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.
Upgrade
Well, to contextualize where this applies, it is a Warehouse Management System at an industrial level . The system has about 15 services running at the same time and asking the database all the time about the movement of 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 around 10 labels per minute at peak time 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 Vehicle) that have services such as: Issuer of tasks for AGV , Planner of Tasks to be carried out , etc. and they are constantly polluting against the database looking for new tasks to perform. The same is true of forklift operator terminals who constantly poole the database for new container movement orders.
It remains to be added 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 to have because it would solve many architectural problems) since it is a system adapted to an old system from the year 1995 approx. 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).
Answer:
A solution could be using the " Query Notifications " that allow, from .NET, to receive notifications of changes made to the SQL Server data. In this MSDN article you can see a simple example of how to detect changes and receive events.
It might also be interesting to tie this together 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 , …