The application sorts strings differently than the database

Question

I am trying to sort a list of products by their name in a .Net application written in C#, to obtain the same list that I would get from an SQL Server database through an order by: select * from Products order by ProductName

Unfortunately, the application sorting behaves differently than the database sorting. It is probably related to the collation: the database has an SQL_Latin1_General_CP1_CI_AS collation.

How can I make the application sort these strings exactly like the database does?

Thanks.

---

UPDATE: I finally obtained a good result by using the code from the comments below, and changing the compare options to Ordinal:

private CompareOptions myOptions = CompareOptions.Ordinal ;

Also, this link contains some very useful information related to SQL collations: http://blogs.msdn.com/michkap/archive/2005/11/08/490305.aspx

Answer 1

you'll probably need to use the culture you wish to sort in. Look at the example of the StringComparer: http://msdn.microsoft.com/en-us/library/system.stringcomparer.currentculture.aspx

Answer 2

Thanks. It does not work yet, but this is probably the right direction. Here is the code I am trying now:

((List)orderDetails).Sort(new OrderDetailComparer());

where OrderDetailComparer is:

public class OrderDetailComparer : IComparer<OrderDetail>
{
  private CompareInfo myComp = CompareInfo.GetCompareInfo("en-US");
  private CompareOptions myOptions = CompareOptions.StringSort;

  public int Compare(OrderDetail a, OrderDetail b)
  {
    if (a == b) return 0;
    if (a == null) return -1;
    if (b == null) return 1;

    return myComp.Compare ( a.Product.ProductNameForSorting, b.Product.ProductNameForSorting, myOptions );
  }
}

Still no result.

What do you think? How do I get the information from the database, to know what culture I should use?

Thanks.