SQLite UDFs : Adding Sqrt and CToF functions

According to the list of core functions in SQLite, there is no native support for the most mathematics functions (sqrt, log, etc). Furthermore, user may need non-standard functions for his own task (e.g. conversion Celsius to Fahrenheit). This issue can be resolved with user-defined functions (UDFs).

Support for UDFs is added to dotConnect for SQLite starting with the 2.50 version. UDF provides a mechanism for extending the functionality of the SQLite engine by adding a function that can be evaluated in the SQL statements of SQLiteCommand. For more information, please refer to our documentation to the SQLiteAggregateFunction, SQLiteCollationFunction, SQLiteScalarFunction classes (there are samples of using them). 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.

Examples

1. 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));
       }
     }

   And using it

    
   SQLiteConnection sqLiteConnection = new SQLiteConnection(
     @"Data Source=D:SQLitetest.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();

2. 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);
       }
     }

   Using the function:

    
   SQLiteConnection sqLiteConnection = new SQLiteConnection(
     @"Data Source=D:SQLitetest.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();