SQLite是轻量级的数据库,关于它的常规用法就不一一介绍了,本文主要介绍了如何创建自定义函数,对其进行扩展,以满足开发中的个性化需求。
SQLiteFunction简单介绍
SQLiteFunction是一个抽象类,主要用于处理用户自定义函数。主要成员如下:
自定义函数
有三种类型函数可以自定义,分别是:Scalar,Aggregate,Collation。
Scalar:标量(对单条数据进行计算的函数)
Aggregate:聚合(对多条数据进行计算的函数)
Collation:集合(用于排序)
下面分别创建一种函数表示其简单用法,注意要引用命名空间 System.Data.SQLite
/// <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);
}
}
Arguments为参数个数,等于-1可为任意个参数。
使用自定义函数
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}", "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();
}
}
}
}