iosdev

Introduction to MonoTouch Library sqlite-net

by on April 24, 2013 10:12 am

In my previous article, I gave a tutorial on how we can use Xamarin.iOS (formally known as Monotouch) to build iOS mobile applications using C#. In this blog post, I will introduce a third party library that can aid your mobile application development: sqlite-net.

Introducing sqlite-net (ORM)

The sqlite-net library provides simple, easy-to-use object relation mapping for the SQLite database. The API was designed specifically for mobile applications written in the .NET platform. The library is a single C# source file that is imported into your project with no additional dependences. Database operations can either be synchronous and asynchronous.

Table and Column Definitions

To define your tables, sqlite-net uses attributes on the domain model’s public properties. The minimal required for defining a table is the PrimaryKey attribute. The preferred data type for the primary key is an integer. By default, the table and column names will use the class and properties from the domain model for their names.

Let’s look at an example domain:

using SQLite;
namespace Com.Khs.CommandRef.Model
{
    [Table("category")]
    public class Category
    {
        [PrimaryKey]
        public long Id { get; set; }
        public string Description { get; set; }
    }
}

When defining the model, the C# data types that sqlite-net supports are Integers, Booleans, Enums, Singles, Doubles, Strings, and DateTime. Here are a list of database attributes that define your table and columns:

  • Table – Define a specific name for the table.
  • Column – Define a specific name for the column.
  • PrimaryKey – Define the primary key for the table.
  • AutoIncrement – Guarantees the primary key as having a unique id value. The domain model property should be an integer.
  • Indexed – Defines the column as an index.
  • Ignore – Does not add the class property as a column in the table.

Initialize Database

When the iOS application begins to load, I create a database connection and initialize the tables during the FinishedLaunching method from the AppDelegate class. First, create the connection to the database using the SQLiteConnection or SQLiteAsyncConnection method. The CreateTable or CreateAsyncTable method will create a new table for the connection if it does not already exist in the database. The Connection property will be used by the application for accessing the database.

using SQLite;

namespace Com.Khs.CommandRef
{
    [Register ("AppDelegate")]
    public partial class AppDelegate : UIApplicationDelegate
    {
        public SQLiteConnection Connection { get; private set; }

        public override bool FinishedLaunching (UIApplication application, NSDictionary launcOptions)
        {
            InitializeDatabase();
            return true;
        }

        protected void InitializeDatabase ()
        {
//Synchronous connection
            Connection = new SQLiteConnection(DbName);

//Ansynchronous connection
            Connection = new SQLiteAsyncConnection(DbName);

//Create Tables
            Connection.CreateTable<Category>();
            Connection.CreateTable<Reference>();
            Connection.CreateTable<User>();
            Connection.CreateTable<Command>();
        }

        public string DbName
        {
            get { return Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "commandref.db"); }
        }
    }

For the remainder of the blog, I will constrain my examples using only the synchronous database methods. If you want asynchronous operations, use the corresponding ‘Async’ method names. (As an example: using InsertAsync instead of Insert.)

CRUD Operations

Now that we have the connection created and tables initialized, we can now do some CRUD operations on the database.

Inserting data into your database is as simple as creating a new model object and calling either the Insert or InsertOrReplace method. The InsertOrReplace method will first delete the existing record if it exists, and then insert the new record. If the AutoIncrement is set on a primary key, the model will return with the new ID.

        public void AddCategory(SQLiteConnection db)
        {
            //Single object insert
            var category = new Category { Description = "Test" };
            var rowsAdded = Db.Insert(category);
            Console.WriteLine("SQLite Insert - Rows Added;" + rowsAdded);

            //Insert list of objects
            List<Category> categories = new List<Category>();
            for ( int i = 0; i < 5; i++)
            {
                categories.Add( new Category { Description = "Test " + i });
            }
            rowsAdded = Db.InsertAll(categories);
            Console.WriteLine("SQLite Insert - Rows Added;" + rowsAdded);
        }

The operations for update and delete work in similar way as the insert operation:

         public void DeleteCategory(SQLiteCommand db, Category category)
        {
            //Single object delete
            var rowsDeleted = Db.Delete<Category>(category);
            Console.WriteLine("SQLite Delete - Rows Deleted: " + rowsDeleted);

            //Delete all objects
            rowsDeleted = Db.DeleteAll<Category>();
        }

        public void UpdateCategory(SQLiteCommand db, Category category, List<Category> categories)
        {
            //Single object update
            var rowsUpdated = Db.Update(category);
            Console.WriteLine("SQLite Update - Rows Updated: " + rowsUpdated);

            //Update list of objects
            rowsUpdated = Db.UpdateAll(categories);
            Console.WriteLine("SQLite Update - Rows Updated: " + rowsUpdated);
        }

There are two options for querying the database, using predicates or low-level queries. When using the predicates option, the Table method is used. Additional predicates such as Where and OrderBy can be used to tailor the queries.

Let’s look at some examples:

        public void QueryCategory(SQLiteCommand db)
        {
            //Query the database using predicates.
            //Return all the objects.
            var categories = Db.Table<Category>().OrderBy(c => c.Description);

            //Use Where predicate
            var category = Db.Table<Category>().Where(c => c.Description.Equals("Test"));

            //Use low level queries
            categories = Db.Query<Category>("select * from category where Description = ?", "Test");
        }

To simplify the query statements, sqlite-net provides Find and Get methods. They will return single object matching the predicate. In the previous example, the query could have been written in the following way.

category = Db.Find(c => c.Description.Equals("Test"));

Additional Features

The sqlite-net also provides a simple transaction framework.

  • BeginTransaction – Starts a new database transaction. Throws exception when a transaction is already started.
  • SaveTransactionPoint – If a transaction is not started, then a new transaction will be created. Otherwise, set a new rollback point. The database will rollback to the last saved transaction point.
  • Commit – Commits the current transaction.
  • Rollback – Completely rolls back the current transaction.
  • RollbackTo – Rollback to an existing save point set by the SaveTransactionPoint.
        public void TransactionOperation()
        {
            Db.BeginTransaction( () => {
                // Do some database work.
                // Commits the transaction when done.
            });

            //Another transaction call
            Db.BeginTransaction();

            //Check that the transaction is still active
            if ( Db.IsInTransaction )
            {
                //Close and commit the transaction
                Db.Commit();
            }
        }

This article shows some of the capabilities of the sqlite-net library. If you would like to learn more about the sqlite-net, check it out on Github and see the code, examples, and wiki for more information. Good luck!

— Mark Fricke, asktheteam@keyholesoftware.com

  • Share:

Leave a Reply

Things Twitter is Talking About
  • Every member of an #Agile team affects its overall success. See what not to do - http://t.co/2nfYQNqJXs Agile Team Member Anti-Patterns
    March 4, 2015 at 3:21 PM
  • .@NebraskaCC is just 2 weeks away & tickets are still available! #KC developers: Lincoln is just a quick 3-hour drive away. Well worth it!
    March 3, 2015 at 10:59 AM
  • Happy Punday from the Keyhole team! :-) We hope you're having a great day. http://t.co/p0iDzAY9qL
    March 2, 2015 at 2:54 PM
  • There's a new post on the Keyhole dev blog from @joshuarob01 - Agile Team Member Anti-Patterns http://t.co/2nfYQNIkP0 #agile
    March 2, 2015 at 10:57 AM
  • Heard of #GrokOla yet? Q&A with our expert dev team + code-sensitive wiki for tribal knowledge http://t.co/oCITNW9xEf http://t.co/61dIP4Q4jE
    February 27, 2015 at 1:38 PM
  • Rapid appdev has a bad rep, but there are ways to bring development time down the right way. Don't Fear the Rapid - http://t.co/8CKhAzmysb
    February 27, 2015 at 1:05 PM
  • RT @DZoneLinks: Swift 1.2 Arrives: 13 New Features - http://t.co/07q5vavZsT - @DZoneLinks Big Link by mswatcher
    February 26, 2015 at 2:17 PM
  • Our GrokStars are at it again - they've released a free #GrokOla primer. Get to know Java Lambdas: http://t.co/D2iLLj8mph
    February 26, 2015 at 9:13 AM
  • RT @dbgrandi: OH: “Do programmers have any specific superstitions?” “Yeah, but we call them best practices.”
    February 25, 2015 at 6:05 PM
  • New Primer: Introduction to the #Backbonejs MVC Framework - http://t.co/VLRJ4b5fwj Free #GrokOla tutorial available to the public.
    February 25, 2015 at 4:26 PM
  • #RabbitMQ: messaging software built on AMQP protocol. Learn relevant concepts & how to avoid common "gotchas" here: http://t.co/ZwMXlhspJ0
    February 25, 2015 at 3:20 PM
  • #Java is OO but contains non-object primitives. Autoboxing feels more like a band-aid. Do primitives need to go? http://t.co/A8ChCBHXJO
    February 25, 2015 at 1:44 PM
  • We're excited for Tech Night tonight! @bricemciver will present to the team on Leaflet.js in preparation for his @nebraskacc talk. Lucky us!
    February 24, 2015 at 4:15 PM
  • Do Primitives need to go in enterprise apps? - http://t.co/A8ChCBqmle New #Java post on the Keyhole blog.
    February 24, 2015 at 3:31 PM
  • When you pair #JAXB & #JPA, you can expect some "gotchas." Here are techniques to help you overcome the hurdles - http://t.co/J1s5DpcsCp
    February 24, 2015 at 8:15 AM
  • Do Primitives Need To Go? - http://t.co/A8ChCBHXJO New #Java post on the Keyhole blog by @jhoestje
    February 23, 2015 at 10:44 AM
  • New to #JavaScript prototypal inheritance? Here are some notes to help you along the way - http://t.co/NTIDZS6Uhy
    February 20, 2015 at 12:10 PM
  • XML Manipulation With XML Copy Editor: http://t.co/iHHmyAUQqU Good, free tool for when you need to manipulate an #XML document directly.
    February 20, 2015 at 10:35 AM
  • We've been releasing some free #Grokola tutorials. See them all in one spot - http://t.co/WDt5fWa728 #JavaScript #Backbonejs #nodejs #java
    February 19, 2015 at 2:42 PM
  • Code For Maintainability So The Next Developer Doesn't Hate You - http://t.co/iG2wW2rSWj Eight helpful tips to do so.
    February 19, 2015 at 11:20 AM
Keyhole Software
8900 State Line Road, Suite 455
Leawood, KS 66206
ph: 877-521-7769
© 2015 Keyhole Software, LLC. All rights reserved.