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

  • Question


  • 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 => o.OrderPrice > price1).ToList();
                }
                else if (price2 != 0)
                {
                    priceresult = ResultOfSortOrder.Where(o => o.OrderPrice < price2).ToList();
                }
                return priceresult;
            }

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

    Monday, July 1, 2019 8:20 AM

All replies

  • Hello,

    There is a dedicated site for asking ASP.NET questions which has sub-forums for ASP.NET Core and data access.

    https://forums.asp.net/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 1, 2019 10:24 AM
  • ASP.NET WebAPI is using the ASP.NET MVC pattern without the views. Your controller is too fat  IMO. And a lot of this code you are showing should be in a class or classes in the Models folder or in a classlib project called a Service Layer with the class or classes using an Interface so that they can be DI into the controller, like the example.

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs

    <copied>

    An MVC model contains all of your application logic that is not contained in a view or a controller. The model should contain all of your application business logic, validation logic, and database access logic. For example, if you are using the Microsoft Entity Framework to access your database, then you would create your Entity Framework classes (your .edmx file) in the Models folder.

    A view should contain only logic related to generating the user interface. A controller should only contain the bare minimum of logic required to return the right view or redirect the user to another action (flow control). Everything else should be contained in the model.

    In general, you should strive for fat models and skinny controllers. Your controller methods should contain only a few lines of code. If a controller action gets too fat, then you should consider moving the logic out to a new class in the Models folder.

    <end>

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/models-data/validating-with-a-service-layer-cs

    ASP.NET Core WebAPI using objects in a Core classlib project the Data Access Layer. Your Action method has more code than the entire controller I am showing.

    ASP.NET issues can be discussed at the ASP.NET forums.

    http://forums.asp.net/

    using System.Collections.Generic;
    using DAL;
    using Entities;
    using Microsoft.AspNetCore.Mvc;
    
    namespace ProgMgmntCore2Api.Controllers
    {
        [Produces("application/json")]
        [Route("api/[controller]")]
        [ApiController]
    
        public class ProjectController : ControllerBase, IProjectController
        {
            private readonly IDaoProject _daoProject;
    
            public ProjectController(IDaoProject daoProject)
            {
                _daoProject = daoProject;
            }
    
            [HttpGet]
            [Route("GetProjById")]
            public DtoProject GetProjectById(int id)
            {
                return  _daoProject.GetProjectById(id);
            }
            
            [HttpGet]
            [Route("GetProjsByUserId")]
            public List<DtoProject> GetProjectsByUserId(string userid)
            {
                return _daoProject.GetProjectsByUserId(userid);
            }
    
            [HttpPost]
            [Route("CreateProject")]
            public void Post_CreateProject(DtoProject dto)
            {
                _daoProject.CreateProject(dto);
            }
    
            [HttpPost]
            [Route("DeleteProject")]
            public void Post_DeleteProject(DtoId dto)
            {
                _daoProject.DeleteProject(dto.Id);
            }
    
            [HttpPost]
            [Route("UpdateProject")]
            public void Post_UpdateProject(DtoProject dto)
            {
                _daoProject.UpdateProject(dto);
            }
        }
    }
    

    Monday, July 1, 2019 10:58 AM