.net core 2.1 console app with http client factory

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.

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();**  

                    }  
                }  
  
            }  

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.

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;  
        }  
    }