[HowTo] Make User Defined Function In SQLite ADO.NET With C#

Sometime we want to make user defined function and use it in our query to the database. In Ms. SQL Server and Oracle DBMS support user defined function, stored procedure and transactional. Since SQLite is zero configuration, serverless and single database file, SQLite doesn’t support user defined function (If you don’t know what is SQLite and how to use SQLite ADO.Net with C# please refer to this post). User defined function can be made outside of the SQLite that means you make custom function with your own programming language and later that function can be binded to SQLite core function.

SQLite ADO.Net full support user defined function that means we can make our user defined function in C# language and later we can use the library from SQLite ADO.Net to bind it to SQLite core function then we can call that function in our query to the database. To make a user defined function in SQLite ADO.Net with C# is not hard, you just need a class which derived from SQLiteFunction Class and override Invoke function. See the example below:

[SQLiteFunction(Name = "ToUpper", Arguments = 1, FuncType = FunctionType.Scalar)]
    public class ToUpper: SQLiteFunction
    {
        public override object Invoke(object[] args)
        {
            return args[0].ToString().ToUpper();
        }
    }

That’s it, when you compile and run your application, SQLite ADO.Net will automatically bind that user defined function to SQLite Core function. And then you can use that function in your query without a problem.

If you have any other tricks or tips about SQLite ADO.Net please feel free to leave a comment. Thanks and have a nice day!