c# - Store & retrieve array in SQLite database in Xamarin

Question

I wanted to store and retrieve an array in a SQLite database in my Xamarin App. I'm following this Xamarin Tutorial on SQLite.

These values will be then show in Picker. I used Models earlier to store array and then get the values and list it in Picker, it was working fine, but the only problem was that it wasn't stored permanently (by closing app array list get empty).

Banks

using SQLite;

namespace Mobile.App
{
    public class Banks
    {
        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }
        public string Name { get; set; }
        public string Number { get; set; }
    }
}

Database.cs

public class Database
{
    readonly SQLiteAsyncConnection _database;

    public Database(string dbPath)
    {
        _database = new SQLiteAsyncConnection(dbPath);
        _database.CreateTableAsync<SQLiteBank>().Wait();
    }

    public Task<List<Banks>> GetBanksAsync(string name)
    {
        return _database.Table<Banks>().ToListAsync();
    }

    public Task<int> SaveBanksAsync(Banks bank)
    {
        return _database.InsertAsync(bank);
    }
}

ViewModel.cs

Store and retrieve the array list in the database. When trying to save the array into the database, I get an error:

Cannot implicitly convert type 'string[]' to 'string'

When trying to retrieve the array from the database, I get an error:

Cannot convert from 'System.Collections.Generic.List<Mobile.App.Banks>' to 'string'

// Array of Bank Name and Number

string[] Name = new string[] { "Bank 1", "Bank 2", "Bank 3" };
string[] Number = new string[] { "000000", "111111", "222222" };

// Save it in database

await App.Database.SaveBanksAsync(new Banks
{
Name = Name, // Cannot implicity convert typer 'string[]' to 'string'
Number = Number // // Cannot implicity convert typer 'string[]' to 'string'
});

var bankName = await App.Database.GetBanksAsync(Name);

_bankList.Add(bankName); // Cannot convert from 'System.Collections.Generic.List<Mobile.App.Banks>' to 'string'

And then get the values in Picker

private ObservableCollection<string> _bankList = new ObservableCollection<string>();
public ObservableCollection<string> BankList
{
    get
    {
        return _bankList;
    }
    set
    {
        this.RaiseAndSetIfChanged(ref _bankList, value);
    }
}

<Picker 
    x:Name="picker_bank" 
    Title="Select Bank"
    ItemsSource="{Binding BankList}"
    SelectedItem="{Binding SelectedBank}">
</Picker>

Answer 1

instead of doing this

string[] Name = new string[] { "Bank 1", "Bank 2", "Bank 3" };
string[] Number = new string[] { "000000", "111111", "222222" };

try this

List<Banks> banks = new List<Banks>();

// repeat for each bank
banks.Add(new Banks { Name = "Bank1", Number = "00000" });

then you can save them to your db

foreach(var bank in banks)
{
    await App.Database.SaveBanksAsync(bank);
}

next, this doesn't work

var bankName = await App.Database.GetBanksAsync(Name);

because GetBanksAsync returns all banks, not just the name of one bank.

BankList should be an ObservableCollection<Banks>, not string. Then you can do this

// load all banks into a list
var banks = await App.Database.GetBanksAsync();
// create an observablecollection from that list
BankList = new ObservableCollection<Banks>(banks);