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

How to: Creating a Web App with ASP.NET MVC using C#, RAZOR and Visual Studio

In this basic tutorial, we’ll learn how to create a web-based application using an excellent combination of several development technologies that speak for themselves. Do you want to know what I’m talking about? Ok, let’s review each of them.

What is ASP.NET?

Microsoft ASP.NET is a framework used to develop web applications, dynamic web sites and web services. There have been 6 frameworks so far since this technology was created: 1.0, 1.1, 2.0, 3.5, 4.0 and the current version 4.5. Every framework has been adding more and more classes, methods, types, etc., so the programmers are able to do more things using these new features.

What is MVC?

MVC is a software architecture pattern which separates the development of applications in three concepts or components: Model, View and Controller.  As you can see, this is where the name comes from.  Just look at the first letter of each word of the components (yeah I know it’s not difficult to realize, just wanted to make sure you did), let’s see what they are about:

Model - It manages the entity definition, data access and business logic of our application.

View - It represents the user interface and where the user can see the information provided by the model.

Controller - It’s how the Model and View components communicate or interact. It’s the link between them two.

MVC is a pattern so it's not exclusive of any company or license. There are many MVC frameworks. For example, Cocoa, using Objective C licensed by Apple, Ruby On Rails, using Ruby licensed by MIT and Grails using JAVA licensed by APACHE.  We could continue with so much more, but the one we are focusing on in this tutorial is Microsoft ASP.NET MVC, using  C#. Currently there are 4 versions of ASP.NET MVC and in this tutorial we’ll use MVC 3.

What is C#?

C# is an object-oriented programming language developed by Microsoft as part of the .NET framework.

What is Razor?

Razor is an ASP.NET programming syntax that allows the programmers to write some C# or Visual Basic code embedded in an html page and is used to create dynamic web pages. It was released by Microsoft for Visual Studio 2010 in January 2011 as part of ASP.NET MVC 3.

What is Visual Studio?

It is a tool, or technically speaking, an Integrated Development Environment (IDE) used to create applications of several kinds such as windows form applications, web sites, web applications and web services. It supports different programming languages like C#, as well as Visual Basic, C, C++, and provides support for Python and Ruby.

Well, now that we know a little more about these software development technologies and we have all our ingredients, let’s mix them.

1. Open Visual Studio 2012, go to "File/New" menu and click on "Project". A window like the following will open:

 

2. In this window, select within the Visual C# options, ASP.NET MVC 3 Web Application.
3. Then in the following window, select the template “Empty”, and “Razor” as View engine, and then click Ok.

 

Now, you can see a web application Project created with the folders that you need: Models, Views, and Controller, plus the reference, app data, scripts, and other files.

 

4. Now right-click on the folder “Controllers” and "Add/Controller” and you’ll see a window where you can enter the name of your controller. Let’s name it “Index”, and for the template option let’s leave Empty Controller. Don’t remove the suffix “Controller” in the controller name because it’s indicating that this one is a controller class.

 

5. Now, right-click on the folder “Models” and select Add/Class. Name this class whatever you want as this will be the Entity to show in the View. Let’s name it “IndexModel” (the name doesn’t have to include the word “index”). Then, add some properties to the class. For this example let’s add ID, Description and Comments properties.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;namespace MyMvcApplication.Models
{
public class IndexModel
{
public int ID { get; set; }
public string Description { get; set; }
public string Comments { get; set; } }
}

6. Now, right-click on the folder “Views” and create a new folder named “Index” (as the controller). Then right-click on that folder and select Add/View. Let’s name the view as “Index” also and as a result we’ll have a new “Index. cshtml”.

 

Now, that you've added our Controller, Model and View files created, let’s add some code there.

7. Let’s add some code to the controller which will look like this:

 

As you can see the methods that send models to the view commonly return an ActionResult, and these methods are called Actions. This action will send the data to a View with the same name by default, unless you specify the name of another one in the “return View ()” clause.

8. Now, let’s add some code to the view which will look like this.

 

You have to add the reference to the Model at the top of the page. In this case, our Model is a list so that’s why the word “IEnumerable” shows up in this View. If the Model was a single element, just say MyMvcApplication. Models. IndexModel.

9. Now, run the Project using your favorite browser, and make sure you add to the URL the action “Index”. 

 

10. That’s it! We have our first MVC Application with ASP.NET MVC 3, using C# and Razor.

MVC is an interesting software development technology which pretends to organize our applications using these three layers. Therefore, it is a good approach when every member of a development team works only with part of the code, like the business logic for example (Back-End), and other developers work only with Front-End.

I know this is a very simple application, but I just wanted you to know more about the famous MVC and how it works.

References:

Model View Controller

Razor view engine

Rommel Sanchez Coronel is a software engineer with 7+ years of experience mostly in web applications. He likes analyzing, designing and developing applications and complex Information Systems. He works at iTexico as a .NET developer. 

View the original article here