Xamarin Android C#SQLite参数查询 [英] Xamarin Android C# SQLite Parameter Query

问题描述

我正在尝试根据Xamarin Android C#中的参数返回数据.拉回所有数据时，我已经使用了下面的代码，但是我需要使用SQLite WHERE查询来返回特定数据.

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.

我需要使用AutoCompleteTextView字段中的值作为参数.

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

推荐答案

如何在查询中使用参数?

How do I use a parameter with my query?

假设您使用的是Sqlite net包变体之一(我使用 sqlite-net-pcl )，则按以下顺序创建带有参数的字符串数组:在SQL查询中将它们替换(通过?标记).

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 ?).

在此示例中，直接从我的一个应用程序中抽出，并在方法中传递了两个参数，分别是 string 和 bool .

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

由于布尔值以整数形式存储在SQLite中，因此需要转换 0 或 1 的字符串表示形式.

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

另一个对空白进行了修剪，我将SQL字符串通配符％添加到字符串的开头和结尾.

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

我用这两个变量创建一个字符串数组，并且我的SQL语句包含两个?，它们将按照在SQL查询语句中找到的参数从左到右的顺序替换为这些参数

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.

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;
}

这篇关于Xamarin Android C#SQLite参数查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！