I am working on reading JSON data from the URL and insert it into the SQL table. I have used this sample URL https://raw.githubusercontent.com/wedeploy-examples/supermarket-web-example/master/products.json and create a Model class file as below.
View Model Class
public class ApiJsonViewModel
{
public string Title { get; set; }
public string Type { get; set; }
public string Description { get; set; }
public string Filename { get; set; }
public string Height { get; set; }
public string Width { get; set; }
public string Price { get; set; }
public string Rating { get; set; }
}
I have a form with one textbox control to display JSON key data from the third-party API URL and a drop-down list with values from the database.
The model class used to populate dropdownlist
public class K360DbCatgViewModel
{
public string Name { get; set; }
public string MetaTitle { get; set; }
public string MetaKeywords { get; set; }
public string MetaDescription { get; set; }
public string ShortDescription { get; set; }
public string Description { get; set; }
public string Specification { get; set; }
public decimal Price { get; set; }
public decimal? OldPrice { get; set; }
public decimal? SpecialPrice { get; set; }
public DateTimeOffset? SpecialPriceStart { get; set; }
public DateTimeOffset? SpecialPriceEnd { get; set; }
public int StockQuantity { get; set; }
public string Sku { get; set; }
public string Gtin { get; set; }
public string NormalizedName { get; set; }
public int DisplayOrder { get; set; }
public int ReviewsCount { get; set; }
public double? RatingAverage { get; set; }
}
Razor View Page
<table class="table" id="tb_properties" style="width:100%">
<tr>
@if (ViewBag.ApiProp != null)
{
@foreach (var itemApiProp in ViewBag.ApiProp)
{
<td>
<input type="text" value="@itemApiProp.Key" class="form-control" />
<select class="form-control">
<option value="">--Select-- </option>
@foreach (var itemK360Prop in ViewBag.K360Prop)
{
<option>@itemK360Prop.Key</option>
}
</select>
</td>
}
}
</tr>
<tr>
<td>
<button type="submit" class="btn btn-primary" style="margin-
right:50px">Catalog Mapping</button>
</td>
</tr>
</table>
Controller code to fetch values for the view controls
public IActionResult Index()
{
string strAPIUrl = "https://raw.githubusercontent.com/wedeploy-examples/supermarket-web-example/master/products.json";
string jsonUrlProducts;
using (WebClient client = new WebClient())
{
jsonUrlProducts = client.DownloadString(strAPIUrl);
}
CreateDynamicAPiProp(jsonUrlProducts);
CreateK360Prop();
return View();
}
[HttpGet]
public IActionResult CreateDynamicAPiProp(string ApiUrl)
{
Dictionary<string, object> dictResultsAdd = new Dictionary<string, object>();
var objResponseB = JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(ApiUrl);
foreach (Dictionary<string, object> DictMainKV in objResponseB)
{
foreach (KeyValuePair<string, object> item in DictMainKV)
{
dictResultsAdd.Add(item.Key, item.Value);
}
break;
}
ViewBag.ApiProp = dictResultsAdd;
return RedirectToAction("Index");
}
[HttpGet]
public IActionResult CreateK360Prop()
{
var ListK360Prop = new List<ApiMapDbViewModel>();
PropertyInfo[] propertyInfosK360 = typeof(K360DbCatgViewModel).GetProperties();
foreach (PropertyInfo propertyInfoK360 in propertyInfosK360)
{
ListK360Prop.Add(new ApiMapDbViewModel{Value = propertyInfoK360.Name.ToString(), Key = propertyInfoK360.Name.ToString()});
}
ViewBag.K360Prop = ListK360Prop;
return RedirectToAction("Index");
}
I have used the below jQuery and passed the JSON Model object to controller insert method on HTTP post to save selected records.
jQuery Code
@section scripts{
<script>
$(function() {
$("button[type='submit']").click(function() {
event.preventDefault();
var properties = [];
$("#tb_properties tr:first").find("td").each(function(index, item) {
var propertyname = $(item).find("input[type='text']").val();
var selctedvalue = $(item).find("select").val();
properties.push('"' + propertyname + '":"' + selctedvalue + '"');
});
var jsonstr = '{' + properties.join(",") + '}';
var jsobject = JSON.parse(jsonstr);
$.ajax({
type: "Post",
url: "/KEMap/Insert",
data: {
jsonModel: jsobject
},
success: function(response) {
toastr.info(response.status + "<br>" + "<br>" + response.message);
$("#tb_properties select").val("");
$("#partial_div").load(window.location.href + " #partial_div");
},
error: function(xhr, textStatus, errorThrown) {
console.log('in error');
}
});
});
});
</script>
Insert Method
[HttpPost]
public IActionResult Insert(ApiJsonViewModel jsonModel)
{
Type type = jsonModel.GetType();
PropertyInfo[] props = type.GetProperties();
List<K360mapMaster> K360mapListObj = new List<K360mapMaster>();
K360mapListObj = props.Where(c => !string.IsNullOrEmpty(c.GetValue(jsonModel, null)?.ToString())).Select(c => new K360mapMaster()
{ClientCatalog = c.Name, K360catalog = c.GetValue(jsonModel, null)?.ToString()}).ToList();
if (K360mapListObj.Count > 0)
{
_context.K360mapMasters.AddRange(K360mapListObj);
_context.SaveChanges();
return Json(new { Status = "Sucess", Message = "Mapped" });
}
return Json(new { Status = "Fail", Message = "Not done" });
}
SQL Table
CREATE TABLE [dbo].[K360Map_Master](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ClientCatalog] [nvarchar](450) NOT NULL,
[K360Catalog] [nvarchar](450) NOT NULL,
[MapFlag] [bit] NOT NULL,
CONSTRAINT [PK_K360Map_Master] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Everything working fine. I was able to insert selected textbox and dropdown list values into the SQL table.
The problem is JSON data I am using from the third-party API URL differ dynamically. For example for the below 2 sample URLs, I need to create again 2 model class file.https://gist.githubusercontent.com/dpetersen/1237910/raw/6ceb2161f756d4b4d5c22754d1ed8d869249f186/product_grid.js https://raw.githubusercontent.com/mdn/fetch-examples/master/fetch-json/products.json.
I get the feedback as hardcoded coding with static data. I was not permitted to create a separate model class for each URL.
I also don't know how to proceed without creating a model class file. I need to pass selected control values to the controller insert method without a JSON model object.
I hope anybody can help me here.
