How to Create a Database Mobile App with SQLite and Xamarin Studio

When we work on mobile app development, it is just a matter of time when we face the need for data storage; information that can be the backbone for the mobile app to just a single data such as the score for a game.

Nowdays every mobile app with minimum data storage needs to use a database. Since the mobile devices do not offer the same memory and processing capacity as a computer, we need to use specially designed systems for those environments.

So I will guide you in this basic tutorial on how to create a database for your mobile app working with the cross-platform Xamarin studio, which is a great tool for this example. 

What is SQLite?

SQLite is a database engine, compatible with ACID. Unlike client-server systems, SQLite is linked to the mobile app by becoming part of it. Every operation is performed within the mobile app through calls and methods provided by the SQLite library which is written in C and has a relatively smaller size.

Create a new Android mobile application solution in Xamarin Studio

If you don't have Xamarin Studio, don't worry. You can download it here: Xamarin Studio

Database class

1. Right click project BD_Demo --> Add --> New File… --> Android Class (Database)

Database class is for handling SQLiteDatabase object. We are now going to create objects and methods for handling CRUD (Create, Read, Update and Delete) operations in a database table. Here is the code: 

//Required assemblies
using Android.Database.Sqlite;
using System.IO;

namespace BD_Demo
{
    class Database
    {
        //SQLiteDatabase object for database handling
        private SQLiteDatabase sqldb;
        //String for Query handling
        private string sqldb_query;
        //String for Message handling
        private string sqldb_message;
        //Bool to check for database availability
        private bool sqldb_available;
        //Zero argument constructor, initializes a new instance of Database class
        public Database()
        {
            sqldb_message = "";
            sqldb_available = false;
        }
        //One argument constructor, initializes a new instance of Database class with database name parameter
        public Database(string sqldb_name)
        {
            try
            {
                sqldb_message = "";
                sqldb_available = false;
                CreateDatabase(sqldb_name);
            }
            catch (SQLiteException ex) 
            {
                sqldb_message = ex.Message;
            }
        }
        //Gets or sets value depending on database availability
        public bool DatabaseAvailable
        {
            get{ return sqldb_available; }
            set{ sqldb_available = value; }
        }
        //Gets or sets the value for message handling
        public string Message
        {
            get{ return sqldb_message; }
            set{ sqldb_message = value; }
        }
        //Creates a new database which name is given by the parameter
        public void CreateDatabase(string sqldb_name)
        {
            try
            {
                sqldb_message = "";
                string sqldb_location = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
                string sqldb_path = Path.Combine(sqldb_location, sqldb_name);
                bool sqldb_exists = File.Exists(sqldb_path);
                if(!sqldb_exists)
                {
                    sqldb = SQLiteDatabase.OpenOrCreateDatabase(sqldb_path,null);
                    sqldb_query = "CREATE TABLE IF NOT EXISTS MyTable (_id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR, LastName VARCHAR, Age INT);";
                    sqldb.ExecSQL(sqldb_query);
                    sqldb_message = "Database: " + sqldb_name + " created";
                }
                else
                {
                    sqldb = SQLiteDatabase.OpenDatabase(sqldb_path, null, DatabaseOpenFlags.OpenReadwrite);
                    sqldb_message = "Database: " + sqldb_name + " opened";
                }
                sqldb_available=true;
            }
            catch(SQLiteException ex) 
            {
                sqldb_message = ex.Message;
            }
        }
        //Adds a new record with the given parameters
        public void AddRecord(string sName, string sLastName, int iAge)
        {
            try
            {
                sqldb_query = "INSERT INTO MyTable (Name, LastName, Age) VALUES ('" + sName + "','" + sLastName + "'," + iAge + ");";
                sqldb.ExecSQL(sqldb_query);
                sqldb_message = "Record saved";
            }
            catch(SQLiteException ex) 
            {
                sqldb_message = ex.Message;
            }
        }
        //Updates an existing record with the given parameters depending on id parameter
        public void UpdateRecord(int iId, string sName, string sLastName, int iAge)
        {
            try
            {
                sqldb_query="UPDATE MyTable SET Name ='" + sName + "', LastName ='" + sLastName + "', Age ='" + iAge + "' WHERE _id ='" + iId + "';";
                sqldb.ExecSQL(sqldb_query);
                sqldb_message = "Record " + iId + " updated";
            }
            catch(SQLiteException ex)
            {
                sqldb_message = ex.Message;
            }
        }
        //Deletes the record associated to id parameter
        public void DeleteRecord(int iId)
        {
            try
            {
                sqldb_query = "DELETE FROM MyTable WHERE _id ='" + iId + "';";
                sqldb.ExecSQL(sqldb_query);
                sqldb_message = "Record " + iId + " deleted";
            }
            catch(SQLiteException ex) 
            {
                sqldb_message = ex.Message;
            }
        }
        //Searches a record and returns an Android.Database.ICursor cursor
        //Shows all the records from the table
        public Android.Database.ICursor GetRecordCursor()
        {
            Android.Database.ICursor sqldb_cursor = null;
            try
            {
                sqldb_query = "SELECT*FROM MyTable;";
                sqldb_cursor = sqldb.RawQuery(sqldb_query, null);
                if(!(sqldb_cursor != null))
                {
                    sqldb_message = "Record not found";
                }
            }
            catch(SQLiteException ex) 
            {
                sqldb_message = ex.Message;
            }
            return sqldb_cursor;
        }
        //Searches a record and returns an Android.Database.ICursor cursor
        //Shows records according to search criteria
        public Android.Database.ICursor GetRecordCursor(string sColumn, string sValue)
        {
            Android.Database.ICursor sqldb_cursor = null;
            try
            {
                sqldb_query = "SELECT*FROM MyTable WHERE " + sColumn + " LIKE '" + sValue + "%';";
                sqldb_cursor = sqldb.RawQuery(sqldb_query, null);
                if(!(sqldb_cursor != null))
                {
                    sqldb_message = "Record not found";
                }
            }
            catch(SQLiteException ex) 
            {
                sqldb_message = ex.Message;
            }
            return sqldb_cursor;
        }
    }
}

Records Layout

2. Expand Resources Folder on Solution Pad

    a) Right click Layout Folder --> Add --> New File… --> Android Layout (record_view)

We need a layout for each item we are going to add to our database table. We do not need to define a layout for every item and this same layout can be re-used as many times as the items we have.

    android:orientation="horizontal"
    android:layout_width="match_parent"
    android:layout_height="wrap_content">
            android:text="ID"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/Id_row"
        android:layout_weight="1"
        android:gravity="center"
        android:textSize="15dp"
        android:textColor="#ffffffff"
        android:textStyle="bold" />
            android:text="Name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/Name_row"
        android:layout_weight="1"
        android:textSize="15dp"
        android:textColor="#ffffffff"
        android:textStyle="bold" />
            android:text="LastName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/LastName_row"
        android:layout_weight="1"
        android:textSize="15dp"
        android:textStyle="bold"
        android:textColor="#ffffffff" />
            android:text="Age"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/Age_row"
        android:layout_weight="1"
        android:textSize="15dp"
        android:textStyle="bold"
        android:textColor="#ffffffff"
        android:gravity="center" />

Main Layout

3. Expand Resources folder on Solution Pad --> Expand Layout folder

    a) Double Click Main layout (Main.axml)

Xamarin automatically makes Form Widgets's IDs available by referencing Resorce.Id class.

 

Note: I highly recommended putting images into Drawable folder.

-Expand Resources Folder

  ~Right Click Drawable folder --> Add --> Add Files…

    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent">
            android:orientation="horizontal"
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:background="#ff004150">
                    android:text="Name"
            android:gravity="center"
            android:layout_width="wrap_content"
            android:layout_height="fill_parent"
            android:layout_weight="1"
            android:textSize="20dp"
            android:textStyle="bold"
            android:textColor="#ffffffff" />
                    android:text="Last Name"
            android:gravity="center"
            android:layout_width="wrap_content"
            android:layout_height="fill_parent"
            android:layout_weight="1"
            android:textSize="20dp"
            android:textColor="#ffffffff"
            android:textStyle="bold" />
                    android:text="Age"
            android:gravity="center"
            android:layout_width="wrap_content"
            android:layout_height="fill_parent"
            android:layout_weight="1"
            android:textSize="20dp"
            android:textStyle="bold"
            android:textColor="#ffffffff" />
    
            android:orientation="horizontal"
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:background="#ff004185">
                    android:inputType="textPersonName"
            android:id="@+id/txtName"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1" />
                    android:id="@+id/txtLastName"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1" />
                    android:inputType="number"
            android:id="@+id/txtAge"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1" />
    
            android:orientation="horizontal"
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:paddingLeft="10dp"
        android:background="#ff004185"
        android:gravity="center">
                    android:layout_width="50dp"
            android:paddingLeft="10dp"
            android:layout_height="50dp"
            android:background="@drawable/add"
            android:id="@+id/imgAdd"
            android:layout_marginLeft="5dp"
            android:layout_marginRight="10dp" />
                    android:layout_width="50dp"
            android:paddingLeft="10dp"
            android:layout_height="50dp"
            android:background="@drawable/save"
            android:id="@+id/imgEdit"
            android:layout_marginLeft="10dp"
            android:layout_marginRight="10dp" />
                    android:layout_width="50dp"
            android:paddingLeft="10dp"
            android:layout_height="50dp"
            android:background="@drawable/delete"
            android:id="@+id/imgDelete"
            android:layout_marginLeft="10dp"
            android:layout_marginRight="10dp" />
                    android:layout_width="50dp"
            android:paddingLeft="10dp"
            android:layout_height="50dp"
            android:background="@drawable/search"
            android:id="@+id/imgSearch"
            android:layout_marginLeft="10dp"
            android:layout_marginRight="10dp" />
    
            android:text="Message"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:id="@+id/shMsg"
        android:background="#ff004185"
        android:textColor="#ffffffff"
        android:textStyle="bold"
        android:textSize="15dp"
        android:gravity="center" />
            android:orientation="horizontal"
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:paddingLeft="10dp"
        android:background="#ff004150"
        android:gravity="center">
                    android:text="ID"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textColor="@android:color/white"
            android:textSize="20dp"
            android:layout_weight="1"
            android:gravity="center"
            android:id="@+id/id" />
                    android:text="Name"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textColor="@android:color/white"
            android:gravity="center"
            android:textSize="20dp"
            android:layout_weight="1"
            android:id="@+id/name" />
                    android:text="Last Name"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textColor="@android:color/white"
            android:layout_weight="1"
            android:gravity="center"
            android:textSize="20dp"
            android:id="@+id/last" />
                    android:text="Age"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textColor="@android:color/white"
            android:layout_weight="1"
            android:textSize="20dp"
            android:gravity="center"
            android:id="@+id/age" />
    
            android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="match_parent"
        android:paddingLeft="10dp"
        android:id="@+id/listItems" />

Main Activity class

4. Double Click Main Activity (MainActivity.cs)

We have to get object instances from main layout and provide them with an event. Main events will be Add, Edit, Delete and Search for the image buttons we have defined. We have to populate our ListView object with the data stored in the database or create a new one in case it does not exist.

namespace BD_Demo
{
    //Main activity for app launching
    [Activity (Label = "BD_Demo", MainLauncher = true)]
    public class MainActivity : Activity
    {
        //Database class new object
        Database sqldb;
        //Name, LastName and Age EditText objects for data input
        EditText txtName, txtAge, txtLastName;
        //Message TextView object for displaying data
        TextView shMsg;
        //Add, Edit, Delete and Search ImageButton objects for events handling
        ImageButton imgAdd, imgEdit, imgDelete, imgSearch;
        //ListView object for displaying data from database
        ListView listItems;
        //Launches the Create event for app
        protected override void OnCreate (Bundle bundle)
        {
            base.OnCreate (bundle);
            //Set our Main layout as default view
            SetContentView (Resource.Layout.Main);
            //Initializes new Database class object
            sqldb = new Database("person_db");
            //Gets ImageButton object instances
            imgAdd = FindViewById (Resource.Id.imgAdd);
            imgDelete = FindViewById (Resource.Id.imgDelete);
            imgEdit = FindViewById (Resource.Id.imgEdit);
            imgSearch = FindViewById (Resource.Id.imgSearch);
            //Gets EditText object instances
            txtAge = FindViewById (Resource.Id.txtAge);
            txtLastName = FindViewById (Resource.Id.txtLastName);
            txtName = FindViewById (Resource.Id.txtName);
            //Gets TextView object instances
            shMsg = FindViewById (Resource.Id.shMsg);
            //Gets ListView object instance
            listItems = FindViewById (Resource.Id.listItems);
            //Sets Database class message property to shMsg TextView instance
            shMsg.Text = sqldb.Message;
            //Creates ImageButton click event for imgAdd, imgEdit, imgDelete and imgSearch
            imgAdd.Click += delegate {
                //Calls function AddRecord for adding a new record
                sqldb.AddRecord (txtName.Text, txtLastName.Text, int.Parse (txtAge.Text));
                shMsg.Text = sqldb.Message;
                txtName.Text = txtAge.Text = txtLastName.Text = "";
                GetCursorView();
            };

            imgEdit.Click += delegate {
                int iId = int.Parse(shMsg.Text);
                //Calls UpdateRecord function for updating an existing record
                sqldb.UpdateRecord (iId, txtName.Text, txtLastName.Text, int.Parse (txtAge.Text));
                shMsg.Text = sqldb.Message;
                txtName.Text = txtAge.Text = txtLastName.Text = "";
                GetCursorView();
            };

            imgDelete.Click += delegate {
                int iId = int.Parse(shMsg.Text);
                //Calls DeleteRecord function for deleting the record associated to id parameter
                sqldb.DeleteRecord (iId);
                shMsg.Text = sqldb.Message;
                txtName.Text = txtAge.Text = txtLastName.Text = "";
                GetCursorView();
            };

            imgSearch.Click += delegate {
                //Calls GetCursorView function for searching all records or single record according to search criteria
                string sqldb_column = "";
                if (txtName.Text.Trim () != "") 
                {
                    sqldb_column = "Name";
                    GetCursorView (sqldb_column, txtName.Text.Trim ());
                } else
                    if (txtLastName.Text.Trim () != "") 
                {
                    sqldb_column = "LastName";
                    GetCursorView (sqldb_column, txtLastName.Text.Trim ());
                } else
                    if (txtAge.Text.Trim () != "") 
                {
                    sqldb_column = "Age";
                    GetCursorView (sqldb_column, txtAge.Text.Trim ());
                } else 
                {
                    GetCursorView ();
                    sqldb_column = "All";
                }
                shMsg.Text = "Search " + sqldb_column + ".";
            };
            //Add ItemClick event handler to ListView instance
            listItems.ItemClick += new EventHandler (item_Clicked);
        }
        //Launched when a ListView item is clicked
        void item_Clicked (object sender, AdapterView.ItemClickEventArgs e)
        {
            //Gets TextView object instance from record_view layout
            TextView shId = e.View.FindViewById (Resource.Id.Id_row);
            TextView shName = e.View.FindViewById (Resource.Id.Name_row);
            TextView shLastName = e.View.FindViewById (Resource.Id.LastName_row);
            TextView shAge = e.View.FindViewById (Resource.Id.Age_row);
            //Reads values and sets to EditText object instances
            txtName.Text = shName.Text;
            txtLastName.Text = shLastName.Text;
            txtAge.Text = shAge.Text;
            //Displays messages for CRUD operations
            shMsg.Text = shId.Text;
        }
        //Gets the cursor view to show all records
        void GetCursorView()
        {
            Android.Database.ICursor sqldb_cursor = sqldb.GetRecordCursor ();
            if (sqldb_cursor != null) 
            {
                sqldb_cursor.MoveToFirst ();
                string[] from = new string[] {"_id","Name","LastName","Age" };
                int[] to = new int[] {
                    Resource.Id.Id_row,
                    Resource.Id.Name_row,
                    Resource.Id.LastName_row,
                    Resource.Id.Age_row
                };
                //Creates a SimplecursorAdapter for ListView object
                SimpleCursorAdapter sqldb_adapter = new SimpleCursorAdapter (this, Resource.Layout.record_view, sqldb_cursor, from, to);
                listItems.Adapter = sqldb_adapter;
            } 
            else 
            {
                shMsg.Text = sqldb.Message;
            }
        }
        //Gets the cursor view to show records according to search criteria
        void GetCursorView (string sqldb_column, string sqldb_value)
        {
            Android.Database.ICursor sqldb_cursor = sqldb.GetRecordCursor (sqldb_column, sqldb_value);

            if (sqldb_cursor != null) 
            {
                sqldb_cursor.MoveToFirst ();
                string[] from = new string[] {"_id","Name","LastName","Age" };
                int[] to = new int[] 
                {
                    Resource.Id.Id_row,
                    Resource.Id.Name_row,
                    Resource.Id.LastName_row,
                    Resource.Id.Age_row
                };
                SimpleCursorAdapter sqldb_adapter = new SimpleCursorAdapter (this, Resource.Layout.record_view, sqldb_cursor, from, to);
                listItems.Adapter = sqldb_adapter;
            } 
            else 
            {
                shMsg.Text = sqldb.Message;
            }
        }
    }
}

5. Build solution and run

 

Ok, so that's it for today! I hope that this tutorial has been useful. Feel free to make any questions, complaints, suggestions or comments.

Happy coding!

Francisco Nieves is a current Computer Systems Engineering student with more than 2 years of experience in .NET development. He currently works at iTexico as a Xamarin Mobile Developer for mobile app development projects.

View the original article here

Most 2006-2009 NSA queries of a phone database broke court rules

An undated aerial handout photo shows the National Security Agency (NSA) headquarters building in Fort Meade, Maryland.

Credit: Reuters/NSA/Handout via Reuters

By Joseph Menn

SAN FRANCISCO | Tue Sep 10, 2013 8:31pm EDT

SAN FRANCISCO (Reuters) - The National Security Agency routinely violated court-ordered privacy protections between 2006 and 2009 by examining phone numbers without sufficient intelligence tying them to associates of suspected terrorists, according to U.S. officials and documents that were declassified on Tuesday.

The Foreign Intelligence Surveillance Court, which oversees requests by spy agencies to tap phones and capture email in pursuit of information about foreign targets, required the NSA to have a "reasonable articulable suspicion" that phone numbers were connected to suspected terrorists before agents could search a massive call database to see what other numbers they had connected to, how often and for how long.

But between 2006 and 2009, the agency used an "alert list" to search daily additions to the U.S. calling data, and that list contained mostly numbers that merely been deemed of possible foreign intelligence value, a much lower threshold.

The alert list grew from about 3,980 phone numbers in 2006 to 17,835 by early 2009, and only 2,000 of the larger number met the required standard for certified reasonable suspicion of a terrorist tie, officials said.

Each inquiry could scan for the phone number's called contacts and then those people's contacts, so that many more U.S. residents could have been swept up.

But in official briefings for the press Tuesday, intelligence authorities said that those numbers on the alert list were only checked against new calls, not the historical record of all calls, so that no full "chain analysis" usually resulted.

"This was used by analysts to try to prioritize their work," one official said. "If you're trying to pick 25 players for a major league baseball team, you might give 500 a tryout."

But about 600 U.S. numbers were improperly passed along to the Central Intelligence Agency and Federal Bureau of Investigation as suspicious, the records show. In addition, scores of analysts from the sister agencies had access to the calling database without proper training.

The new disclosures add a fresh perspective to recent statements by the NSA Director Keith Alexander than only 300 or so numbers were run against the master calling database in 2012.

That was years after the secret court concluded it had been badly misled, ordered a temporary halt to the automated searches, and mulled contempt proceedings before the NSA drastically curtailed its practices.

In January 2009, the court ruled that the alert-list procedure was "directly contrary to the sworn attestations of several executive branch officials."

Alexander and other officials responded with filings maintaining that no one at the NSA had fully understood all of the rules around the calling-records database, the software used to search it, and the significance of internal markings.

"From a technical standpoint, there was no single person who had a complete technical understanding," General Alexander told the court in February 2009. He said numerous officials made honest mistakes, such as concluding that restrictions on "archived" phone records did not also apply to the daily influx of new calling records.

The documents were declassified by the Office of the Director of National Intelligence after a long fight with the Electronic Frontier Foundation and the American Civil Liberties Union, that filed a Freedom of Information Act lawsuit two years ago.

The lawsuit gained steam after former NSA contractor Edward Snowden leaked thousands of documents about the agency's practices, including an earlier surveillance court ruling compelling Verizon Communications Inc to turn over all its raw calling records, though not the content of the calls. Officials confirmed that document was genuine and declassified some related papers.

In another lawsuit, the government last month released another ruling by the same 11-member court that found some of the NSA's email collection practices were unconstitutional because they scooped up tens of thousands of emails between Americans.

Two Democratic senators who have long hinted about undisclosed surveillance problems, Ron Wyden for Oregon and Mark Udall for Colorado, said in a joint statement: "When the executive branch acknowledged last month that ‘rules, regulations and court-imposed standards' intended to protect Americans' privacy had been violated thousands of times each year, we said that this confirmation was ‘the tip of a larger iceberg.'

"With the documents declassified and released this afternoon by the Director of National Intelligence, the public now has new information about the size and shape of that iceberg."

Director of National Intelligence James Clapper said in a statement posted to a public website that the latest declassified documents showed that intelligence officials had self-reported problems with the program and corrected them.

"The government has undertaken extraordinary measures to identify and correct mistakes that have occurred in implementing the bulk telephony metadata collection program - and to put systems and processes in place that seek to prevent such mistakes from occurring in the first place," Clapper said.

For a time after the 2009 phone-record ruling, the NSA was required to seek court approval for every query to the database. After the NSA changed its procedures, the court again allowed the agency to conduct queries on its own.

(Reporting by Joseph Menn in San Francisco and Mark Hosenball in Washington; Editing by Tim Dobbyn)

View the original article here