I am doing add and update JSON data into the SQL table.
Below Code working for adding and updating a new set of records
List<CatalogProduct> ListKp = new List<CatalogProduct>(); using (var transaction = _context.Database.BeginTransaction()) { try { int numP = 0; var catalogProducts = _context.CatalogProducts.ToList(); foreach (var kp in ListKp) { if (!catalogProducts.Any(x => x.Name == kp.Name)) { _context.CatalogProducts.Add(kp); } else { //Use AutoMapper automatically do the mapping var config = new MapperConfiguration(cfg => cfg.CreateMap<CatalogProduct, CatalogProduct>().ForMember(c => c.Id, opt => opt.Ignore())); var oldone = catalogProducts.FirstOrDefault(c => c.Name == kp.Name); var mapper = config.CreateMapper(); oldone = mapper.Map<CatalogProduct, CatalogProduct>(kp, oldone); _context.CatalogProducts.Update(oldone); } } numP = _context.SaveChanges(); transaction.Commit(); return Json("No conflicts. " + numP + " product details saved."); } catch (Exception ex) { transaction.Rollback(); return Json("Error occurred." + ex.Message); throw new Exception(); } } Sample JSON data
{ "title": "Brown eggs", "type": "dairy", "description": "Raw organic brown eggs in a basket", "filename": "0.jpg", "height": 600, "width": 400, "price": 28.1, "rating": 4 }, { "title": "Sweet fresh stawberry", "type": "fruit", "description": "Sweet fresh stawberry on the wooden table", "filename": "1.jpg", "height": 450, "width": 299, "price": 29.45, "rating": 4 }, First I will add [type] key object values from the above JSON into the products table [NAME] field. It will add a new set of records.
When I try to update the products table [NAME] field with [title] key object values, again it will add a new set of records.
Need to update products table [NAME] field without adding again.
I don't know how to check already existing records in a table with model list values. Already spent much time on this. I am new to EF Core, please anybody can help me
Complete Code
[HttpPost] public IActionResult InsertProductDetails() { using WebClient wc = new WebClient(); string contentString = wc.DownloadString(baseurl); List<Dictionary<string, string>> ListJsonProductContent = new List<Dictionary<string, string>>(); var token = JToken.Parse(contentString); if (token.Type == JTokenType.Array) // "[" { ListJsonProductContent = JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(contentString); } else if (token.Type == JTokenType.Object) // "{" { var ObjectResponse = JsonConvert.DeserializeObject<Dictionary<string, object>>(contentString); foreach (var x in ObjectResponse) { string key = x.Key.ToString(); string val = x.Value.ToString(); foreach (var dicItemML in JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(val)) { ListJsonProductContent.Add(dicItemML); } } } List <K360MappingMaster> ListMappedDataDb = new List<K360MappingMaster>(); var VLinqQuery = from KMM in _context.K360MappingMasters where KMM.ThirdPartyBaseUrlName != null && KMM.ThirdPartyBaseUrlName == baseurl select KMM; ListMappedDataDb = VLinqQuery.ToList(); foreach (var dicItemML in ListJsonProductContent) { Dictionary<string, string> updItem = new Dictionary<string, string>(); foreach (var itemMl in dicItemML) { if (ListMappedDataDb.Select(s => s.ApiCatalog).ToList().Contains(itemMl.Key)) { if (updItem.ContainsKey(ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault())) { if (ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault() == "Specification") { updItem[ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault()] += "<p>" + itemMl.Key + " :" + itemMl.Value + "<p>"; } else { updItem[ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault()] += " " + itemMl.Value; } } else { if (ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault() == "Specification") { updItem.Add(ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault(), "<p>" + itemMl.Key + " :" + itemMl.Value + "<p>"); } else { updItem.Add(ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault(), itemMl.Value); } } } dicItemML.Remove(itemMl.Key); } foreach (var itemM2 in updItem) { dicItemML.Add(itemM2.Key, itemM2.Value); } } List<CatalogProduct> ListKp = new List<CatalogProduct>(); foreach (var dicItem in ListJsonProductContent) { CatalogProduct Ctgkp = new CatalogProduct { Name = dicItem.ContainsKey("Name") ? dicItem["Name"] : "No Product", Slug = dicItem.ContainsKey("Name") ? string.Concat(dicItem["Name"].Where(c => !char.IsWhiteSpace(c))).ToLower() : "No Slug", Price = dicItem.ContainsKey("Price") ? decimal.Parse(dicItem["Price"], CultureInfo.InvariantCulture) : default, ShortDescription = dicItem.ContainsKey("ShortDescription") ? dicItem["ShortDescription"] : null, Description = dicItem.ContainsKey("Description") ? dicItem["Description"] : null, Specification = dicItem.ContainsKey("Specification") ? dicItem["Specification"] : null, RatingAverage = dicItem.ContainsKey("RatingAverage") ? double.Parse(dicItem["RatingAverage"], CultureInfo.InvariantCulture) : null}; ListKp.Add(Ctgkp); } using (var transaction = _context.Database.BeginTransaction()) { try { int numP = 0; var catalogProducts = _context.CatalogProducts.ToList(); foreach (var kp in ListKp) { if (!catalogProducts.Any(x => x.Name == kp.Name)) { _context.CatalogProducts.Add(kp); } else { //Use AutoMapper automatically do the mapping var config = new MapperConfiguration(cfg => cfg.CreateMap<CatalogProduct, CatalogProduct>().ForMember(c => c.Id, opt => opt.Ignore())); var oldone = catalogProducts.FirstOrDefault(c => c.Name == kp.Name); var mapper = config.CreateMapper(); oldone = mapper.Map<CatalogProduct, CatalogProduct>(kp, oldone); _context.CatalogProducts.Update(oldone); } } numP = _context.SaveChanges(); (from q in _context.K360MappingMasters where q.ThirdPartyBaseUrlName == baseurl select q).ToList().ForEach(x => x.InsertStatusFlag = true); _context.SaveChanges(); transaction.Commit(); return Json("No conflicts. " + numP + " product details saved."); } catch (Exception ex) { transaction.Rollback(); return Json("Error occurred." + ex.Message); throw new Exception(); } } 
没有评论:
发表评论