Xamarin.Forms : Basic SQLite Operations (Android, iOS and Windows)

In this article, we will learn about the basics of SQLite database for all platforms (Android, iOS and Windows).

Previous Chapters

Before going through this articles. I would like to request to see my following previous articles.

• Installing Visual Studio With Xamarin
• Forms: Create Your First Xamarin App For iOS, Android, And Windows
• Forms: Create A Simple Login UI
• Forms: Data Binding (Android, iOS and Windows)

Step 1:

First, create a Xamarin.Forms project with Portable Class Library (PCL) as discussed in my previous article.
Xamarin.Forms: Create Your First Xamarin App For iOS, Android, And Windows

Step 2:

Now we will install sqlite-net-pcl from Nuget Package Manager.

Under PCL project, right click on References and from the Nuget Package Manager. Look for sqlite-net-pcl and install that within Portable Class Library (PCL) project.

 

Step 3:

Install the same SQLite library for each platform (Android, iOS and Windows) under each project. So let’s add sqlite-net-pcl for Android first.

Right click on References under Android project and install sqlite-net-pcl in the similar way like above.

 

Step 4:

Add an XAML Page with name FormsPage.xaml. For this right click on PCL project and add Forms Xaml Page.

 

Step 5:

In the FormsPage.xaml, we add two labels, two input fields, and two buttons as following.

<?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="SQLiteTutorial.FormsPage">

<ContentPage.Content>
<StackLayout Padding="20">
<Label Text="Name" FontSize="25"/>
<Entry x:Name="memberName" Placeholder="Enter Name"></Entry>
<Label Text="Age" FontSize="25"/>
<Entry x:Name="memberAge" Placeholder="Enter Age"></Entry>
<StackLayout Orientation="Horizontal">

<Button Text="Insert Members" BackgroundColor="Gray" TextColor="White" Clicked="InsertMember"/>
<Button Text="Show Memebrs" BackgroundColor="Maroon" TextColor="White" Clicked="ShowMembers"/>
</StackLayout>

</StackLayout>
</ContentPage.Content>
</ContentPage>

It will create a UI like this:

 

Step 6:

We need to create an interface class to define platform specific database file save and creating the database connection.

So in your, PCL project add an Interface class with name ISQLite.

 

Complete code for ISQLite.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteTutorial
{
public interface ISQLite
{
SQLite.SQLiteConnection GetConnection();

}
}

 

Step 7:

Next, we create a Model class with name “Member”.

Right Click on PCL Project > Add > Class > Name it Member and Click Add.

Here, Name, Age are getter setter used to set and get the values with ID which has PrimaryKey and AutoIncrement properties derived from SQLite.

Complete Code snippet for Member class is:

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

namespace SQLiteTutorial
{
public  class Member
{
[PrimaryKey, AutoIncrement]
public int ID { get; set; }
public string Name { get; set; }
public string Age { get; set; }

public Member()

{
}
}
}

 

Step 8:

Now we add another class that represents database which includes all the logic for database operations like Create, Read, Write, Delete, Update, etc.

Let’s add another class with name MemberDatabase for DB logics.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLite;
using Xamarin.Forms;
using System.Collections;

namespace SQLiteTutorial
{
public class MemberDatabase
{
private SQLiteConnection conn;

//CREATE
public MemberDatabase()
{
conn = DependencyService.Get<ISQLite>().GetConnection();
conn.CreateTable<Member>();
}

//READ
public IEnumerable<Member> GetMembers()
{
var members = (from mem in conn.Table<Member>() select mem);
return members.ToList();
}

//INSERT
public string AddMember(Member member)
{
conn.Insert(member);
return "success";
}

//DELETE
public string DeleteMember(int id)
{
conn.Delete<Member>(id);
return "success";
}
}
}

 

Step 9:

Now we need to add platform specific database file creation code to save database file and to create database connection since each platform has different folder environment.

FOR ANDROID

Add a class with name Andorid_SQLite and implement ISQLite interface. Under Android Project > Right Click > Add > New Class.

 

In Andorid_SQLite class, update your code with the following code snippet:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using SQLiteTutorial.Droid;
using Xamarin.Forms;

[assembly: Dependency(typeof(Android_SQLite))]

namespace SQLiteTutorial.Droid
{
public class Android_SQLite : ISQLite
{
public SQLite.SQLiteConnection GetConnection()
{
var dbName = "Members.sqlite";
var dbPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.ApplicationData);
var path = System.IO.Path.Combine(dbPath, dbName);
var conn = new SQLite.SQLiteConnection(path);

return conn;
}
}
}

 

FOR WINDOWS

Add a similar class like above inside Windows Phone/Windows Project. Name it Windows_SQLite.

Now update your code with the following code snippet, in the Windows_SQLite class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Windows.Storage;
using SQLite;
using System.IO;
using Xamarin.Forms;
using SQLiteTutorial.WinPhone;

[assembly: Dependency(typeof(Windows_SQLite))]

namespace SQLiteTutorial.WinPhone
{
public class Windows_SQLite : ISQLite
{
public SQLiteConnection GetConnection()
{

var sqliteFilename = "Member.sqlite";
string path = Path.Combine(ApplicationData.Current.LocalFolder.Path, sqliteFilename);
var conn = new SQLite.SQLiteConnection(path);
return conn;
}
}
}

 

FOR IOS

Add a similar class like above inside iOS Project. Name it IOS_SQLite.

Update your code with the following code snippet, in IOS_SQLite class.

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using Xamarin.Forms;
using SQLite;
using SQLiteTutorial.iOS;

[assembly: Dependency(typeof(IOS_SQLite))]

namespace SQLiteTutorial.iOS
{
public class IOS_SQLite : ISQLite
{
public SQLiteConnection GetConnection()
{
var dbName = "Member.sqlite";
string dbPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
string libraryPath = Path.Combine(dbPath, "..", "Library"); // Library folder
var path = Path.Combine(libraryPath, dbName);
var conn = new SQLite.SQLiteConnection(path);
return conn;
}
}
}

 

Step 10:

Now we will initialize MemberDatabase and Member class and call the AddMember function in the code behind of FormsPage.xaml.cs.

Click events InsertMember and ShowMembers are defined here. And we navigate to MemberList page inside the ShowMember function.

Complete code snippet for FormsPage.xaml.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Xamarin.Forms;

namespace SQLiteTutorial
{
public partial class FormsPage : ContentPage
{
public MemberDatabase memberDatabase;
public Member member;
public FormsPage()
{
InitializeComponent();
}

public void InsertMember(object o, EventArgs e)
{
member = new Member();
memberDatabase = new MemberDatabase();
member.Name = memberName.Text;
member.Age = memberAge.Text;
memberDatabase.AddMember(member);
}

public async void ShowMembers(object o, EventArgs e)
{
await Navigation.PushModalAsync(new MemberList());
}
}
}

 

Step 11:

In the App.cs, update App constructor with:

public App()
{
// The root page of your application
MainPage = new FormsPage();
}

 

Step 12:

Let’s add another xaml page that shows the list of members from database.

Inside PCL, Add a Forms Xaml Page with name MembersList.xaml .

Now, in the MemberList.xaml, add a ListView inside ContentPage.Content. Inside the ListView, we have ItemTemplate and DataTemplate. We use ViewCell to display our content.

Complete XAML code will be:

<?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="SQLiteTutorial.MemberList">
<ContentPage.Content>
<ListView x:Name="listMembers" ItemTapped="OnSelected">
<ListView.ItemTemplate>
<DataTemplate>
<ViewCell>
<StackLayout Spacing="2" HorizontalOptions="Center">
<StackLayout Orientation="Horizontal">
<Label Text="Name: "  FontSize="16"/>
<Label x:Name="firstName"
Text="{Binding Name}"
FontSize="16"
TextColor="Red"/>
</StackLayout>

<StackLayout Orientation="Horizontal">
<Label Text="Age: "  FontSize="16"/>
<Label x:Name="middleName"
Text="{Binding Age}"
FontSize="16"
TextColor="Red"/>
</StackLayout>
</StackLayout>
</ViewCell>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
</ContentPage.Content>
</ContentPage>

 

Step 13:

Look for code behind MemberList.xaml.cs, file and update your code with this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Xamarin.Forms;

namespace SQLiteTutorial
{
public partial class MemberList : ContentPage
{
public MemberDatabase memberDatabase;
public MemberList()
{
InitializeComponent();
memberDatabase = new MemberDatabase();
var members = memberDatabase.GetMembers();
listMembers.ItemsSource = members;

// BindingContext = this;
}

public async void OnSelected(object obj, ItemTappedEventArgs args)
{
var member = args.Item as Member;
await DisplayAlert("You selected", member.Name + " " + member.Age, "OK");
}
}
}

 

Step 14:

Run the application in your android or windows devices/emulators. When you insert members and then view them, then you will be navigated to the Member List Page and see the following output.

Android Screenshot

Windows Screenshot

Note: Since whole project has very high size. So download the Portable Class Library (PCL) only from Xamarin.Forms — GitHub.

---

That’s all from Basic SQLite Operations folks. Keep visiting for more on Xamarin.
Till next, Happy Coding!! 🙂