How to use multiple input parameters in ASP.NET Web API and getting data from database by stored procedure

Question

I am using multiple input parameters in my ASP.NET Web API application but I am not getting the output.

My code is here:

[HttpGet]
[Route("api/tblProducts/{Price1}/{Price2}/{CategoryId}/{Color}/{Size}")]
[ResponseType(typeof(IEnumerable<GetSimilarProducts_Result>))]
public IHttpActionResult GetSimlarProduct(Decimal Price1,Decimal Price2,string CategoryId, string Color, string Size)
{
    IEnumerable<GetSimilarProducts_Result> tblSmlrProduct = db.GetSimilarProducts(Price1, Price2,CategoryId, Color, Size ).AsEnumerable();

    if (tblSmlrProduct == null)
    {
        return NotFound();
    }

    return Ok(tblSmlrProduct);
}

and I using given URI for accessing it

   http://localhost:54393/api/tblProducts/GetsimlarProduct?Price1=1000&Price2=10000&CategoryId=Cat102&Color=Black&Size=M 

Please help me how I can get data from database and my other stored procedure working well My method code made by visual studio

public virtual ObjectResult<GetSimilarProducts_Result> GetSimilarProducts(Nullable<decimal> price1, Nullable<decimal> price2, string size, string categoryId, string color)
        {
            var price1Parameter = price1.HasValue ?
                new ObjectParameter("Price1", price1) :
                new ObjectParameter("Price1", typeof(decimal));

            var price2Parameter = price2.HasValue ?
                new ObjectParameter("Price2", price2) :
                new ObjectParameter("Price2", typeof(decimal));

            var sizeParameter = size != null ?
                new ObjectParameter("Size", size) :
                new ObjectParameter("Size", typeof(string));

            var categoryIdParameter = categoryId != null ?
                new ObjectParameter("CategoryId", categoryId) :
                new ObjectParameter("CategoryId", typeof(string));

            var colorParameter = color != null ?
                new ObjectParameter("Color", color) :
                new ObjectParameter("Color", typeof(string));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<GetSimilarProducts_Result>("GetSimilarProducts", price1Parameter, price2Parameter, sizeParameter, categoryIdParameter, colorParameter);
        }

and this is my stored procedure code

USE [MakaAnOrderDB]
GO

/****** Object:  StoredProcedure [dbo].[GetSimilarProducts]    Script Date: 12/14/2017 12:44:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[GetSimilarProducts]
(@Price1 decimal(18,0),@Price2 decimal(18,0),@Size nvarchar(max),@CategoryId nvarchar(255),@Color nvarchar(20))

As
Begin
Select top(20)* from tblProduct where PrdPrice Between @Price1 And @Price2 And PrdSize=@Size And PrdColor=@Color
End 

GO

Update : my stored procedure which is working well

USE [MakaAnOrderDB]
GO

/****** Object:  StoredProcedure [dbo].[SingleProductDetails]    Script Date: 12/14/2017 12:44:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[SingleProductDetails]
@ProductId nvarchar(255)=''
As
Begin
Select* from tblProduct where ProductId=@ProductId


End

GO

Update: this is c# code of fetching information from SingleProductDetails Procedure

[HttpGet]
        [Route("api/tblProducts/{productId}")]
        [ResponseType(typeof(IEnumerable<SingleProductDetails_Result>))]
        public IHttpActionResult Get(string productId)
        {
            IEnumerable<SingleProductDetails_Result> tblProduct = db.SingleProductDetails(productId).AsEnumerable();
            if (tblProduct == null)
            {
                return NotFound();
            }

            return Ok(tblProduct);
        }

update:Code made by visual studio for procedure sigleProductDetails

public virtual ObjectResult<SingleProductDetails_Result> SingleProductDetails(string productId)
        {
            var productIdParameter = productId != null ?
                new ObjectParameter("ProductId", productId) :
                new ObjectParameter("ProductId", typeof(string));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<SingleProductDetails_Result>("SingleProductDetails", productIdParameter);
        }

Answer 1

You should follow your Route-Template:

[Route("api/tblProducts/{Price1}/{Price2}/{CategoryId}/{Color}/{Size}")]

So instead of:

http://localhost:54393/api/tblProducts/GetsimlarProduct?Price1=1000&Price2=10000&CategoryId=Cat102&Color=Black&Size=M 

Use:

http://localhost:54393/api/tblProducts/1000/10000/Cat102/Black/M 

Update: If you want to get not found in case of an empty result you also should change your if-Statement: Replace:

if (tblSmlrProduct == null)

With:

if (tblSmlrProduct == null || tblSmlrProduct.Count()==0)

My method code made by visual studio

public virtual ObjectResult<GetSimilarProducts_Result> GetSimilarProducts(Nullable<decimal> price1, Nullable<decimal> price2, string size, string categoryId, string color)
        {
            var price1Parameter = price1.HasValue ?
                new ObjectParameter("Price1", price1) :
                new ObjectParameter("Price1", typeof(decimal));

            var price2Parameter = price2.HasValue ?
                new ObjectParameter("Price2", price2) :
                new ObjectParameter("Price2", typeof(decimal));

            var sizeParameter = size != null ?
                new ObjectParameter("Size", size) :
                new ObjectParameter("Size", typeof(string));

            var categoryIdParameter = categoryId != null ?
                new ObjectParameter("CategoryId", categoryId) :
                new ObjectParameter("CategoryId", typeof(string));

            var colorParameter = color != null ?
                new ObjectParameter("Color", color) :
                new ObjectParameter("Color", typeof(string));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<GetSimilarProducts_Result>("GetSimilarProducts", price1Parameter, price2Parameter, sizeParameter, categoryIdParameter, colorParameter);
        }

and this is my stored procedure code

USE [MakaAnOrderDB]
GO

/****** Object:  StoredProcedure [dbo].[GetSimilarProducts]    Script Date: 12/14/2017 12:44:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[GetSimilarProducts]
(@Price1 decimal(18,0),@Price2 decimal(18,0),@Size nvarchar(max),@CategoryId nvarchar(255),@Color nvarchar(20))

As
Begin
Select top(20)* from tblProduct where PrdPrice Between @Price1 And @Price2 And PrdSize=@Size And PrdColor=@Color
End 

GO

Update: As you edited my answer I will add the solution at the end: As stored procedure doesn't have a return value, you can see the result in the output-window when running in SSMS, but get nothing back into your application. Your stored procedure has to be changed to a stored function like this:

USE [MakaAnOrderDB]
GO

/****** Object:  StoredProcedure [dbo].[GetSimilarProducts]    Script Date: 12/14/2017 12:44:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE function [dbo].[GetSimilarProducts]
(@Price1 decimal(18,0),@Price2 decimal(18,0),@Size nvarchar(max),@CategoryId nvarchar(255),@Color nvarchar(20))

As
Begin
Return Select top(20)* from tblProduct where PrdPrice Between @Price1 And @Price2 And PrdSize=@Size And PrdColor=@Color
End 

GO

Answer 2

Another way, in case you want to get rid of that long query string:

1. Create a class from your parameters and pass it as a complex object.
2. Change HttpGet to HttpPost.