When you have a requirement where the structured of the data stored inside the database will change frequently. Typically you opt for a NoSQL solution (MongoDB, CounchDB, etc). This typically means that the stack has to grow with an additional machine as more often than not you always need a relational database inside your project.
All of the major relational databases have now adopted the concept of adding the NoSql paradigm as an additional feature.
MySQL has been supporting this functionality for quite some time now, however support through their .net connector has only been added in recent months. Before I dive in to the code we have to cover the setup details.
The driver communicates to mySQL through the X Protocol. The X Protocol is the new communication layer adopted by MySQL. This means you have to enable the X Plugin on the server side.
The following are the steps to enable the plugin.
You will also need to update you MySQL .Net Connector to the latest version. If you are using the NuGet Package Manager simply search and install the latest version of the MySql.Data package.
Now its time to dive into some coding and get our hands dirty.
- Create the Database
Our first task is to connect to MySQL and create the database.
[code]
string schemaName = "my_test";
// Define the connection URL.
string connectionURL = "mysqlx://root:test@localhost:33060";
// Create the session.
Session session = MySQLX.GetSession(connectionURL);
// Create the schema object.
Schema schema = session.CreateSchema(schemaName);
[/code]
As you have already noticed all actions have to be performed through the X Protocol. We need to establish a session using the GetSession and the connection string (that is passed as a parameter).
Once we have a session we can create the database using CreateSchema api call.
A Schema can store collections of documents. It can be considered as a database when it comes to relational databases.
2. Insert the document into the schema
Now it is time to create a database collection and insert documents into your schema.
[code]
var clientsCollection = schema.CreateCollection("clients");
var doc = new DbBoc("{\"_id\" : 1, \"Name\" : \"Bernardette\, \"Surname\" : \"Smith\" }";
clientsCollection.Add(doc).Execute();
[/code]
From the example above the Add method will “mark” the document as being ready to be added to the collection. Document is only inserted once the Execute method is called.
3. Search for items inside the collection
[code]
DbDoc docParams = new DbDoc( new { Name = "Paul" });
DocResult foundDocuments = clientsCollection.Find("Name = :Name").Bind(docParams).Execute();
while (foundDocuments.Next())
{
Console.WriteLine(foundDocuments.Current["_id"]);
Console.WriteLine();
}
The Find function contains the search term to be used to find the documents. The Bind call defines
the attributes for the search term.
[/code]
4. Modify items inside the collection
[code]
var modifyResult = clientsCollection.Modify("Name = :Name").Bind("Name" : "John").Set("Surname", "Johns");
Console.WriteLine("Records Affected : " + modifyResult.RecordsAffected);
[/code]
5. Delete items inside the collection.
[code]
clientsCollection.Remove("Name = :Name").Bind("Name", "Paul).Execute();
[/code]
We have now completed all the CRUD operations on our collection and we are ready to build our system.
Other factors to put in consideration if you want to opt for such a solution.
MySQL License Model.
A lot of organisations/individuals assume that if the product is open source it can be used for free. It is important to note that MySQL operates in two license models. One is covered by GPL and a commercial license. If your business model violates GPL then you will need to fork out money and but a commercial license.
Typically an option would be to user MariaDB as it is a fork of MySQL. However although MariaDB supports its own JSON functions to allow manipulation of JSON documents. The X Protocol has not been merged into it. MariaDB is not a drop replacement in this case.
Maturity and Performance
Something that has be factored in is that MongoDB and other NoSQL databases have several years of maturity.This paper outlined how MongoDB outranked MySQL when it comes to performance operations on document collections.
Hope you liked this article. Feel free to add me on Linkedin here or contact me on email on [email protected]
