In Brief:
SQLite is the most widely used backend for Android and iOS mobile application. SQLite is a light weight relational database stores a data to a text file on a device.In this post i'll create a sample iOS application to perform an Async SQLite CRUD operations.
In Detail:
There are many way to store data in mobile application like in shared preference,user default, text/json and Xml file etc.
Whenever user need to store large data, SQLite is the better option.
It is best practice to communicate with the database with the background thread without blocking the UI thread.
Here i'm using xamarin Sqlite component to perform the Async CRUD operation. In this example i'm adding the new contact entry to phonebook,update the existing contact and deleting a contact.
Video Demo :
Table structure (PhoneContact table) :
----------------------------------------------------------------------
int | Id | PrimaryKey | AutoIncrement
----------------------------------------------------------------------
string | strContactName |
----------------------------------------------------------------------
long | strContactNumber |
----------------------------------------------------------------------
In steps:
Step 1: Prepare the UI part for display and edit phone contact. I have done the following things on storyboard.
Step 2: Prepare PhoneContact Class
Define class with Phone contact property as follows,
using SQLite;
namespace SqliteDemo
{
public class PhoneContactClass
{
[PrimaryKey, AutoIncrement]
public int Id{ get; set;}
[NotNull]
public string strContactName{get;set;}
public long strContactNumber{ get; set;}
}
}
Step 3: Add SQLite Component
Add SQLite component provided by the Krueger Systems, Inc. to the project.
To support async operation need instantiate SQLiteAsyncConnection class.
using SQLite;
namespace SqliteDemo
{
public static class DbConnectionClass
{
static SQLiteAsyncConnection sqliteAsyncConnection;
public static SQLiteAsyncConnection FnGetConnection()
{
if ( sqliteAsyncConnection == null )
sqliteAsyncConnection = new SQLiteAsyncConnection (System.IO.Path.Combine(ConstantsClass.strDbFolderPath,ConstantsClass.strDatabaseName ));
return sqliteAsyncConnection;
}
}
}
Step 5.) Initialize the async connection object and create table.
Declare the async connection object once and throughout the viewcontroller only this single connection object is used for the SQlite connectivity.
CreateTableAsync creates the table of specified type if the table does not exist.
SQLiteAsyncConnection sqlAsyncConnection;
sqlAsyncConnection = DbConnectionClass.FnGetConnection ();
sqlAsyncConnection.CreateTableAsync<phonecontactclass> ();
Step 6: CRUD operations
6.a)Inserting new record:
Insert a new record of type PhoneContactLocalClass. It returns no of rows affected.
async Task<int>FnInsertRecord(string strContactName,long lngContactNumber)
{
objPhoneContactLocalClass = new PhoneContactClass ();
objPhoneContactLocalClass.strContactName = strContactName;
objPhoneContactLocalClass.strContactNumber = lngContactNumber;
int intRow=await sqlAsyncConnection.InsertAsync ( objPhoneContactLocalClass );
objPhoneContactLocalClass=null;
return intRow;
}
6.b)Read Record:Fetch the record in alphabetical ascending order of a contact name.
//to read all record
async Task<List<PhoneContactClass>> FnGetAllContactList()
{
var lstAllContact=await sqlAsyncConnection.QueryAsync<PhoneContactClass> ( "select Id,strContactName,strContactNumber from PhoneContactClass order by strContactName COLLATE NOCASE ASC" );
FnStopActivityIndicator ();
return lstAllContact;
}
//to read matching record with string
async Task<List<PhoneContactClass>>FnGetContactList(string str)
{
var lstContact =await sqlAsyncConnection.Table<PhoneContactClass> ().Where ( v => v.strContactName.Contains ( str ) ).ToListAsync();
return lstContact;
}
6.c)Update record:
async Task<int> FnUpdateRecord(string strContactName,long lngContactNumber)
{
objPhoneContactClass.strContactName = strContactName;
objPhoneContactClass.strContactNumber = lngContactNumber;
string strQry=string.Format( "update PhoneContactClass set strContactName='{0}',strContactNumber={1} where Id={2}",objPhoneContactClass.strContactName,objPhoneContactClass.strContactNumber,objPhoneContactClass.Id);
int intRows= await sqlAsyncConnection.QueryAsync<phonecontactclass> ( strQry );
return intRows;
}
6.d)Delete record:
async Task <int> FnUpdateRecord()
{
int intRows = await sqlAsyncConnection.DeleteAsync ( objPhoneContactClass );
return intRows;
}
This is the code snippet for SQLite async CRUD operation. Below added the ViewController part of a project.
ViewController.cs
using System;
using SQLite;
using System.Collections.Generic;
using System.Threading.Tasks;
using Foundation;
using UIKit;
namespace SqliteDemo
{
[Foundation.Register ("ViewController")]
public partial class ViewController : UIViewController
{
SQLiteAsyncConnection sqlAsyncConnection;
PhoneContactClass objPhoneContactClass;
ContactListTableSource objContactListTableSource;
BusyIndicatorClass objBusyIndicator;
public ViewController ( IntPtr handle ) : base ( handle )
{
}
public override void ViewDidLoad ()
{
base.ViewDidLoad ();
FnTapEvents ();
FnInitializeView ();
}
public override void ViewWillAppear (bool animated)
{
base.ViewWillAppear (animated);
}
void FnTapEvents()
{
txtSearchBar.SearchButtonClicked +=async delegate(object sender , EventArgs e )
{
if(!string.IsNullOrEmpty(txtSearchBar.Text))
{
FnStartActivityIndicator();
var lstContactList= await FnGetContactList(txtSearchBar.Text);
FnStopActivityIndicator();
FnBindContactList(lstContactList);
}
};
txtSearchBar.TextChanged +=async delegate(object sender , UISearchBarTextChangedEventArgs e )
{
if(!string.IsNullOrEmpty(txtSearchBar.Text))
{
FnStartActivityIndicator();
var lstContactList= await FnGetContactList(txtSearchBar.Text);
FnStopActivityIndicator();
FnBindContactList(lstContactList);
}
};
btnRefreshContactList.TouchUpInside +=async delegate(object sender , EventArgs e )
{
FnStartActivityIndicator();
var contactList=await FnGetAllContactList ();
FnStopActivityIndicator();
FnBindContactList (contactList);
};
}
async void FnInitializeView()
{
FnStartActivityIndicator ();
sqlAsyncConnection = DbConnectionClass.FnGetConnection ();
tableViewContactsList.Hidden=true;
await sqlAsyncConnection.CreateTableAsync<PhoneContactClass> ();
var contactList=await FnGetAllContactList ();
FnStopActivityIndicator ();
FnBindContactList (contactList);
btnAddContact.SetBackgroundImage ( UIImage.FromBundle ( "Images/iconAdd" ) , UIControlState.Normal );
btnRefreshContactList.SetBackgroundImage ( UIImage.FromBundle ( "Images/iconRefreshImg" ) , UIControlState.Normal );
tableViewContactsList.Layer.CornerRadius = 10;
}
async Task<List<PhoneContactClass>> FnGetAllContactList()
{
var lstAllContact=await sqlAsyncConnection.QueryAsync<PhoneContactClass> ( "select Id,strContactName,strContactNumber from PhoneContactClass order by strContactName COLLATE NOCASE ASC" );
FnStopActivityIndicator ();
return lstAllContact;
}
async Task<List<PhoneContactClass>>FnGetContactList(string str)
{
var lstContact =await sqlAsyncConnection.Table<PhoneContactClass> ().Where ( v => v.strContactName.Contains ( str ) ).ToListAsync();
return lstContact;
}
void FnBindContactList(List<PhoneContactClass> lstContactList)
{
if ( lstContactList != null )
{
if(lstContactList.Count>0)
{
if ( objContactListTableSource != null )
{
objContactListTableSource.ConatctRowSelectedEventAction -= FnContactSelected;
objContactListTableSource = null;
}
tableViewContactsList.Hidden=false;
objContactListTableSource = new ContactListTableSource (lstContactList);
objContactListTableSource.ConatctRowSelectedEventAction += FnContactSelected;
tableViewContactsList.Source = objContactListTableSource;
tableViewContactsList.ReloadData ();
}
}
}
void FnContactSelected(PhoneContactClass _objPhoneContactClass)
{
objPhoneContactClass = _objPhoneContactClass;
PerformSegue ("EditContact",this);
}
void FnStartActivityIndicator()
{
objBusyIndicator =new BusyIndicatorClass(UIScreen.MainScreen.Bounds,ConstantsClass.strLoadingMessage);
Add ( objBusyIndicator );
}
void FnStopActivityIndicator()
{
if ( objBusyIndicator != null )
{
objBusyIndicator.Hide();
objBusyIndicator.RemoveFromSuperview();
objBusyIndicator=null;
}
}
//adding new comment
public override void PrepareForSegue (UIStoryboardSegue segue, NSObject sender)
{
base.PrepareForSegue (segue, sender);
if ( segue.Identifier.Equals ( "EditContact" ) )
{
var ContatctEditViewController = segue.DestinationViewController as NewContactViewController;
if ( ContatctEditViewController != null )
{
ContatctEditViewController.objPhoneContactClass = objPhoneContactClass;
}
}
}
}
}
NewContactViewController.cs
Browse the complete project here : https://github.com/suchithm/SqliteDemo.iOS/ Comment if any suggestion/Bugs. Thank you :) .
using System;
using SQLite;
using System.Threading.Tasks;
using UIKit;
namespace SqliteDemo
{
public partial class NewContactViewController : UIViewController
{
SQLiteAsyncConnection sqlAsyncConnection;
PhoneContactClass objPhoneContactLocalClass;
BusyIndicatorClass objBusyIndicator;
internal PhoneContactClass objPhoneContactClass{ get; set;}
public NewContactViewController (IntPtr handle) : base (handle)
{
}
public override void ViewDidLoad ()
{
base.ViewDidLoad ();
FnViewInitialize();
FnTapEvents();
}
void FnViewInitialize()
{
sqlAsyncConnection = DbConnectionClass.FnGetConnection ();
sqlAsyncConnection.CreateTableAsync<PhoneContactClass> ();
btnDeleteContact.Hidden = true;
if ( objPhoneContactClass != null )
{
txtContactName.Text = objPhoneContactClass.strContactName;
txtContactNumber.Text = objPhoneContactClass.strContactNumber.ToString();
btnDeleteContact.Hidden = false;
}
txtContactName.ShouldReturn += ( (textField ) => textField.ResignFirstResponder () );
txtContactNumber.ShouldReturn += ( (textField ) => textField.ResignFirstResponder () );
FnViewCustomization ();
}
void FnTapEvents()
{
btnDone.TouchUpInside += async delegate(object sender , EventArgs e )
{
try
{
var strValidationMsg=FnFieldValidation();
if(!string.IsNullOrEmpty( strValidationMsg))
{
AlertDialogClass.FnShowAlertDialog ( ConstantsClass.strAppName , strValidationMsg , ConstantsClass.strOkButtonText );
return;
}
if ( objPhoneContactClass == null )
{
FnStartActivityIndicator ();
int intRow=await FnInsertRecord ();
FnStopActivityIndicator ();
if ( intRow != 0 )
FnCancel ();
else
AlertDialogClass.FnShowAlertDialog ( ConstantsClass.strAppName , ConstantsClass.strExceptionMessage , ConstantsClass.strOkButtonText );
}
else
{
FnStartActivityIndicator ();
await FnUpdateRecord ();
FnStopActivityIndicator ();
FnCancel ();
}
}
catch
{
FnStopActivityIndicator ();
AlertDialogClass.FnShowAlertDialog ( ConstantsClass.strAppName , ConstantsClass.strExceptionMessage , ConstantsClass.strOkButtonText );
}
};
btnCancel.TouchUpInside += delegate(object sender , EventArgs e )
{
DismissViewController(true,null);
};
btnDeleteContact.TouchUpInside += async delegate(object sender , EventArgs e )
{
try
{
ButtonedAlertClass objButtonedAlert =null;
objButtonedAlert=new ButtonedAlertClass ();
int intButtonIndex = await objButtonedAlert.FnTwoButtonedAlertDialog ( ConstantsClass.strAppName ,ConstantsClass.strDeleteConfirmationText , ConstantsClass.strNegativeBtnText ,ConstantsClass.strPositiveBtnText );
if ( intButtonIndex == 1 )
{
FnStartActivityIndicator();
int intRows = await sqlAsyncConnection.DeleteAsync ( objPhoneContactClass );
FnStopActivityIndicator();
if ( intRows != 0 )
FnCancel ();
else
AlertDialogClass.FnShowAlertDialog ( ConstantsClass.strAppName , ConstantsClass.strExceptionMessage , ConstantsClass.strOkButtonText );
}
}
catch(Exception e2)
{
FnStopActivityIndicator();
Console.WriteLine(e2.Message);
AlertDialogClass.FnShowAlertDialog ( ConstantsClass.strAppName , ConstantsClass.strExceptionMessage , ConstantsClass.strOkButtonText );
}
};
}
async Task<int>FnInsertRecord()
{
objPhoneContactLocalClass = new PhoneContactClass ();
objPhoneContactLocalClass.strContactName = txtContactName.Text;
objPhoneContactLocalClass.strContactNumber =Convert.ToInt64( txtContactNumber.Text);
int intRow=await sqlAsyncConnection.InsertAsync ( objPhoneContactLocalClass );
objPhoneContactLocalClass=null;
return intRow;
}
async Task<int> FnUpdateRecord()
{
objPhoneContactClass.strContactName = txtContactName.Text;
objPhoneContactClass.strContactNumber = Convert.ToInt64 ( txtContactNumber.Text );
string strQry=string.Format( "update PhoneContactClass set strContactName='{0}',strContactNumber={1} where Id={2}",objPhoneContactClass.strContactName,objPhoneContactClass.strContactNumber,objPhoneContactClass.Id);
await sqlAsyncConnection.QueryAsync<PhoneContactClass> ( strQry );
return 0;
}
void FnCancel()
{
txtContactName.Text=string.Empty;
txtContactNumber.Text=string.Empty;
DismissViewController (true,null);
}
void FnStartActivityIndicator()
{
objBusyIndicator =new BusyIndicatorClass(UIScreen.MainScreen.Bounds,ConstantsClass.strLoadingMessage);
Add ( objBusyIndicator );
}
void FnStopActivityIndicator()
{
if ( objBusyIndicator != null )
{
objBusyIndicator.Hide();
objBusyIndicator.RemoveFromSuperview();
objBusyIndicator=null;
}
}
void FnViewCustomization()
{
viewFieldContainer.Layer.MasksToBounds = false;
viewFieldContainer.Layer.CornerRadius = 10;
viewFieldContainer.Layer.ShadowColor = UIColor.DarkGray.CGColor;
viewFieldContainer.Layer.ShadowOpacity = 1.0f;
viewFieldContainer.Layer.ShadowRadius = 6.0f;
viewFieldContainer.Layer.ShadowOffset = new System.Drawing.SizeF(0f, 3f);
txtContactName.Layer.CornerRadius=5;
txtContactNumber.Layer.CornerRadius = 5;
btnDeleteContact.Layer.CornerRadius = 5;
}
string FnFieldValidation()
{
if ( string.IsNullOrEmpty ( txtContactName.Text ) || string.IsNullOrEmpty ( txtContactNumber.Text ) )
{
return ConstantsClass.strMandatoryFields;
}
else if (!(txtContactNumber.Text.Length > 8 && txtContactNumber.Text.Length < 12) )
{
return ConstantsClass.strValidMobileNubmber;
}
else
{
return string.Empty;
}
}
}
}
Browse the complete project here : https://github.com/suchithm/SqliteDemo.iOS/ Comment if any suggestion/Bugs. Thank you :) .
No comments:
Post a Comment