SQLite With Xamarin Forms Step by Step Guide

12,255,491 members ﴾58,031 online﴿articles Q&A forums Sign in lounge Search for articles, questions, tips SQLite with Xamarin Forms Step by Step guide S Ravi‐Kumar‐2012, 1 May 2016 CPOL Rate this:    4.11 ﴾2 votes﴿ In many mobile applications it&#8217;s a requirement to store data locally and for that purpose we use databases with the applications. The type of database [...] In many mobile applications it’s a requirement to store data locally and for that purpose we use databases with the applications. The type of database to use mainly depends upon the requirement of the application, but in most of MIS ﴾Management Information Systems﴿ based application relational databases are used for this purpose. The most commonly used relational database with Xamarin Forms is SQLite. It is the best suited relational database for mobile applications as it has very small footprint. This article will be an step by step guide on how to use a SQLite database with a Xamarin Forms application.We will be using the database to persist employee data. The structure of the database will be like following Diagram: Step‐1: create a new project in Xamarin/Visual Studio ﴾see this article for steps﴿. Name it as ”. There is no out of box tool available in Xamarin Forms, So we will be using a third party plugin for this purpose and the most popular plugin is ‘SQLite.Net‐ PCL’ created by oysteinkrog. Step‐2: In order to use ‘SQLite.Net‐PCL’ plugin, add the nuget package of the plugin to each project of the solution. In Visual Studio this can be done by right click on the solution and selecting ‘Manage NuGet Packages for Solution‘ option. Which will give following screen: Android: Hide   Shrink using System.      }  } Step‐4: Create Class file named ‘SQLiteService‘ implementing ‘ISQLite‘ interface and Write the implementation of this ‘GetConnection‘ in every platform specific code like following given codes: For new database: When we are creating a database in the application itself for the first time then following code will be used. Step‐3: Create a blank interface with one method signature like following code Hide   Copy Code using SQLite.  using SQLiteEx.Net. it can’t automatically initialize the database connection object as the location of the database file varies on different platforms.  using Xamarin.Net‐PCL‘ package will provide us the functionality of manipulating the SQLite database.Droid. Even tough the ‘SQLite.    Copy Code .  namespace SQLiteEx  {      public interface ISQLite      {          SQLiteConnection GetConnection(). So in order to solve this issue we will use dependency service to load the database file in connection object. you will have to individually add the NuGet package by right clicking on the ‘Packages’ folder and selecting ‘Add Packages’ option on each project.Forms.In case of Xamarin Studio.               var conn = new SQLite.Forms.Combine(documentsPath.Net.Exists(path))              {                  File.db3". path).              var conn = new SQLite.Net.  [assembly: Dependency(typeof(SqliteService))]  namespace SQLiteEx.              // Return the database connection               return conn.WriteLine(path).SQLiteConnection(plat.Create(path).          }          #endregion      }  } iOS: Hide   Shrink   Copy Code using SQLiteEx.SQLitePlatformIOS().iOS.Net. // Library folder              var path = Path.XamarinIOS.SpecialFolder.SpecialFolder.db3".IO.  using System.Combine(libraryPath. //  Documents folder              var path = Path.Net.              }              var plat = new SQLite.  [assembly: Dependency(typeof(SqliteService))]  namespace SQLiteEx.WriteLine(path).using System.SQLiteConnection GetConnection()          {              var sqliteFilename = "SQLiteEx.              if (!File.Personal).  using System.SQLiteConnection(plat. //  Documents folder              string libraryPath = Path.".              // This is where we copy in the prepopulated database              Console.              var plat = new SQLite. sqliteFilename).Platform. "Library").SQLiteConnection GetConnection()          {              var sqliteFilename = "SQLiteEx.              Console. sqliteFilename).Net.Net..IO.SQLitePlatformAndroid().Droid  {      public class SqliteService : ISQLite      {          public SqliteService() { }          #region ISQLite implementation          public SQLite.Create(path).Exists(path)) File. ".GetFolderPath(Environment. path).Personal).  using Xamarin.              string documentsPath = Environment.              string documentsPath = Environment.  .              if (!File.iOS  {      public class SqliteService : ISQLite      {          public SqliteService()          {          }          #region ISQLite implementation          public SQLite.GetFolderPath(Environment.XamarinAndroid.Combine(documentsPath.Platform.   .Net.APGameDb).        string documentsPath = System.Raw.Platform.Net.Environment.SQLiteConnection(plat.Exists(path))  {    var s = Forms.  using SQLiteEx.db3".Resources.SQLiteConnection GetConnection ()      {        var sqliteFilename = "SQLiteEx.Net.  FileAccess.OpenRawResource(Resource.            // Return the database connection               return conn.Droid.  [assembly: Dependency(typeof(SqliteService))]  namespace SQLiteEx.Write).  }  #endregion      ///  &lt. In such scenarios first copy the database file at following mentioned locations in platform specific project and then use following code in ‘SQLiteService’ Class Android: Copy the database file in ‘Resources\Raw’ folder of Android platform project.Context.  using Xamarin.XamarinAndroid.GetFolderPath  (System.SQLitePlatformAndroid().summary&gt.      /// helper method to get the database out of /raw/ and into the user filesystem      /// &lt.Environment.                                NAME ###  // This is where we copy in the prepopulated database  Console.          }          #endregion      }  } For Pre‐Created Database: In some application scenarios there may be a requirement that the database is pre‐populated with some data and we are going to use the same in the application.Personal). path).          // write to the stream          ReadWriteStream(s.  if (!File.OpenOrCreate. FileMode.IO.SpecialFolder./summary&gt.Forms.  // RESOURCE          // create a write stream          FileStream writeStream = new FileStream(path. Hide   Shrink   Copy Code using System.Droid  {        public class SqliteService : ISQLite {      public SqliteService () {}      #region ISQLite implementation        public SQLite. writeStream). // Documents folder        var path = Path. sqliteFilename).  var conn = new SQLite.WriteLine (path).Combine(documentsPath.        }              var plat = new SQLite.  using System.                    // Return the database connection     return conn.   }  iOS: Copy the database file in ‘Resources’ folder of Android platform project. "Library").    writeStream. Hide   Shrink   Copy Code using SQLiteEx. path).Net.              // This is where we copy in the prepopulated database              Console.iOS.Forms.SQLitePlatformIOS().          }          #endregion      }  } Step‐5: Create a class in PCL project named as ‘DataAccess‘.                                                          } }  void ReadWriteStream(Stream readStream.XamarinIOS.    Byte[] buffer = new Byte[Length].    int bytesRead = readStream.Personal).Close(). Length). 0)    {      writeStream.SQLiteConnection GetConnection()          {              var sqliteFilename = "SQLiteEx.Close(). This class will contain all the data access codes for the application.Net.WriteLine(path). .              }              var plat = new SQLite.    }    readStream.SQLiteConnection(plat.iOS  {      public class SqliteService : ISQLite      {          public SqliteService()          {          }          #region ISQLite implementation          public SQLite.  using System.              string documentsPath = Environment.Combine(libraryPath.              var conn = new SQLite.  [assembly: Dependency(typeof(SqliteService))]  namespace SQLiteEx.Platform.Net.    // write the required bytes    while (bytesRead &gt. bytesRead).Combine(documentsPath.IO.SpecialFolder.". // Library folder              var path = Path.Exists(path))              {                  File.      bytesRead = readStream.              if (!File.Read(buffer. path)..  using System.Write(buffer. sqliteFilename).              // Return the database connection               return conn.GetFolderPath(Environment. 0. //  Documents folder              string libraryPath = Path.db3".Read(buffer. Stream writeStream)  {    int Length = 256. 0. ".Copy(sqliteFilename. Length). 0.  using Xamarin.   namespace SQLiteEx  {      public class Employee      {          [PrimaryKey.Delete(aEmployee).  using System.(). For Example ‘Employee‘ class for the above given tables in ‘DataAccess‘ class.Net.  using Xamarin. }          public string Department          { get. Employee: Hide   Copy Code using SQLite.          }      }  } As it can be seen from the code above that the application is using dependency service to fetch the database and create the connection object in the constructor of the class which will be used in order to manipulate the data. set.CreateTable&lt. AutoIncrement]          public long EmpId          { get. this we will have to give even if we are using a pre‐created database as the method automatically checks for the table in database and create it if not present.Query<Employee>("Select * From [Employee]").(). }          [NotNull]          public string EmpName          { get.          }          public List<Employee> GetAllEmployees()          {              return dbConn.The code of the class is as follows: Hide   Shrink   Copy Code using SQLite.ISQLite&gt.                      }          public int DeleteEmployee(Employee aEmployee)          {              return dbConn.          }          public int SaveEmployee(Employee aEmployee)          {              return dbConn. }          public string Qualification  . }          public string Designation          { get. set.          }          public int EditEmployee(Employee aEmployee)          {              return dbConn. Step‐6: Create the Plain Old CLR Object ﴾POCO﴿ classes for the table﴾s﴿ present in the database.Insert(aEmployee).Update(aEmployee).Net.              // create the table(s)              dbConn.          public DataAccess()          {              dbConn = DependencyService.GetConnection(). set.  namespace SQLiteEx  {      public class DataAccess      {          SQLiteConnection dbConn.Employee&gt. set.Forms.Get&lt.Attributes. Secondly the code to create tables are there in constructor also.         { get. Hide   Shrink   Copy Code using Xamarin. Now let’s create the screens of our sample application to see the CRUD operation code. it will show the current list of Employees and give an option to add new employee. Manage Employees 2.The Application will contain following screens: 1.Forms.Xaml.Forms. Add Employee 3. view the details of the Employee and manage departments.          }          public static DataAccess DAUtil          {              get              {                  if (dbUtils == null)                  {                      dbUtils = new DataAccess().              }          }          protected override void OnStart()          {              // Handle when your app starts          }          protected override void OnSleep()          {              // Handle when your app sleeps          }          protected override void OnResume()          {              // Handle when your app resumes          }      }  } This completes the basic structure of application using a SQLite database to persist data of the application.  [assembly: XamlCompilation(XamlCompilationOptions.          public App()          {              // The root page of your application              MainPage = new NavigationPage(new ManageEmployee()). }      }  } Step‐7: Create the static object property of ‘DataAccess‘ in ‘App‘ class like following code. The XAML code of this page will be like : .Compile)]  namespace SQLiteEx  {      public class App : Application      {          static DataAccess dbUtils. Edit Employee Manage Employees: This screen is the home screen of the application. set.  using Xamarin. Show Employee Details 4.                  }                  return dbUtils. This will enable us to use manipulate the data from any screen of the application. GetAllEmployees().ItemTemplate>          <DataTemplate>            <ViewCell>              <StackLayout Orientation="Horizontal" Padding="5. 0.Hide   Shrink   Copy Code <?xml version="1.com/winfx/2009/xaml"               x:Class="SQLiteEx.HeaderTemplate>        <ListView. the application executes ‘OnSelection‘ method on ‘ItemSelected‘ event of list to show the detailed employee information.  namespace SQLiteEx  {      public partial class ManageEmployee : ContentPage      {          public ManageEmployee()          {              InitializeComponent().5.  using Xamarin.              lstData.microsoft.5.5. 20.Content>      <ListView x:Name="lstData" HasUnevenRows="false" Header="Header Value" Footer="Footer"   ItemSelected="OnSelection" >        <ListView.5.FooterTemplate>      </ListView>    </ContentPage.FooterTemplate>          <DataTemplate>            <StackLayout Orientation="Horizontal" Padding="5.Content>  </ContentPage> As per the above XAML.              var vList = App.com/schemas/2014/forms"               xmlns:x="http://schemas.Padding>      <OnPlatform x:TypeArguments="Thickness" iOS="0. Hide   Shrink using System.ItemTemplate>        <ListView.5">                <Label Text="{Binding EmpName}" FontSize="Medium" />                <Label Text="{Binding Designation}" FontSize="Medium" />                <Label Text="{Binding Department}" FontSize="Medium" />              </StackLayout>            </ViewCell>          </DataTemplate>        </ListView. SelectedItemChangedEventArgs e)          {    Copy Code .5.5">              <Button Text="Add New Employee" Clicked="OnNewClicked" />            </StackLayout>          </DataTemplate>        </ListView.HeaderTemplate>          <DataTemplate>            <StackLayout Orientation="Horizontal" BackgroundColor="Blue" Padding="5.Padding>    <ContentPage.5.DAUtil. ‘OnNewClicked‘ method on ‘Clicked‘ event of ‘Add New Employee‘ button.ManageEmployee" Title="Manage Employees" >    <ContentPage. The code behind of this page will contain following code.ItemsSource = vList. 0" />    </ContentPage.          }          void OnSelection(object sender.0" encoding="utf‐8" ?>  <ContentPage xmlns="http://xamarin.5">              <Label Text="Name" FontSize="Medium" FontAttributes="Bold" TextColor="White"/>              <Label Text="Designation" FontSize="Medium" FontAttributes="Bold" TextColor="White"/>              <Label Text="Department" FontSize="Medium" FontAttributes="Bold" TextColor="White"/>            </StackLayout>                 </DataTemplate>        </ListView.Forms. SelectedItem.0" WinPhone="0. The XAML code of this page will be like: Hide   Copy Code <?xml version="1.Content>  </ContentPage> The code behind of this page will contain following code: Hide   Copy Code using System.com/winfx/2009/xaml" x:Class="SQLiteEx.0" />                    </ContentView.  using Xamarin.PushAsync(new AddEmployee()).  .Forms.AddEmployee" Title="Add New  Employee">        <ContentView.15.          }          public void OnNewClicked(object sender.                   //ItemSelected is called on deselection.0.Content>                      <Button BackgroundColor="#fd6d6d" Text="Save" TextColor="White"  Clicked="OnSaveClicked" />                    </ContentView. EventArgs args)          {              Navigation.Content>          <TableView Intent="Settings" BackgroundColor="White">            <TableRoot>              <TableSection Title="Add New Employee">                <EntryCell x:Name="txtEmpName" Label="Employee Name" Keyboard="Text" />                <EntryCell x:Name="txtDesign" Label="Designation" Keyboard="Text" />                <EntryCell x:Name="txtDepartment" Label="Department" Keyboard="Text" />                <EntryCell x:Name="txtQualification" Label="Qualification" Keyboard="Text"  />                             <ViewCell>                  <ContentView Padding="0.SelectedItem == null)              {                  return.microsoft.Content>                  </ContentView>                </ViewCell>              </TableSection>            </TableRoot>          </TableView>        </ContentView.com/schemas/2014/forms"  xmlns:x="http://schemas.Padding>                      <OnPlatform x:TypeArguments="Thickness" iOS="10.  namespace SQLiteEx  {      public partial class AddEmployee : ContentPage      {          public AddEmployee()          {              InitializeComponent().Padding>                    <ContentView.          }      }  } Add Employee: As the name suggests this page will be used for adding a new employee and will be called upon click of ‘Add New Employee’ button on ‘Manage Employee’ page.              Navigation.                   //which results in SelectedItem being set to null              }              var vSelUser = (Employee)e.PushAsync(new ShowEmplyee(vSelUser)).0" encoding="utf‐8" ?>  <ContentPage xmlns="http://xamarin.            if (e.0">                    <ContentView. Row="4" Grid.ColumnDefinitions>          <ColumnDefinition Width="10"/>          <ColumnDefinition Width="*"/>          <ColumnDefinition Width="*"/>          <ColumnDefinition Width="10"/>        </Grid.Row ="4" Grid.Row="5" Grid.0" encoding="utf‐8" ?>  <ContentPage xmlns="http://xamarin.Column="0" Grid.              Navigation.Row ="0" Grid.SaveEmployee(vEmployee).Column="1" Text="Qualification" />        <Label Grid.Column="2" Text="Delete" Clicked="OnDeleteClicked" />      </Grid>    </ContentView.ColumnDefinitions>        <Label Grid.Row ="5" Grid.Row ="3" Grid.ColumnSpan="2" Text="Employee Details" />        <Label Grid.Column="1" Text="Department" />        <Label Grid.RowDefinitions>          <RowDefinition Height="*"/>          <RowDefinition Height="*"/>          <RowDefinition Height="*"/>          <RowDefinition Height="*"/>          <RowDefinition Height="*"/>          <RowDefinition Height="*"/>          <RowDefinition Height="*"/>        </Grid.RowDefinitions>        <Grid.Column="2" Text ="{Binding EmpName}" />        <Label Grid.Column="1" Text="Designation" />        <Label Grid.                  Department = txtDepartment. EventArgs args)          {              var vEmployee = new Employee()              {                  EmpName = txtEmpName.Row ="2" Grid.Column="1" Text="Edit Details" Clicked="OnEditClicked" />        <Button Grid.        }          public void OnSaveClicked(object sender.ShowEmplyee" Title="View  Employee">    <ContentView.          }      }  } Show Employee Details: As the name suggests. this page will be used to show the complete details of the employee and also will have the option to invoke ‘Edit Employee’ page and ‘Delete Employee’ to delete the details of employee from database.Text.Column="1" Text="Name" />        <Label Grid.              App.DAUtil.Column="2" Text ="{Binding Department}"/>        <Label Grid.Text              }.Text.Row="3" Grid.                  Qualification = txtQualification.PushAsync(new ManageEmployee()).Column="2" Text ="{Binding Qualification}" />        <Button Grid.Row="1" Grid.com/winfx/2009/xaml" x:Class="SQLiteEx.Text.Row ="1" Grid.Content>  </ContentPage> And the code behind of this page will contain following code: Hide   Shrink   Copy Code .microsoft. The XAML code of this page is like: Hide   Shrink   Copy Code <?xml version="1.Row="2" Grid.Content>      <Grid>        <Grid.com/schemas/2014/forms"  xmlns:x="http://schemas.Column="2" Text ="{Binding Designation}"/>        <Label Grid.                  Designation = txtDesign.  "Are you Sure ?".DeleteEmployee(mSelEmployee).Padding>                <ContentView.Content>              </ContentView>            </ViewCell>          </TableSection>  .Padding>                  <OnPlatform x:TypeArguments="Thickness" iOS="10.Content>      <TableView Intent="Settings" BackgroundColor="White">       <TableRoot>          <TableSection Title="Edit Employee">            <EntryCell x:Name="txtEmpName" Label="Employee Name" Text ="{Binding EmpName}"  Keyboard="Text" />            <EntryCell x:Name="txtDesign" Label="Designation" Text ="{Binding Designation}"  Keyboard="Text" />            <EntryCell x:Name="txtDepartment" Label="Department" Text ="{Binding Department}"  Keyboard="Text" />            <EntryCell x:Name="txtQualification" Label="Qualification" Text ="{Binding Qualification}"  Keyboard="Text" />            <ViewCell>              <ContentView Padding="0.0" />                </ContentView.PushAsync(new ManageEmployee()).              BindingContext = mSelEmployee. EventArgs args)          {              bool accepted = await DisplayAlert("Confirm". this page will be used to edit the details of the employee. "Yes". EventArgs args)          {              Navigation.using System.Forms.microsoft.EditEmployee" Title="Edit  Employee">    <ContentView.0" WinPhone="0.  namespace SQLiteEx  {      public partial class ShowEmplyee : ContentPage      {          Employee mSelEmployee.PushAsync(new EditEmployee(mSelEmployee)).              mSelEmployee = aSelectedEmp.0">                <ContentView. The XAML code of this page is like: Hide   Shrink   Copy Code <?xml version="1.          }      }  } Edit Employee: As the name suggests.15.DAUtil.              }              await Navigation.0.              if (accepted)              {                  App.  using Xamarin.          }          public async void  OnDeleteClicked(object sender.          public ShowEmplyee(Employee aSelectedEmp)          {              InitializeComponent().com/winfx/2009/xaml" x:Class="SQLiteEx. "No").0" encoding="utf‐8" ?>  <ContentPage xmlns="http://xamarin.Content>                  <Button BackgroundColor="#fd6d6d" Text="Save" TextColor="White"  Clicked="OnSaveClicked" />                </ContentView.com/schemas/2014/forms"  xmlns:x="http://schemas.          }          public void OnEditClicked(object sender. Happy Coding Reference : Xamarin Forms Documentation. is licensed under The Code Project Open License ﴾CPOL﴿ Share EMAIL About the Author TWITTER .              mSelEmployee.Text.  using Xamarin.          public EditEmployee(Employee aSelectedEmp)          {              InitializeComponent(). License This article.Designation = txtDesign. Let me know if I has missed anything or have any suggestions.      </TableRoot>      </TableView>    </ContentView.Text.PushAsync(new ManageEmployee()). this article contains step by step guide to use SQLite database in Xamarin Forms mobile application. along with any associated source code and files.Content>  </ContentPage> And the code behind of this page will contain following code: Hide   Copy Code using System.DAUtil.Department = txtDepartment.Text.          }      }  } As mentioned earlier in the article.              mSelEmployee = aSelectedEmp.Text.          }          public void OnSaveClicked(object sender.  namespace SQLiteEx  {      public partial class EditEmployee : ContentPage      {          Employee mSelEmployee.Qualification = txtQualification. EventArgs args)          {              mSelEmployee.              BindingContext = mSelEmployee. The example code of this article can be found at GitHub.              mSelEmployee.EditEmployee(mSelEmployee).EmpName = txtEmpName.              Navigation.              App.              mSelEmployee.Forms. WPF/Silverlight: Step By Step Guide to MVVM Microsoft Guide to Building Great Apps Step by Step Guide to Delicious OAuth API SAPrefs .. I am an IT professional with 14 years of experience in architecting.Netscape-like Preferences Dialog Step by Step Guide to Trace the ASP. Search Comments   Go   .. The articles here are sourced from my blog : http://err2solution.S Ravi‐Kumar‐2012 Architect India Hi There.NET Application for Beginners Generate and add keyword variations using AdWords API Comments and Discussions   You must Sign In to use this message board.com/ You may also be interested in. Currently working in an multinational company in India as Solutions Architect. designing and building IT solutions for complex business needs in form of mobile & web applications using Microsoft technologies. 1999‐2016 .8.‐‐ There are no messages in this forum ‐‐ Permalink | Advertise | Privacy | Terms of Use | Mobile Web02 | 2.1 | Last Updated 2 May 2016 Seleccionar idioma Layout: fixed | fluid ▼ Article Copyright 2016 by S Ravi‐Kumar‐2012 Everything else Copyright © CodeProject.160426.