轉載：在c#中使用sqlite的3種自定義函式

來源 https://blog.csdn.net/lc156845259/article/details/68944742
自定義函式

有三種型別函式可以自定義，分別是：Scalar,Aggregate,Collation。
Scalar：標量（對單條資料進行計算的函式）
Aggregate：聚合（對多條資料進行計算的函式）
Collation：集合（用於排序）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

public class test
{
    /// <summary>
    /// 求平方根
    /// </summary>
    [SQLiteFunction(Name = "sqrt", Arguments = 1, FuncType = FunctionType.Scalar)]
    public class Sqrt : SQLiteFunction
    {
        public override object Invoke(object[] args)
        {
            double d = Convert.ToDouble(args[0]);
            return Math.Sqrt(d);
        }
    }

    /// <summary>
    /// 求平均
    /// </summary>
    [SQLiteFunction(Name = "mean", Arguments = -1, FuncType = FunctionType.Aggregate)]
    public class Mean : SQLiteFunction
    {
        int step = 0;
        public override void Step(object[] args, int stepNumber, ref object contextData)
        {
            double sum = Convert.ToDouble(contextData);
            sum += Convert.ToDouble(args[0]);
            contextData = sum;
            step++;
        }
        public override object Final(object contextData)
        {
            double sum = Convert.ToDouble(contextData);
            double mean = sum / step;
            return mean;
        }
    }

    /// <summary>
    /// 中文排序
    /// </summary>
    [SQLiteFunction(FuncType = FunctionType.Collation, Name = "pinyin")]
    public class PinYin : SQLiteFunction
    {
        public override int Compare(string x, string y)
        {
            return string.Compare(x, y);
        }
    }
/*
 create table student(id int,high int,name varchar(10));
 insert into student values(1,100,'張三');
 insert into student values(2,10,'李四');
 insert into student values(3,50,'王五');
 .save d:/test.db
*/ 
 static void Main(string[] args)
 {
     //註冊自定義函式
     SQLiteFunction.RegisterFunction(typeof(Sqrt));
     SQLiteFunction.RegisterFunction(typeof(Mean));
     SQLiteFunction.RegisterFunction(typeof(PinYin));

     using (var connection = new SQLiteConnection(string.Format("Data source={0}", "d:/test.db")))
     {
         connection.Open();
         using (var com = connection.CreateCommand())
         {
             com.CommandText = "select sqrt(id) as value from student ";
             using (SQLiteDataReader dr = com.ExecuteReader())
             {
                 while (dr.Read())
                 {
                     double value = Convert.ToDouble(dr["value"]);
                     Console.WriteLine(value);
                 }
                 dr.Close();
             }

             com.CommandText = "select mean(high) as val from student ";
             using (SQLiteDataReader dr = com.ExecuteReader())
             {
                 if (dr.Read())
                 {
                     double value = Convert.ToDouble(dr["val"]);
                     Console.WriteLine(value);
                 }
                 dr.Close();
             }

             com.CommandText = "select name from student ORDER BY name COLLATE  pinyin";
             using (SQLiteDataReader dr = com.ExecuteReader())
             {
                 while (dr.Read())
                 {
                     Console.WriteLine(dr["name"].ToString());
                 }
                 dr.Close();
             }
         }
     }
 }
}  

編譯方法
從http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
下載了http://system.data.sqlite.org/downloads/1.0.112.0/sqlite-netFx40-binary-Win32-2010-1.0.112.0.zip
解壓縮，然後在那個解壓縮目錄執行
c:\Windows\Microsoft.NET\Framework\v4.0.30319\csc ..\sqlite_udf.cs -r:System.Data.SQLite.dll