什么.NET StringComparer相当于SQL的Latin1_General_CI_AS [英] What .NET StringComparer is equivalent SQL's Latin1_General_CI_AS

问题描述

我实现我的数据库和我的C＃code之间的缓存层。这样做是为了高速缓存的基于参数到查询某些数据库查询的结果。该数据库使用的是默认排序规则 - 无论是 SQL_Latin1_General_CP1_CI_AS 或 Latin1_General_CI_AS ，我认为基于一些简单的谷歌搜索是等价的平等，只是排序不同。

I am implementing a caching layer between my database and my C# code. The idea is to cache the results of certain DB queries based on the parameters to the query. The database is using the default collation - either SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS, which I believe based on some brief googling are equivalent for equality, just different for sorting.

我需要一个.NET StringComparer，可以给我相同的行为，至少在平等的测试和哈希code生成，数据库的归类使用。我们的目标是能够使用的StringComparer在.NET词典在C＃code，以确定是否一个特定的字符串键已经在高速缓存或不

I need a .NET StringComparer that can give me the same behavior, at least for equality testing and hashcode generation, as the database's collation is using. The goal is to be able to use the StringComparer in a .NET dictionary in C# code to determine whether a particular string key is already in the cache or not.

一个真正简单的例子：

var comparer = StringComparer.??? // What goes here?

private static Dictionary<string, MyObject> cache =
    new Dictionary<string, MyObject>(comparer);

public static MyObject GetObject(string key) {
    if (cache.ContainsKey(key)) {
        return cache[key].Clone();
    } else {
        // invoke SQL "select * from mytable where mykey = @mykey"
        // with parameter @mykey set to key
        MyObject result = // object constructed from the sql result
        cache[key] = result;
        return result.Clone();
    }
}
public static void SaveObject(string key, MyObject obj) {
    // invoke SQL "update mytable set ... where mykey = @mykey" etc
    cache[key] = obj.Clone();
}

它之所以重要的是，StringComparer数据库的归类相匹配的是，假阳性和假阴性会有不好的影响为code。

The reason it's important that the StringComparer matches the database's collation is that both false positives and false negatives would have bad effects for the code.

如果该StringComparer说两个键A和B相等时的数据库认为它们是不同的，则有可能是在与这两个密钥数据库两行，但缓存将prevent第二人曾经获得返回如果要求A和B在继承 - 因为获得B就错误地击中缓存，并返回被检索A中的对象

If the StringComparer says that two keys A and B are equal when the database believes they are distinct, then there could be two rows in the database with those two keys, but the cache will prevent the second one ever getting returned if asked for A and B in succession - because the get for B will incorrectly hit the cache and return the object that was retrieved for A.

现在的问题是比较微妙，如果StringComparer说A和B是不同的数据库时认为他们是平等的，但同样存在问题。 GetObject的呼吁这两个键就可以了，并返回相应的同一个数据库行对象。但随后调用SaveObject与主要的A将离开高速缓存不正确;仍然会有对具有旧数据密钥B缓存项。随后GetObject的（B）将给予过时的信息。

The problem is more subtle if the StringComparer says that A and B are different when the database believes they are equal, but no less problematic. GetObject calls for both keys would be fine, and return objects corresponding to the same database row. But then calling SaveObject with key A would leave the cache incorrect; there would still be a cache entry for key B that has the old data. A subsequent GetObject(B) would give outdated information.

因此​​，对于我的code正常工作，我需要的StringComparer匹配相等测试和哈希code代数据库的行为。我使用Google到目前为止已经产生了大量的有关事实，SQL排序和.NET的比较是不完全等同的信息，但是没有详细说明上的区别是什么，他们是否被限制为仅在差异排序，或者是否有可能找到一个StringComparer，等效于一个的特定的SQL排序如果不需要一个通用的解决方案。

So for my code to work correctly I need the StringComparer to match the database behavior for equality testing and hashcode generation. My googling so far has yielded lots of information about the fact that SQL collations and .NET comparisons are not exactly equivalent, but no details on what the differences are, whether they are limited to only differences in sorting, or whether it is possible to find a StringComparer that is equivalent to a specific SQL collation if a general-purpose solution is not needed.

（附注 - 缓存层是通用的，所以我不能让特定的假设是什么关键的性质是什么，整理将是适当的所有的表在我的数据库共享相同的默认服务器排序规则我。需要匹配的排序规则，因为它的存在）

(Side note - the caching layer is general purpose, so I cannot make particular assumptions about what the nature of the key is and what collation would be appropriate. All the tables in my database share the same default server collation. I just need to match the collation as it exists)

推荐答案

看看的<一个href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.metadata.collationinfo.aspx"相对=nofollow> Col​​lat​​ionInfo 类。它坐落在一个名为 Microsoft.SqlServer.Management.SqlParser.dll 组装虽然我不能完全确定从哪里得到这一点。目前的静态列表<一href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.metadata.collationinfo.collations.aspx"相对=nofollow> 排序规则 （名称）和一个静态方法的 GetCollat​​ionInfo （按名称）。

Take a look at the CollationInfo class. It is located in an assembly called Microsoft.SqlServer.Management.SqlParser.dll although I am not totally sure where to get this. There is a static list of Collations (names) and a static method GetCollationInfo (by name).

每个 Col​​lat​​ionInfo 有一个<一个href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.metadata.collationinfo.comparer.aspx"相对=nofollow> 的Comparer 。这是不完全一样的 StringComparer ，但也有类似的功能。

Each CollationInfo has a Comparer. It is not exactly the same as a StringComparer but has similar functionality.

编辑： Microsoft.SqlServer.Management.SqlParser.dll是共同管理的一部分对象（SMO）封装。此功能可以下载SQL Server 2008 R2的位置：

Microsoft.SqlServer.Management.SqlParser.dll is a part of the Shared Management Objects (SMO) package. This feature can be downloaded for SQL Server 2008 R2 here:

http://www.microsoft.com/download/ EN / details.aspx？ID = 16978＃SMO

编辑： Col​​lat​​ionInfo 确实有一个命名属性<一个href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.metadata.collationinfo.equalitycomparer.aspx"相对=nofollow> EqualityComparer 这是一个的IEqualityComparer＆LT;字符串＆GT;

CollationInfo does have a property named EqualityComparer which is an IEqualityComparer<string>.

这篇关于什么.NET StringComparer相当于SQL的Latin1_General_CI_AS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！