Local Databases in Android and iOS through Xamarin

Hi everybody, today I’m going to teach you how to use, create and manage Local DB in Android and iOS through Xamarin, first of all, we need:

• Xamarin(with all the developer tools)
• Mac (if you want to program in iOS)
• Droiddraw

 Now, well we need the diagram of the DB, I’m going to create a simple DB with 1 table; this will be the diagram:

table1
id	int	PRIMARY KEY AUTO_INCREMENT
field_1	varchar(50)
field_2	double

-->

We can see that this is a simple DB, but for now it will work. Well now it's programming time, so we'll follow the next steps:

• Create the interface, for this we can use droiddraw for android

VIEW

 

XML

<?xml version="1.0" encoding="utf-8"?>
<AbsoluteLayout
    android:id="@+id/widget0"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    xmlns:android="http://schemas.android.com/apk/res/android">
<EditText
    android:id="@+id/txtf1"
    android:layout_width="171dp"
    android:layout_height="wrap_content"
    android:text="EditText"
    android:textSize="18sp"
    android:layout_x="123dp"
    android:layout_y="17dp" />
<EditText
    android:id="@+id/txtf2"
    android:layout_width="169dp"
    android:layout_height="wrap_content"
    android:text="EditText"
    android:textSize="18sp"
    android:layout_x="123dp"
    android:layout_y="69dp" />
<TextView
    android:id="@+id/widget35"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Field 1"
    android:layout_x="23dp"
    android:layout_y="27dp" />
<TextView
    android:id="@+id/widget36"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Field 2"
    android:layout_x="26dp"
    android:layout_y="84dp" />
<TextView
    android:id="@+id/widget37"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Field 3"
    android:layout_x="29dp"
    android:layout_y="138dp" />
<EditText
    android:id="@+id/txtFields"
    android:layout_width="273dp"
    android:layout_height="118dp"
    android:text="EditText"
    android:textSize="18sp"
    android:layout_x="23dp"
    android:layout_y="264dp" />
<Button
    android:id="@+id/btnSave"
    android:layout_width="93dp"
    android:layout_height="wrap_content"
    android:text="Save"
    android:layout_x="116dp"
    android:layout_y="378dp" />
</AbsoluteLayout>

• Open Xamarin studio
• Create a new Project for Android or/and iOS
• If you choose iOS, you must choose it with storyboard

Android
iOS

• Create the interface for your iOS device
• Double click on MainStoryboard.storyboard, xCode will appear. Create the interface

NOTE: If you remember, in the post: My first app with iOS 6, I talked a little bit about the text Button, well in this exercise i follow the advice of hide that button. For this example i called that button: text

• Link variables

• Add references:
• Mono.Data.Sqlite
• System.Data

• Create a new class with this code:

  /*Libreries that we need*/
using System;
using Mono.Data.Sqlite;
using System.Data;

    public class DB
    {
        public DB ()
        {
        }
        /*Here we create the DB or verify if the DB Exists*/
        public void createDB(){
        /*I use this line to get the pat to the root of files of the phone and save that directory in "documents"*/
        var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        try{
            /*Check if the DB exists*/
            var connectionString = String.Format("Data Source={0};Version=3;",documents+"Database1.db");
        }catch(Exception ex){
            /*If the DB nos exists, we create it*/
            SqliteConnection.CreateFile(documents+"//Database1.db");
        }finally{
            /*Here we create the table*/
            /*I use this line to conenct to the file .db, The Physical DB
              It's like: USE Dtabase; in SQL
             */
            var connectionString = String.Format("Data Source={0};Version=3;",documents+"Database1.db");
            using (var conn= new SqliteConnection(connectionString))
            {
                /*This line open the connection to the DB*/
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    /*Here we create the table if the table not exist*/
                    cmd.CommandText = "CREATE TABLE IF NOT EXISTS Table1 (ID INTEGER PRIMARY KEY AUTOINCREMENT , field_1 varchar(50), field_2 double)";
                    /*This line convert the variable to a SQL Query*/
                    cmd.CommandType = CommandType.Text;
                    /*This line execute the SQL Command*/
                    cmd.ExecuteNonQuery();
                    /*This line close the connection to the DB*/
                    conn.Close();
                }
            }
        }
        }

        /*Here we Insert into the DB*/
        public void insertDB(string d1, double d2){
        var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        var connectionString = String.Format("Data Source={0};Version=3;", documents+"Database1.db");
            using (var conn= new SqliteConnection(connectionString))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    /*Here we insert the values into the DB*/
                    cmd.CommandText = "INSERT INTO Table1 (field_1, field_2) " +
                        "VALUES('"+d1+"','"+d2+"')";
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }
        /*Here we get the values of the DB*/
        public string GetValues(){
        var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        var connectionString = String.Format("Data Source={0};Version=3;", documents+"Database1.db");
            using (var conn= new SqliteConnection(connectionString))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    /*This line is used to select all the items of the DB*/
                    cmd.CommandText = "SELECT * FROM Table1";
                    cmd.CommandType = CommandType.Text;
                    /*I use the ExecuteReader() to get an array of all the records in my DB*/
                    SqliteDataReader reader=cmd.ExecuteReader();
                    string Values="";
                    /*I use this loop to get all the records*/
                    while(reader.Read()){
                        /*I use the function GetValue(n), to get the especific record due to the array that I get before
                          we can see I select the position 1 and 2 due to I have the ID and its position is 0 due to
                          the order of the Table 
                         */
                        string val1=""+reader.GetValue(1);
                        string val2=""+reader.GetValue(2);
                        /*I use this variable to save all the records in a specific format*/
                        Values=Values+"field1="+val1+", field2="+val2+"\n";
                    }
                    conn.Close();
                    return Values;
                }
            }
        }
    }

• Program themain class
• Initialize the variables and get their values
• Program the button

Android
Button now=FindViewById<Button> (Resource.Id.btnSave); EditText fields=FindViewById<EditText>(Resource.Id.txtFields); EditText field1=FindViewById<EditText>(Resource.Id.txtf1); EditText field2=FindViewById<EditText>(Resource.Id.txtf2); now.Click += delegate {    DB w=new DB();    w.createDB();    w.insertDB(field1.Text,double.Parse(field2.Text));    fields.Text=w.GetValues(); };
iOS
[Outlet] MonoTouch.UIKit.UITextView txtResult { get; set; } [Outlet] MonoTouch.UIKit.UITextField txtField2 { get; set; } [Outlet] MonoTouch.UIKit.UITextField txtField1 { get; set; } [Action ("SaveButton:")] partial void SaveButton (MonoTouch.Foundation.NSObject sender); partial void SaveButton (MonoTouch.Foundation.NSObject sender){     DB w=new DB();     string field1=txtField1.Text;     double field2=double.Parse(txtField2.Text);     w.createDB();     w.insertDB(field1,field2);     txtResult.Text=w.GetValues(); } [Action ("text:")] partial void text (MonoTouch.Foundation.NSObject sender); partial void text (MonoTouch.Foundation.NSObject sender){     txtField1.ResignFirstResponder();     txtField2.ResignFirstResponder(); }

• See what happened

Android
iOS

If you wanted you can download the DB.cs clicking here

Well guys that's it for now, I hope this helps you out, see you later, have fun and be happy ^_^/