Devart Blog

SQLite UDFs : Adding Sqrt and CToF functions

Posted by on October 12th, 2009

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();

One Response to “SQLite UDFs : Adding Sqrt and CToF functions”

  1. carlos alberto Says:

    oiiiiiiiiiii galeráaaa eu tenhooo um tibiaaa tô jogandooo viu tdu blz tchau.