.net core 2.1 console app with http client factory

Cenk 981 Reputation points
2021-02-28T12:36:35.513+00:00

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 so far. 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.) I am not familiar with .net core console application so please be gentle :) I will retrieve these values below;

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

Can you please guide me in the right direction for inserting values into the database table in the console application.

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 GamePurchase
{
    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 GamePurchase
{
    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;
        }
    }
}
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,073 questions
.NET CLI
.NET CLI
A cross-platform toolchain for developing, building, running, and publishing .NET applications.
324 questions
.NET Runtime
.NET Runtime
.NET: Microsoft Technologies based on the .NET software framework.Runtime: An environment required to run apps that aren't compiled to machine language.
1,136 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Timon Yang-MSFT 9,576 Reputation points
    2021-03-01T08:43:17.173+00:00

    I am not familiar with Polly, so the following are only suggestions for storing data in the database.

    We need to install a nuget package in .Net Core to use ADO.Net related classes:System.Data.SqlClient

           public static void InsertData()   
            {  
                string connString = @"";  
                using (SqlConnection sqlConnection = new SqlConnection(connString))  
                {  
                    sqlConnection.Open();  
      
                    using (SqlCommand command = new SqlCommand())  
                    {  
                        command.Connection = sqlConnection;  
                        string sql = @"select count(*) from tableName where id =@id";  
                        command.CommandText = sql;  
                        command.Parameters.Add("ID", SqlDbType.Int).Value = 4;  
                       
                        int countNum = int.Parse(command.ExecuteScalar().ToString());  
                        if (countNum != 0)   
                        {  
                            Console.WriteLine("Existed!");  
                            return;  
                        }  
      
                        string insertSql = @"";  
      
                        command.CommandText = insertSql;  
      
                        command.Parameters.Clear();  
      
                        command.Parameters.Add("new params", SqlDbType.VarChar).Value = "value";  
                        command.ExecuteNonQuery();  
                    }  
                }  
            }  
    

    You can also use EF Core, which is more convenient to use, but it requires you to spend some time learning it.

    Entity Framework Core


    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Cenk 981 Reputation points
    2021-03-01T08:54:15.53+00:00

    Hi @Timon Yang-MSFT , thanks for your reply. I set id and orderDate composite unique key so I think I don't need to use a select statement for checking if data already inserted. if I get an error when inserting data, that means data is already inserted. Does it make sense? Should I call the sample InsertData function in the foreach loop without the select portion?

    var response = await httpClient.GetAsync("https://test/orders?startDate=1612126800000");  
                Console.WriteLine(DateTime.Now);  
      
                if (response.IsSuccessStatusCode)  
                {  
                    Root orders = await response.Content.ReadAsAsync<Root>();  
      
                    if (orders.Content.Count > 0)  
                    {  
                        foreach (var content in orders.Content)  
                        {  
                            Console.Write(content.CustomerId + " " + content.CustomerFirstName + " " + content.CustomerLastName + " " + content.CustomerEmail +" " + content.TotalPrice + " "+ content.InvoiceAddress.Phone + " " + content.Lines[0].ProductCode + " " +content.Lines[0].ProductName);  
    
                            **CALL InsertData();**  
    
                        }  
                    }  
      
                }  
    
    0 comments No comments

  3. Timon Yang-MSFT 9,576 Reputation points
    2021-03-02T06:49:39.293+00:00

    Yes, you can handle it this way, but you should pay attention to catching the exception as accurately as possible, instead of catching "Exception e" directly, otherwise it may mislead you when other errors occur.

    In addition, if there is a lot of data that needs to be inserted into the database at one time, it is better to use SqlBulkCopy to insert data than to use the above code.

    Just convert the collection to Datatable and then use the sample code below to insert it all at once.

        class Program  
        {  
            static void Main(string[] args)  
            {  
                List<MyClass> myClasses = new List<MyClass>();  
                myClasses.Add(new MyClass() { ID = 4, Name = "Timon" });  
                myClasses.Add(new MyClass() { ID = 5, Name = "Tom" });  
                myClasses.Add(new MyClass() { ID = 6, Name = "Jerry" });  
      
                DataTable dataTable = ListToDataTable.ToDataTable<MyClass>(myClasses);  
                InsertData(dataTable);  
            }  
            public static void InsertData(DataTable dataTable)  
            {  
                string connString = @"";  
                try  
                {  
                    using (SqlConnection sqlConnection = new SqlConnection(connString))  
                    {  
                        sqlConnection.Open();  
      
                        using (SqlBulkCopy bulkCopy =  
                                   new SqlBulkCopy(sqlConnection))  
                        {  
                            bulkCopy.DestinationTableName =  
                                "dbo.Student";  
      
                            try  
                            {  
                                // Write from the source to the destination.  
                                bulkCopy.WriteToServer(dataTable);  
                            }  
                            catch (SqlExceptionex)  
                            {  
                                Console.WriteLine(ex.Message);  
                            }  
      
                        }  
                    }  
                }  
                catch (Exception)  
                {  
                    //do something.  
                }  
            }  
        }  
      
        class MyClass  
        {  
            public int ID { get; set; }  
            public string Name { get; set; }  
        }  
        public static class ListToDataTable  
        {  
            public static DataTable ToDataTable<T>(List<T> items)  
            {  
                DataTable dataTable = new DataTable(typeof(T).Name);  
                //Get all the properties by using reflection     
                PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);  
                foreach (PropertyInfo prop in Props)  
                {  
                    //Setting column names as Property names    
                    dataTable.Columns.Add(prop.Name);  
                }  
                foreach (T item in items)  
                {  
                    var values = new object[Props.Length];  
                    for (int i = 0; i < Props.Length; i++)  
                    {  
      
                        values[i] = Props[i].GetValue(item, null);  
                    }  
                    dataTable.Rows.Add(values);  
                }  
      
                return dataTable;  
            }  
        }  
    

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. Cenk 981 Reputation points
    2021-03-02T07:45:39.16+00:00

    Thank you @Timon Yang-MSFT , this code is working. I wonder if you have any suggestions to make it more maintainable, testable, robust, and dynamic. By the way, this will be a scheduled task that runs every hour, that's why I stop the application after insertion.

    class Program  
        {  
            static async Task Main(string[] args)  
            {  
                  
                 
                var builder = new HostBuilder()  
                    .ConfigureServices((hostContext, services) =>  
                    {  
                        //Setting up API Client  
                        services.AddHttpClient("OrdersClient", client =>  
                        {  
                            client.BaseAddress =  
                                new Uri("https://test/282675/orders?status=Created");  
      
                            client.DefaultRequestHeaders.Add("Accept", "application/json");  
                        });  
      
                        services.AddSingleton<IHostedService, BusinessService>();  
      
                        //Setting up app settings configuration  
                        var config = LoadConfiguration();      
                        services.AddSingleton(config);  
                    });  
      
                await builder.RunConsoleAsync();  
            }  
              
            public static IConfiguration LoadConfiguration()  
            {  
                var builder = new ConfigurationBuilder()  
                    .SetBasePath(Directory.GetCurrentDirectory())  
                    .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);  
      
                return builder.Build();  
            }  
        }  
    
    
    public class BusinessService : IHostedService  
        {  
            private IHttpClientFactory _httpClientFactory;  
            private readonly IHostApplicationLifetime _applicationLifetime;  
            private IConfiguration _configuration;  
            public BusinessService(IHttpClientFactory httpClientFactory, IHostApplicationLifetime applicationLifetime, IConfiguration configuration)  
            {  
                _httpClientFactory = httpClientFactory;  
                _applicationLifetime = applicationLifetime;  
                _configuration = configuration;  
            }  
      
            public async Task StartAsync(CancellationToken cancellationToken)  
            {  
                await MakeRequestsToRemoteService();  
                //Stop Application  
                _applicationLifetime.StopApplication();  
            }  
      
            public async Task MakeRequestsToRemoteService()  
            {  
                HttpClient httpClient = _httpClientFactory.CreateClient("OrdersClient");  
      
                var authenticationBytes = Encoding.ASCII.GetBytes("test:1234");  
      
                httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic",  
                    Convert.ToBase64String(authenticationBytes));  
                httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));  
      
                var response = await httpClient.GetAsync("https://test/282675/orders?startDate=1612126800000");  
                Console.WriteLine(DateTime.Now);  
      
                if (response.IsSuccessStatusCode)  
                {  
                    Root orders = await response.Content.ReadAsAsync<Root>();  
      
      
                    foreach (var content in orders.Content)  
                    {  
                        Console.Write(content.CustomerId + " " + content.CustomerFirstName + " " +  
                                      content.CustomerLastName + " " + content.CustomerEmail + " " + content.TotalPrice +  
                                      " " + content.InvoiceAddress.Phone + " " + content.Lines[0].ProductCode + " " +  
                                      content.Lines[0].ProductName);  
                        InsertData(content);  
                    }  
      
      
                }  
                  
                  
            }  
      
            public void InsertData(Content content)  
            {  
                 
                Console.Write(_configuration["ConnectionStrings:Development"]);  
                 
                string connString = _configuration["ConnectionStrings:Development"];  
                using (SqlConnection sqlConnection = new SqlConnection(connString))  
                {  
                    sqlConnection.Open();  
      
                    using (SqlCommand command = new SqlCommand())  
                    {  
                        command.Connection = sqlConnection;  
                        string sql = @"insert into Orders (id, customerId, firstName, lastName, phone, productCode, productName, price, orderDate, status) values (@id, @customerId, @firstName, @lastName, @phone, @productCode, @productName, @price, @orderDate, @status)";  
                        command.CommandText = sql;  
                          
                        try  
                        {  
                            command.Parameters.Add("id", SqlDbType.BigInt).Value = content.Id;  
                            command.Parameters.Add("customerId", SqlDbType.Int).Value = content.CustomerId;  
                            command.Parameters.Add("firstName", SqlDbType.VarChar).Value = content.CustomerFirstName;  
                            command.Parameters.Add("lastName", SqlDbType.VarChar).Value = content.CustomerLastName;  
                            command.Parameters.Add("phone", SqlDbType.VarChar).Value = content.InvoiceAddress.Phone;  
                            command.Parameters.Add("productCode", SqlDbType.Int).Value = content.Lines[0].ProductCode;  
                            command.Parameters.Add("productName", SqlDbType.VarChar).Value = content.Lines[0].ProductName;  
                            command.Parameters.Add("price", SqlDbType.Float).Value = content.TotalPrice;  
                            command.Parameters.Add("orderDate", SqlDbType.BigInt).Value = content.OrderDate;  
                            command.Parameters.Add("status", SqlDbType.TinyInt).Value = 1; //Retrieved  
      
                            command.ExecuteNonQuery();  
                        }  
                        catch (SqlException exception)  
                        {  
                            if (exception.Number == 2627) // Cannot insert duplicate key row in object error  
                            {  
                                Console.WriteLine("Duplicates...");  
                            }  
                            else  
                                throw; // Throw exception if this exception is unexpected  
                        }  
                    }  
                }  
            }  
      
            public Task StopAsync(CancellationToken cancellationToken)  
            {  
                return Task.CompletedTask;  
            }  
        }