Xamarin Android C# SQLite Parameter Query

Question

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 return specific data.

I need to use the value from an AutoCompleteTextView field for the parameter..

       protected override void OnCreate(Bundle savedInstanceState)
    {
        base.OnCreate(savedInstanceState);
        // Set our view from the "main" layout resource
        //ActionBar.NavigationMode = ActionBarNavigationMode.Tabs;
        SetContentView(Resource.Layout.Main);
        db = new DBHelper(this);
        sqliteDB = db.WritableDatabase;

        container = FindViewById<LinearLayout>(Resource.Id.container);

        var btnEmergencyServices = FindViewById<Button>(Resource.Id.btnEmergencyServices);
        btnEmergencyServices.Click += btnEmergencyServices_onClick;
    }

private void EmergencyServicesData()
    {
        ICursor selectData = sqliteDB.RawQuery("select POLICE, FIRE, MEDICAL from EmergencyServices WHERE COUNTRY = @Country", new string[] { });
        if (selectData.Count > 0)
        {
            selectData.MoveToFirst();
            do
            {
                EmergencyServices emergencyServices = new EmergencyServices();
                emergencyServices.POLICE = selectData.GetString(selectData.GetColumnIndex("POLICE"));
                emergencyServices.FIRE = selectData.GetString(selectData.GetColumnIndex("FIRE"));
                emergencyServices.MEDICAL = selectData.GetString(selectData.GetColumnIndex("MEDICAL"));
                EmergencyServices.Add(emergencyServices);
            }
            while (selectData.MoveToNext());
            selectData.Close();
        }
        foreach (var item in EmergencyServices)
        {
            LayoutInflater layoutInflater = (LayoutInflater)BaseContext.GetSystemService(Context.LayoutInflaterService);
            View addView = layoutInflater.Inflate(Resource.Layout.EmergencyServices, null);
            TextView txtPolice = addView.FindViewById<TextView>(Resource.Id.txtPolice);
            TextView txtFire = addView.FindViewById<TextView>(Resource.Id.txtFire);
            TextView txtMedical = addView.FindViewById<TextView>(Resource.Id.txtMedical);
            txtPolice.Text = item.POLICE;
            txtFire.Text = item.FIRE;
            txtMedical.Text = item.MEDICAL;
            container.AddView(addView);
        }
    }

I cannot really make heads or tails of if this is the case, but if you're using SqliteCommand anywhere use the Parameters collection property. What exactly is DBHelper — 580
– 580, Commented Jun 16, 2018 at 22:01

SushiHangover · Accepted Answer · 2018-06-17 00:27:23Z

2

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

public async 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;", new string[2] { param1, param2 });
    return packages;
}

edited Jun 17, 2018 at 0:27

answered Jun 16, 2018 at 22:47

SushiHangover

74.3k11 gold badges113 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

WHoward Over a year ago

Thanks this is really helpful, I'm assuming I can take the value from my AutoCompleteTextView as my parameter easily enough?

SushiHangover Over a year ago

@WHoward Absolutely, my example is from an SearchView (Xamarin.Android-based) and passes the user's search entry to this method to filter the RecyclerView, in your case you would take the returned IList and present that to the user as dropdown list under the auto-complete entry (assuming that is your end goal).

WHoward Over a year ago

Kinda, the user will enter the country of choice and based on that the data associated with that will be returned/displayed to the user. Thanks again

Collectives™ on Stack Overflow

Xamarin Android C# SQLite Parameter Query

1 Answer 1

Example

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Example

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related