Documentation Home
MySQL Connector/NET Developer Guide
Related Documentation Download this Manual

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

4.9 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 5.7.12 with the X Protocol enabled. For setup instructions, see Setting Up MySQL as a Document Store.

  • Connector/NET 8.0.8

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

    shell> 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.5.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();

    }
  }
}

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.