CODEWITHSUNDEEP
c#mysqlasp.net-mvc-4
Guilherme Oderdenge
Guilherme Oderdenge

Reputation: 5001

Idea to sort (My)SQL results with C#

The goal.

Sort the results provided by MySQL depending on parameter passed by controller.

The scenario.

There are a few controllers on my application and some of them bring to the user data from database.

When I access ~/Products/Compare?Id=1&OrderBy=lowestPriceToBiggest I want to show the comparison of product with id 1 sorting by lowestPriceToBiggest.

To summarize, I want to make my procedure's ORDER BY behave according the value of parameter OrderBy passed by URL.

Spotlight.

My ProductsController is:

public ActionResult Products(Nullable<int> id, string orderBy)
{
    List<Products> productsList = Build.ProductsList(id, orderBy);
    return View(productsList);
}

My query is:

Select product.Name As productName
From app_products As product
Where product.Id = 1
Order By product.Price Asc

As you can see, until now, there isn't communication between my C#'s code and SQL's code to sort the list of products. I'm stuck now. How can I covert something like biggestPriceToLowest provided by the parameter to SQL code? I mean, the orderBy parameter's returns me biggestPriceToLowest and I need to convert it to

[...]
Order By product.Price Desc

Can you see the Desc? How can I convert the string biggestPriceToLowest provided by the URL to Desc, to send as parameter to my procedure?

Only to emphasize: without any manipulation, the orderBy parameter of my controller is sending biggestPriceToLowest (string) or lowestPriceToBiggest (string) to Build.ProductsList(), but I need to convert these strings into Desc or Asc respectively.

What I've already thought about.

I thought to make a switch to convert each string into SQL code — but I do not know if it is a good practice or if what I'm doing it is the right/best way.

Eventually, what is the question?

Based on my scenario, what do I have to make? A switch? There exists some trick that I do not have knowledge?

Technical details.

I'm using C# + MVC 4 + Asp.Net + Razor Engine + MySQL.

Upvotes: 1

Views: 390

Answers (2)

Nicholas King
Nicholas King

Reputation: 938

In your Action I would do something like this and add the case statements as required

switch(orderBy) 
{ 
     case "priceASC": return productsList.OrderBy(x =>x.Price); 
     default: return productList 
}

Upvotes: 2

renefc3
renefc3

Reputation: 339

You could use ODATA to make the query: http://msdn.microsoft.com/pt-br/magazine/jj658971.aspx http://www.codeproject.com/Articles/393623/OData-Services

you will make a call to a URL rest and get the result

Upvotes: 0

Related Questions