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);
        }

Can you also add the Code of the db.SingleProductDetails-Method? I think there is the difference. — Nikolaus
– Nikolaus, Commented Dec 15, 2017 at 11:43
@Nikolus Sir Are you asking for C# code or code made by visual studio or database stored procedure code? — Akshay Tomar
– Akshay Tomar, Commented Dec 15, 2017 at 12:41

Nishith Shah · Accepted Answer · 2017-12-08 23:50:40Z

1

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

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

answered Dec 8, 2017 at 23:50

Nishith Shah

3931 gold badge3 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Akshay Tomar Over a year ago

But it is a get request and i m getting data from procedure then if i will make it post then it will work or not ?

Nishith Shah Over a year ago

You can change your get request to post request and still get the data. Post will allow you to post your parameters as a complex object rather than sending in a query string. This is just another way of doing it. It is helpful when your URL exceeds the URL characters limit.

Nikolaus · Accepted Answer · 2017-12-14 04:30:48Z

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

edited Dec 14, 2017 at 4:30

answered Dec 8, 2017 at 20:56

Nikolaus

1,8781 gold badge14 silver badges19 bronze badges

9 Comments

Akshay Tomar Over a year ago

i already use that but this is also not working and giving same output and output is [ ] .Please help me.

Nikolaus Over a year ago

Are you sure, that there is something in the database, that fits? It seems, thats your problem.

Nikolaus Over a year ago

@Akshay Tomar If updated my answer with another possible issue.

Akshay Tomar Over a year ago

@Nikolus Sir In database when i gives input same like in application then its working and give desirable output but In my web api application its not working properly and it will give a empty block [ ] like that and i used your updated syntax but it's not working . same output

Nikolaus Over a year ago

@AkshayTomar Can you please add the sql-statement and the code of the GetSimilarProducts-method? This could be important to find the source of your issue.

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related