Using Json Documents in MySQL

Introduction

Before some days I started a new project with blazor. I am using a MySQL database and I tried to work somehow with json documents in MySQL. During searching, I met this gratefull page : https://www.sitepoint.com/use-json-data-fields-mysql-databases/ . Then, I decided to create a nuget package that should do the full communication between my application and the json oriented database. Ready to know more? Let’s go!

Installation

In order to use the package, you just need to install it from here. Or directly with command:

Install-Package Xamabrouk.MySQLJsonBased

This package is using two other important packages :

• Xamabrouk.MySQLJsonBased.Entity : Provides entities used in the package. (Installation)
• Xamabrouk.MySQLJsonBased.Interface : Provides interfaces used in the package. (Installation)

Let’s code !

First of all, we create a new console .Net core project. The we install our package.

Then let’s say you want to manage user roles. Create than a UserRole class. This class has to implement the interface IJsonTable which defines the Id of your future DB-table, like:

public class UserRole : IJsonTable
{
public ObjectId Id { get; set; }
public string Name { get; set; }
public override string ToString() => $"{Id} : {Name}";
}

In the program class, you create a new instance of MySQLDriver:

MySQLDriver driver = new MySQLDriver($"Server=YOUR_SERVER;Port=YOUR_PORT;Database=YOUR_DB;User=YOUR_USER;Pwd=USER_PASSWORD;");

Now, it is too easy to insert a new row in your table. You just need to call the method Save or SaveAsync:

bool result = driver.Save(new UserRole() { Name = "Administrator" });

Or,

bool result = await driver.SaveAsync<UserRole>(new UserRole());

The Save method, calls the method Update if the item exists already in the table, and Insert if not.

The same game with Delete and Async methods (SaveAsync, UpdateAsync, DeleteAsync, etc. ) …

Note

You dont have to create the table manually in the database. When you call Save the table will be created automatically if it doesn’t exists.

Table Structure

And here is an example of using the JsonTableAttribute:

[JsonTable(name:"YourTableName",jsonFieldName:"JsonDocument")]
public class UserRole : IJsonTable
{
   public ObjectId Id { get; set; }
   public string Name { get; set; }

   public override string ToString() => $"{Id} => {Name}";
}

Now if you run the driver.Save(new UserRole() { Name = "Administrator" }); again, a new table ‘YourTableName’ will be created and a new row (new role in this case) will be inserted:

Conclusion

It’s very simple to use and learn this package. It has only one class MySQLDriver that you can use also as a base class for other classes.

I hope you will enjoy this!

• Simple project : https://github.com/MabroukENG/MySQLJsonBasedSimple
• Source code: https://github.com/MabroukENG/MySQLJsonBased