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

• Minimum Requirements

• Import the Document Store Sample

• Add References to Required DLLs

• Import Namespaces

• Create a Session

• Find a Row Within a Collection

• Insert a New Document into a Collection

• Update an Existing Document

• Delete a Specific Document

• Close the Session

• Complete Code Example

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:

   $> 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:

   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:

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.

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:

// 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

//Insert a new document with an identifier
var obj = new { _id = "UKN", Name = "Unknown" };
Result r = myCollection.Add(obj).Execute();

Update an Existing Document

// 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

r = myCollection.Remove("_id = :id").Bind("id", "UKN").Execute();

Close the Session

session.Close();

Complete Code Example

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

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

    }
  }
}