Tool to write a scheduled job that sends an email every 5 minutes using .Net core & SQL Server

Question

My requirement is to write a batch program/job that sends an email every 5 minutes. It will query an sql server database.

My team is a microsoft shop so we are exposed to .Net core and SQL server.

I prefer a solution that can work in

• an on-premise infrastructure.
• or a cloud based environment such as Azure or AWS.

---

At this time, below are the options I am considering.

A .net core 1.1 console application that can be run as a scheduled job or hosted as an azure webjob.

Other tools that can be used to fill the plate:

docker, mailkit, logging frameworks such as serilog, nlog.

Answer 1

You can use the free Microsoft Visual Studio 2017 Community Edition. It has .NET Core support. SQL Server support is not needed by the IDE itself. Instead, .NET Core brings the SQL Server data access

Besides that, you don't need much: a timer and email.

Answer 2

I have two suggestions

My first solution would be to use SQL Server Agent to run a Stored Procedure at the time intervals required. Use sp_send_dbmail to send emails from within the Stored procedure. The database query can be run inside the stored procedure. This is entirely contained with SQL Server , the DBA may need to configure sending emails from SQL Server and I should say I've not sent emails from cloud based database environments.

The second suggestion is to use Powershell to execute the SQL Server query and send the email. The query output can be massaged in Powershell before sending if required. Run this using Task Scheduler.

(edit) Thomas mentioned using Visual Studio. Installing SQL Server Data Tools in Visual Studio gives you full access to SQL Server.