Using Json Documents in MySQL

Posted on by Mabrouk Mahdhi

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!

0Shares

Software engineer by Messer Information Services GmbH, Germany.

47 Replies to “Using Json Documents in MySQL”

  16. I’ve been browsing online more than 3 hours today,
    yet I never found any interesting article like
    yours. It’s pretty worth enough for me. In my view, if all webmasters and bloggers made good content as you did, the web will be a lot more useful than ever before.

    Reply

  21. Howdy, i read your blog from time to time and i own a similar one and i was just wondering if you get a lot of spam
    comments? If so how do you prevent it, any plugin or anything you can advise?
    I get so much lately it’s driving me crazy so any help is very much appreciated.

    Reply

  22. Good day! I could have sworn I’ve been to this site before but after reading through some of
    the post I realized it’s new to me. Nonetheless,
    I’m definitely happy I found it and I’ll
    be bookmarking and checking back often!

    Reply

  24. Hi! I know this is kinda off topic but I was wondering which blog platform are you using for this website?

    I’m getting tired of WordPress because I’ve had issues with hackers and I’m looking at options for another
    platform. I would be awesome if you could point me in the direction of a good platform.

    Reply

  27. Excellent beat ! I wish to apprentice even as you amend your site, how could i subscribe for a weblog website?

    The account helped me a acceptable deal. I were tiny bit acquainted of this your broadcast provided vivid transparent idea

    Reply

  30. Hey there superb blog! Does running a blog similar to this take a great deal of
    work? I have virtually no expertise in computer programming however I had been hoping to start my own blog soon. Anyway,
    if you have any recommendations or techniques for new blog owners
    please share. I understand this is off subject but I just needed to ask.
    Thanks a lot!

    Reply

  35. Hello would you mind letting me know which hosting company you’re
    utilizing? I’ve loaded your blog in 3 completely
    different internet browsers and I must say this blog loads a lot quicker then most.
    Can you suggest a good hosting provider at a fair
    price? Thank you, I appreciate it!

    Reply

  36. Definitely believe that which you stated. Your favorite reason appeared
    to be on the net the simplest thing to be aware of. I say
    to you, I certainly get annoyed while people think about worries that they plainly do not know
    about. You managed to hit the nail upon the top and
    defined out the whole thing without having side effect , people can take a signal.
    Will likely be back to get more. Thanks

    Reply

  37. Its like you read my mind! You seem to know so much about this, like
    you wrote the book in it or something. I think that you
    could do with some pics to drive the message home a little bit, but other than that, this is
    magnificent blog. A great read. I’ll certainly be back.

    Reply

  41. Do you mind if I quote a few of your posts as long as I provide credit and sources
    back to your weblog? My blog is in the exact same area of interest as yours and my users would certainly benefit from a lot
    of the information you provide here. Please let me know if
    this ok with you. Thank you!

    Reply

  42. Hey would you mind sharing which blog platform you’re
    working with? I’m planning to start my own blog soon but I’m having a difficult time
    deciding between BlogEngine/Wordpress/B2evolution and
    Drupal. The reason I ask is because your design and style seems
    different then most blogs and I’m looking for something unique.
    P.S Sorry for getting off-topic but I had to ask!

    Reply

  44. Heya i’m for the first time here. I came across this
    board and I to find It truly helpful & it helped me out
    much. I am hoping to provide something back and aid others
    such as you helped me.

    Reply

Leave a Reply

Your email address will not be published.