by Michael S. Kaplan, published on 2005/10/09 03:31 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/10/09/478705.aspx
Prior posts in this series:
Extending collation support in SQL Server and Jet, Part 0 (HISTORY)
Extending collation support in SQL Server and Jet, Part 1 (the broad strokes)
Extending collation support in SQL Server and Jet, Part 2 (generating sort keys)
Extending collation support in SQL Server and Jet, Part 2.1 (is this on?)
I thought I would finish that class first, and then be able to move on to the next steps....
Now, if you remember in string.Compare is for sissies (not for people who want SQLCLR consistency) there are all of the flag values that change between SQL Server/Windows and the CompareOptions enumeration. We will use that information for a private method in our class and a special constructor to take the SQL Server flags values.
Here goes:
sealed public class CustomSqlCollation {
//
// Private members
//
private CultureInfo m_cultureInfo;
private CompareInfo m_compareInfo;
private CompareOptions m_options;
private int m_flags;
private string m_name;
private int m_lcid;
private const int NORM_IGNORECASE = 0x00000001;
private const int NORM_IGNOREKANATYPE = 0x00010000;
private const int NORM_IGNORENONSPACE = 0x00000002;
private const int NORM_IGNORESYMBOLS = 0x00000004;
private const int NORM_IGNOREWIDTH = 0x00020000;
private const int SORT_STRINGSORT = 0x00001000;
private static CompareOptions CompareOptionsFromFlags(int flags)
{
CompareOptions options = CompareOptions.None;
if ((flags & NORM_IGNORECASE) != 0) { options |= CompareOptions.IgnoreCase; }
if ((flags & NORM_IGNOREKANATYPE) != 0) { options |= CompareOptions.IgnoreKanaType; }
if ((flags & NORM_IGNORENONSPACE) != 0) { options |= CompareOptions.IgnoreNonSpace; }
if ((flags & NORM_IGNORESYMBOLS) != 0) { options |= CompareOptions.IgnoreSymbols; }
if ((flags & NORM_IGNOREWIDTH) != 0) { options |= CompareOptions.IgnoreWidth; }
if ((flags & SORT_STRINGSORT) != 0) { options |= CompareOptions.StringSort; }
return options;
}
private static CompareOptions FlagsFromCompareOptions(CompareOptions options)
{
int flags = 0;
if ((options & CompareOptions.IgnoreCase) != 0) { flags |= NORM_IGNORECASE; }
if ((options & CompareOptions.IgnoreKanaType) != 0) { flags |= NORM_IGNOREKANATYPE; }
if ((options & CompareOptions.IgnoreNonSpace) != 0) { flags |= NORM_IGNORENONSPACE; }
if ((options & CompareOptions.IgnoreSymbols) != 0) { flags |= NORM_IGNORESYMBOLS; }
if ((options & CompareOptions.IgnoreWidth) != 0) { flags |= NORM_IGNOREWIDTH; }
if ((options & CompareOptions.StringSort) != 0) { flags |= SORT_STRINGSORT; }
return flags;
}
//
// Constructors
//
public CustomSqlCollation(string name, CompareOptions options) {
this.m_options = options;
this.m_flags = FlagsFromCompareOptions(options);
this.m_cultureInfo = CultureInfo.GetCultureInfo(name, false);
this.m_compareInfo = this.m_cultureInfo.CompareInfo;
this.m_name = name;
this.m_lcid = this.m_compareInfo.LCID;
}
public CustomSqlCollation(int lcid, CompareOptions options) {
this.m_options = options;
this.m_flags = FlagsFromCompareOptions(options);
this.m_cultureInfo = CultureInfo.GetCultureInfo(lcid, false);
this.m_compareInfo = this.m_cultureInfo.CompareInfo;
this.m_name = this.m_compareInfo.Name;
this.m_lcid = lcid;
}
public CustomSqlCollation(string name, int flags) {
this.m_flags = flags;
this.m_options = CompareOptionsFromFlags(flags);
this.m_cultureInfo = CultureInfo.GetCultureInfo(name, false);
this.m_compareInfo = this.m_cultureInfo.CompareInfo;
this.m_name = name;
this.m_lcid = this.m_compareInfo.LCID;
}
public CustomSqlCollation(int lcid, int flags) {
this.m_flags = flags;
this.m_options = CompareOptionsFromFlags(flags);
this.m_cultureInfo = CultureInfo.GetCultureInfo(lcid, false);
this.m_compareInfo = this.m_cultureInfo.CompareInfo;
this.m_name = this.m_compareInfo.Name;
this.m_lcid = lcid;
}
public int LCID {
get {
return this.m_lcid;
}
}
public string Name {
get {
return this.m_name;
}
}
// Method to return an index value
public byte[] GetSortKey(string input) {
return this.m_compreInfo.GetSortKey(input, this.m_options);
//
// Compare overrides
//
public int Compare(string string1, string string2) {
return this.m_compareInfo.Compare(string1, string2, this.m_options);
}
public int Compare(string string1, int offset1, string string2, int offset2) {
return this.m_compareInfo.Compare(string1, offset1, string2, offset2, this.m_options);
}
public int Compare(string string1, int offset1, int length1, string string2, int offset2, int length2) {
return this.m_compareInfo.Compare(string1, offset1, length1, string2, offset2, length2, this.m_options);
}
public int IndexOf(string source, string value) {
return this.m_compareInfo.IndexOf(source, value, this.m_options);
}
public int IndexOf(string source, char value, int startIndex) {
return this.m_compareInfo.IndexOf(source, value, startIndex, this.m_options);
}
public int IndexOf(string source, string value, int startIndex) {
return this.m_compareInfo.IndexOf(source, value, startIndex, this.m_options);
}
public int IndexOf(string source, char value, int startIndex, int count) {
return this.m_compareInfo.IndexOf(source, value, startIndex, count, this.m_options);
}
public int IndexOf(string source, string value, int startIndex, int count) {
return this.m_compareInfo.IndexOf(source, value, startIndex, count, this.m_options);
}
//
// LastIndexOf overrides
//
public int LastIndexOf(string source, char value) {
return this.m_compareInfo.LastIndexOf(source, value, this.m_options);
}
public int LastIndexOf(string source, string value) {
return this.m_compareInfo.LastIndexOf(source, value, this.m_options);
}
public int LastIndexOf(string source, char value, int startIndex) {
return this.m_compareInfo.LastIndexOf(source, value, startIndex, this.m_options);
}
public int LastIndexOf(string source, string value, int startIndex) {
return this.m_compareInfo.LastIndexOf(source, value, startIndex, this.m_options);
}
public int LastIndexOf(string source, char value, int startIndex, int count) {
return this.m_compareInfo.LastIndexOf(source, value, startIndex, count, this.m_options);
}
public int LastIndexOf(string source, string value, int startIndex, int count) {
return this.m_compareInfo.LastIndexOf(source, value, startIndex, count, this.m_options);
}
//
// IsPrefix method
//
public int IsPrefix(string source, string prefix) {
return this.m_compareInfo.IsPrefix(source, prefix, this.m_options);
}
//
// IsSuffix method
//
public int IsSuffix(string source, string suffix) {
return this.m_compareInfo.IsSuffix(source, suffix, this.m_options);
}
}
Some random notes on the class:
For now, I have left off the overrides to take other CompareOptions choices within the various methods. This is something that can be added later without too much trouble if people need them.
I am also creating a class here that could mostly be an override of CompareInfo, but I am resisting doing that for the moment, since that adds other requirements that may or may not be worth taking on.
The constructors that take flags values will handle string sorts, which SQL Server does not support but Windows does. It is just a few extra lines of code so it is no big deal if it is included or not.
Finally, of course when writing the private CompareOptionsFromFlags and FlagsFromCompareOptions methods, I could have done something clever with the numbers that are different rather than just building up the new numbers fully, but this just seems more self-documenting for an operation that probably does not happen very often.
I'll write more in the series tomorrow or the day after....
This post brought to you by "Ǫ" (U+01ea, a.k.a. LATIN CAPITAL LETTER O WITH OGONEK)
# silverpie on 10 Oct 2005 9:27 AM:
# Michael S. Kaplan on 11 Oct 2005 6:25 PM:
# Michael S. Kaplan on 12 Oct 2005 6:31 AM:
referenced by