Xamarin Android C# Sqlite Parameter Query

I'm trying to return data based on a parameter in Xamarin Android C#. I've got the below code working when pulling all data back, however I need to use a SQLite WHERE query to retu

Solution 1:

How do I use a parameter with my query?

Assuming you are using one of the Sqlite net package variants (I use sqlite-net-pcl), you create a string array with the parameters in the order that they are to be substituted within the SQL query (marked via ?).

In this example, yanked straight from one of my apps, is passing two parameters in the method, a string and a bool.

One needs to convert the string representation of 0 or 1 as booleans are stored as integers in SQLite.

The other gets whitespace trimmed and I add the SQL string wildcard % to beginning and end of string.

I create a string array with those two variables and my SQL statement contains two ? that will be replaced with those parameters in the left-2-right order that they are found in the SQL query statement.

Example

publicasync Task<IList<Package>> DbGetSearchedPackagesAsync(string constraint, bool isUserApp = true)
{
    var param1 = Convert.ToInt32(isUserApp).ToString();
    var param2 = $"%{constraint.Trim()}%";
    var packages = await conn.QueryAsync<Package>("SELECT * FROM Package WHERE UserApp = ? AND Name LIKE ? ORDER BY Name COLLATE NOCASE ASC;", newstring[2] { param1, param2 });
    return packages;
}