// POST api/GetCount public HttpResponseMessage Post(WebApiParams param) { // 結果表示するメッセージ string message = ""; Dictionary<string, string> dic = new Dictionary<string, string>(); // B層呼出し+都度コミット LayerB layerB = new LayerB(); layerB.SelectCount(param); // 結果(正常系) message = param.Obj.ToString() + "件のデータがあります"; dic.Add("Message", message); return Request.CreateResponse(HttpStatusCode.OK, dic); }
/// <summary> /// Delete records in shippers table /// </summary> /// <param name="param">param</param> /// <param name="param">param</param> public void Delete(WebApiParams param) { using (NorthwindEntities context = new NorthwindEntities()) { // Creating object to store the result set. object dynObj; // Gets the Shipper record using Linq to Entities based on ShipperID Shipper objShipper = context.Shippers.FirstOrDefault(x => x.ShipperID == param.ShipperId); if (objShipper != null) { // Deletes Shipper entity from the context object. context.Shippers.Remove(objShipper); } // Saves all changes made in the context object to the database. dynObj = context.SaveChanges(); param.Obj = dynObj; } }
/// <summary> /// Counts the total number of records from shippers table /// </summary> /// <param name="param">param</param> public void SelectCount(WebApiParams param) { DaoLayer daoObj = new DaoLayer(); daoObj.SelectCount(param); }
/// <summary> /// Gets the shipper table based on input conditions /// </summary> /// <param name="param"></param> public void SelectByCondition(WebApiParams param) { DaoLayer daoObj = new DaoLayer(); daoObj.SelectByCondition(param); }
/// <summary> /// Inserts data to the shipper table /// </summary> /// <param name="param"></param> public void Insert(WebApiParams param) { DaoLayer daoObj = new DaoLayer(); daoObj.Insert(param); }
/// <summary> /// Deletes data from the shipper table based on shipper id /// </summary> /// <param name="param"></param> public void Delete(WebApiParams param) { DaoLayer daoObj = new DaoLayer(); daoObj.Delete(param); }
/// <summary> /// Updates data to the shipper table /// </summary> /// <param name="param"></param> public void Update(WebApiParams param) { DaoLayer daoObj = new DaoLayer(); daoObj.Update(param); }
// POST api/SelectListEF public HttpResponseMessage Post(WebApiParams param) { // B層呼出し+都度コミット LayerB layerB = new LayerB(); layerB.SelectAll_List(param); // 結果(正常系) List<Shipper> shipperData = new List<Shipper>(); shipperData = (List<Shipper>)param.Obj; return Request.CreateResponse(HttpStatusCode.OK, shipperData); }
// POST api/Select public HttpResponseMessage Post(WebApiParams param) { // B層呼出し+都度コミット LayerB layerB = new LayerB(); layerB.SelectByCondition(param); // 結果(正常系) List<Shipper> shipperData = new List<Shipper>(); shipperData = (List<Shipper>)param.Obj; if (shipperData.Count == 0) { // 結果表示するメッセージ string message = ""; Dictionary<string, string> dic = new Dictionary<string, string>(); // 結果(正常系) message = "No records found"; dic.Add("NoRecords", message); return Request.CreateResponse(HttpStatusCode.OK, dic); } else { return Request.CreateResponse(HttpStatusCode.OK, shipperData); } }
/// <summary> /// Insert record in Shippers table /// </summary> /// <param name="param">param</param> /// <param name="param">param</param> public void Insert(WebApiParams param) { using (NorthwindEntities context = new NorthwindEntities()) { // Creating object to store the result set. object dynObj; // Creating Shipper object. Shipper objShipper = new Shipper(); // Set vlaues to the Shipper Entity. if (!string.IsNullOrWhiteSpace(param.CompanyName)) { objShipper.CompanyName = param.CompanyName; objShipper.Phone = param.Phone; // Adds Shipper Entity to the context object. context.Shippers.Add(objShipper); } // Saves all changes made in the context object to the database. dynObj = context.SaveChanges(); param.Obj = dynObj; } }
/// <summary> /// Update records in shippers table /// </summary> /// <param name="param">param</param> /// <param name="param">param</param> public void Update(WebApiParams param) { using (NorthwindEntities context = new NorthwindEntities()) { // Creating object to store the result set. object dynObj; // Gets the Shipper record using Linq to Entities based on ShipperID Shipper objShipper = context.Shippers.FirstOrDefault(x => x.ShipperID == param.ShipperId); if (objShipper != null) { // Set vlaues to the Shipper Entity. if (!string.IsNullOrWhiteSpace(param.CompanyName)) { objShipper.CompanyName = param.CompanyName; } if (!string.IsNullOrWhiteSpace(param.Phone)) { objShipper.Phone = param.Phone; } } // Saves all changes made in the context object to the database. dynObj = context.SaveChanges(); param.Obj = dynObj; } }
/// <summary> /// Count the total number of records from shippers table /// </summary> /// <param name="param">param</param> public void SelectCount(WebApiParams param) { using (NorthwindEntities context = new NorthwindEntities()) { switch (param.ddlMode2) { // 静的SQL case "static": int total = (from m in context.Shippers select m).Count(); param.Obj = total; break; case "dynamic": param.Obj = context.Shippers.Count(); break; } } }
/// <summary> /// Select records from shippers table based on various conditions /// </summary> /// <param name="param"></param> public void SelectByCondition(WebApiParams param) { List<Shipper> shipperList = new List<Shipper>(); // Get Shipper data from database based on condition. IQueryable<Shipper> parentQuery = null; using (NorthwindEntities context = new NorthwindEntities()) { // Get Shipper data if ShipperID, CompanyName and Phone number are entered if (param.ShipperId != 0 && !string.IsNullOrEmpty(param.CompanyName) && !string.IsNullOrEmpty(param.Phone)) { parentQuery = context.Shippers; parentQuery = parentQuery.Where(c => c.ShipperID == param.ShipperId && c.CompanyName == param.CompanyName && c.Phone == param.Phone).OrderByDescending(o => o.ShipperID); var dynQuery = parentQuery.ToList(); foreach (var item in dynQuery) { Shipper shipperItem = new Shipper(); shipperItem.ShipperID = item.ShipperID; shipperItem.CompanyName = item.CompanyName; shipperItem.Phone = item.Phone; shipperList.Add(shipperItem); } } else { if (param.ShipperId != 0) { parentQuery = context.Shippers; // If only ShipperID is entered if (string.IsNullOrEmpty(param.CompanyName) && string.IsNullOrEmpty(param.Phone)) { parentQuery = parentQuery.Where(c => c.ShipperID == param.ShipperId); } // If only ShipperID and CompanyName are entered else if (!string.IsNullOrEmpty(param.CompanyName) && string.IsNullOrEmpty(param.Phone)) { parentQuery = parentQuery.Where(c => c.ShipperID == param.ShipperId && c.CompanyName == param.CompanyName); } // If only ShipperID and Phone number are entered else if (string.IsNullOrEmpty(param.CompanyName) && !string.IsNullOrEmpty(param.Phone)) { parentQuery = parentQuery.Where(c => c.ShipperID == param.ShipperId && c.Phone == param.Phone); } // Execute the query and Assign Shipper data to the data table var dynQuery = parentQuery.ToList(); foreach (var item in dynQuery) { Shipper shipperItem = new Shipper(); shipperItem.ShipperID = item.ShipperID; shipperItem.CompanyName = item.CompanyName; shipperItem.Phone = item.Phone; shipperList.Add(shipperItem); } } else if (!string.IsNullOrEmpty(param.CompanyName)) { parentQuery = context.Shippers; // If CompanyName and Phone number are entered if (param.Phone != null) { parentQuery = parentQuery.Where(c => c.CompanyName == param.CompanyName && c.Phone == param.Phone); } // If only CompanyName is entered else { parentQuery = parentQuery.Where(c => c.CompanyName == param.CompanyName).OrderBy(o => o.ShipperID); } // Execute the query and Assign Shipper data to the data table var dynQuery = parentQuery.ToList(); foreach (var item in dynQuery) { Shipper shipperItem = new Shipper(); shipperItem.ShipperID = item.ShipperID; shipperItem.CompanyName = item.CompanyName; shipperItem.Phone = item.Phone; shipperList.Add(shipperItem); } } // If only Phone number is entered. else if (!string.IsNullOrEmpty(param.Phone)) { parentQuery = context.Shippers; parentQuery = parentQuery.Where(c => c.Phone == param.Phone).OrderBy(o => o.ShipperID); var dynQuery = parentQuery.ToList(); foreach (var item in dynQuery) { Shipper shipperItem = new Shipper(); shipperItem.ShipperID = item.ShipperID; shipperItem.CompanyName = item.CompanyName; shipperItem.Phone = item.Phone; shipperList.Add(shipperItem); } } } } param.Obj = shipperList; }
/// <summary> /// SelectAll_List method /// </summary> /// <param name="param">param</param> /// <param name="param">param</param> public void SelectAll_List(WebApiParams param) { List<Shipper> shipperList = new List<Shipper>(); using (NorthwindEntities context = new NorthwindEntities()) { switch (param.ddlMode2) { case "static": // Execute static SQL List<Shipper> staticQuery = null; switch (param.ddlOrderColumn) { case "c1": if (param.ddlOrderSequence == "A") { staticQuery = (from shipper in context.Shippers orderby shipper.ShipperID select shipper).ToList(); } else { staticQuery = (from shipper in context.Shippers orderby shipper.ShipperID descending select shipper).ToList(); } break; case "c2": if (param.ddlOrderSequence == "A") { staticQuery = (from shipper in context.Shippers orderby shipper.CompanyName select shipper).ToList(); } else { staticQuery = (from shipper in context.Shippers orderby shipper.CompanyName descending select shipper).ToList(); } break; case "c3": if (param.ddlOrderSequence == "A") { staticQuery = (from shipper in context.Shippers orderby shipper.Phone select shipper).ToList(); } else { staticQuery = (from shipper in context.Shippers orderby shipper.Phone descending select shipper).ToList(); } break; } foreach (var item in staticQuery) { Shipper shipperItem = new Shipper(); shipperItem.ShipperID = item.ShipperID; shipperItem.CompanyName = item.CompanyName; shipperItem.Phone = item.Phone; shipperList.Add(shipperItem); } param.Obj = shipperList; break; case "dynamic": // Execute dynamic SQL IQueryable<Shipper> dynQuery = null; switch (param.ddlOrderColumn) { case "c1": if (param.ddlOrderSequence == "A") { dynQuery = context.Shippers.OrderBy(o => o.ShipperID); } else { dynQuery = context.Shippers.OrderByDescending(o => o.ShipperID); } break; case "c2": if (param.ddlOrderSequence == "A") { dynQuery = context.Shippers.OrderBy(o => o.CompanyName); } else { dynQuery = context.Shippers.OrderByDescending(o => o.CompanyName); } break; case "c3": if (param.ddlOrderSequence == "A") { dynQuery = context.Shippers.OrderBy(o => o.Phone); } else { dynQuery = context.Shippers.OrderByDescending(o => o.Phone); } break; } foreach (var item in dynQuery) { Shipper shipperItem = new Shipper(); shipperItem.ShipperID = item.ShipperID; shipperItem.CompanyName = item.CompanyName; shipperItem.Phone = item.Phone; shipperList.Add(shipperItem); } param.Obj = shipperList; break; } } }