Insert Json response into SQL database using ADO.Net in ASP.Net Core MVC

fp2021
 
on Jul 21, 2021 06:12 AM
Sample_436526.zip
2045 Views

I have a .Net Core Web API project that uses Odata queries to retrieve information in JSON format.

I need to be able to save/insert the JSON information into a SQL table using ADO.Net instead of Entity Framework. 

Does anyone have any advice or examples of how to accomplish this? 

I'm new to .Net Core MVC, but I have manged to retrieve the JSON response and display on screen in a div.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 21, 2021 06:57 AM

Hi fp2021,

Check this example. Now please take its reference and correct your code.

Database

I have made use of the following table Customers with the schema as follows. 

You can download the database table SQL by clicking the download link below.

Download SQL file

Model

public class CustomerModel
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}

Namespaces

using System.Data.SqlClient;
using System.Net;
using Newtonsoft.Json;
using Microsoft.Extensions.Configuration;

Controller

public class HomeController : Controller
{
    private IConfiguration Configuration;
    public HomeController(IConfiguration _configuration)
    {
        Configuration = _configuration;
    }
    public IActionResult Index()
    {
        //Fetch the JSON string.
        ServicePointManager.Expect100Continue = true;
        ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
        string json = new WebClient().DownloadString("https://raw.githubusercontent.com/aspsnippets/test/master/Customers.json");
        List<CustomerModel> customers = JsonConvert.DeserializeObject<List<CustomerModel>>(json);
        foreach (CustomerModel customer in customers)
        {
            InsertCustomer(customer.CustomerId, customer.Name, customer.Country);
        }

        return View();
    }

    private void InsertCustomer(int id, string name, string country)
    {
        string constr = this.Configuration.GetConnectionString("ConStr");
        using (SqlConnection con = new SqlConnection(constr))
        {
            string query = "INSERT INTO CustomersInf VALUES(@Id,@Name,@Country)";
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Country", country);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

View

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
</body>
</html>

Inserted record in database

For more details on reading connection string from appsettings.json refer below article.

.Net Core: Set SQL Server Connection String in AppSettings.json