Automating REST API data to csv with C#

Question

I have data that I'm bringing back from an API using the GET call. I'm storing this to .csv, however new to both C# and Visual Studio packages. The data will update overnight therefore I need this package to run daily. What I'm trying to ask is via SQL server I would normally add the Stored Procedure to a SQL job and setup a scheduler. What is the best practice on automated C# scripts? Is Windows Task Scheduler the best option? or is there a preferred solution to this?

Answer 1

To get data from source to sql server.

(One way) (C# "centric")

In the DotNetCore world.

https://learn.microsoft.com/en-us/aspnet/core/fundamentals/host/hosted-services?view=aspnetcore-5.0&tabs=visual-studio

In ASP.NET Core, background tasks can be implemented as hosted services. A hosted service is a class with background task logic that implements the IHostedService interface. This topic provides three hosted service examples:

Background task that runs on a timer. Hosted service that activates a scoped service. The scoped service can use dependency injection (DI). Queued background tasks that run sequentially.

This would be the code that reads your source-data "CSV", and sends the data to Sql Server.

There are already several "read csv" nuget libraries available.

For example:

https://www.nuget.org/packages/CsvHelper/

=========

(Another way) (Sql Server "centric")

https://learn.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-ver15

https://www.sqlshack.com/how-to-import-export-csv-files-with-r-in-sql-server-2016/

============

Then you create a "WebApi" project that will read from SqlServer......and server up the data.

========

On the "hosted service", there is more info here: Does it make sense to run a c# worker service in docker?

Answer 2

You can create a heartbeat task for this. In general, you will need a heartbeat manager, that is a class that has an infinite loop, with a sleep scheduled for a while, so it will not eat up your resources.

Sleep

You will need a

while (true) {
    //Perform the tasks
    Thread.Sleep(1000); //Sleeps for a second
}

In your heartbeat manager.

Heartbeat interface

It's advisable to create an interface for your heartbeat tasks, which has an IsActive boolean method a Run and a Join. All your tasks (initially you will have a single task, but this approach supports multiple tasks) will need to implement this interface.

Managing the tasks

In your infinite cycle you will need an IEnumerable of IHeartbeat (or whatever name you give the interface) element and loop the elements of the IEnumerable, like this:

while (true) {
    for (IHeartbeat hb in myHeartbeats) {
        if (hb.IsActive()) hb.Run();
    }
    Thread.Sleep(1000);
}

Synchronizing

You may want to synchronize your jobs. The best way to do that is to initialize an empty List as the first step of your while, in your foreach loop add every hb whose IsActive was true to the List and after the foreach loop have another foreach loop where you call hb.Join().

Implementation of a hearbeat task

You need to implement IHeartbeat to ensure consistency and under the hood you will need to create a Thread for the job that's started, so the job will work and you could Join it when needed.

IsActive

Checks whether the current time fulfills the criteria for the job to be run (whether we are at the right time).

Run

Creates a Thread and uses it in order to execute the job's bulk.

Join

Calls myThread.Join().

Timing

For the sake of simplicity, the sleep gets 1000 milliseconds, or, in simpler terms a second in this case, but you may want to enhance this a bit according to your needs.