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 FunctionEnd 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� 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
