ASP.NET Long Running SQL Server Procedure - How to Handle? Messages?
I have a web application that has to eliminate a lengthy SQL Server procedure (to restore a table) when certain criteria are met. What is the recommended procedure for this?
I thought that when the criteria are met, the record will be inserted into the database, and the scheduled SQL server job will check this table at a given interval and shut down the procedure. Seems a bit hacky.
What is the preferred way to handle this? As a related question, what is the preferred way to handle future events (i.e. send an email 10 days after the user has signed up?)
Is this type of scenario what messaging is?
source to share
Your approach is definitely viable and is one of the ways I've seen it. For example, in a complex network with gigabytes of inventory data, the company implemented basically what you described to partially update the inventory data throughout the day.
Some of the other approaches I've seen involved a scheduling service sitting on the application server that would invoke a piece of code that would handle the event. (For example, the second part of your question). Having this in the application layer allows event handling to scale, and you can use the DB as a backup store so you don't lose any events.
Do you need a user to see the results of this action? You can also start an asynchronous call to process the results and then connect to the user and provide state information. If you don't want to hang on to the user, then you need a state table that will save the result.
The last thing I would look at is a service broker. I haven't used it yet, and I'm not sure if it fits, but I would consider it as an option. We started looking into it as a way to start long, disconnected workflows, but I left the company before we got anywhere.
source to share
I would like to notify the user who made changes that require a rebuild (a) that the rebuild should have taken place, and (b) that the rebuild was complete. I would like to do this both by post in the UI and by email. I would prefer an application (rather than a DB to handle email).
I would like to be able to notify this in the UI long after the transaction is complete (if they log out and then come back later the next day).
When are you saying that the scheduling service on the app server refers to something like a Windows service that is solely responsible for handling this type of thing?
source to share