10bet网址
Connectors and APIs Manual
Download this Manual
PDF (US Ltr)- 5.2Mb
PDF (A4)- 5.2Mb


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. For concepts and additional usage examples, seeX 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_xdatabase 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 theworld_xdatabase sample, follow these steps:

  1. Downloadworld_x.zipfrom//www.delbede.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 namedworld_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, seemysql — The MySQL Command-Line Client.

  4. Execute theworld_x.sqlscript to create the database structure and insert the data as follows:

    mysql> SOURCE /temp/world_x.sql;

    Replace/temp/with the path to theworld_x.sqlfile 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(); } } }