Introduction of SQLlite a modern local database engine for windows Store app & Windows Phone App

As mobile computing becoming part our life, smart phone, tablet are eating up the market share of traditional desk top, laptop computer market, the software runs in these mobile devices ( apps) also taking up software product market share. Even my 7 years daughter knows how to search apps , install apps and use apps in both iPad and Surface. Sure enough, a good database engine for these apps would be a great help for modern app developers.

As it is now, none of these traditional desktop database engines can be used in any of these mobile platforms (iOS, Android, Windows 8, WP 8). Through my research, I found a local database engine that can be used in all these platforms and the best of it is : it is free! and more, it is open source ! That is SQLite ( read as SQL Light).

http://www.sqlite.org/ is its official site, you can find all you need about SQLite here. For example, you can find all its language syntax on this section of the site http://www.sqlite.org/lang.html

Its official description read as "SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine."

More specific resources on individual platform are also widely available. I am going to list a few here :

http://www.raywenderlich.com/913/sqlite-tutorial-for-ios-making-our-app

http://www.vogella.com/tutorials/AndroidSQLite/article.html

http://developer.nokia.com/community/wiki/How_to_use_SQLite_in_Windows_Phone

http://code.msdn.microsoft.com/windowsapps/Sqlite-For-Windows-8-Metro-2ec7a882

As my research has been focused on Windows Store App development using C# XAML, I spent most of my research time on SQLite on Windows 8 and windows 8.1 in windows store app using C# and XAML, though I did have a "Hello, word!" app running in my Samsung Galaxy 10 and read lots material and articles on Objective C. However, the difference among these platforms are mostly how to get the engine and how to interact with it in your own language. Even they are different languages in different platform. (android uses JDK, ADK and Java; iOS uses Objective-C and iOS SDK; windows app uses Windows library and Windows Phone SDK and C#) Believe me or not, the code looks very similar, they are all C#/Java/C like kind of languages. The key differences is the API in different SDKs.

For IDE wise, In android you use Eclipse ( freely downloadable); in iOS you use XCode ( freely downloadable in apple Mac store, but you must have a Mac computer, like you have to have a windows OS computer to develop android or windows store app); for windows platform, of course, you use Visual Studio ( express edition is also free)

Specific on Window platform, there are 3 different hardware architectures, X86, X64 and ARM. SQLite for windows are offered in the form of PCL ( Potable Class Library) that means the same library can be used in 3 different architectures. This is very important, as when you develop a store app, you want your app able to run in windows 64 bit and 32 bit OS, and also you want your app runs in window RT OS like surface or surface 2.

Okay, coming back to SQLite. the syntax of SQLite in these platforms are identical, the only difference is in different platform there are different packages in different languages to make your programming with SQLite more enjoyable. From now on, my introduction on SQLite will be focus on using SQLite in Windows store app. As it is an introduction article, I will touch on the following topics

1) where to get it

2) how to create tables

3) how CRUD are done in SQLite database

4) how to support transaction

Okay let's get it started. some logistics first. In order to develop Windows 8.0 store app, you need have a computer with Window 8.0 and Visual Studio 2012 installed. In order to develop window 8.1 store app you need to have Windows 8.1 and Visual Studio 2013 installed. for Visual Studio, any edition will do, including Express Edition. Express Edition of Visual Studio is freely available from Microsoft site. Besides the tool, you need to have app developer license. At this moment, it is free. With the developer license you can develop store apps and run the app in your local computer. If you want to publish your app to the store, or deploy your app to other devices ( side loading) or publish it to your corporate app store, you need to have an windows store account. , which is about $50 a year for individual. ( much lower than that in iOS platform) about $500 an year for company account ( I am not exactly sure about the figures, please do not hold me on those.)

1. how to get SQLite

once you have all these ironed out, you are set to go for creating your first app. Fire up your Visual Studio and select your project type as <Windows Store>, pick one of the app template, give it a name and click Ok. after a second you will have a windows store project that is runnable.

now, we have a windows store app project, and we need to add SQLite component to the project.

under <Tool> click on <Extensions and Updates> you will be presented with Extensions and Updates window.

Type SQLite in search box, click on <Search>, you will get one or two entries depends on your OS version

Click on <Install> you will get the database engine installed to your Visual Studio.

go back to Visual Studio, under <tool><Library package Manager> click on <Manage NuGet packages for solution>, you will be presented with Manage Nuget package. on the left site of the window, select nugget.org and search for SQLite on the right. you will get window show below:

click install on sqlite-net, which is a client library for SQLite. it is highly recommended to install this package. since you are here you might want to play around in Nuget package manager, you could find lots of goodies free. ( this is new since Visual studio 2012)

once you are done with that, your project will have a reference to SQLite for Windows Runtime (Windows 8.1) and another reference to Microsoft Visual C++ 2013 Runtime Package for Windows and have 2 files added to your projects: (SQLite.cs and SQLiteAsync.cs). With that you can declare that you got SQLite for your project and you are ready to explore the world of SQLite.

2. how to create / access database ( start from now you will see some C# or SQL code )

protected readonly SQLiteAsyncConnection _connection;

var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, dbName);

_connection = new SQLiteAsyncConnection(dbPath);

SQLiteAsyncConnection will open the database with the db name specified, if the database does not exist, it will create one for you.

3. how to create tables

there are 2 ways to create tables, you can pick up one of them for that meets your need:

simple one first.

await _connection.CreateTableAsync<Module>();

where Module is a class with some SQLite attributes on it

public class Module

{

[PrimaryKey, AutoIncrement]

public int Id { get; set; }

[Column("Title")]

public string Title { get; set; }

[Column("Description")]

public string Description { get; set; }

[Ignore]

public List<Word> Words { get; set; }

more complex way:

await _connection.ExecuteAsync(SQLScripts.Tables.Create.Module);

where module is a string with SQLite create table script as following:

CREATE TABLE IF NOT EXISTS Module (

Id INTEGER PRIMARY KEY AUTOINCREMENT,

Title TEXT NOT NULL,

Description TEXT NOT NULL

IsFavorite INTEGER NOT NULL CHECK (IsFavorite = 0 or IsFavorite = 1),

ProductId TEXT NULL,

@"OfferId INTEGER NULL

the first way is simple, but if you want to have complete control on the table schema, SQL script will offer better control

4. how to drop tables

await _connection.DropTableAsync<Word>();

await _connection.DropTableAsync<Module>();

5. how to insert rows to the table

there most common 2 ways of inserting rows are as following:

connection.InsertAll(lesson.LessonWords); and

connection.Insert(lesson);

6. how to update row in the table

similar to insert, there are 2 common ways to update rows in table.

connection.Update(module);

connection.UpdateAll(module.WordsToBeUpdated);

7. how to delete

connection.Delete(module);

from 4 to 7 , module, lesson are the instances of classes with SQLite attributes similar to the one I show above in Module class, WordsToBeUpdated and (lesson.LessonWords are instances of list <T> of the similar class.

8. how to run queries

connection.Execute(SQLScripts.Procedures.DeleteLessonWord);

9. how to support transaction

await _connection.RunInTransactionAsync((SQLiteConnection connection, List<Module> modules ) =>

{

foreach (var module in modules)

{

Save(module, connection);

}

});

_connection.RunInTransaction(() =>

{

var affectedRow = _connection.Insert(row);

if (affectedRow > 0)

{

row.CascadeId();

_connection.InsertAll(row.LessonWords);

};

});

As you can see there are 2 files, SQLite.cs and SQLiteASync. For almost all functionalities there are 2 versions, one is for async and one is for sync. For example, we have DropTableAsync we also have DropTable, the difference is the connection object, for async we use SQLiteAsyncConnection for non async we use SQLiteConnection

Recommended further reading topics could be indexing, foreign key, constraint, triggers.