Now, this is a totally different topic, obviously. Ok, so you basically found out that that there are issues with the sort order. This is actually a problem I stubmeld into some time ago - the SQL sort orders are not per definition "correct" when you compare them to the .net locales. I think this is more that .NET does not sort correctly / langauge dependant, but I have not fully verified this.
Now, from what you posted originally I take you do not actually care about the order being correct for any locale (like when you present it to the user and the user would call you an idiot because your program presents a list in the wrong sort order). If this is the case, then the topic is basically finding ONE sort order that works in common between SQL Server and .NET. As long, as it seems, as they both match, the answers are correct for you (again, comparing this to a presentatation to the end user, where this may be still wrong). As you said, Latin1_General_CI_AS seems to be about the best you can get your hands on. Be happy you have not to present anything to the user at the same time. Thomas Tomiczek THONA Software & Consulting Ltd. (Microsoft MVP C#/.NET) (CTO PowerNodes Ltd.) > -----Original Message----- > From: Unmoderated discussion of advanced .NET topics. > [mailto:[EMAIL PROTECTED] On Behalf Of Jon > Stonecash > Sent: Montag, 26. Juli 2004 15:55 > To: [EMAIL PROTECTED] > Subject: Re: [ADVANCED-DOTNET] Common Sort Order for .NET and > SQL Server > > My apologies to you Thomas. it has been a long weekend and I > could have been more complete in the question. I am sorting > the data (ORDER BY) in SQL, but .NET does not see the data > coming in from the stream as ordered. > It is mostly sorted but there are some items "out of order". > I decided to create a test program to see which collations > worked and which did not: > > Option Strict On > Option Explicit On > > Imports System > Imports System.data.sqlclient > Imports System.Globalization > > Module Module1 > > Private _Connection As SqlConnection > > Sub Main() > > Try > _Connection = New SqlConnection _ > > ("server=(local);uid=sa;pwd=pass4jcs;database=ISI;Max Pool > Size=200") > _Connection.Open() > TryCollation("SQL_Latin1_General_Cp1_CS_AS") > TryCollation("SQL_Latin1_General_Cp1_CI_AS") > TryCollation("Latin1_General_CS_AS") > TryCollation("Latin1_General_CI_AS") > TryCollation("SQL_Latin1_General_Cp1250_CS_AS") > TryCollation("SQL_Latin1_General_Cp1250_CI_AS") > TryCollation("SQL_Latin1_General_Cp1251_CS_AS") > TryCollation("SQL_Latin1_General_Cp1251_CI_AS") > TryCollation("SQL_Latin1_General_Cp1253_CS_AS") > TryCollation("SQL_Latin1_General_Cp1253_CI_AS") > TryCollation("SQL_MixDiction_Cp1253_CS_AS") > TryCollation("SQL_AltDiction_Cp1253_CS_AS") > TryCollation("SQL_Latin1_General_Cp1253_CI_AI") > TryCollation("SQL_Latin1_General_Cp1254_CS_AS") > TryCollation("SQL_Latin1_General_Cp1254_CI_AS") > TryCollation("SQL_Latin1_General_Cp1255_CS_AS") > TryCollation("SQL_Latin1_General_Cp1255_CI_AS") > TryCollation("SQL_Latin1_General_Cp1256_CS_AS") > TryCollation("SQL_Latin1_General_Cp1256_CI_AS") > TryCollation("SQL_Latin1_General_Cp1257_CS_AS") > TryCollation("SQL_Latin1_General_Cp1257_CI_AS") > Catch myException As Exception > Console.WriteLine(myException.ToString) > Finally > _Connection.Close() > End Try > Console.Read() > > End Sub > Private Function TryCollation(ByVal theCollation As > String) As Int32 > > Dim myCompare As Int32 > Dim myErrorCount As Int32 = 0 > Dim myDataReader As SqlDataReader > > Try > Dim mySQL As String = "SELECT > CONVERT(varchar(255), CT.[Name]) " > _ > & " COLLATE {0} AS AddRemoveName" _ > & " FROM [Application List] AL" _ > & " INNER JOIN [Count of Installed > Applications] CT ON CT.AppKeyID = AL.AppKeyID" _ > & " WHERE(CT.[Name] Is Not NULL)" _ > & " ORDER BY AddRemoveName" > Dim myCommand As New > SqlCommand(String.Format(mySQL, theCollation), _Connection) > myDataReader = myCommand.ExecuteReader() > Dim myLastName As String = "" > Dim myCurrentName As String = "" > While myDataReader.Read > myCurrentName = myDataReader.GetString(0).Trim > myCompare = String.Compare(myLastName, > myCurrentName, True, > CultureInfo.InvariantCulture) > Select Case myCompare > Case Is < 0, Is = 0 > ' this is goodness > Case Else > myErrorCount += 1 > 'Console.WriteLine(String.Format("{0} > should be lower than {1}", _ > ' myLastName, myCurrentName)) > End Select > myLastName = myCurrentName > End While > myDataReader.Close() > Console.WriteLine("Collation = " & theCollation & > " Error count = " & myErrorCount.ToString) > Catch myException As Exception > Console.WriteLine(myException.ToString) > Finally > myDataReader.Close() > End Try > End Function > > End Module > > This gives the following results: > > Collation = SQL_Latin1_General_Cp1_CS_AS Error count = 4 > Collation = SQL_Latin1_General_Cp1_CI_AS Error count = 4 > Collation = Latin1_General_CS_AS Error count = 0 Collation = > Latin1_General_CI_AS Error count = 0 Collation = > SQL_Latin1_General_Cp1250_CS_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1250_CI_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1251_CS_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1251_CI_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1253_CS_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1253_CI_AS Error count = 13 Collation = > SQL_MixDiction_Cp1253_CS_AS Error count = 175 Collation = > SQL_AltDiction_Cp1253_CS_AS Error count = 4 Collation = > SQL_Latin1_General_Cp1253_CI_AI Error count = 4 Collation = > SQL_Latin1_General_Cp1254_CS_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1254_CI_AS Error count = 15 Collation = > SQL_Latin1_General_Cp1255_CS_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1255_CI_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1256_CS_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1256_CI_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1257_CS_AS Error count = 13 Collation = > SQL_Latin1_General_Cp1257_CI_AS Error count = 13 > > What this suggests is that I should sort using collation > "Latin1_General_CI_AS" to get .NET to agree with the sort > done by SQL Server. > > > >From: Thomas Tomiczek <[EMAIL PROTECTED]> > >Reply-To: "Unmoderated discussion of advanced .NET topics." > ><[EMAIL PROTECTED]> > >To: [EMAIL PROTECTED] > >Subject: Re: [ADVANCED-DOTNET] Common Sort Order for .NET and SQL > >Server > >Date: Mon, 26 Jul 2004 15:30:20 +0200 > > > >Ok, let's go. > > > > > -----Original Message----- > > > From: Unmoderated discussion of advanced .NET topics. > > > [mailto:[EMAIL PROTECTED] On Behalf Of Jon > > > Stonecash > > > Sent: Montag, 26. Juli 2004 14:13 > > > To: [EMAIL PROTECTED] > > > Subject: [ADVANCED-DOTNET] Common Sort Order for .NET and > SQL Server > > > > > > I am having a problem getting SQL Server and .NET to agree on the > > > sort order for some data. > > > >Really? > > > > > My sort column in SQL Server is character data ([varchar] > > > (255) COLLATE SQL_Latin1_General_CP1_CI_AS). As I > understand this, > > > the data sorts in dictionary order. Thus, a value of > "F-SA" sorts > > > between "FR" and "FT". > > > >Tip: you seem to haves kipped reading the beginner section > of your SQL > >documentation. Your understanding is totally unsubstantiated. > > > > > The problem is that .NET seems to think that the data is not > > > in order. I > > > >And guess what, .NET is right with this. > > > >Beginner SQL: unless you explicitly sort the data, it is unsorted in > >the returned data set. > > > > > have tried all of the CultureInfo settings that I can think of to > > > convince .NET to see this as ordered. I have a need to > > > >Why not fix your bugs regarding any basick nowledge how SQL works, > >instead of trying to get .NET agreeing? > > > > > process these rows and compare them to another sorted > stream of data > > > and the comparison is going nuts when it gets to these "special > > > cases". > > > >Then why do you not sort the data? > > > > > Is there a way to get SQL Server and .NET on the same (code) page > > > without sorting the data internally after reading it from SQL > > > Server? > > > >No. SQL 101. Unless the SQL statement specifies a sort order, sort > >order is random. > > > >That said - why do you not just specify a sort order in your query? > >This is pretty simple, and you do not have to resort on the client > >then. It just is not automatic. > > > >Note: SQL is SET ORIENTED. Sets do not have an intrinsic sort order. > > > >Thomas Tomiczek > >THONA Software & Consulting Ltd. > >(Microsoft MVP C#/.NET) > >(CTO PowerNodes Ltd.) > > > > > > > > Jon Stonecash > > > > > > =================================== > > > This list is hosted by DevelopMentor(r) > http://www.develop.com Some > > > .NET courses you may be interested in: > > > > > > NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles > > > http://www.develop.com/courses/gaspdotnetls > > > > > > View archives and manage your subscription(s) at > > > http://discuss.develop.com > > > > > > >=================================== > >This list is hosted by DevelopMentor(r) > http://www.develop.com Some .NET > >courses you may be interested in: > > > >NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles > >http://www.develop.com/courses/gaspdotnetls > > > >View archives and manage your subscription(s) at > >http://discuss.develop.com > > =================================== > This list is hosted by DevelopMentor(r) http://www.develop.com > Some .NET courses you may be interested in: > > NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles > http://www.develop.com/courses/gaspdotnetls > > View archives and manage your subscription(s) at > http://discuss.develop.com > =================================== This list is hosted by DevelopMentorŪ http://www.develop.com Some .NET courses you may be interested in: NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles http://www.develop.com/courses/gaspdotnetls View archives and manage your subscription(s) at http://discuss.develop.com
