Using SQLite.NET ORM in Xamarin Application - Javatechig
New Post has been published on http://javatechig.com/cross-platform/xamarin/using-sqlite-net-orm-in-xamarin-application
Using SQLite.NET ORM in Xamarin Application
ORM stands for Object Relational Mapping. SQLite.NET ORM is an light weight and open source library that allows .NET and Mono application to store data in SQLite database. This is available as a free component in Xamarin component store. SQLite.NET supports for Android, iOS and Windows platform, and hence the database operation logic can shared across platforms. Some of the key features of SQLite.NET ORM includes
Easy to ingrate component available to use in existing Xamarin project
It is an light weight thin wrapper, and is efficient without performance trade-off
Support simple CURD operations and support for simple, complex SQL quires
There are two approaches to integrate SQLite.NET ORM in Xamarin application
SQLite.NET is a very thin library with single file. You can download SQLite.cs file from Github and add into your application. SQLite.NET library binds directly to the native device SQLite database engine on each platform.
Adding from component store
SQLite.NET is listed free Xamarin Component Store and available for both iOS and Android platform. To include this into your Xamarin Studio project, you can , either double-click on the Components folder in the solution pad, or and right click to select Component Manager.
You will notice that the component manager will be loaded with already downloaded components and you can open Component store from the window. Accessing component manager requires log in to your Xamarin account. Once logged, you can browse through available components and add them to project.
Once the component is added to your Xamarin project, you are good to use this library to write SQL operations.
Before you can store data into SQLite, you first need to create a database, if not created already. Create a blank database or open existing by passing the file path the SQLiteConnection class constructor.
private string DbName = "Intentory_DB.db"; string path = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal); SQLiteConnection dbConn = new SQLiteConnection (System.IO.Path.Combine (path, DbName));
Depending on what platform you are targeting the file location will be different. For iOS and Android you can use Environment class as shown in the above code snippet, to construct a valid path
The new SQLiteConnection opens the database file, if the file under mentioned path is already available, else it will create a new database.
Once your database connection is opened, you can execute the following methods to command different database operations.
In this example, we will be storing items to Inventory table. Below is the structure of Inventory class. As you can see in the code snippet below, we have declared different SQL attributes to Inventory class model.
class Inventory [PrimaryKey, AutoIncrement] public int Id get; set; [NotNull] public int ItemCode get; set; public String Name get; set; public String Category get; set; public double Price get; set; public bool StockAvailable get; set;
Once you have your model ready, you can invoke the below methods to create table, if not exist already.
//Creating inventory object for storing into db Inventory newInventory = new Inventory (); newInventory.ItemCode = Convert.ToInt32(ItemCode.Text); newInventory.Name = ItemName.Text; newInventory.Category = ItemCategory.Text; newInventory.Price = Convert.ToDouble(ItemPrice.Text); newInventory.StockAvailable = IsStockAvailable.Checked; //Inserting record into database dbConn.Insert (newInventory);
Retrieve data from database
Use the following syntax to retrieve all the records from database
IEnumerable<Inventory> table = dbConn.Table<Inventory> (); foreach (Inventory s in table) Console.WriteLine (s.ItemCode + " " + s.Name);
To retrieve object by primary key, you can use following code syntax.
//3 is primary key var item = db.Get<Inventory>(3);
Update record into database
//Inserting record into database dbConn.Update (item);
