Question .net core 2.1 console app with http client factory question

[raysefo]

Hi guys,
I would like to implement a scheduled task which checks an API for every hour and insert data into a database if data is not in the database already. I read some articles and came out with this. I wonder if you can guide me to insert data into a SQL table if it is not already there. (By the way, most probably I will not use the Polly retry mechanism in my code.)

using System;
using System.IO;
using System.Net.Http;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Polly;
using Polly.Extensions.Http;
using Polly.Registry;

namespace TrendyolGamePurchase
{
    class Program
    {
        static async Task Main(string[] args)
        {
            //Read App Settings
            var build = new ConfigurationBuilder();
            BuildConfig(build);

            var config = build.Build();
          
            Console.Write(config["ConnectionStrings:Development"]);
          
            //Polly Retry
            var builder = new HostBuilder()
                .ConfigureServices((hostContext, services) =>
                {
                    IPolicyRegistry<string> registry = services.AddPolicyRegistry();

                    //First Policy
                    IAsyncPolicy<HttpResponseMessage> httpWaitAndRetryPolicy =
                        Policy.HandleResult<HttpResponseMessage>(r => !r.IsSuccessStatusCode)
                            .WaitAndRetryAsync(3, retryAttempt => TimeSpan.FromSeconds(Math.Pow(3, retryAttempt)));

                    registry.Add("SimpleWaitAndRetryPolicy", httpWaitAndRetryPolicy);

                    //Second Policy
                    IAsyncPolicy<HttpResponseMessage> noOpPolicy = Policy.NoOpAsync()
                        .AsAsyncPolicy<HttpResponseMessage>();

                    registry.Add("NoOpPolicy", noOpPolicy);

                    //Third Policy
                    var timeOutPolicy = Policy.TimeoutAsync(TimeSpan.FromSeconds(10));
                        
                    registry.Add("timeOutPolicy", timeOutPolicy);

                    services.AddHttpClient("TestClient", client =>
                    {
                        client.BaseAddress = new Uri("http://test//api/v2/web/game/purchase");
                        client.DefaultRequestHeaders.Add("Accept", "application/json");
                    }).AddPolicyHandlerFromRegistry((policyRegistry, httpRequestMessage) =>
                    {
                        if (httpRequestMessage.Method == HttpMethod.Post)
                        {
                            Console.WriteLine(DateTime.Now);
                            return policyRegistry.Get<IAsyncPolicy<HttpResponseMessage>>("SimpleWaitAndRetryPolicy");
                        }
                        return policyRegistry.Get<IAsyncPolicy<HttpResponseMessage>>("NoOpPolicy");
                    });

                    services.AddSingleton<IHostedService, BusinessService>();
                });

            await builder.RunConsoleAsync();
        }
        static void BuildConfig(IConfigurationBuilder builder)
        {
            builder.SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);


        }
    }
}

using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Extensions.Hosting;

namespace TrendyolGamePurchase
{
    public class BusinessService : IHostedService
    {
        private IHttpClientFactory _httpClientFactory;
        public BusinessService(IHttpClientFactory httpClientFactory)
        {
            _httpClientFactory = httpClientFactory;
        }

        public async Task StartAsync(CancellationToken cancellationToken)
        {
            await MakeTestRequestsToRemoteService();
        }

        public async Task MakeTestRequestsToRemoteService()
        {
            HttpClient httpClient = _httpClientFactory.CreateClient("TestClient");

            var authenticationBytes = Encoding.ASCII.GetBytes("Test:12345");

            httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic",
                Convert.ToBase64String(authenticationBytes));
            httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

            var content = new FormUrlEncodedContent(new[]
            {
                new KeyValuePair<string, string>("productCode", "1"),
                new KeyValuePair<string, string>("quantity","1"),
                new KeyValuePair<string, string>("shopNo","Palas"),
                new KeyValuePair<string, string>("safeNo","Palas"),
                new KeyValuePair<string, string>("cashierNo","Palas")

            });
            var response = await httpClient.PostAsync("http://test//api/v2/web/game/purchase", content);
            

        }

        public Task StopAsync(CancellationToken cancellationToken)
        {
            return Task.CompletedTask;
        }
    }
}

 

[raysefo]

Hello guys,

I have a console app and I am retrieving data from an API every 2 hours. I would like to insert data if only new into this table but I wonder if there is a way to check data before inserting it into the table. If I have over 100 records every fetch from this API and I don't want to manually check one record at a time to see if data in a certain column already exist, how do I write the query to check if data in my records already exist before inserting?

Thanks.

 

[jmcilhinney]

What exactly constitutes data already existing? Is it one specific column? Multiple columns? We need details because there's no one magic solution when everyone's data is different.

 

[jmcilhinney]

Is this basically your previous question again but with more information?

 

[NoUserHere]

Merged topics.

 

[NoUserHere]

How does one normally check if data exists before you update records?

You can update records without reading them if they match a condition? That condition depends if it exists in your database data structure. Ie a datetime field, last updated etc.

Its hard to answer that when you haven't told us anything relating to your database scheme for where/what data is stored.

 

[NoUserHere]

If the data being received by your API has a newer date/datetime than that of your where clause of the date/datetime you are checking against in your database, then you can update those tables where the data is of an older date/time. This assumes your API provides a date/time for the data being received. If it does not, write your own API instead of using someone else's who is unwilling to edit theirs for you. I believe we've been over this one once before.

 

[Skydiver]

And that code at the beginning still doesn't give us a hint of the data you are trying to insert into your database, or the schema of your database.

 

[raysefo]

@jmcilhinney, @Sheepings, @Skydiver thank you. I will get these data below;

id, customerId, firstName, lastName, phone, productCode, orderDate (unix epoch time)

I will use raw SQL in my app, it's much simpler for me.

 

[Skydiver]

And how do you determine a duplicate? All values match exactly, or just some particular columns match? If the latter, which columns do you want to match to declare a duplicate?

 

[raysefo]

id and orderDate will determine a duplicate

 

[Skydiver]

So why not just setup a unique key combined key for id and orderDate? Try to insert. If the insert fails due to a duplicate key, then you know that there was a duplicate, plus you got to do it with one database call, instead of trying to do a query first, and then an insert if the query comes back empty.

 

[raysefo]

thank you @Skydiver this sounds perfect I am not familiar with the .net core console application, can you please guide me in the right direction?

 

[Skydiver]

Setting up the unique combined keys has nothing to do with .NET Core console applications. It's all about SQL. I am in the NoSQL side of the court, so hopefully someone who likes SQL can help you out.

 

[raysefo]

setting up the unique key is not problematic for me either But how to set up insert statement into console app is actually I would like to know.