July 2014 – Subodh Pushpak's Blog

Universal apps are wonderful addition for developers to new thriving apps ecosystem of Windows Phone and windows 8. Now, developers can target both the platforms with uniform approach without duplication of efforts.

For those from iOS / android background; the approach to universal apps on these platform is different and thus is the outcome.

So, as it is evident; using Sqlite (or CoreData) code or entities on universal iOS app is no brainer; if planned well. On Microsoft platform introduction of portable libraries and now universal shared project have made it easier to share code targeting various devices.

There is an awesome initiative to bridge this gap on https://sqlitepcl.codeplex.com/. However, it turns out that the library until now (at the time of writing this blog); it only supports only direct SQL commands (no LINQ provider) for CRUD operations, which in-turns means that there is no support for strongly typed code-first entity classes.

I ended up creating a proof-of-concept on same and the idea was to create a portable library with a “DataManger” class which abstracts all the sqlite functions so that they can be used by both WP and Windows 8 app Projects. In windows universal apps there is a shared library in the solution for sharing code among these two projects; however; a reference cannot be added to shared project. And hence, the need of yet another portable library.

Here is a snapshot of how it looks like:

The DBmanager encapsulates the sqlite details for CRUD operations:

 public static void PrepareDB()
        {
            using (var connection = new SQLiteConnection("Storage.db"))
            {
                using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS Users (Id INTEGER  PRIMARY KEY AUTOINCREMENT , Name NVARCHAR(100), ModifiedOn DATETIME, CreatedOn DATETIME, IsActive INTEGER);"))
                {
                    statement.Step();
                }

                using (var statement = connection.Prepare(@"INSERT INTO Users (Name,CreatedOn, ModifiedOn, IsActive)  VALUES(@Name,DATETIME('NOW'), DATETIME('NOW'),@IsActive);"))
                {
                    statement.Bind("@Name", "Subodh Pushpak");
                    //statement.Bind("@CreatedOn", DateTime.Now.Ticks);
                    statement.Bind("@IsActive", 1);

                    // Inserts data.
                    statement.Step();

                    // Resets the statement, to that it can be used again (with different parameters).
                    statement.Reset();
                    statement.ClearBindings();
                }
            }
        }

        public static List<UserEntity> GetData()
        {
            List<UserEntity> users = new List<UserEntity>();
            using (var connection = new SQLiteConnection("Storage.db"))
            {
                using (var statement = connection.Prepare(@"SELECT id,Name,CreatedOn, ModifiedOn FROM Users ORDER BY Name;"))
                {
                    while (statement.Step() == SQLiteResult.ROW)
                    {
                        UserEntity userEntity = new UserEntity();
                        userEntity.Id = statement.GetValue<long>(0);
                        userEntity.Name = statement.GetValue<string>(1);
                        userEntity.CreatedOn = statement.GetValue<string>(2);
                        userEntity.ModifiedOn = statement.GetValue<string>(3);
                        users.Add(userEntity);
                    }
                }
            }
            return users;
        }

Alternately; if you require code-first, strongly typed entities to code sqlite against; you may follow alternative approach; which is inspired by Xamarin and tested against Xamarin android and iOS apps as well. This approach depends heavily on sharing code using “link files”

The solution essentially looks like:

The entity may be defined as. Notice how Primary Key and Auto increment has been defined:

public abstract class BusinessEntityBase : IBusinessEntity
	{
		public BusinessEntityBase ()
		{
		}
		
		[PrimaryKey, AutoIncrement]
        public int Id { get; set; }

        public string ModifiedOn { get; set; }
        public string CreatedOn { get; set; }
        public bool IsActive { get; set; }

	}

Another interesting code here is of Sqlite.cs and LocalDatabase: To make it work on all platforms (iOS, Android, Windows Phone, Windows 8) below is the code of particular interest; which defines where should be the Sqlite file be created.

public static string DatabaseFilePath

{

get {

var path = "LocalDB.db3";

#if SILVERLIGHT //WINDOWS_PHONE //

path = "LocalDB.db3";

#else

#if NETFX_CORE

path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "LocalDB.db3");

#else

#if ANDROID

string libraryPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); ;

#else

// need to put in /Library/ on iOS5.1 to meet Apple’s iCloud terms

// (non-user-generated data should NOT be in Documents)

string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder

string libraryPath = Path.Combine (documentsPath, "../Library/");

#endif

path = Path.Combine (libraryPath, "LocalDB.db3");

#endif

return path;
}

}

Here is the output on windows 8 and WP8

The proof of concept code using SqllitePCL is hosted at http://1drv.ms/1zMdQYz

The proof of concept code using entity code first class is @ http://1drv.ms/1zMdGk2

Enjoy!!!

Month: July 2014

Using Sqlite on Universal Apps