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; using System.Xml; namespace CertAuthAdmin.Controllers { [Route("api/[controller]/[action]")] public class VendorMstrController : Controller { private CertAuthContext _context; public List tbl { get; set; } public VendorMstrController(CertAuthContext context) { _context = context; } [HttpGet] public async Task Get(DataSourceLoadOptions loadOptions) { var vendormstr = _context.VendorMstr.Select(i => new { i.Id, i.VendorId, i.VendorNbr, i.VendorName, i.VendorAddress1, i.VendorAddress2, i.VendorCity, i.VendorState_Province, i.VendorCountry, i.VendorPostalCode, i.VendorPhone1, i.VendorPhone2, i.VendorPhoneType1, i.VendorPhoneType2, i.VendorFax, i.VendorContact, i.CreditLimit, i.CreditRating, i.Status, i.Comments, 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(vendormstr, 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("VendorMstr_GetAllWD", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { VendorMstr_WD rec = new VendorMstr_WD(); rec.Id = (int)rdr["Id"]; rec.VendorId = rdr["VendorId"].ToString(); rec.VendorNbr = rdr["VendorNbr"].ToString(); rec.VendorName = rdr["VendorName"].ToString(); rec.VendorAddress1 = rdr["VendorAddress1"].ToString(); rec.VendorAddress2 = rdr["VendorAddress2"].ToString(); rec.VendorCity = rdr["VendorCity"].ToString(); rec.VendorState_Province = rdr["VendorState_Province"].ToString(); rec.StateDesc = rdr["StateDesc"].ToString(); rec.VendorCountry = rdr["VendorCountry"].ToString(); rec.CountryDesc = rdr["CountryDesc"].ToString(); rec.VendorPostalCode = rdr["VendorPostalCode"].ToString(); rec.VendorPhone1 = rdr["VendorPhone1"].ToString(); rec.VendorPhone2 = rdr["VendorPhone2"].ToString(); rec.VendorPhoneType1 = rdr["VendorPhoneType1"].ToString(); rec.PhoneType1Desc = rdr["PhoneType1Desc"].ToString(); rec.VendorPhoneType2 = rdr["VendorPhoneType2"].ToString(); rec.PhoneType2Desc = rdr["PhoneType2Desc"].ToString(); rec.VendorFax = rdr["VendorFax"].ToString(); rec.VendorContact = rdr["VendorContact"].ToString(); rec.CreditLimit = (decimal)rdr["CreditLimit"]; rec.CreditRating = rdr["CreditRating"].ToString(); rec.CreditRatingDesc = rdr["CreditRatingDesc"].ToString(); rec.Status = rdr["Status"].ToString(); rec.StatusDesc = rdr["StatusDesc"].ToString(); rec.Comments = rdr["Comments"].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 VendorMstr(); var valuesDict = JsonConvert.DeserializeObject(values); PopulateModel(model, valuesDict); if(!TryValidateModel(model)) return BadRequest(GetFullErrorMessage(ModelState)); var result = _context.VendorMstr.Add(model); await _context.SaveChangesAsync(); return Json(result.Entity.VendorId); } [HttpPut] public async Task Put(int key, string values) { var model = await _context.VendorMstr.FirstOrDefaultAsync(item => item.Id == key); if(model == null) return StatusCode(409, "VendorMstr not found"); var valuesDict = JsonConvert.DeserializeObject(values); PopulateModel(model, valuesDict); if(!TryValidateModel(model)) return BadRequest(GetFullErrorMessage(ModelState)); await _context.SaveChangesAsync(); return Ok(); } [HttpDelete] public async Task Delete(int key) { var model = await _context.VendorMstr.FirstOrDefaultAsync(item => item.Id == key); _context.VendorMstr.Remove(model); await _context.SaveChangesAsync(); } private void PopulateModel(VendorMstr model, IDictionary values) { string ID = nameof(VendorMstr.Id); string VENDORID = nameof(VendorMstr.VendorId); string VENDOR_NBR = nameof(VendorMstr.VendorNbr); string VENDOR_NAME = nameof(VendorMstr.VendorName); string VENDOR_ADDRESS1 = nameof(VendorMstr.VendorAddress1); string VENDOR_ADDRESS2 = nameof(VendorMstr.VendorAddress2); string VENDOR_CITY = nameof(VendorMstr.VendorCity); string VENDOR_STATE_PROVINCE = nameof(VendorMstr.VendorState_Province); string VENDOR_COUNTRY = nameof(VendorMstr.VendorCountry); string VENDOR_POSTAL_CODE = nameof(VendorMstr.VendorPostalCode); string VENDOR_PHONE1 = nameof(VendorMstr.VendorPhone1); string VENDOR_PHONE2 = nameof(VendorMstr.VendorPhone2); string VENDOR_PHONE_TYPE1 = nameof(VendorMstr.VendorPhoneType1); string VENDOR_PHONE_TYPE2 = nameof(VendorMstr.VendorPhoneType2); string VENDOR_FAX = nameof(VendorMstr.VendorFax); string VENDOR_CONTACT = nameof(VendorMstr.VendorContact); string CREDIT_LIMIT = nameof(VendorMstr.CreditLimit); string CREDIT_RATING = nameof(VendorMstr.CreditRating); string STATUS = nameof(VendorMstr.Status); string COMMENTS = nameof(VendorMstr.Comments); string TIME_STAMP = nameof(VendorMstr.TimeStamp); if (values.Contains(ID)) { model.Id = Convert.ToInt32(values[ID]); } if(values.Contains(VENDORID)) { model.VendorId = Convert.ToString(values[VENDORID]); } if(values.Contains(VENDOR_NBR)) { model.VendorNbr = Convert.ToString(values[VENDOR_NBR]); } if(values.Contains(VENDOR_NAME)) { model.VendorName = Convert.ToString(values[VENDOR_NAME]); } if(values.Contains(VENDOR_ADDRESS1)) { model.VendorAddress1 = Convert.ToString(values[VENDOR_ADDRESS1]); } if(values.Contains(VENDOR_ADDRESS2)) { model.VendorAddress2 = Convert.ToString(values[VENDOR_ADDRESS2]); } if(values.Contains(VENDOR_CITY)) { model.VendorCity = Convert.ToString(values[VENDOR_CITY]); } if(values.Contains(VENDOR_STATE_PROVINCE)) { model.VendorState_Province = Convert.ToString(values[VENDOR_STATE_PROVINCE]); } if(values.Contains(VENDOR_COUNTRY)) { model.VendorCountry = Convert.ToString(values[VENDOR_COUNTRY]); } if(values.Contains(VENDOR_POSTAL_CODE)) { model.VendorPostalCode = Convert.ToString(values[VENDOR_POSTAL_CODE]); } if(values.Contains(VENDOR_PHONE1)) { model.VendorPhone1 = Convert.ToString(values[VENDOR_PHONE1]); } if(values.Contains(VENDOR_PHONE2)) { model.VendorPhone2 = Convert.ToString(values[VENDOR_PHONE2]); } if(values.Contains(VENDOR_PHONE_TYPE1)) { model.VendorPhoneType1 = Convert.ToString(values[VENDOR_PHONE_TYPE1]); } if(values.Contains(VENDOR_PHONE_TYPE2)) { model.VendorPhoneType2 = Convert.ToString(values[VENDOR_PHONE_TYPE2]); } if(values.Contains(VENDOR_FAX)) { model.VendorFax = Convert.ToString(values[VENDOR_FAX]); } if(values.Contains(VENDOR_CONTACT)) { model.VendorContact = Convert.ToString(values[VENDOR_CONTACT]); } if(values.Contains(CREDIT_LIMIT)) { model.CreditLimit = Convert.ToDecimal(values[CREDIT_LIMIT], CultureInfo.InvariantCulture); } if(values.Contains(CREDIT_RATING)) { model.CreditRating = Convert.ToString(values[CREDIT_RATING]); } if(values.Contains(STATUS)) { model.Status = Convert.ToString(values[STATUS]); } if(values.Contains(COMMENTS)) { model.Comments = Convert.ToString(values[COMMENTS]); } 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); } } }