// POST api/GetCount
public HttpResponseMessage Post(WebApiParams param)
{
// 結果表示するメッセージ
string message = "";
Dictionary<string, string> dic = new Dictionary<string, string>();
// Ｂ層呼出し＋都度コミット
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)
{
// Ｂ層呼出し＋都度コミット
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)
{
// Ｂ層呼出し＋都度コミット
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;
}
}
}
