sqlite "select distinct" windows 8 app c#

Question

I am trying to read the unique values from an SQLITE table column and display it in a listbox.

This SQL statement

SELECT DISTINCT [COLUMN NAME]
FROM [TABLE NAME]

returns the correct results when I run the statement outside of my app using an SQLITE browser, but I cannot seem to get the results into a list (or as individual items) within my app to add to the listbox.

I have the most recent SQLITE3 package in my c#/XAML app and it works great.

Here is what I have that works, but this seems to be an inefficient process:

I have a "qvdb" class to define string values...

public class qvdb
    {
        [PrimaryKey, AutoIncrement] 
        public int qRecord { get; set; }

        [MaxLength(3000)]
        public string qCol_1 { get; set; }
        [MaxLength(3000)]
        public string qCol_2 { get; set; }
        [MaxLength(3000)]
        public string qCol_3 { get; set; }
        [MaxLength(3000)]
        public string qCol_4 { get; set; }
        [MaxLength(3000)]
        public string qCol_5 { get; set; }
        [MaxLength(3000)]
        public string qCol_6 { get; set; }
        [MaxLength(3000)]
        public string qCol_7 { get; set; }
        [MaxLength(3000)]
        public string qCol_8 { get; set; }
        [MaxLength(3000)]
        public string qCol_9 { get; set; }
        [MaxLength(3000)]
        public string qCol_10 { get; set; }
}

... an application-wide list to hold the strings...

public static List<String> qCols = new List<String>();

... and a process for putting them in a list...

public static async Task mProcessColumns(IEnumerable<qvdb> input)
        {
             var dbExists = await qFunctions.fnFileExistsAsync(ApplicationData.Current.LocalFolder, qgv.fileDBLocal);
            if (!dbExists) return;

            try
            {
                qCols.Clear();
                foreach (var item in input)
                {
                    qCols.Add(item.qCol_1);
                    qCols.Add(item.qCol_2);
                    qCols.Add(item.qCol_3);
                    qCols.Add(item.qCol_4);
                    qCols.Add(item.qCol_5);
                    qCols.Add(item.qCol_6);
                    qCols.Add(item.qCol_7);
                    qCols.Add(item.qCol_8);
                    qCols.Add(item.qCol_9);
                    qCols.Add(item.qCol_10);
}

... and a clunky "step through each record" way using the above to fill the listbox:

 private async Task mLoadListboxes(int varZeroRow, int deZeroCol)
        {
            //Debug.WriteLine("mLoadListboxes");

            try
            {
                // add a list
                var tempList = new List<string>();

                // check for no records ... no records means nothing to load
                if (lbRecord.Items == null) return;

                // add unique items to a list
                for (iRecord = 0; iRecord <= lbRecord.Items.Count - 1; iRecord++)
                {
                    var fillLBs = await qgv.db.QueryAsync<qvdb>("SELECT * FROM qvdb WHERE qRecord=" +
                                                                Convert.ToInt32(lbRecord.Items[iRecord].ToString()) +
                                                                ";");
                    await qProcessColumns.mProcessColumns(fillLBs);


                    if (qProcessColumns.qCols[varZeroRow] == null) continue;
                    var tempTextToDedup = qProcessColumns.qCols[varZeroRow];
                    if (tempTextToDedup == null) continue;
                    if (tempList.Contains(tempTextToDedup)) continue;
                    if (tempTextToDedup != " " && tempTextToDedup != "")
                    {
                        // add to list
                        tempList.Add(tempTextToDedup);
                    }
                }


                // sort list
                tempList.Sort();

                // set lb items to list
                qgv.deListboxes[deZeroCol].ItemsSource = tempList;

                Task.WaitAll();
            }
            catch (Exception)
            {
                qgv.failed = true;
            }
        }

The "SELECT DISTINCT" seems to bypass alot of steps and return a list that can be sorted and added to a listbox using .ItemsSource.

---

Thanks for the guidance CL. So following this suggestion I created the class exactly as you suggested:

public class JustOneColumn
    {
        public string value { get; set; }
    }

I first tried to directly set the ItemsSource property of the listbox to the query variable and got this error:

I then tried to create a method to process the results:

public static List<String> qDistinctColElements = new List<String>();



public static void mProcessDistinctColElements(IEnumerable<JustOneColumn> input)
        {
            try
            {
                qDistinctColElements.Clear();
                foreach (var item in input)
                {
                    qDistinctColElements.Add(item.value);
                }
            }
            catch (Exception) { failedProcessColumns = true; }

        }

When I try to call this method ...

var colContents = qgv.db.QueryAsync<JustOneColumn>("SELECT DISTINCT qCol_" + (_v + 1) + " FROM qvdb;");
qProcessColumns.mProcessDistinctColElements(colContents);  
qgv.deListboxes[_v].ItemsSource = colContents;

...I get a squiggly line under colContents saying "Error 2 Argument 1: cannot convert from 'System.Threading.Tasks.Task System.Collections.Generic.List QuantiView.JustOneColumn' to 'System.Collections.Generic.IEnumerable QuantiView.JustOneColumn'"

I verified that the sql query works in SQLite Database Browser and also looked at the various output using Debug.Writeline and saw no returned values.

Answer 1

I had the same problem. I have tried the solution with JustOneColumn table shown here. It did not work until I add as value alias to my sql statement, i.e.

String sql = "SELECT DISTINCT col AS value FROM myTable"

IEnumerable<OneIntColumn> result = db.Query<OneIntColumn>(sql)

Note that in my case OneIntColumn.value is of type int. Without the proper column alias in SQL statement query was always returning 0 as a result.

Answer 2

Your class qvdb corresponds to the table returned by the SELECT * FROM qvdb query.

For the table returned by the SELECT DISTINCT ... query, you should also create a corresponding class:

public class JustOneColumn
{
    public string value { get; set; }
}

... qgv.db.QueryAsync<JustOneColumn>("SELECT DISTINCT MyCol FROM ...");