Using a query in an Adapter for listview

Question

I have a listview that I fill from an Adapter. My original code the data was being returned from a table, but now I need to get the code from a query with a join so the examples I used will no longer work and I haven't been able to find out how to use a query for this. I'm using an ORMrepository. In my ORMrepository I have this function

        public IList<Coe> GetmyCoe()
    {
      using (var database = new SQLiteConnection(_helper.WritableDatabase.Path))
      {
        string query = "SELECT Coe.Id, Adult.LName + ',  ' + Adult.MName AS Name, Coe.Createdt FROM Adult INNER JOIN Coe ON Adult.CoeMID = Coe.Id";
        return database.Query<Coe>(query);
      }
    }

which actually returns the data I want. then in my Activity page I have this.

  _list = FindViewById<ListView>(Resource.Id.List);
  FindViewById<ListView>(Resource.Id.List).ItemClick += new System.EventHandler<ItemEventArgs>(CoeList_ItemClick);

   var Coe = ((OmsisMobileApplication)Application).OmsisRepository.GetmyCoe();
  _list.Adapter = new CoeListAdapter(this, Coe);

My Adapter page is where I have the problem, I know it is set up to to looking at a table which I'm not doing anymore. But I don't know how to change it for what I'm passing into it now. Current CoeListAdapter is:

    public class CoeListAdapter : BaseAdapter
{
  private IEnumerable<Coe> _Coe;
    private Activity _context;

    public CoeListAdapter(Activity context, IEnumerable<Coe> Coe)
    {
      _context = context;
      _Coe = Coe;
    }

    public override View GetView(int position, View convertView, ViewGroup parent)
    {
        var view = (convertView
            ?? _context.LayoutInflater.Inflate(
                Resource.Layout.CoeListItem, parent, false)
                ) as LinearLayout;
        var Coe = _Coe.ElementAt(position);
        view.FindViewById<TextView>(Resource.Id.CoeMID).Text = Coe.Id.ToString();
        //view.FindViewById<TextView>(Resource.Id.GrdnMaleName).Text = Coe.Name;
        view.FindViewById<TextView>(Resource.Id.CreateDt).Text = Coe.CreateDt;
        return view;
    }

    public override int Count
    {
      get { return _Coe.Count(); }
    }

    public Coe GetCoe(int position)
    {
      return _Coe.ElementAt(position);
    }

    public override Java.Lang.Object GetItem(int position)
    {
        return null;
    }

    public override long GetItemId(int position)
    {
        return position;
    }
}

How do I set up the CoeListAdapter.cs page so that it can use the passed in data. As you can see I have a commented out lines where I fill a TextView which error because Coe.Name is not in the table model for Coe. but it is returned in the query. I believe my problem is IEnumerable but what do I change it to. I'm new to Mobile developement and suing VS2010 for Mono

score 0 · Answer 1 · edited May 23 '17 at 12:31

The problem probably lies with the binding/mapping of the object not the creation of the view. Or probably more specifically, the query.

 Adult.LName + ',  ' + Adult.MName AS Name

this should be:

Adult.LName || ',  ' || Adult.MName AS Name

See also: String concatenation does not work in SQLite

From the sqlite docs: http://www.sqlite.org/lang_expr.html under the Operators heading:

The unary operator + is a no-op. It can be applied to strings, numbers, blobs or NULL and it always returns a result with the same value as the operand.

Note that there are two variations of the equals and not equals operators. Equals can be either = or ==. The non-equals operator can be either != or <>. The || operator is "concatenate" - it joins together the two strings of its operands. The operator % outputs the value of its left operand modulo its right operand.

The result of any binary operator is either a numeric value or NULL, except for the || concatenation operator which always evaluates to either NULL or a text value.

This shows that the + will evaluate to zero. If you use ||, the value will either be the correct value or NULL (if either of Adult.LName or Adult.MName is NULL). This can be fixed by:

coalesce(first, '') || ', ' || coalesce(second, '')

but this may result in , LastName or FirstName,. Another way would be to create another two properties in Coe called LName and MName. Then bind the values to those properties and use the Name property like this:

public string Name
{
    get { return string.Join(", ", LName, MName); }
}

This will probably be better as you can change how the Name appears especially if there are different combinations of First, Middle and Last names in different places.

And off topic:

I believe my problem is IEnumerable...

This is probably not too true as it returns the correct values. A better way would be to use IList as IEnumerable will iterate through the list each time to get the item as it does not know that the collection is actually a list. (I think)

score 0 · Answer 2 · answered Apr 19 '12 at 16:47

thanks for the help on the concantination, I did find that was wrong, I did fix my problem, I was using an example by Greg Shackles on how to set up using a data base. what I had to do was create a new model with the elements I was wanting. So I created a new Model and called it CoeList, then everywhere I had List or IEnumerable I changed it to List or IEnumerable and it worked.

Using a query in an Adapter for listview

2 Answers2