Question Call API every x minutes

[Mr C]

I'm trying to figure out the best option to handle this type of scenario.

I have a C# web application that inserts data into a SQL table. Once a new record has been added to the SQL table, a unique id is assigned, and the record is assigned a status value of "Available". I will need to scan the table every 30 minutes to identify any records that have a status of "Available". If a record has an "Available" status, an API will need to get called to retrieve data and insert into another table.

Would scheduling a SQL job to run a stored procedure every 30 minutes be the best option? If any "Available" statuses are found, call the API from the stored procedure for each record?

Should I use Windows Task Scheduler or something else in .NET to call the API?

 

[Skydiver]

Is the SQL table in a machine running Microsoft SQL server?
If yes, is your DBA okay with you installing managed code stored procedures on his SQL server? If no, then you need to look at some other host for your recurring checks and API calls.
If yes, is the SQL locked behind a firewall that will block access to the API that that you need to call? If no, then your C# code living on the server should be able to make its API calls freely.
If yes, will your network security team allow you to punch a hole through the firewall? If no, then you need to look at some other host for your recurring checks and API calls.

The next best solution is to write an actual Windows service. You setup the service to be running all the time, but it'll only periodically check the database table and do the API calls. This presumes that you are allowed to install homegrown Windows services on your server(s).

The next best solution after that is a small console program that is run by a scheduled task periodically. This again presumes that you are allowed to install a console program and configure scheduled tasks on your server(s).

If all you are allowed to do is deploy a web app and related databse configurations, then the next best solution is to use HangFire and setup your own recurring job from within your web app. Be aware that this approach may incur the wrath of your IIS admins if you abuse the CPU or RAM resources on the IIS box.

(I know that I hate the team that decided to use Hangfire on our IIS servers. I gave the same advice above about preferring an WIndows service, then followed by a scheduled task on some dedicated server -- not the IIS boxes. But they wanted everything integrated into their monolithic microservice -- yes, an oxymoron. Since their jobs (not Hangfire itself) were leaking memory, their app kept on freezing. It's because I told them not to use HangFire in the first place unless they are very disciplined about writing code. Since they are not disciplined, their code was leaking memory. I sent them multiple memory dumps showing that their app was leaking. The freezing they were seeing was the effects of all the garbage collection runs, but effectively doesn't collect anything. It was always, "Yeah, yeah. it's in our backlog." They didn't believe me until they managed to make multiple servers (with 48GB of RAM each) fall over, and management was asking for a root cause analysis. No, I'm not bitter, really. )

 

[Mr C]

Thanks for the advice!

I've received feedback from out DBA and he is definitely not okay with installed managed code on the SQL Server. So, option 1 is off of the table.

I'm still not sure about the security requirements or if I'm able to install/configure the other two options on the servers.

I saw that you mentioned using a Windows Service as the next best solution. Is it because you believe it would be easier to implement and schedule? Just curious about why it would be a better option than the console app.

 

[Skydiver]

It's the right tool for the job.

Windows Service:

Scheduled Task plus Console App:

Both of them will drill holes at a fixed angle. But if you know that you'll be drilling thousands of holes, then the drill press makes more sense than the drill guide with a drill.

But if all you have is the space/budget/skills/approval-of-(s)he-who-must-be-obeyed for the drill guide with a drill, then I guess that's the right solution.

 

[Mr C]

Got it! I'm still waiting to see which option I will be allowed to use.

If I'm able to use the windows service option, would the best approach be to create a .NET Core app and host it as windows service?

Thanks again. I'm still fairly new to .NET and have not created a Windows Service before.