Documentation Home
MySQL Connector/NET Developer Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb


MySQL Connector/NET Developer Guide  /  Connector/NET Tutorials  /  Tutorial: Basic CRUD Operations with Connector/NET

6.6 Tutorial: Basic CRUD Operations with Connector/NET

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.

Minimum Requirements

  • MySQL Server 8.0.11 with X Protocol enabled

  • Connector/NET 8.0.11

  • Visual Studio 2013/2015/2017

  • world_x database sample

Import the Document Store 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:

  1. Download world_x.zip from http://dev.mysql.com/doc/index-other.html.

  2. 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.

  3. 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.

  4. Execute the world_x.sql script to create the database structure and insert the data as follows:

    Press CTRL+C to copy
    mysql> SOURCE /temp/world_x.sql;

    Replace /temp/ with the path to the world_x.sql file on your system.

Add References to Required DLLs

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 Namespaces

Import the required namespaces by adding the following statements:

Press CTRL+C to copy
using MySqlX.XDevAPI; using MySqlX.XDevAPI.Common; using MySqlX.XDevAPI.CRUD;

Create a Session

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 copy
string 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);

Find a Row Within a Collection

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"]); }

Insert a New Document into a Collection

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();

Update an Existing Document

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"]); } }

Delete a Specific Document

Press CTRL+C to copy
r = myCollection.Remove("_id = :id").Bind("id", "UKN").Execute();

Close the Session

Press CTRL+C to copy
session.Close();

Complete Code Example

The following example shows the basic operations that you can perform with a collection.

Press CTRL+C to copy
using 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(); } } }