API data import into SQL Server database using console application written in C#

Question

How to use console application to do API data import into SQL Server database? I want to know how to import API data as xml into SQL Server database. My API has more than 10000+ records and I want to import all of them into a SQL Server database or import as xml string. I am using console application to write the code.

This is the code for my console application:

class Program
{
    static void Main(string[] args)
    {
        HttpClient client = new HttpClient();
        Console.WriteLine("Calling Api...");
        client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/xml"));

        var responseTask = client.GetAsync("http://localhost/webapi/api/user");
        Person person = null;
        responseTask.Wait();

        if (responseTask.IsCompleted)
        {
            var result = responseTask.Result;

            if (result.IsSuccessStatusCode)
            {
                var messageTask = result.Content.ReadAsAsync<Person[]>();
                messageTask.Wait();
                
                Console.WriteLine("Message From Api : " + messageTask.Result);

                using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
                {
                    con.Open();

                    using (con)
                    {
                        string sqlquery = "INSERT INTO tblPerson (ID, Name, Mobile, Birthday) VALUES (@id, @name, @mobile, @birthday)";

                        using (SqlCommand cmd = new SqlCommand(sqlquery, con))
                        {
                            cmd.Parameters.AddWithValue("@id", person.ID);
                            cmd.Parameters.AddWithValue("@name", person.Name);
                            cmd.Parameters.AddWithValue("@mobile", person.Mobile);
                            cmd.Parameters.AddWithValue("@birthday", person.Birthday);
                            
                            cmd.ExecuteNonQuery();
                        }
                        con.Close();
                    }
                }
            }
        }
    }
}

Class:

public class Person
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Mobile { get; set; }
    public DateTime Birthday { get; set; }
}

My code cannot store API data into SQL Server database, I don't know why

so i need to import api existing data to sql server database through console application, this is what i want to do all the time — user19928919
– user19928919, Commented Sep 16, 2022 at 7:48
.Result can cause a deadlock (although in a console app that is unlikely). Instead use await. What's the second using (con) for? — Charlieface
– Charlieface, Commented Sep 16, 2022 at 8:20
using (con) is for connection to the database which is for inserting api data — user19928919
– user19928919, Commented Sep 16, 2022 at 8:34

jason.kaisersmith · Accepted Answer · 2022-09-16 09:12:57Z

4

Looking at your SQL query, you specify 4 columns with the following parameter names;

@ID
@Name
@Mobile
@Birthday

But in your parameters you have specified none of these, but instead a totally different parameter name.

cmd.Parameters.AddWithValue("@premisesid", person.ID);
cmd.Parameters.AddWithValue("@premisesid", person.Name);
cmd.Parameters.AddWithValue("@premisesid", person.Mobile);
cmd.Parameters.AddWithValue("@premisesid", person.Birthday);

So you need to change these to use the correct parameter names

cmd.Parameters.AddWithValue("@ID", person.ID);
cmd.Parameters.AddWithValue("@Name", person.Name);
cmd.Parameters.AddWithValue("@Mobile", person.Mobile);
cmd.Parameters.AddWithValue("@Birthday", person.Birthday);

edited Sep 16, 2022 at 9:12

answered Sep 16, 2022 at 7:30

jason.kaisersmith

9,7233 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

Good catch! :-)

Charlieface Over a year ago

AddWithValue is Evil instead specify data types and lengths explicitly

Collectives™ on Stack Overflow

API data import into SQL Server database using console application written in C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related