SQLite UDFs: Adding Sqrt and CToF Functions
According to the list of core functions in SQLite,
there is no native support for the most of mathematics functions (sqrt, log, etc). This issue can be resolved with user-defined functions (UDFs).
Furthermore, user may need non-standard
functions for his own task (e.g. conversion Celsius to Fahrenheit).
UDF provide a mechanism for extending the functionality of the SQLite engine by adding
a function that can be evaluated in the SQL statements of SQLiteCommand.
Full support for user-defined functions and collating sequences means that in many cases
if SQLite doesn't have a feature, you can write it yourself in your favorite .NET language.
Writing UDF's and collating sequences has never been easier.
We have implemented some of the functions in the dotConnect for SQLite data provider.
You can refer to the list of implemented functions in the Standard SQL Function Support article.
Examples
- Here is an example of creating and using of the function, that calculates the square root.
public class SqrtFunction<T1>: SQLiteScalarFunction<T1,double> {
public SqrtFunction(): base("Sqrt") {
}
protected override double Execute(T1 arg, SQLiteConnection connection) {
return Math.Sqrt(Convert.ToDouble(arg));
}
}
Public Class SqrtFunction(Of T1)
Inherits SQLiteScalarFunction(Of T1, Double)
' Methods
Public Sub New()
MyBase.New("Sqrt")
End Sub
Protected Overrides Function Execute(ByVal arg As T1, ByVal connection As SQLiteConnection) As Double
Return Math.Sqrt(Convert.ToDouble(arg))
End Function
End Class
And using it
SQLiteConnection sqLiteConnection = new SQLiteConnection(
@"Data Source=D:\SQLite\test.db");
sqLiteConnection.Open();
SqrtFunction<double> function = new SqrtFunction<double>();
sqLiteConnection.RegisterFunction(function);
SQLiteCommand command = new SQLiteCommand("select sqrt(9.0)", sqLiteConnection);
double result = (double)command.ExecuteScalar();
sqLiteConnection.UnRegisterFunction(function);
sqLiteConnection.Close();
Dim sqLiteConnection As New SQLiteConnection("Data Source=D:\SQLite\test.db")
sqLiteConnection.Open
Dim function As New SqrtFunction(Of Double)
sqLiteConnection.RegisterFunction([function])
Dim command As New SQLiteCommand("select sqrt(9.0)", sqLiteConnection)
Dim result As Double = CDbl(command.ExecuteScalar)
sqLiteConnection.UnRegisterFunction([function])
sqLiteConnection.Close
- This sample implements scalar function that makes the "Celsius to Fahrenheit" conversion.
public class CelsToFahrFunction : SQLiteScalarFunction<long, long> {
public CelsToFahrFunction() : base("CToF") {
}
protected override long Execute(long parameter, SQLiteConnection connection) {
return Convert.ToInt64((9.0f / 5.0f) * parameter + 32);
}
}
Public Class CelsToFahrFunction
Inherits SQLiteScalarFunction(Of Long, Long)
' Methods
Public Sub New()
MyBase.New("CToF")
End Sub
Protected Overrides Function Execute(ByVal parameter As Long, ByVal connection As SQLiteConnection) As Long
Return Convert.ToInt64(CSng(((1.8! * parameter) + 32!)))
End Function
End Class
Using the function:
SQLiteConnection sqLiteConnection = new SQLiteConnection(
@"Data Source=D:\SQLite\test.db");
sqLiteConnection.Open();
CelsToFahrFunction function = new CelsToFahrFunction();
sqLiteConnection.RegisterFunction(function);
SQLiteCommand command = new SQLiteCommand("select CtoF(-40)", sqLiteConnection);
long result = (long)command.ExecuteScalar();
sqLiteConnection.UnRegisterFunction(function);
sqLiteConnection.Close();
[Visual Basic]
Dim sqLiteConnection As New SQLiteConnection("Data Source=D:\SQLite\test.db")
sqLiteConnection.Open
Dim function As New CelsToFahrFunction
sqLiteConnection.RegisterFunction([function])
Dim command As New SQLiteCommand("select CtoF(-40)", sqLiteConnection)
Dim result As Long = CLng(command.ExecuteScalar)
sqLiteConnection.UnRegisterFunction([function])
sqLiteConnection.Close
See Also
Using parameters