Idea to sort (My)SQL results with C#

Question

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.

Yes, @NicholasKing — I can! Seriously, I do not thought this before. By the way, how can I change biggestPriceToLowest into List's sorting? — Guilherme Oderdenge
– Guilherme Oderdenge, Commented Jul 26, 2013 at 13:36

Nicholas King · Accepted Answer · 2013-07-26 13:37:57Z

2

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 
}

answered Jul 26, 2013 at 13:37

Nicholas King

9387 silver badges22 bronze badges

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

6 Comments

Nicholas King Over a year ago

I don't see why it would not be considered good practice dependant on scenario your using it in.

Guilherme Oderdenge Over a year ago

@Habib Performance question — that's why I like to post here, on Stack. Thanks about the information. Do you want to convert this comment into an answer?

Nicholas King Over a year ago

@Habib I agree, but like I said as with anything it depends on scenario, I don't see anything inherently wrong with this approach though.

Nicholas King Over a year ago

@GuilhermeOderdenge Performance questions are so overrated as Jeff Atwood has said hardware is cheap programmers are not codinghorror.com/blog/2008/12/…. Dependant on the size of productlist sorting it in the application vs db will be negligible

Guilherme Oderdenge Over a year ago

Got it, Nicholas. I'm using your solution. Thanks!

|

renefc3 · Accepted Answer · 2013-07-26 13:37:02Z

0

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

answered Jul 26, 2013 at 13:37

renefc3

3394 silver badges17 bronze badges

Collectives™ on Stack Overflow

Idea to sort (My)SQL results with C#

The goal.

The scenario.

Spotlight.

What I've already thought about.

Eventually, what is the question?

Technical details.

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The goal.

The scenario.

Spotlight.

What I've already thought about.

Eventually, what is the question?

Technical details.

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related