Tag Archive : Sql

/ Sql

Every good app needs to use a database. This database can be local (present in the app itself), or it can be remote (saved somewhere on the internet). The third option (fairly common) is to use both and then use a synchronisation mechanism between the two databases. In the case of a local database, the best possible option for our apps is to use an SQL database and use the SQLite library. This library can be used on mobile phones and PCs and is present on a multitude of different applications.

Use SQLite in Xamarin

In theory (I tell you later a better way) to add and use a SQL database in Xamarin.Forms you need to install the “sqlite-net-pcl” library in your projects (.netstandard, Android, iOS, UWP) and then create Dependencies Services (one for each platform).

The whole procedure is not particularly complicated, but it takes about half an hour. If you multiply half an hour for each project you are going to create, you will see that the amount of time lost becomes considerable.

After so many projects and many lost hours I decided to create and make available for everyone my library based on “sqlite-net-pcl“.

My library is called MtSql and allows you to add a SQL database to Xamarin apps in few seconds and in an extremely easy way. Going from 30 minutes to a few seconds is no small feat so I’ll explain how.

MTSQL

My library works with Xamarin and Windows, so you can use it in practically every project where you need to use a database. As this is a blog about Xamarin, I show you how it works on this platform.

Here are the steps to use a SQLite database in your Xamarin.Forms project.

  • Right click on your solution and select Manage Nuget Packages for Solution
  • Search for “MarcTron” (so you can see my other plugins too 🙂 ) or directly MTSql. Install MarcTron.Sqlite in your projects (.netstandard, Android, iOS, UWP).
  • Finished!!!! You have nothing else to do, right now you are ready to use a SQL database inside your app. Time taken: few seconds 🙂

How to use MTSQL

Now that the library is installed in your solution, we need to tell our app which database to use, so we need to establish a connection with the database. To do this we have several possibilities:

using MarcTron.Plugin;
...

SQLiteConnection conn = MtSql.Current.GetConnection("yourdbname.db3");

in this case we have established a connection with the “yourdbname.db3” database. If we want to create an asynchronous connection, we can instead write:

SQLiteAsyncConnection conn = MtSql.Current.GetConnectionAsync("yourdbname.db3");

Since version 1.2 of MTSql I have created an additional Helper to make everything even easier, so to establish the connection with your database, you can write:

MtSql.Helper.Initialize("yourdbname.db3");

PLEASE NOTE: You must always initialise the database with one of the three previous methods before using it.

In my library, to simplify your life, I’ve created a basic template for the tables in your database. This model is called BaseModel (I write it here just to show it to you):

public class BaseModel
{
    [PrimaryKey]
    public string Id { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }
}

Using it is very simple. For example, if you have a table called User with a Name field, you can write the class that defines it as:

public class User : BaseModel
{
    public string Name{ get; set; }
}

Doing so your table will have not only the Name field but also a primary Id key and two CreatedAt, UpdatedAt dates, which specify when the data has been created and modified (very useful if you want to sort your tables by creation or update date).

The helper I created has inside a method called Save. This method will deal not only with inserting or updating a record within your tables but also creating a GUID for you (if you have not already created it) and updating the value of CreatedAt and UpdatedAt. FANTASTIC!

PLEASE NOTE: The Save method inserts an object if its primary key value is not present in the table while updating it if its value is already in the table.

You don’t have to use the BaseModel class but I suggest you to do so and now I’ll show you why.

An Example

Now let’s see a quick example to initialise the database, insert an object and then update it:

MtSql.Helper.Initialize("MyDatabase.db3");
MtSql.Helper.CreateTable<User>();

User user = new User() {Name = "Marco"};
MtSql.Helper.Save(user);

user .Name = "Marco Troncone";
MtSql.Helper.Save(user);

In the first line we have initialised the database (Don’t forget it, it is fundamental to do it).

We then created a User table using the User class written above. At this point we created a user and gave him the name Marco.

When we call the method

MtSql.Helper.Save(user);

since user does not have an Id, the Save method will create one automatically (if you want you can assign one yourself) and will add the user to the table, setting CreatedAt and UpdatedAt to the current time.

At this point we try to change the user name and save the change:

user.Name = "Marco Troncone";
MtSql.Helper.Save(user);

This time user has already an Id and this Id is already present in the table, this time the Save method will not add a new object to the table but will update the current object, automatically updating UpdatedAt. Easy right?

Thanks to the MTSql plugin, in exactly 6 lines of code we have initialised our database, created a table, added an object and modified an object.

Other methods inside the Helper

In the previous example, we’ve seen some helper methods (Initialise, CreateTable, Save), let’s see what the other methods we have:

//To insert a single item inside the table
Insert<TType>(TType item);

//To insert multiple items inside the table
InsertAll<TType>(IEnumerable<TType> items);

//To update an item inside the table
Update<TType>(TType item);

//To find an item inside the table
FirstOrDefault<TType>(Expression<Func<TType, bool>> expression);

//To get from the table the item with Id itemId
GetFromId<TType>(string itemId);

//To get from the table the items with parameter quals to value
GetBy<TType>(string parameter, object value);

//To get all the items from the table
GetAll<TType>();

//To delete the item from the table
Delete<TType>(TType item);

//To delete ALL the items from the table. WARNING!!!
DeleteAll<TType>();

//To see if an item exists
Exists<TType>(string itemId);

//To count the number of items in a table
Count<TType>();

The Helper I’ve created has all the main methods to work on your database. If some methods are not available, you can directly access the database (and therefore all its methods) using the command:

MtSql.Helper.Conn

this is a direct link to the database connection and from here you can find all the supported methods.

Conclusions

Creating a database could be a complicated operation but thanks to the MTSql library, creating a database for our Xamarin projects. Forms is incredibly easy. Install the library from Nuget and in a few seconds and a few lines of code, you have a database ready to be used in your Android, iOS and UWP projects.

Do you have any doubts or suggestions? Add a comment below, and if the article and the library were helpful to you, share them.

Sqlite for Xamarin made easy

May 31, 2019 | Plugin, Tutorial | 12 Comments

Let’s see how we can add a Sqlite database to your Xamarin projects in an incredibly easy way.

To help you to speed up your Xamarin development, I?ve created a set of plugins, one of them is MTSQL. Thanks to this plugin you can add a Sqlite database with a single line of code. The plugin is built on top of the Sqlite-net plugin by Frank A. Krueger.

A couple of useful link you can find useful:

Nuget link:https://www.nuget.org/packages/MarcTron.SQL

Project website:http://www.xamarinexpert.it/plugins/mt-sql/

To report any issue:https://bitbucket.org/marcojak81/mtsql

And now let?s see how to integrate the plugin inside your Xamarin Forms solution.

First of all we need to install the plugin. To do that, do a right-click on your solution and click on Manage NuGet Packages for Solution

Now search the package MarcTron.Sqlite, click on it and remember to select all your projects (.Net Standard project + all the main application projects) then click Install.

The Plugin will take care to install for you also the Sqlite-net plugin byFrank A. Krueger.

If everything worked as expected, you will see the version of the plugin next to each of the projects you have selected in the previous step.

Now it’s time to try the plugin to see how easy it is.

Inside the MainPage constructor you can see that the only line of code you need to create your database and open a connection to it is

SQLiteConnection conn = MTSql.Current.GetConnection("YOURDBNAME.db3");

Remember to replace the string with the name you want for your database.

using MarcTron.Plugin.MTSql;
using SQLite;
using Xamarin.Forms;

namespace YOURNAMESPACE
{
    public partial class MainPage : ContentPage
    {
        class TestTable
        {
            [PrimaryKey, AutoIncrement]
            public int Id { get; set; }

            public string name { get; set; }

            public TestTable()
            {
            }

            public TestTable(string name)
            {
                this.name = name;
            }
        }

        public MainPage()
        {
            InitializeComponent(); //The only line you need to create your database.
            SQLiteConnection conn = MTSql.Current.GetConnection("YOURDBNAME.db3"); 

            //This in case you want to use an async connection
            //SQLiteAsyncConnection connAsync = MTSql.Current.GetConnectionAsync();

            //This is just for test...
            //Create the table TestTable
            conn.CreateTable<TestTable>();
            //Insert some elements
            conn.Insert(new TestTable("A"));
            conn.Insert(new TestTable("B"));
            conn.Insert(new TestTable("C"));
            //Verify that the elements are there
            Label1.Text = "Rows:" + conn.Table<TestTable>().Count();
        }
    }
}

As you can see in the image after we obtain a connection to the database, we can create a table and add some elements to it. Just for test I have added 3 items. If everything works as expected we should see inside the text “Rows:3” on screen. And this is exactly what we get launching the app on UWP.

So, thanks to this plugin, you have your Sqlite database inside your app with only 1 line of code.

What do you think? Add your comment at the end of the page.

Xamarin.Forms is fantastic, you write your code once inside your PCL/.NetStandard project and it will run on many different platforms.However sometimes we need to access directly the native platform. To do so, we can use something called DependencyService.DependencyService allows us to use platform-specific functionality from shared code.To use the DependencyService we need:

  • An interface inside our shared code
  • An implementation for each platform
  • A registration

After the DependencyService is ready, then we can call it from our shared code.If we want to add a Sqlite database to our Xamarin.Forms project, because we cannot create it directly in our shared code, we need to use a DependencyService so in this tutorial we are going to see how to use the DependencyService to create and use a Sqlite database in our multi platform app.

TUTORIAL

First of all, let’s create an empty Xamarin.Forms project using a .NetStandard project. For a tutorial on how to create a Xamarin.Forms project, you can read this guide: http://www.xamarinexpert.it/2018/03/03/xamarin-forms-a-guide-for-beginners/

Empty Xamarin Project
Fig.1 Structure of an empty Xamarin.Forms Project

In the first image you can see the structure of a Xamarin.Forms project.As we said, the creation of a DependencyService consists of 3 steps.

First Step – The interface

Now the first step to do, is to create the Interface for our DependencyService.Let’s create and add an interface called ISql to our .NetStandard project:

public interface ISql
{
    SQLiteConnection GetConnection(string dbname = "database.db3");
    SQLiteAsyncConnection GetConnectionAsync(string dbname = "database.db3");
}

This interface exposes two methods:GetConnection: Get a connection for the Sqlite database.GetConnectionAsync: Get an async connection for the Sqlite database.Really easy. Don’t you think?

Second Step – The implementation

Inside this second step, we need to implement the interface in each platform. Let’s start with Android

Android

We need to create in Android a class the implements the ISql interface, let’s call this class SqlImplementation:

public class SqlImplementation : ISql
{
    public SQLiteConnection GetConnection(string dbname = "database.db3")
    {
        var sqliteFilename = dbname;
        string documentsPath = System.Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        // Documents folder
        var path = Path.Combine(documentsPath, sqliteFilename);
        var conn = new SQLiteConnection(path, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex);
        return conn;
    }

    public SQLiteAsyncConnection GetConnectionAsync(string dbname = "database.db3")
    {
        var sqliteFilename = dbname;
        string documentsPath = System.Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
        var path = Path.Combine(documentsPath, sqliteFilename);
        return new SQLiteAsyncConnection(path);
    }
}

We have basically implemented the two methods of our ISql interface. These two methods return the native connection to an Android Sqlite database.

iOS

Now we need to create the same class inside the native  iOS project:

public class SqlImplementation : ISql
{
    public SQLiteConnection GetConnection(string dbname = "database.db3")
    {
        var sqliteFilename = dbname;
        string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder

        var path = Path.Combine(documentsPath, sqliteFilename);
        var conn = new SQLiteConnection(path, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex);
        return conn;
    }

    public SQLiteAsyncConnection GetConnectionAsync(string dbname = "database.db3")
    {
        var sqliteFilename = dbname;
        string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
        var path = Path.Combine(documentsPath, sqliteFilename);
        return new SQLiteAsyncConnection(path);
    }
}

As you can see the code is basically the same but it’s IMPORTANT to add the implementation to each platform you need to use, otherwise you’ll get an error.

UWP

Now it’s time to implement the interface for UWP:

public class SqlImplementation : ISql
{
    public SQLiteConnection GetConnection(string dbname = "database.db3")
    {
        var sqliteFilename = dbname;
        var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, sqliteFilename);
        var conn = new SQLiteConnection(path, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex);
        return conn;
    }

    public SQLiteAsyncConnection GetConnectionAsync(string dbname = "database.db3")
    {
        var sqliteFilename = dbname;
        var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, sqliteFilename);
        return new SQLiteAsyncConnection(path);
    }
}

and this concludes the second step.

Third Step – The registration

it’s extremely important to register the DependencyService otherwise it will not work.Don’t worry, this step is incredibly easy, the only thing you need to do is to add this line of code in each SqlImplementation class just before the namespace:

[assembly: Dependency(typeof(SqlImplementation))]

so for example the final code of your Android implementation will be:

[assembly: Dependency(typeof(SqlImplementation))]

namespace YOURNAMESPACE
{
    public class SqlImplementation : ISql
    {
        public SQLiteConnection GetConnection(string dbname = "database.db3")
        {
            var sqliteFilename = dbname;
            string documentsPath = System.Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
            var path = Path.Combine(documentsPath, sqliteFilename);
            var conn = new SQLiteConnection(path, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex);
            return conn;
        }

        public SQLiteAsyncConnection GetConnectionAsync(string dbname = "database.db3")
        {
            var sqliteFilename = dbname;
            string documentsPath = System.Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
            var path = Path.Combine(documentsPath, sqliteFilename);
            return new SQLiteAsyncConnection(path);
        }
    }
}

REMEMBER to add the assembly line to EACH of your SqlImplementation classes.

HOW TO USE IT

Now the DependencyService is ready, we just need to call use it. If we want to get the SqLite connection we just need to use a single line of code inside our share code:

SQLiteConnection database = DependencyService.Get<ISQLite>().GetConnection();

Now you have your sql connection in you shared code so you can start to use your SqLite database.

Sqlite Plugin

It’s extremely easy to use the DependencyService to add a Sqlite database to your Xamarin.Forms app, but if you want an already made solution, you can use my Sqlite plugin so after you install it, you can directly use your database with a single line of code:

SQLiteConnection conn = MTSql.Current.GetConnection("yourdatabasename.db3");

It cannot be easier than that.You can find a tutorial on how to use the plugin here: http://www.xamarinexpert.it/2018/03/01/sqlite-made-easy/The official page is here: http://www.xamarinexpert.it/plugins/mt-sqlite/You can find the nuget package here: https://www.nuget.org/packages/MarcTron.Sqlite

More

You have doubts about the DependencyService or Sql? You want to know more? Ask me adding a comment below this article.