This tutorial provides instructions to get you started using MySQL as a document store with MySQL Connector/NET.
For concepts and additional usage examples, see X DevAPI User Guide.
MySQL Server 8.0.11 with X Protocol enabled
Connector/NET 8.0.11
Visual Studio 2013/2015/2017
world_x
database sample
A MySQL script is provided with data and a JSON collection. The sample contains the following:
Collection
countryinfo: Information about countries in the world.
Tables
country: Minimal information about countries of the world.
city: Information about some of the cities in those countries.
countrylanguage: Languages spoken in each country.
To install the world_x
database sample, follow
these steps:
Download
world_x.zip
from http://dev.mysql.com/doc/index-other.html.Extract the installation archive to a temporary location such as
/tmp/
.Unpacking the archive results in two files, one of them named
world_x.sql
.Connect to the MySQL server using the MySQL Client with the following command:
Press CTRL+C to copy$> mysql -u root -p
Enter your password when prompted. A non-root account can be used as long as the account has privileges to create new databases. For more information about using the MySQL Client, see mysql — The MySQL Command-Line Client.
Execute the
world_x.sql
script to create the database structure and insert the data as follows:Press CTRL+C to copymysql> SOURCE /temp/world_x.sql;
Replace
/temp/
with the path to theworld_x.sql
file on your system.
Create a new Visual Studio Console Project targeting .NET Framework 4.6.2 (or later), .NET Core 1.1, or .NET Core 2.0. The code examples in this tutorial are shown in the C# language, but you can use any .NET language.
Add a reference in your project to the following DLLs:
MySql.Data.dll
Google.Protobuf.dll
Import the required namespaces by adding the following statements:
Press CTRL+C to copyusing MySqlX.XDevAPI; using MySqlX.XDevAPI.Common; using MySqlX.XDevAPI.CRUD;
A session in the X DevAPI is a high-level database session concept that is different from working with traditional low-level MySQL connections. It is important to understand that this session is not the same as a traditional MySQL session. Sessions encapsulate one or more actual MySQL connections.
The following example opens a session, which you can use later to retrieve a schema and perform basic CRUD operations.
Press CTRL+C to copystring schemaName = "world_x"; // Define the connection string string connectionURI = "mysqlx://test:test@localhost:33060"; Session session = MySQLX.GetSession(connectionURI); // Get the schema object Schema schema = session.GetSchema(schemaName);
After the session is instantiated, you can execute a find operation. The next example uses the session object that you created:
Press CTRL+C to copy// Use the collection 'countryinfo' var myCollection = schema.GetCollection("countryinfo"); var docParams = new DbDoc(new { name1 = "Albania", _id1 = "ALB" }); // Find a document DocResult foundDocs = myCollection.Find("Name = :name1 || _id = :_id1").Bind(docParams).Execute(); while (foundDocs.Next()) { Console.WriteLine(foundDocs.Current["Name"]); Console.WriteLine(foundDocs.Current["_id"]); }
Press CTRL+C to copy//Insert a new document with an identifier var obj = new { _id = "UKN", Name = "Unknown" }; Result r = myCollection.Add(obj).Execute();
Press CTRL+C to copy// using the same docParams object previously created docParams = new DbDoc(new { name1 = "Unknown", _id1 = "UKN" }); r = myCollection.Modify("_id = :Id").Bind("id", "UKN").Set("GNP", "3308").Execute(); if (r.AffectedItemsCount == 1) { foundDocs = myCollection.Find("Name = :name1|| _id = :_id1").Bind(docParams).Execute(); while (foundDocs.Next()) { Console.WriteLine(foundDocs.Current["Name"]); Console.WriteLine(foundDocs.Current["_id"]); Console.WriteLine(foundDocs.Current["GNP"]); } }
Press CTRL+C to copyr = myCollection.Remove("_id = :id").Bind("id", "UKN").Execute();
The following example shows the basic operations that you can perform with a collection.
Press CTRL+C to copyusing MySqlX.XDevAPI; using MySqlX.XDevAPI.Common; using MySqlX.XDevAPI.CRUD; using System; namespace MySQLX_Tutorial { class Program { static void Main(string[] args) { string schemaName = "world_x"; string connectionURI = "mysqlx://test:test@localhost:33060"; Session session = MySQLX.GetSession(connectionURI); Schema schema = session.GetSchema(schemaName); // Use the collection 'countryinfo' var myCollection = schema.GetCollection("countryinfo"); var docParams = new DbDoc(new { name1 = "Albania", _id1 = "ALB" }); // Find a document DocResult foundDocs = myCollection.Find("Name = :name1 || _id = :_id1").Bind(docParams).Execute(); while (foundDocs.Next()) { Console.WriteLine(foundDocs.Current["Name"]); Console.WriteLine(foundDocs.Current["_id"]); } //Insert a new document with an id var obj = new { _id = "UKN", Name = "Unknown" }; Result r = myCollection.Add(obj).Execute(); //update an existing document docParams = new DbDoc(new { name1 = "Unknown", _id1 = "UKN" }); r = myCollection.Modify("_id = :Id").Bind("id", "UKN").Set("GNP", "3308").Execute(); if (r.AffectedItemsCount == 1) { foundDocs = myCollection.Find("Name = :name1|| _id = :_id1").Bind(docParams).Execute(); while (foundDocs.Next()) { Console.WriteLine(foundDocs.Current["Name"]); Console.WriteLine(foundDocs.Current["_id"]); Console.WriteLine(foundDocs.Current["GNP"]); } } // delete a row in a document r = myCollection.Remove("_id = :id").Bind("id", "UKN").Execute(); //close the session session.Close(); Console.ReadKey(); } } }