Skip to content

C# Basic Example

This document will guide you how to use C# to build a simple application and implement the CRUD (create, read, update, delete) function.

Prepare before starting

Steps

Step 1: Create a C# application

Use the dotnet command to create an application. For example, create a new app called myapp:

dotnet new console -o myapp

Then switch to the myapp directory

Step 2: Add the MySQL Connector/NET NuGet package

Install the MySql.Data package using the NuGet package manager:

dotnet add package MySql.Data

Step 3: Connect to MO Intelligence instance for operation

Write code to connect to MO Intelligence instances, create a student table and perform additions, deletions, modifications and search operations. Write the following code in the Program.cs file:

using System;
using MySql.Data.MySqlClient;

class Program
{

    static void ExecuteSQL(MySqlConnection connection, string query)
    {
        using (MySqlCommand command = new MySqlCommand(query, connection))
        {
            command.ExecuteNonQuery();
        }
    }
    static void Main(string[] args)
    {
        Program n = new Program();
        string connectionString = "server=freetier-01.cn-hangzhou.cluster.matrixonecloud.cn;user=585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin;database=test;port=6001;password=xxx";
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            try{
            connection.Open();
            Console.WriteLine("Connection established");
            // Create table
            ExecuteSQL(connection,"CREATE TABLE IF NOT EXISTS Student (id INT auto_increment PRIMARY KEY, name VARCHAR(255),age int,remark VARCHAR(255) )");
            Console.WriteLine("Table creation succeeded!");
            //Insert data
            ExecuteSQL(connection,"INSERT INTO Student(name,age) VALUES ('Zhang San',22),('Li Si',25),('Zhao Wu',30)");
            Console.WriteLine("Successfully inserted data!");
            //Update data
            ExecuteSQL(connection,"UPDATE Student SET remark = 'Updated' WHERE id = 1");
            Console.WriteLine("Successfully updated data!");
            //Delete data
            ExecuteSQL(connection,"DELETE FROM Student WHERE id = 2");
            Console.WriteLine("Successfully deleted data!");
            //Query data
            MySqlCommand command = new MySqlCommand("SELECT * FROM Student", connection);
            using (MySqlDataReader reader = command.ExecuteReader())
            {
                While (reader.Read())
                {
                    Console.WriteLine($"Name: {reader["name"]}, Age: {reader["age"]}, Note: {reader["remark"]}");
                }
            }
            Console.WriteLine("Data query succeeded!");
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);

            }
            Finally
            {
                Console.WriteLine("Prepare to disconnect");
                connection.Close();
                Console.WriteLine("Disconnect successfully!");
            }

            //connection.Close();
        }
    }
}

Step 4: Run the program

Execute the command dotnet run in the terminal:

(base) admin@admindeMacBook-Pro myapp % dotnet run
The connection has been established
Table building successfully!
Data was inserted successfully!
Data was updated successfully!
Data was deleted successfully!
Name: Zhao Wu, Age: 30, Notes:
Name: Zhang San, Age: 22, Notes: Updated
Data query was successful!
Prepare to disconnect
Disconnect successfully!

Step 5: Check the data

Use the Mysql client to connect to the MO Intelligence instance to query the Student table:

mysql> select * from student;
+------+-------+---------+
| id | name | age | remark |
+------+-------+---------+
| 3 | Zhao Wu | 30 | NULL |
| 1 | Zhang San | 22 | Updated |
+------+-------+---------+
2 rows in set (0.00 sec)

You can see that the data returns correctly.