How to use local database in Xamarin.form?

Question

I'm following a tutorial step by step to create a local database by using SQLite.net-PCL. However, when running the app, I tried to insert a data into the database, but it threw me a 'System.NullReferenceException'.

What I have done:

I'm only using SQLite.Net-TCL (1.2.1) package, and also added it into Android and iOS.
I noticed some tutorials saying that I should implement sqlite database on android and iOS, but I didn't find the tutorial is using this way, or maybe the IFileHelper interface has the same function?
I keep FileHelper interface in my Android folder
After debug, the database code passed. So the problem should happen when inserting data.
The exception happened at this line: if (person.ID != 0), and this line : await App.DB.SaveItemAsync(person);

Tutorial: https://developer.xamarin.com/guides/xamarin-forms/application-fundamentals/databases/

This is my database code:

using System.Threading.Tasks;
using System.Collections.Generic;
using System;
using SQLite;

namespace DatabaseDemo
{
public class Database
{
    readonly SQLiteAsyncConnection _database;

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

    public Task<List<Model>> GetItemsAsync()
    {
        return _database.Table<Model>().ToListAsync();
    }

    public Task<int> SaveItemAsync(Model person)
    {
        if (person.ID != 0)
        {
            return _database.UpdateAsync(person);

        }
        else
        {
            return _database.InsertAsync(person);
        }
    }
}
}

This is my interface:

<StackLayout Margin="20" VerticalOptions="StartAndExpand">
    <Label Text = "Name"/>
    <Entry Text = "{Binding Name}" />
    <Button Text = "Save" Clicked="OnSaveClicked" />
</StackLayout>

using Xamarin.Forms;
using System;

namespace DatabaseDemo
{
    public partial class DatabaseDemoPage : ContentPage
    {
        public DatabaseDemoPage()
        {
            InitializeComponent();
        }

    async void OnSaveClicked(object sender, EventArgs e)
    {
        var person = (Model)BindingContext;
        await App.DB.SaveItemAsync(person);
    }
}
}

App.cs

using Xamarin.Forms;

namespace DatabaseDemo
{
    public partial class App : Application
    {
        static Database database;
        public static Database DB
        {
            get
            {
                if (database == null)
                {
                    database = new Database(DependencyService.Get<IFileHelper>().GetLocalFilePath("db.db3"));
                }
                return database;
            }
        }

        public App()
        {
            InitializeComponent();

            MainPage = new DatabaseDemoPage();
        }
    }
}

I also created a public interface, and also created for Andriod and iOS.

using System;
namespace DatabaseDemo
{
    public interface IFileHelper
    {
        string GetLocalFilePath(string filename);
    }
}

Alessandro Caliaro Alessandro Caliaro · Accepted Answer · 2017-03-24T10:47:37

You have to create Android side...

[assembly: Dependency(typeof(FileHelper))]
namespace Todo.Droid
{
    public class FileHelper : IFileHelper
    {
        public string GetLocalFilePath(string filename)
        {
            string path = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            return Path.Combine(path, filename);
        }
    }
}

Here all info

Once the interface has been defined, use the DependencyService to obtain an implementation and get a local file path (note that this interface has not been implemented yet). The following code gets an implementation in the App.Database property:

static TodoItemDatabase database;

public static TodoItemDatabase Database
{
  get
  {
    if (database == null)
    {
      database = new TodoItemDatabase(DependencyService.Get<IFileHelper>().GetLocalFilePath("TodoSQLite.db3"));
    }
    return database;
  }
}

The TodoItemDatabase constructor is shown below:

public TodoItemDatabase(string dbPath)
{
  database = new SQLiteAsyncConnection(dbPath);
  database.CreateTableAsync<TodoItem>().Wait();
}

This approach creates a single database connection that is kept open while the application runs, therefore avoiding the expense of opening and closing the database file each time a database operation is performed.

How to use local database in Xamarin.form?

2 Answers