Accessing math functions with sqlite-net-pcl and Xamarin.Forms

Question

When writing a C# console App, and using System.Data.SQLite, I am able to perform SQL commands such as:

string cosfun = string.Format("UPDATE test SET cosColumn = column1*cos(20));

However, when I try using a similar command in Xamarin.Forms, using the sqlite-net-pcl package, I get the followin error: SQLite.SQLiteException: 'no such function: cos'

I have found a similar question on SO (Custom SQLite functions in Xamarin.iOS). However, I didn't fully understand the response. I now have the following questions:

1) Can I make custom SQL functions using sqlite-net-pcl in Xamarin.Forms? If so, could someone please share a simple (but complete) example of how to do this? 2) Is there anyway for me to access the same math functions (pow, cos, sin, etc.) that I can access when writing console Apps in C#? 3) Is there another way to do this? For example, can I read columns from the database into a List, then perform the required math functions, and feed that back into the database? Would this be a terrible idea with a large database?

Thanks in advance,

Dustin

Answer 1

1. First is OK.

The SQLite-net PCL by Frank Kreuger is the one that Xamarin University uses in their XAM160 - Working with SQLite and Mobile Data class: https://university.xamarin.com/classes/track/cross-platform-design

2. Second is Ok.

You can find some documentation on how to get started on the Xamarin developer site: http://developer.xamarin.com/recipes/android/data/databases/sqlite/

3. Third answer is clear.

More Info:

You can refer to official document in here, Another similar discussion may be helpful for you this.

Answer 2

Correct me if I'm wrong, but what you're trying to do is essentially have two columns where one contains a set of data, and the other contains the result of a simple mathematical operation from the first column. From this you have two columns where one is dependent on the other, which means you are occupying double the necessary memory space. For a 100 entries, that's alright. For 1,000,000? Less so.

I personally thing you are better off not having cosColumn, and you should calculate the cosine when you read the data. For example:

// In your C# code...

public class MyData
{
    public double Column1 { get; set; } = 0.0;
    public double Cosine => Math.Cos(Column1);
}

In the above, the cosine value is never stored or created in neither C# or SQLite, but it is obtained only when needed. This makes it much more memory-friendly to the SQLite table, and it implements a better SQLite structure.

---

In the code above, the line:

public double Cosine => Math.Cos(Column1);

is exactly equivalent to:

public double Cosine
{
    get 
    { 
        return Math.Cos(Column1); 
    }
}

There's no real difference between the two, and you save a lot of line-space. You can find more information on the => notation from this StackOverflow answer by Alex Booker.

Let's go through an example of implementing this structure. Suppose you have a database with 1 column with the name Column1, and you want to apply a Cosine function to this value and display it. Your code might look like:

// Read from database object of type MyData
MyData data = ReadOneValueFromDatabase<MyData>();

// Display values in a label
MyValueLabel.Text = "Database value: " + data.Column1.ToString();
MyCosineLabel.Text = "Cosine value: " + data.Cosine.ToString();

The object data will store the value of Column1 from the database in Column1, but not Cosine. The value of Cosine is only obtained when you call data.Cosine.