how to access multiple tables in a same database dynamically in asp.net core

[Swarnava Sen]

namespace ProductApi.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class InfoController : ControllerBase
{
private readonly PDetailsContext _context;

public InfoController(PDetailsContext context)
{
_context = context;
}

// GET: api/Info
[HttpGet]
public dynamic GetDynamic()
{
var Reqst = Request.Query;
List<Products> TakeResult = null;
List<Products> ResultOfSort = null;
List<Products> ResultOfSkip = null;
List<Products> ResultOfPrice = null;
List<Order> TakeResultOrder = null;
List<Order> ResultOfSortOrder = null;
List<Order> ResultOfSkipOrder = null;
List<Order> ResultOfPriceOrder = null;
PropClass PC = new PropClass();
foreach (var k in Reqst)
{
if (k.Key.Equals("tablename"))
{
PC.tablename = k.Value;
}
if (k.Key.Equals("sortColName"))
{
PC.sortColName = k.Value;
}
if (k.Key.Equals("sortdirection"))
{
PC.sortdirection = k.Value;
}
if (k.Key.Equals("skip"))
{
PC.skip = Convert.ToInt32(k.Value);
}
if (k.Key.Equals("take"))
{
PC.take = Convert.ToInt32(k.Value);
}
if (k.Key.Equals("priceGt"))
{
PC.priceGt = Convert.ToInt32(k.Value);
}
if (k.Key.Equals("priceLt"))
{
PC.priceLt = Convert.ToInt32(k.Value);
}
if (k.Key.Equals("colour"))
{
PC.colour = k.Value;
}
}
if (PC.tablename == "Products")
{
var data = _context.Products.ToList();
var sorting = (from p in data select p);
// Sorting Part
if (PC.sortColName != null && PC.sortdirection != null)
{
TakeResult = sortMethod(data, PC.sortColName, PC.sortdirection);
}
if (PC.skip != 0 || PC.take != 0 && TakeResult != null)
{
ResultOfSort = SkipMethod(TakeResult, PC.skip, PC.take);
}
if (PC.priceGt != 0 || PC.priceLt != 0 && ResultOfSort != null)
{
ResultOfSkip = PriceMethod(ResultOfSort, PC.priceGt, PC.priceLt);
}
if (PC.colour != null && ResultOfSkip != null)
{
ResultOfPrice = ColourMethod(ResultOfSkip, PC.colour);
return ResultOfPrice;
}
}
else if(PC.tablename=="Orders")
{
var dataOrder = _context.Orders.ToList();
var sortingOrder = (from p in dataOrder select p);
// Sorting Part
if (PC.sortColName != null && PC.sortdirection != null)
{
TakeResultOrder = sortMethodOrder(dataOrder, PC.sortColName, PC.sortdirection);
}
if (PC.skip != 0 || PC.take != 0 && TakeResultOrder != null)
{
ResultOfSortOrder = SkipMethodOrder(TakeResultOrder, PC.skip, PC.take);
}
if (PC.priceGt != 0 || PC.priceLt != 0 && ResultOfSortOrder != null)
{
ResultOfSkipOrder = PriceMethodOrder(ResultOfSortOrder, PC.priceGt, PC.priceLt);
}
if (PC.colour != null && ResultOfSkipOrder != null)
{
ResultOfPriceOrder = ColourMethodOrder(ResultOfSkipOrder, PC.colour);
return ResultOfPriceOrder;
}
}
return BadRequest();
//return PC;
}


//Sorting
private List<Products> sortMethod(List<Products> data, string sortColName, string sortdirection)
{
var sorting = (from p in data
select p).ToList();
var stcolName = typeof(Products).GetProperty(sortColName);
List<Products> sortingRes = null;
switch (sortdirection)
{
case "desc":
sortingRes = sorting.OrderByDescending(p => stcolName.GetValue(p)).ToList();
break;

default:
sortingRes = sorting.OrderBy(p => stcolName.GetValue(p)).ToList();
break;

}
return sortingRes;
}
//Skip and Take Method
private List<Products> SkipMethod(List<Products> TakeResult, int skip, int take)
{
var data = _context.Products.ToList();
var result = (from s in TakeResult.Skip(skip).Take(take) select s).ToList();
return result;
}

//Price
private List<Products> PriceMethod(List<Products> ResultOfSort, float price1, float price2)
{
List<Products> priceresult = null;
if (price1 != 0)
{
priceresult = ResultOfSort.Where(p => p.Price > price1).ToList();
}
else if (price2 != 0)
{
priceresult = ResultOfSort.Where(p => p.Price < price2).ToList();
}
return priceresult;
}

//Colour Method
private List<Products> ColourMethod(List<Products> ResultOfSkip, string clr)
{
List<Products> resClr = null;
resClr = ResultOfSkip.Where(p => p.Colour == clr).ToList();
return resClr;
}

//Order Table
//Sorting
private List<Order> sortMethodOrder(List<Order> dataOrder, string sortColName, string sortdirection)
{
var sortingOrder = (from p in dataOrder
select p).ToList();
var stcolNameOrder = typeof(Order).GetProperty(sortColName);
List<Order> sortingRes = null;
switch (sortdirection)
{
case "desc":
sortingRes = sortingOrder.OrderByDescending(o => stcolNameOrder.GetValue(o)).ToList();
break;

default:
sortingRes = sortingOrder.OrderBy(o => stcolNameOrder.GetValue(o)).ToList();
break;

}
return sortingRes;
}
//Skip and Take Method
private List<Order> SkipMethodOrder(List<Order> TakeResultOrder, int skip, int take)
{
var dataOrder = _context.Orders.ToList();
var result = (from s in TakeResultOrder.Skip(skip).Take(take) select s).ToList();
return result;
}

//Price
private List<Order> PriceMethodOrder(List<Order> ResultOfSortOrder, float price1, float price2)
{
List<Order> priceresult = null;
if (price1 != 0)
{
priceresult = ResultOfSortOrder.Where(o => rderPrice > price1).ToList();
}
else if (price2 != 0)
{
priceresult = ResultOfSortOrder.Where(o => rderPrice < price2).ToList();
}
return priceresult;
}

//Colour Method
private List<Order> ColourMethodOrder(List<Order> ResultOfSkipOrder, string clr)
{
List<Order> resClr = null;
resClr = ResultOfSkipOrder.Where(o => rderColour == clr).ToList();
return resClr;
}

Continue reading...