How to use SQLite with C# and Xamarin

Objective: Learn to use SQLite using C# and XAML in Xamarin with the help of the sqlite-net-pcl NuGet package.

You need to know: Basic C#, XAML, and you need to have a little experience with Xamarin.

Let’s begin:

Create a new project and replace the MainPage.xaml code with this one:

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" x:Class="TTXSqlitePosts01.MainPage">

    <StackLayout>
        <StackLayout Orientation="Horizontal">
            <Button Text="Add" Clicked="BtnAdd"/>
            <Button Text="Delete" Clicked="BtnDelete" HorizontalOptions="EndAndExpand"/>
        </StackLayout>
        <ListView x:Name="LVMain">
            <ListView.ItemTemplate>
                <DataTemplate>
                    <TextCell Text="{Binding Name}" TextColor="Red"/>
                </DataTemplate>
            </ListView.ItemTemplate>
        </ListView>
    </StackLayout>

</ContentPage>

And the MainPage.xaml.cs code with this one:

using System;
using Xamarin.Forms;

namespace TTXSqlitePosts01
{
	public partial class MainPage : ContentPage
	{
		public MainPage()
		{
			InitializeComponent();
		}

		void BtnAdd(object sender, System.EventArgs e)
		{
		}

		void BtnDelete(object sender, System.EventArgs e)
		{
		}
	}
}

Read the code from both files and put the name of your project instead of “TTXSqlitePosts01”.

Run the app to make sure there are no problems.

You should see something like this:




Add the sqlite-net-pcl NuGet package to all the platforms you will be using:

 

Now to make this work in all platforms, we will create an interface in the PCL (Portable Class Library) and an implementation in each platform of your project.

First we create a folder called “DB” in the PCL and inside we create an interface called IDb:

 

This is the code for IDb:


SQLiteAsyncConnection GetConnection();

And remember to add:


using SQLite;

Now, create a folder also called “DB” for each platform you will be using and inside create a class called “Db”.

In this example I’ll use Android but it’s the same process for iOS.

This is the code for Db.cs:

using System.IO;
using SQLite;
using TTXSqlitePosts01.DB;
using TTXSqlitePosts01.Droid.DB;
using Xamarin.Forms;

[assembly: Dependency(typeof(Db))]

namespace TTXSqlitePosts01.Droid.DB
{
    public class Db : IDb
    {
        public SQLiteAsyncConnection GetConnection()
        {
            var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments);
            var path = Path.Combine(documentsPath, "MySqlite.db3");

            return new SQLiteAsyncConnection(path);
        }
    }
}

 

Remember to put the name of your project instead of “TTXSqlitePost01”.

In MainPage.xaml.cs, for this example we will create a “Post” class to function as our table in our SQL database:


public class Post
    {
        [PrimaryKey,AutoIncrement]
        public int Id { get; set; }
        [MaxLength(255)]
        public string Name { get; set; }
    }


 

Your MainPage.xaml.cs code should now look like this:

Remember to add:


using SQLite;

Now still here in MainPage.xaml.cs add the following code to store our DB connection:


private SQLiteAsyncConnection _connection;

And we will use this to fill the ListView:


private ObservableCollection<Post> _posts;

Your code should now look like this:

 

Inside the MainPage constructor after InitializeComponent() add the following code:


_connection = DependencyService.Get<DB.IDb>().GetConnection();

 

Override the OnAppearing method to add this:

protected override async void OnAppearing()
        {
            await _connection.CreateTableAsync<Post>();
            var posts = await _connection.Table<Post>().ToListAsync();
            _posts = new ObservableCollection<Post>(posts);
            LVMain.ItemsSource = _posts;

            base.OnAppearing();
        }

Remember to make it async

It should look like this:

 

Inside the BtnAdd method add the following code:


var r = new Random();
            int randomNumber = r.Next();
            var post = new Post() { Name = "Post: " + randomNumber };
            await _connection.InsertAsync(post);
            _posts.Add(post);

Remember to make it async so it will look like this:

Run the app and you should see something like this:

 

Inside the BtnDelete method add the following code:


var post = _posts[0];
            await _connection.DeleteAsync(post);
            _posts.Remove(post);

 

Remember to make it async so it will look like this:

Run the app and you should see something like this:

 

Fin.

 

 

About the author

AezioX

Programmer


>