如何将自定义排序规则应用于SQLite数据库的特定列？ [英] How to apply a custom collation to specific columns of an SQLite database?

问题描述

在使用iBATIS.NET将数据映射到SQLite数据库的C＃应用​​程序中，我希望能够按与英文字母稍有不同的字母（特定的字符和某些字符的顺序）对特定的列进行排序是不同的）。我想从数据库中获取已经排序的结果，因为由于分页，无法对应用程序本身进行排序。

In a C# application that uses iBATIS.NET to map data to an SQLite database I want to be able to sort specific columns by an alphabet that slightly differs from the English alphabet (there are some special characters and the order of some characters is different). I want to get from the database the results that are already sorted, because sorting in the application itself is impossible due to pagination.

经过一些研究，我得出结论，应用自定义排序规则可能是这里唯一的选择。这个结论正确吗？

After some research I concluded that applying a custom collation may be the only option here. Is this conclusion correct?

但是，我无法确定在上述情况下如何做到这一点。 iBATIS API似乎与归类无关，我看不出有一种明显的方法可以对数据库配置进行修补。那么，怎么办呢？

However, I couldn't find out how this could be done in the described situation. iBATIS API seemingly has nothing to do with collations and I don't see an obvious way to achieve this tinkering with the database configuration. So, how could this be done?

推荐答案

用户定义的函数和排序规则是ADO.NET驱动程序的功能，而不是位于其之上的ORM。 SQlite ADO.Net驱动程序确实允许您定义自定义函数，请检查此问题的第二个答案：

User-defined functions and collations are a feature of the ADO.NET driver, not the ORM that sits on top of it. The SQlite ADO.Net driver does allow you to define custom functions, check the second answer to this question :

/// <summary>
/// User-defined collating sequence using the current UI culture.
/// </summary>
[SQLiteFunction(Name = "MYSEQUENCE", FuncType = FunctionType.Collation)]
class MySequence : SQLiteFunction
{
  public override int Compare(string param1, string param2)
  {
    return String.Compare(param1, param2, true);
  }
}

使用前需要在代码中注册该函数

You need to register the function in your code before using it with

SQLiteFunction.RegisterFunction(typeof(MySequence));

性能可能是个问题，因为每次比较时您都要为Interop通话支付费用

Performance may be an issue as you'll pay the price of an Interop call each time a comparison is made

这篇关于如何将自定义排序规则应用于SQLite数据库的特定列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！