SQLite Async operation in Xamarin.iOS application

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 my previous post shared my thought about How to play youtube video in Xamarin.Android, How to implement sliding menu in Xamarin android and in iOS, Best Practice and issues with ListView in Android Xamarin, How to avoid ImageBitmap OutOfMemoryException and Rounded corner Image in android Xamarin,Drawing path between two location in xamarin android.

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,
1
2
3
4
5
6
7
8
9
10
11
12
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.

Step 4: Create SQLite connection class.
To support async operation need instantiate SQLiteAsyncConnection class.
using SQLite;
1
2
3
4
5
6
7
8
9
10
11
12
13
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.
1
2
3
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.
1
2
3
4
5
6
7
8
9
10
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
//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)
1
2
3
4
5
6
7
{
 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: 
1
2
3
4
5
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
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 :) .
Posted by at
Labels: , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)