using DevExtreme.AspNet.Data; using DevExtreme.AspNet.Mvc; using Microsoft.Data.SqlClient; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.ModelBinding; using Microsoft.EntityFrameworkCore; using Newtonsoft.Json; using System; using System.Collections; using System.Collections.Generic; using System.Globalization; using System.Linq; using System.Threading.Tasks; using CertAuth.Data; using CertAuth.Domain; namespace CertAuthAdmin.Controllers { [Route("api/[controller]/[action]")] public class ItemsController : Controller { private CertAuthContext _context; public List tbl { get; set; } public ItemsController(CertAuthContext context) { _context = context; } [HttpGet] public async Task Get(DataSourceLoadOptions loadOptions) { var items = _context.Items.Select(i => new { i.Id, i.Category, i.Team, i.League, i.Entity, i.Description, i.InitialVendorKey, i.InitialDate, i.ItemName, i.ItemSubType, i.ItemType, i.RFIDKey, i.Status, i.SubCategory, i.TimeStamp }); // If you work with a large amount of data, consider specifying the PaginateViaPrimaryKey and PrimaryKey properties. // In this case, keys and data are loaded in separate queries. This can make the SQL execution plan more efficient. // Refer to the topic https://github.com/DevExpress/DevExtreme.AspNet.Data/issues/336. // loadOptions.PrimaryKey = new[] { "Id" }; // loadOptions.PaginateViaPrimaryKey = true; return Json(await DataSourceLoader.LoadAsync(items, loadOptions)); } [HttpGet] public IEnumerable GetAll_WD() { string connStr = Globals.connStr; List lst; lst = new List(); IEnumerable theList; using (SqlConnection con = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand("Items_GetAllWD", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Items_WD rec = new Items_WD(); rec.Id = (int)rdr["Id"]; rec.ItemType = rdr["ItemType"].ToString(); rec.ItemSubType = rdr["ItemSubType"].ToString(); rec.Category = rdr["Category"].ToString(); rec.SubCategory = rdr["SubCategory"].ToString(); rec.League = rdr["League"].ToString(); rec.Team = rdr["Team"].ToString(); rec.Entity = rdr["Entity"].ToString(); rec.ItemName = rdr["ItemName"].ToString(); rec.Description = rdr["Description"].ToString(); rec.InitialDate = (DateTime)rdr["InitialDate"]; string IVK = rdr["InitialVendorKey"].ToString(); if (IVK != "") { rec.InitialVendorKey = (int)rdr["InitialVendorKey"]; } else { rec.InitialVendorKey = 0; } string RFK = rdr["RFIDKey"].ToString(); if (RFK != "") { rec.RFIDKey = (int)rdr["RFIDKey"]; } else { rec.RFIDKey = 0; } rec.InitialVendorName = rdr["InitialVendorName"].ToString(); rec.TagUID = rdr["TagUID"].ToString(); rec.Status = rdr["Status"].ToString(); rec.StatusDesc = rdr["StatusDesc"].ToString(); rec.TimeStamp = (DateTime)rdr["TimeStamp"]; lst.Add(rec); } con.Close(); } theList = lst; return theList; //return Json(await DataSourceLoader.LoadAsync(items, loadOptions)); } [HttpPost] public async Task Post(string values) { var model = new Items(); var valuesDict = JsonConvert.DeserializeObject(values); PopulateModel(model, valuesDict); if(!TryValidateModel(model)) return BadRequest(GetFullErrorMessage(ModelState)); var result = _context.Items.Add(model); await _context.SaveChangesAsync(); return Json(result.Entity.Id); } [HttpPut] public async Task Put(int key, string values) { var model = await _context.Items.FirstOrDefaultAsync(item => item.Id == key); if(model == null) return StatusCode(409, "Items not found"); var valuesDict = JsonConvert.DeserializeObject(values); PopulateModel(model, valuesDict); if(!TryValidateModel(model)) return BadRequest(GetFullErrorMessage(ModelState)); _context.Items.Update(model); await _context.SaveChangesAsync(); return Ok(); } [HttpDelete] public async Task Delete(int key) { var model = await _context.Items.FirstOrDefaultAsync(item => item.Id == key); _context.Items.Remove(model); await _context.SaveChangesAsync(); } private void PopulateModel(Items model, IDictionary values) { string ID = nameof(Items.Id); string CATEGORY = nameof(Items.Category); string LEAGUE = nameof(Items.League); string TEAM = nameof(Items.Team); string ENTITY = nameof(Items.Entity); string DESCRIPTION = nameof(Items.Description); string INITIAL_VENDORKEY = nameof(Items.InitialVendorKey); string INITIAL_DATE = nameof(Items.InitialDate); string ITEM_NAME = nameof(Items.ItemName); string ITEM_SUB_TYPE = nameof(Items.ItemSubType); string ITEM_TYPE = nameof(Items.ItemType); string RFID_KEY = nameof(Items.RFIDKey); string STATUS = nameof(Items.Status); string SUB_CATEGORY = nameof(Items.SubCategory); string TIME_STAMP = nameof(Items.TimeStamp); if(values.Contains(ID)) { model.Id = Convert.ToInt32(values[ID]); } if(values.Contains(CATEGORY)) { model.Category = Convert.ToString(values[CATEGORY]); } if (values.Contains(LEAGUE)) { model.League = Convert.ToString(values[LEAGUE]); } if (values.Contains(TEAM)) { model.Team = Convert.ToString(values[TEAM]); } if (values.Contains(ENTITY)) { model.Entity = Convert.ToString(values[ENTITY]); } if (values.Contains(DESCRIPTION)) { model.Description = Convert.ToString(values[DESCRIPTION]); } if(values.Contains(INITIAL_VENDORKEY)) { model.InitialVendorKey = Convert.ToInt32(values[INITIAL_VENDORKEY]); } if(values.Contains(RFID_KEY)) { model.RFIDKey = Convert.ToInt32(values[RFID_KEY]); } if(values.Contains(INITIAL_DATE)) { model.InitialDate = Convert.ToDateTime(values[INITIAL_DATE]); } if(values.Contains(ITEM_NAME)) { model.ItemName = Convert.ToString(values[ITEM_NAME]); } if(values.Contains(ITEM_SUB_TYPE)) { model.ItemSubType = Convert.ToString(values[ITEM_SUB_TYPE]); } if(values.Contains(ITEM_TYPE)) { model.ItemType = Convert.ToString(values[ITEM_TYPE]); } if(values.Contains(STATUS)) { model.Status = Convert.ToString(values[STATUS]); } if(values.Contains(SUB_CATEGORY)) { model.SubCategory = Convert.ToString(values[SUB_CATEGORY]); } if(values.Contains(TIME_STAMP)) { model.TimeStamp = Convert.ToDateTime(values[TIME_STAMP]); } } private string GetFullErrorMessage(ModelStateDictionary modelState) { var messages = new List(); foreach(var entry in modelState) { foreach(var error in entry.Value.Errors) messages.Add(error.ErrorMessage); } return String.Join(" ", messages); } } }