Xamarin sqlite-net-pcl accessing values of database internal

Question

I'm using Visual Studio 2017, new to SQLite and can't get my head behind how I get to access the values saved in the database and get it back to the elements of the class.

public class MyObject
{
    [PrimaryKey, AutoIncrement]
    public int IndexNumber
    {get;set;}
    [MaxLength(20)]
    public int SomeID
    {get;set;}
    [MaxLength(70)]
    public string SomeName
    {get; set;}
    [MaxLength(25)]
    public string Telephone
    {get; set;}
    public DateTime someTime
    {get; set;}
    //...some more data
}


public class MyDB
{
    readonly SQLiteAsyncConnection database;

    public MyDB (string dbPath);

    database.CreateTableAsync<MyObject>().Wait();

    public Task<List<MyObject>> GetMyObjectAsync()
    {
        return database.Table<MyObject>().ToListAsync();
    }

    public Task<MyObject> GetSingleObjectAsync(int ind)
    {
        return database.Table<MyObject>().Where(i => i.IndexNumber == ind).FirstOrDefaultAsync();
    }
    public Task<int> SaveMyObjectAsync(MyObject object)
    {
        if (object.IndexNumber == 0)
        {
            return database.InsertAsync(object)
        }
        else
        {
            return database.UpdateAsync(object);
        }
    }
    public Task<int> DeleteMyObjectAsync(MyObject object)
    {
        return database.DeleteAsync(object);
    }
}

Inputting data through the .xaml pages works without any problems so far, selecting the object in ListView works as well.

The problem now is that I just can't seem to find a proper way to convert the returned Task back to an object to update some of those values with data only supplied through a HTTP response which itself does not always supply all the data for the datatable, e.g. only the ID and a code if it was successful in the first response. It then needs other data from the object and again delivers values for different elements of the object which is why I'd like to know if there was some method to get a row of data back to a temporary object to update it in the database, remove it if needed (canceled through the HTTP response) or modify it.

if you use async/await it will greatly simplify your async methods, instead of having to deal with Tasks — Jason, Jan 27 '18 at 21:27
Thanks, exactly the problem I was having, now it works properly! — Kael Tharas, Jan 28 '18 at 00:07

score 0 · Accepted Answer · answered Jan 27 '18 at 23:06

Your problem is all about not using await keyword. Whenever you call asynchronous method with no await keyword you are doing it wrong :) I strongly recommend you to read James Montemagno blog post about it here.

Here is solution of your problems.

public class MyDB
    {
        readonly SQLiteAsyncConnection database;

        public MyDB(string dbPath)
        {
            database = new SQLiteAsyncConnection(dbPath);
        }

        public async Task CreteMyDb()
        {
            await database.CreateTableAsync<MyObject>();
        }

        public async Task<List<MyObject>> GetMyObjectsAsync()
        {
            return await database.Table<MyObject>().ToListAsync();
        }

        public async Task<MyObject> GetSingleObjectAsync(int ind)
        {
            return await database.Table<MyObject>().Where(i => i.IndexNumber == ind).FirstOrDefaultAsync();
        }

        public async Task<int> SaveMyObjectAsync(MyObject myObject)
        {
            if (myObject.IndexNumber == 0)
            {
                return await database.InsertAsync(myObject);
            }
            else
            {
                return await database.UpdateAsync(myObject);
            }
        }

        public async Task<int> DeleteMyObjectAsync(MyObject myObject)
        {
            return await database.DeleteAsync(myObject);
        }
    }

And example usage of methods.

            await this.myDb.CreteMyDb();
            await this.myDb.SaveMyObjectAsync(new MyObject()
            {
                SomeID = 1,
                SomeName = "Adam",
                someTime = DateTime.Now,
                Telephone = "53535353"
            });

            var list = await this.myDb.GetMyObjectAsync();

Let me know if it helped!

Tyvm, it did indeed help! – Kael Tharas Jan 28 '18 at 00:08 — Kael Tharas, Jan 28 '18 at 00:08

Xamarin sqlite-net-pcl accessing values of database internal

1 Answers1