Delpin Susai Raj Thursday, 21 February 2019

Xamarin.Forms - Bing Spell Check Using Cognitive Service

In this blog post, you will learn how to Detect and correct spelling mistakes in your app using Cognitive Service Bing Spell Check API in Xamarin forms.

Introduction



Xamarin.Forms code runs on multiple platforms - each of which has its own filesystem. This means that reading and writing files is most easily done using the native file APIs on each platform. Alternatively, embedded resources are a simpler solution to distribute data files with an app.

Cognitive Services

Xamarin and Cognitive Services together can infuse your apps, websites, and bots with intelligent algorithms to see, hear, speak, understand and interpret your user needs through natural methods of communication. Also, they help you transform your business with AI today.

Use AI to solve business problems

  • Vision
  • Speech
  • Knowledge
  • Search
  • Language

Bing Spell Check API
  1. Bing Spell Check API help users correct spelling errors, recognise the difference among names, brand names and slang, as well as understand homophones as they are typing.
  2. Bing Spell Check API has developed a web-based spell-checker.
  3. Bing Spell Check API returns a list of words it does recognize with suggested replacements.


Prerequisites

  • Visual Studio 2017 or Later(Windows or Mac)
  • Bing Spell Check API Key

Setting up a Xamarin.Forms Project

Start by creating a new Xamarin.Forms project. You’ll learn more by going through the steps yourself.
Visual Studio 2019 has more options in the opening window. Clone or check out the code from any repository or, open a project or solution for your computer.
Now, you need to click "Create a new project".

Now, filter by Project Type: Mobile
Choose the Mobile App (Xamarin. forms) project under C# and Mobile.
 Name your app. You probably want your project and solution to use the same name as your app. Put it on your preferred location for projects and click "Create".
 Now, select the blank app and target platforms - Android, iOS and Windows (UWP).

Subsequently, go to the solution. In there, you get all the files and sources of your project (.NET Standard). Now, select XAML page and double-click to open the MainPage.Xaml page.
 You now have a basic Xamarin.Forms app. Click the Play button to try it out.

Get Bing Spell Check API Key

 In this step, get Bing Spell Check API Key. Go to the following link.
https://azure.microsoft.com/en-in/services/cognitive-services/

Click "Try Cognitive Services for free".


Now, you can choose Bing Spell Check API under Language APIs. Afterward, click "Get API Key".

Now, the API key is activated. You can use it now.
Note
The trial key is available only for 7 days.

Setting up the User Interface

Go to MainPage.Xaml and write the following code.
MainPage.xaml

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
xmlns:local="clr-namespace:XamarinCognitive"
x:Class="XamarinCognitive.MainPage">
<StackLayout>
<StackLayout>
<StackLayout HorizontalOptions="Center" VerticalOptions="Start">
<Image x:Name="imgBanner" Source="banner.png" ></Image>
<Image Margin="0,0,0,10" x:Name="imgCognitive" HeightRequest="100" Source="cognitiveservice.png" ></Image>
<Label Margin="0,0,0,10" Text="Bing Spell Check" FontAttributes="Bold" FontSize="Large" TextColor="Gray" HorizontalTextAlignment="Center" ></Label>
<Entry x:Name="txtSearch" Placeholder="Type here.."></Entry>
<Button x:Name="btnCheck" WidthRequest="50" Text="Check" Clicked="BtnCheck_Clicked" />
<StackLayout HorizontalOptions="CenterAndExpand" Margin="10,0,0,10">
<ListView x:Name="suggestionsList">
</ListView>
</StackLayout>
</StackLayout>
</StackLayout>
</StackLayout>
</ContentPage>
view raw MainPage.xaml hosted with ❤ by GitHub

Click the play button to try it out.

NuGet Packages
Now, add the following NuGet Packages.
  • Newtonsoft.Json
Add Newtonsoft.Json NuGet
Go to Solution Explorer and select your solution. Right-click and select "Manage NuGet Packages for Solution". Search "Newtonsoft.Json" and add Package. Remember to install it(.NET Standard).

Create a Model
In this step, you can create a model for Deserializing your response.
ResponseModel.cs

using System;
using System.Collections.Generic;
using System.Text;
namespace XamarinCognitive
{
public class ResponseModel
{
public string _type { get; set; }
public List<FlaggedToken> flaggedTokens { get; set; }
}
public class Suggestion
{
public string suggestion { get; set; }
public double score { get; set; }
}
public class FlaggedToken
{
public int offset { get; set; }
public string token { get; set; }
public string type { get; set; }
public List<Suggestion> suggestions { get; set; }
}
}

Bing Spell Check.
In this step, write the following code for Bing Spell Check.
Note:
Try out the spell check capabilities with Bing Spell Search API v7.

  • ‘Spell’ is more aggressive in order to return better search results,
  • ‘Proof’ is less aggressive and adds capitalisation, basic punctuation and other features to aid document creation.
MainPage.xaml.cs

using Xamarin.Forms;
using Newtonsoft.Json;
using System.Net.Http;
using System.Net.Http.Headers;
namespace XamarinCognitive
{
public partial class MainPage : ContentPage
{
static string host = "https://api.cognitive.microsoft.com";
static string path = "/bing/v7.0/spellcheck?";
static string params_ = "mkt=en-US&mode=proof";
static string key = "cdf8476d88*******66b08e53";
public MainPage()
{
InitializeComponent();
}
private async void BtnCheck_Clicked(object sender, EventArgs e)
{
List<string> listSuggestions = new List<string>();
var responseString = await SpellCheck(txtSearch.Text);
var result = JsonConvert.DeserializeObject<ResponseModel>(responseString);
if(result.flaggedTokens.Count!=0)
{
foreach (var item in result.flaggedTokens[0].suggestions)
{
listSuggestions.Add(item.suggestion);
}
suggestionsList.ItemsSource = null;
suggestionsList.ItemsSource = listSuggestions;
}
}
//Bing Spell Check
public async static Task<string> SpellCheck(string text)
{
HttpClient client = new HttpClient();
client.DefaultRequestHeaders.Add("Ocp-Apim-Subscription-Key", key);
HttpResponseMessage response = new HttpResponseMessage();
string uri = host + path + params_;
List<KeyValuePair<string, string>> values = new List<KeyValuePair<string, string>>();
values.Add(new KeyValuePair<string, string>("text", text));
using (FormUrlEncodedContent content = new FormUrlEncodedContent(values))
{
content.Headers.ContentType = new MediaTypeHeaderValue("application/x-www-form-urlencoded");
response = await client.PostAsync(uri, content);
}
string client_id;
if (response.Headers.TryGetValues("X-MSEdge-ClientID", out IEnumerable<string> header_values))
{
client_id = header_values.First();
Console.WriteLine("Client ID: " + client_id);
}
string contentString = await response.Content.ReadAsStringAsync();
return contentString;
}
}
}

Click the Play button to try it out.

I hope you have understood how to Detect and correct spelling mistakes in your app using Cognitive Service Bing Spell Check API in Xamarin.Forms.

More Sample from Github 

Thanks for reading. Please share comments and feedback. Happy Coding :)

Delpin Susai Raj Thursday, 7 February 2019

Xamarin.Forms - Working with LiteDB CRUD Operations

In this blog post , you will learn how to use LiteDB Database with CRUD Operations in Xamarin.Forms.


Introduction




Xamarin.Forms code runs on multiple platforms - each of which has its own filesystem. This means that reading and writing files is most easily done using the native file APIs on each platform. Alternatively, embedded resources are a simpler solution to distribute data files with an app.

LiteDB



  1. LiteDB is serverless database. fully written in C# code.
  2. LiteDB stores documents in the BSON (Binary JSON) data format like MongoDB.
  3. LiteDB is a simple and fast NoSQL database. use to build Mobile, Desktop and small web applications. 



Prerequisites


  • Visual Studio 2017 or Later(Windows or Mac)


Setting up a Xamarin.Forms Project

Start by creating a new Xamarin.Forms project. You’ll learn more by going through the steps yourself.or download source here

https://github.com/susairajs/XamarinForms-LiteDB

Visual Studio 2019 has more options in the opening window. Clone or check out the code from any repository or, open a project or solution for your computer.

Now, you need to click "Create a new project".




Now, filter by Project Type: Mobile
Choose the Mobile App (Xamarin. forms) project under C# and Mobile.

Name your app. You probably want your project and solution to use the same name as your app. Put it on your preferred location for projects and click "Create".

Now, select the blank app and target platforms - Android, iOS and Windows (UWP).
Subsequently, go to the solution. In there, you get all the files and sources of your project (.NET Standard). Now, select XAML page and double-click to open the MainPage.Xaml page.

You now have a basic Xamarin.Forms app. Click the Play button to try it out.


Setting up the User Interface

Go to MainPage.Xaml and write the following code.

MainPage.xaml

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
xmlns:local="clr-namespace:XamarinLiteDB"
x:Class="XamarinLiteDB.MainPage">
<StackLayout>
<StackLayout>
<StackLayout HorizontalOptions="Center" VerticalOptions="Start">
<Image x:Name="imgBanner" Source="banner.png" ></Image>
<Image Margin="0,0,0,10" HeightRequest="100" Source="liteDB.png" ></Image>
<Label Margin="0,0,0,10" Text="LiteDB" FontAttributes="Bold" FontSize="Large" TextColor="Gray" HorizontalTextAlignment="Center" ></Label>
<Entry x:Name="txtPersonId" Placeholder="PersonId Update and Delete"></Entry>
<Entry x:Name="txtName" Placeholder="Enter Person Name"></Entry>
<StackLayout HorizontalOptions="CenterAndExpand" Orientation="Horizontal">
<Button x:Name="btnAdd" WidthRequest="200" Text="Add" Clicked="BtnAdd_Clicked" />
<Button x:Name="btnRead" WidthRequest="200" Text="Read" Clicked="BtnRead_Clicked" />
</StackLayout>
<StackLayout HorizontalOptions="CenterAndExpand" Orientation="Horizontal">
<Button x:Name="btnUpdate" WidthRequest="200" Text="Update" Clicked="BtnUpdate_Clicked" />
<Button x:Name="btnDelete" WidthRequest="200" Text="Delete" Clicked="BtnDelete_Clicked" />
</StackLayout>
<ListView x:Name="lstPersons">
<ListView.ItemTemplate>
<DataTemplate>
<TextCell Text="{Binding Name}" Detail="{Binding PersonId}"></TextCell>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
</StackLayout>
</StackLayout>
</StackLayout>
</ContentPage>
view raw MainPage.xaml hosted with ❤ by GitHub


Click the Play button to try it out.



NuGet Packages

Now, add the following NuGet Packages.


  • LiteDB


Add LiteDB NuGet

Go to Solution Explorer and select your solution. Right-click and select "Manage NuGet Packages for Solution". Search "LiteDB" and add Package. Remember to install it for .NET Standard project.



Create a Model

In this step, you can create a model to Create a Table.

Person.cs

namespace XamarinLiteDB
{
public class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
}
}
view raw Person.cs hosted with ❤ by GitHub




Get Local File Path

Now, Write the following code to get local file path for storing the database in App.xaml.cs

App.xaml.cs

static LiteDBHelper db;
public static LiteDBHelper LiteDB
{
get
{
if (db == null)
{
db = new LiteDBHelper(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "XamarinLiteDB.db"));
}
return db;
}
}
view raw App.xaml.cs hosted with ❤ by GitHub



Create a Table 

In this step, Write the following code to create a LiteCollection and create the table in LiteDBHelper.cs constructor.

LiteDBHelper.cs

protected LiteCollection<Person> personCollection;
public LiteDBHelper(string dbPath)
{
using (var db = new LiteDatabase(dbPath))
{
personCollection = db.GetCollection<Person>("Persons");
}
}
view raw LiteDBHelper.cs hosted with ❤ by GitHub



Check Database 

if you need to explore your LiteDB database download LiteDBViewer 

https://github.com/falahati/LiteDBViewer/releases

Example 



Read All

Now, write the code to read all data from LiteDB Database.

LiteDBHelper.cs

//Get All Persons
public List<Person> GetAllPersons()
{
var persons= new List<Person>(personCollection.FindAll());
return persons;
}
view raw LiteDBHelper.cs hosted with ❤ by GitHub


MainPage.Xaml.cs

//Get All Persons
var personList=App.LiteDB.GetAllPersons();
if(personList.Count!=0)
{
lstPersons.ItemsSource = personList;
}



Click the Play button to try it out.




Insert

Now, write the following code to insert data into LiteDB Database.

//Add New Person
public void AddPerson(Person person)
{
personCollection.Insert(person);
}
private void BtnAdd_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtName.Text))
{
Person person = new Person()
{
Name = txtName.Text
};
//Add New Person
App.LiteDB.AddPerson(person);
txtName.Text = string.Empty;
DisplayAlert("Success", "Person Added", "OK");
//Get All Persons
var personList = App.LiteDB.GetAllPersons();
if (personList.Count != 0)
{
lstPersons.ItemsSource = personList;
}
}
}



Click the Play button to try it out.




Read

Now, write the following code to read data from LiteDB Database.

//Get Person
public Person GetPerson(int personId)
{
var person = GetAllPersons().Where(a => a.PersonId == personId).FirstOrDefault();
return person;
}
private void BtnRead_Clicked(object sender, EventArgs e)
{
if(!string.IsNullOrEmpty(txtPersonId.Text))
{
var person= App.LiteDB.GetPerson(Convert.ToInt32(txtPersonId.Text));
if(person!=null)
{
txtName.Text = person.Name;
DisplayAlert("Success", "Person Name: "+person.Name, "OK");
}
else
{
DisplayAlert("Faild", "Person is not available", "OK");
}
}
}



Click the Play button to try it out.




Update

Now, write the following code to update data to LiteDB Database.

//Update Person
public void UpdatePerson(Person person)
{
personCollection.Update(person);
}
private void BtnUpdate_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtPersonId.Text))
{
Person person = new Person()
{
PersonId = Convert.ToInt32(txtPersonId.Text),
Name = txtName.Text
};
//Update person
App.LiteDB.UpdatePerson(person);
txtName.Text = string.Empty;
txtPersonId.Text = string.Empty;
DisplayAlert("Success", "Person Updated", "OK");
//Get All Persons
var personList = App.LiteDB.GetAllPersons();
if (personList.Count != 0)
{
lstPersons.ItemsSource = personList;
}
}
}



Click the Play button to try it out.




Delete

Now, write the following code to delete data from LiteDB Database.

//Delete Person
public void DeletePerson(int personId)
{
personCollection.Delete(a => a.PersonId == personId);
}
private void BtnDelete_Clicked(object sender, EventArgs e)
{
if(!string.IsNullOrEmpty(txtPersonId.Text))
{
//Delete Person
App.LiteDB.DeletePerson(Convert.ToInt32(txtPersonId.Text));
txtPersonId.Text = string.Empty;
DisplayAlert("Warning", "Person Deleted!", "OK");
//Get All Persons
var personList = App.LiteDB.GetAllPersons();
if (personList.Count != 0)
{
lstPersons.ItemsSource = personList;
}
}
}


Click the Play button to try it out.




Full code

LiteDBHelper.cs

using LiteDB;
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
namespace XamarinLiteDB
{
public class LiteDBHelper
{
protected LiteCollection<Person> personCollection;
public LiteDBHelper(string dbPath)
{
using (var db = new LiteDatabase(dbPath))
{
personCollection = db.GetCollection<Person>("Persons");
}
}
//Get All Persons
public List<Person> GetAllPersons()
{
var persons= new List<Person>(personCollection.FindAll());
return persons;
}
//Add New Person
public void AddPerson(Person person)
{
personCollection.Insert(person);
}
//Update Person
public void UpdatePerson(Person person)
{
personCollection.Update(person);
}
//Get Person
public Person GetPerson(int personId)
{
var person = GetAllPersons().Where(a => a.PersonId == personId).FirstOrDefault();
return person;
}
//Delete Person
public void DeletePerson(int personId)
{
personCollection.Delete(a => a.PersonId == personId);
}
}
}
view raw LiteDBHelper.cs hosted with ❤ by GitHub


MainPage.Xaml.cs

using Xamarin.Forms;
namespace XamarinLiteDB
{
public partial class MainPage : ContentPage
{
public MainPage()
{
InitializeComponent();
//Get All Persons
var personList=App.LiteDB.GetAllPersons();
if(personList.Count!=0)
{
lstPersons.ItemsSource = personList;
}
}
private void BtnAdd_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtName.Text))
{
Person person = new Person()
{
Name = txtName.Text
};
//Add New Person
App.LiteDB.AddPerson(person);
txtName.Text = string.Empty;
DisplayAlert("Success", "Person Added", "OK");
//Get All Persons
var personList = App.LiteDB.GetAllPersons();
if (personList.Count != 0)
{
lstPersons.ItemsSource = personList;
}
}
}
private void BtnRead_Clicked(object sender, EventArgs e)
{
if(!string.IsNullOrEmpty(txtPersonId.Text))
{
var person= App.LiteDB.GetPerson(Convert.ToInt32(txtPersonId.Text));
if(person!=null)
{
txtName.Text = person.Name;
DisplayAlert("Success", "Person Name: "+person.Name, "OK");
}
else
{
DisplayAlert("Faild", "Person is not available", "OK");
}
}
}
private void BtnUpdate_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtPersonId.Text))
{
Person person = new Person()
{
PersonId = Convert.ToInt32(txtPersonId.Text),
Name = txtName.Text
};
//Update person
App.LiteDB.UpdatePerson(person);
txtName.Text = string.Empty;
txtPersonId.Text = string.Empty;
DisplayAlert("Success", "Person Updated", "OK");
//Get All Persons
var personList = App.LiteDB.GetAllPersons();
if (personList.Count != 0)
{
lstPersons.ItemsSource = personList;
}
}
}
private void BtnDelete_Clicked(object sender, EventArgs e)
{
if(!string.IsNullOrEmpty(txtPersonId.Text))
{
//Delete Person
App.LiteDB.DeletePerson(Convert.ToInt32(txtPersonId.Text));
txtPersonId.Text = string.Empty;
DisplayAlert("Warning", "Person Deleted!", "OK");
//Get All Persons
var personList = App.LiteDB.GetAllPersons();
if (personList.Count != 0)
{
lstPersons.ItemsSource = personList;
}
}
}
}
}



I hope you have understood, how to use LiteDB Database with CRUD Operations in Xamarin.Forms. Thanks for reading. Please share your comments and feedback.

Happy Coding :)
Delpin Susai Raj Wednesday, 6 February 2019

Xamarin.Forms - SQLite Database CRUD Operations

In this blog post, you will learn how to use SQLite Database with CRUD Operations in Xamarin.Forms.


Introduction



Xamarin.Forms code runs on multiple platforms - each of which has its own filesystem. This means that reading and writing files is most easily done using the native file APIs on each platform. Alternatively, embedded resources are a simpler solution to distribute data files with an app.

SQLite


  1. SQLite is a Light weight database that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. 
  2. SQLite is the most used database in the world. It is built into all mobile phones.



Prerequisites


  • Visual Studio 2017 or Later(Windows or Mac)


Setting up a Xamarin.Forms Project

Start by creating a new Xamarin.Forms project. You’ll learn more by going through the steps yourself.or download source here

https://github.com/susairajs/XamarinForms-SQLite

Visual Studio 2019 has more options in the opening window. Clone or check out the code from any repository or, open a project or solution for your computer.

Now, you need to click "Create a new project".



Now, filter by Project Type: Mobile
Choose the Mobile App (Xamarin. forms) project under C# and Mobile.

Name your app. You probably want your project and solution to use the same name as your app. Put it on your preferred location for projects and click "Create".

Now, select the blank app and target platforms - Android, iOS and Windows (UWP).
Subsequently, go to the solution. In there, you get all the files and sources of your project (.NET Standard). Now, select XAML page and double-click to open the MainPage.Xaml page.

You now have a basic Xamarin.Forms app. Click the Play button to try it out.


Setting up the User Interface

Go to MainPage.Xaml and write the following code.

MainPage.xaml

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
xmlns:local="clr-namespace:XamarinSQLite"
x:Class="XamarinSQLite.MainPage">
<StackLayout>
<StackLayout>
<StackLayout HorizontalOptions="Center" VerticalOptions="Start">
<Image x:Name="imgBanner" Source="banner.png" ></Image>
<Image Margin="0,0,0,10" HeightRequest="100" Source="SQLite.png" ></Image>
<Label Margin="0,0,0,10" Text="SQLite" FontAttributes="Bold" FontSize="Large" TextColor="Gray" HorizontalTextAlignment="Center" ></Label>
<Entry x:Name="txtPersonId" Placeholder="PersonId Update and Delete"></Entry>
<Entry x:Name="txtName" Placeholder="Enter Person Name"></Entry>
<StackLayout HorizontalOptions="CenterAndExpand" Orientation="Horizontal">
<Button x:Name="btnAdd" WidthRequest="200" Text="Add" Clicked="BtnAdd_Clicked" />
<Button x:Name="btnRead" WidthRequest="200" Text="Read" Clicked="BtnRead_Clicked" />
</StackLayout>
<StackLayout HorizontalOptions="CenterAndExpand" Orientation="Horizontal">
<Button x:Name="btnUpdate" WidthRequest="200" Text="Update" Clicked="BtnUpdate_Clicked"/>
<Button x:Name="btnDelete" WidthRequest="200" Text="Delete" Clicked="BtnDelete_Clicked" />
</StackLayout>
<ListView x:Name="lstPersons">
<ListView.ItemTemplate>
<DataTemplate>
<TextCell Text="{Binding Name}" Detail="{Binding PersonID}"></TextCell>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
</StackLayout>
</StackLayout>
</StackLayout>
</ContentPage>
view raw MainPage.xaml hosted with ❤ by GitHub



Click the Play button to try it out.



NuGet Packages

Now, add the following NuGet Packages.


  • sqlite-net-pc


Add sqlite-net-pc NuGet

Go to Solution Explorer and select your solution. Right-click and select "Manage NuGet Packages for Solution". Search "sqlite-net-pc" and add Package. Remember to install it for each project (.NET Standard, Android, iO, and UWP).



Create a Model

In this step, you can create a model for create a table.

Person.cs

namespace XamarinSQLite
{
public class Person
{
[PrimaryKey, AutoIncrement]
public int PersonID { get; set; }
public string Name { get; set; }
}
}
view raw Person.cs hosted with ❤ by GitHub


Get Local File Path

Now, Write the following code to get local file path for storing the database in App.xaml.cs

App.xaml.cs

static SQLiteHelper db;
public static SQLiteHelper SQLiteDb
{
get
{
if (db == null)
{
db = new SQLiteHelper(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "XamarinSQLite.db3"));
}
return db;
}
}
view raw App.xaml.cs hosted with ❤ by GitHub



Create a Table 

In this step, Write the following code to create a SQLite Connection and create the table in SQLiteHelper.cs constructor.

SQLiteHelper.cs

SQLiteAsyncConnection db;
public SQLiteHelper(string dbPath)
{
db = new SQLiteAsyncConnection(dbPath);
db.CreateTableAsync<person>().Wait();
}
view raw SQLiteHelper.cs hosted with ❤ by GitHub




Read All

Now, write the code to read all data from SQLite Database.

SQLiteHelper.cs

//Read All Items
public Task<list erson="">&gt; GetItemsAsync()
{
return db.Table<person>().ToListAsync();
}
view raw SQLiteHelper.cs hosted with ❤ by GitHub


MainPage.Xaml.cs

protected async override void OnAppearing()
{
base.OnAppearing();
//Get All Persons
var personList = await App.SQLiteDb.GetItemsAsync();
if(personList!=null)
{
lstPersons.ItemsSource = personList;
}
}


Click the Play button to try it out.



Insert

Now, write the following code to insert data into SQLite Database.

//Insert and Update new record
public Task<int> SaveItemAsync(Person person)
{
if (person.PersonID != 0)
{
return db.UpdateAsync(person);
}
else
{
return db.InsertAsync(person);
}
}
private async void BtnAdd_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtName.Text))
{
Person person = new Person()
{
Name = txtName.Text
};
//Add New Person
await App.SQLiteDb.SaveItemAsync(person);
txtName.Text = string.Empty;
await DisplayAlert("Success", "Person added Successfully", "OK");
//Get All Persons
var personList = await App.SQLiteDb.GetItemsAsync();
if (personList != null)
{
lstPersons.ItemsSource = personList;
}
}
else
{
await DisplayAlert("Required", "Please Enter name!", "OK");
}
}




Click the Play button to try it out.




Read

Now, write the following code to read data from SQLite Database.

//Read Item
public Task<person> GetItemAsync(int personId)
{
return db.Table<person>().Where(i =&gt; i.PersonID == personId).FirstOrDefaultAsync();
}
private async void BtnRead_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtPersonId.Text))
{
//Get Person
var person = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtPersonId.Text));
if(person!=null)
{
txtName.Text = person.Name;
await DisplayAlert("Success","Person Name: "+ person.Name, "OK");
}
}
else
{
await DisplayAlert("Required", "Please Enter PersonID", "OK");
}
}



Click the Play button to try it out.




Update

Now, write the following code to update data to SQLite Database.

//Insert and Update new record
public Task<int> SaveItemAsync(Person person)
{
if (person.PersonID != 0)
{
return db.UpdateAsync(person);
}
else
{
return db.InsertAsync(person);
}
}
private async void BtnUpdate_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtPersonId.Text))
{
Person person = new Person()
{
PersonID=Convert.ToInt32(txtPersonId.Text),
Name = txtName.Text
};
//Update Person
await App.SQLiteDb.SaveItemAsync(person);
txtPersonId.Text = string.Empty;
txtName.Text = string.Empty;
await DisplayAlert("Success", "Person Updated Successfully", "OK");
//Get All Persons
var personList = await App.SQLiteDb.GetItemsAsync();
if (personList != null)
{
lstPersons.ItemsSource = personList;
}
}
else
{
await DisplayAlert("Required", "Please Enter PersonID", "OK");
}
}



Click the Play button to try it out.




Delete

Now, write the following code to delete data from SQLite Database.

//Delete
public Task<int> DeleteItemAsync(Person person)
{
return db.DeleteAsync(person);
}
private async void BtnDelete_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtPersonId.Text))
{
//Get Person
var person = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtPersonId.Text));
if (person != null)
{
//Delete Person
await App.SQLiteDb.DeleteItemAsync(person);
txtPersonId.Text = string.Empty;
await DisplayAlert("Success", "Person Deleted", "OK");
//Get All Persons
var personList = await App.SQLiteDb.GetItemsAsync();
if (personList != null)
{
lstPersons.ItemsSource = personList;
}
}
}
else
{
await DisplayAlert("Required", "Please Enter PersonID", "OK");
}
}



Click the Play button to try it out.




Full code

SQLiteHelper.cs

using System.Threading.Tasks;
using SQLite;
namespace XamarinSQLite
{
public class SQLiteHelper
{
SQLiteAsyncConnection db;
public SQLiteHelper(string dbPath)
{
db = new SQLiteAsyncConnection(dbPath);
db.CreateTableAsync<person>().Wait();
}
//Insert and Update new record
public Task<int> SaveItemAsync(Person person)
{
if (person.PersonID != 0)
{
return db.UpdateAsync(person);
}
else
{
return db.InsertAsync(person);
}
}
//Delete
public Task<int> DeleteItemAsync(Person person)
{
return db.DeleteAsync(person);
}
//Read All Items
public Task<list erson="">&gt; GetItemsAsync()
{
return db.Table<person>().ToListAsync();
}
//Read Item
public Task<person> GetItemAsync(int personId)
{
return db.Table<person>().Where(i =&gt; i.PersonID == personId).FirstOrDefaultAsync();
}
}
}
view raw SQLiteHelper.cs hosted with ❤ by GitHub


MainPage.Xaml.cs

using Xamarin.Forms;
namespace XamarinSQLite
{
public partial class MainPage : ContentPage
{
public MainPage()
{
InitializeComponent();
}
protected async override void OnAppearing()
{
base.OnAppearing();
//Get All Persons
var personList = await App.SQLiteDb.GetItemsAsync();
if(personList!=null)
{
lstPersons.ItemsSource = personList;
}
}
private async void BtnAdd_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtName.Text))
{
Person person = new Person()
{
Name = txtName.Text
};
//Add New Person
await App.SQLiteDb.SaveItemAsync(person);
txtName.Text = string.Empty;
await DisplayAlert("Success", "Person added Successfully", "OK");
//Get All Persons
var personList = await App.SQLiteDb.GetItemsAsync();
if (personList != null)
{
lstPersons.ItemsSource = personList;
}
}
else
{
await DisplayAlert("Required", "Please Enter name!", "OK");
}
}
private async void BtnRead_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtPersonId.Text))
{
//Get Person
var person = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtPersonId.Text));
if(person!=null)
{
txtName.Text = person.Name;
await DisplayAlert("Success","Person Name: "+ person.Name, "OK");
}
}
else
{
await DisplayAlert("Required", "Please Enter PersonID", "OK");
}
}
private async void BtnUpdate_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtPersonId.Text))
{
Person person = new Person()
{
PersonID=Convert.ToInt32(txtPersonId.Text),
Name = txtName.Text
};
//Update Person
await App.SQLiteDb.SaveItemAsync(person);
txtPersonId.Text = string.Empty;
txtName.Text = string.Empty;
await DisplayAlert("Success", "Person Updated Successfully", "OK");
//Get All Persons
var personList = await App.SQLiteDb.GetItemsAsync();
if (personList != null)
{
lstPersons.ItemsSource = personList;
}
}
else
{
await DisplayAlert("Required", "Please Enter PersonID", "OK");
}
}
private async void BtnDelete_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtPersonId.Text))
{
//Get Person
var person = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtPersonId.Text));
if (person != null)
{
//Delete Person
await App.SQLiteDb.DeleteItemAsync(person);
txtPersonId.Text = string.Empty;
await DisplayAlert("Success", "Person Deleted", "OK");
//Get All Persons
var personList = await App.SQLiteDb.GetItemsAsync();
if (personList != null)
{
lstPersons.ItemsSource = personList;
}
}
}
else
{
await DisplayAlert("Required", "Please Enter PersonID", "OK");
}
}
}
}



I hope you have understood, how to use SQLite Database with CRUD Operations in Xamarin.Forms. Thanks for reading. Please share your comments and feedback.

Happy Coding :)