Sqlite Database in Xamarin Android

This post will show how to implement persistence in Sqlite database in Xamarin Android. This is a continuation of my last post ListView with Xamarin Android. In that post we left off where we can now display the listview with our custom row, read that post to get caught up or look through the source code at Github. From that post or the code you can see that the model class for this project is called ServiceItem.cs with the following properties’

1

2

3

4

5

6

7

8

publicclassServiceItem

{

publicintId{get;set;}

publicstringName{get;set;}

publicstringDescription{get;set;}

publicdoublePrice{get;set;}

publicstringCategory{get;set;}

}

This is the class we want to save to the database. We need to create a database table with five columns: id, name, description, price and category. I will use the SQLite.net ORM to implement the database.

Add Nuget SQlite.net Nuget package. SQlite.Net is also available as a Xamarin component, but I see that Nuget packages are favored over Xamarin components for packages where a Nugerwhen an option exists.

Select the two files SQLite.cs and SQLiteAsync.cs, right click, select refactor and move to the DataAccess folder you created earlier if you followed the last blog post. The is just for project organization and structure.

Add two files ServicesDatabase.cs and ServicesRepository.cs. The ServicesDatabase.cs file will contain the creation of the database. Open the ServicesDatabase.cs and copy and paste or type the following code. Be warned you will get some compiler error but we will fix shortly.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

publicclassServicesDatabase:SQLiteConnection

{

staticreadonly objectLocker=newobject();

///

/// Initialize a new instance of the ServiceDatabase

///

///

publicServicesDatabase(stringpath):base(path)

{

//Create the tables

CreateTable();

Log.Info(MainActivity.Tag,"Database Created");

}

//Get all items in the database method

publicIEnumerable GetItems()whereT:IBusinessEntity,new()

{

lock(Locker)

{

return(fromiinTable()selecti).ToList();

}

}

//Get specific item in the database method with Id

publicTGetItem(intid)whereT:IBusinessEntity,new()

{

lock(Locker)

{

returnTable().FirstOrDefault(x=&gt;x.Id==id);

}

}

publicintSaveItem(Titem)whereT:IBusinessEntity

{

lock(Locker)

{

if(item.Id!=0)

{

Update(item);

returnitem.Id;

}

returnInsert(item);

}

}

publicintDeleteItem(intid)whereT:IBusinessEntity,new()

{

lock(Locker)

{

returnDelete(newT(){Id=id});

}

}

}

Notice that we created the database with only one line of code CreateTable. That is the power of ORM (Object Relational Mappings) like SQLite.net, Entity Framework,etc. With that simple line of code, a database will be created with the corresponding number of columns. In comparison, to create this one table without the help of an ORM, here is the number of lines of code we need to replace just that one line of code. This is native Android/Java

Again, all these lines of code is abstracted away by the ORM with one line of code

1

CreateTable

We need to add some attributes to the model class ServiceItem so SQLite.net will know what class it is suppose to work with. Update the ServiceItem.cs as follows

1

2

3

4

5

6

7

8

9

publicclassServiceItem:IBusinessEntity

{

[PrimaryKey,AutoIncrement,Column("_id")]

publicintId{get;set;}

publicstringName{get;set;}

publicstringDescription{get;set;}

publicdoublePrice{get;set;}

publicstringCategory{get;set;}

}

The data annotation should be self explanatory, we are telling SQLite.net that our Id property is the primary key and should be auto incremented and the standard in native Android SQLite is to name the Id _id. However, you now notice our class is extending from IBusinessEntity. Here is the explanation, to create the table we used the method CreateTable to create the table. Now imagine if this app grows as I intend to further develop it, then this app will need to create invoices, that means we need Transaction table, so we create another table using CreateTable ; the problem is that now we need to define another set of CRUD methods for each of our table. So there will be multiple insert methods in the database for each table that is represented.

The solution is to create an abstract class or an interface from which all your database aware classes should inherit from. To allow flexibility for all the different classes you will need to define their unique properties and methods, this interface will be as simple as possible, in this case it only have an Id. This way a person class can inherit from it and a Car class can inherit from it. Here is the IBusinessEntity.cs content, note that this is often referred to as IEntity.cs in many .Net projects.

1

2

3

4

5

6

7

namespaceXamarinDroidCustomListView.BusinessLayer.Contracts

{

publicinterfaceIBusinessEntity

{

intId{get;set;}

}

}

Repository and Business Manager Classes.
Now we have a database with standard CRUD functionality defined, how do we use it from our app. We can simple program against this database directly from our app, however that will limit your ability to extend or test your app. Enter yet another layer of abstraction. The goal is to program against the Repository, which then handles instantiating the database and calling applicable methods in the database. The benefit of this approach is that you can now test your app functionalities without involving the overhead of dealing with a database. And also you can decide to save your data to the cloud and not locally and this change will be transparent to your app because it is not dependent on concrete implementation to the database. Here my ServiceItem Repository

And finally yet another abstraction, this time the business layer class. This is where you define your business logic. Why another complexities, why not use the repository directly. Well because not every action you need to perform in your model class needs to be persisted in the database. For now the business layer code for this app is pretty basic, here is the code

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

publicstaticclassServicesManager

{

staticServicesManager()

{

}

publicstaticServiceItem GetServiceItem(intid)

{

returnServicesRepository.GetServiceItem(id);

}

publicstaticIList GetServiceItems()

{

returnnewList(ServicesRepository.GetServiceItems());

}

publicstaticintSaveServiceItem(ServiceItem item)

{

returnServicesRepository.SaveServiceItem(item);

}

publicstaticintDeleteServiceItem(intid)

{

returnServicesRepository.DeleteServiceItem(id);

}

}

With this you should be able to save and retrieve data from the database. In the next post, I will show how to get data from the user interface and save to the database/.