c# - How to wipe data in a table each time you save to a database

Question

I've currently built a Visual Studio C# project that saves API data into a database through Entity Framework. Everything works fine, but I've noticed each time I run the application, the data is duplicated in the table.

I have to restart it every now and then since there is new data that needs storing, but I was thinking is there an easy way or even just a way that wipes the table before you save the data to the DB each time you run the application?

Here is the code I'm running at the moment.

public static void getAllRequestData()
{
    var client = new RestClient("[My API URL]");
    var request = new RestRequest();
    var response = client.Execute(request);

    if (response.StatusCode == System.Net.HttpStatusCode.OK)
    {
        string rawResponse = response.Content;
        AllRequests.Rootobject result = JsonConvert.DeserializeObject<AllRequests.Rootobject>(rawResponse);

        using (var db = new TransitionContext())
        {
            db.RequestDetails.AddRange(result.Operation.Details);
            db.SaveChanges();
        } //Utilising EF to save data to the DB
    }
} //Method that calls and stores API data

This method is used to get API data, deserialize it, then save it to a Db.

public class TransitionContext : DbContext
{
    private const string connectionString = @"[My Server]";

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(connectionString);
    }

    public DbSet<AllRequests.Detail> RequestDetails { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AllRequests.Detail>().HasKey(r => r.Id);
    }
}

This is my Entity Framework DbContext that scaffolds the table and maps the data to each column.

This all works, but what would I have to include in my code for it to stop duplicating data each time I run the project? Currently, I get just over 1000 rows in my table each time I run the project, eventually, it gets clogged. I want it to be the same data each time I run it except with a few additions that are added each week.

Any help would be appreciated, thank you.

question from:https://stackoverflow.com/questions/65836985/how-to-wipe-data-in-a-table-each-time-you-save-to-a-database

Answer 1

Solving this issue will depend on how the API works. If the data is returned in a sequence that doesn't change between calls, then you can remember the index of the last object saved in the database and next time save only fields that have a higher index. However if order isn't maintained between calls then you will be left having to manually compare objects to check for duplicates, which is not very time efficient but will be better than nothing. The thing is that second option won't work reliably if each item returned isn't unique. In that case the only viable method would be to clear the table entirely and reinsert everything. You can empty the table by calling db.RequestDetails.RemoveRange(db.RequestDetails). This might even be faster than the comparation method anyways.