From: Frans Bouma <[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 18:03:44 +0200
> > > 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) > > > > If you use a case insensitive compare, do you get the same > >errors? (your code will also give 1 error at first, unless the read > >name is "", you have to read 2 names up front.) > > > > First, I know that none of the column valuess are equal to an > empty string. > If I compare what I got from the database to the empty > string, I know that none of the rows will compare equal (and > If they do, it is not an error).
ok, but you want the right return value :) THe first time you compare the read name with the empty string, which might (or might not, I don't know what's defined for that, have to look that up) give the wrong return value.
------------------------------------- All that the test logic was trying to achieve is to see if the collation returned values that conformed with the invarient culture, case insensitive compare of .NET. I assumed that an empty string would sort lower than any other string. All that I was doing was to compare successive values in the stream of values (as sorted by SQL Server according to a specified collation) to see if they were "properly ordered" with respect to the invariant culture. As I expected, most of the collations did not produce such an ordered stream. What I wanted to find out if there were any that did. I found a couple that worked (at least for the data that I was using). ------------------------------------
> Second, I believe that the compare that I used is case > insensitive. The True parameter indicates that I should ignore case.
Ok, I didn't check the Compare() method signature in detail if there was a case insensitive parameter.
> > Also, the collations you specify do relate to cultures. So > >always comparing them to the invariant culture is nonsense. Some > >collations define codepages with characters not available in the > >invariant culture (like islandic) See hte documentation of > >CultureInfo.InvariantCulture. > > I understand this. What I was trying to do is find out which > collations gave me data that .NET invariant culture thought > was sorted.
You can't say that based on whatever testing you do. Other data than your testdata might give different results. The only collation you can use is English, default page, case insensitive, which is the default when installing SqlServer. All other collations do not necessarily match with the page used in the invariant culture's regional settings data.
Again, I understand that. I had tried all of the other things that I could think of and came up empty. This at least gets me to a place where I can get some work done. I would like to better understand what is going on, because I know full well that this could come back to bite me later.
The problem that I have is that the default SQL Server collation, SQL_Latin1_General_CP1_CI_AS, fails the test that I ran. It does not produce a stream of values that the invariant culture compare thinks is sorted. I am assuing that this is the collation that is English, default page, case insensitive. If it is not, could you give me the collation name that corresponds to this set of characteristics?
BTW, the collation that I identified through this testing as (at least initially) workable is Latin1_General_CI_AS. I would assume that this is culturally neutral. Again, if this is not the one to use to get the English, default code page, case insensitive, please point me in the right direction.
> In actual fact, I copied the list of supported > collations from the Books On Line, and ran each of them > through this process. I did read the documenation in BOL and > in MSDN and made web searches and asked some of my co-workers > and so on. > The test program was a desparate shotgun approach. > Sometimes, like this time, the shotgun approach produces an answer.
You could also have read the invariant culture help, which would have told you that you can't reliably use that one for a DEFINED culture, which is what you're doing, IMHO.
I did read that as well as many other sections of the help. I did not see anything that said exactly which SQL collation what the one to use with a invariant culture. It did say that I should consider which collation to use, but I did not see any details. But then, I have about 90 hours in the hopper for last week. Frans, I am sure that I am not meeting any one's expectations right now. I am just trying to get the work done as best as I can.
> > > Select Case myCompare > > > Case Is < 0, Is = 0 > > > ' this is goodness > > > > this can be caused by case sensitivity, or by usage of a > >different codepage than the 'invariant culture'. If you're using the > >invariant culture ONLY in your .net code, you NEED to use > the default > >collation in sqlserver as well. > > In my database the default collation is > SQL_Latin1_General_CP1_CI_AS. I assume that this is what you > meant. The problem is that this collation sorts data > slightly differently from what .NET thinks is sorted.
no, what _you_ think is sorted according to the invariant culture :). This doesn't have to match, as the codepage used by a given collation does or doesn't have to match with the invariant culture's code page, these two are completely not related.
> At this point, all that I care about is matching up the data > from an external source to my internal database. The > approach that I took was to sort both sets of data and walk > along both streams, making comparisons.
You can only do that if you use on both sides: - the same collation (sqlserver) - the same cultural settings. (.NET)
Collation of the remote server was requestable through a T-SQL statement, the .net culture is known to you, so that would have been more easier I think.
> I must say that I was surprised that there was so little > material available about the nuts and bolts of collations and > sort orders. Perhaps, I should say (so as not to be mistaken > for someone who is arrogant), I could not find such data. > That is way I created the test program and performed tests > that a more rational (or less tired) person might have > skipped knowing that they were "nonsence".
Well, your test wasn't correct either.
Collations are things often hidden for most SqlServer users, as the default is sufficient for most of the users. However IF there is a collation used differently from the default, the code accessing the database has to work with a similar culture as well, to make the codepages match, which is a natural way of doing things.
Frans.
-------------------------------------------------------------------- Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com My .NET Blog : http://weblogs.asp.net/FBouma Microsoft MVP (C#) --------------------------------------------------------------------
=================================== 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
