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

user19928919 0
Viewed 42

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

console-application sql-server c# api
1 Answers

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);
jason.kaisersmith0
Related