C# 資料上雲端：申請 TiDB Cloud、用 .NET 連上並建好第一張表

CREATE DATABASE MyInventory;

USE MyInventory;

CREATE TABLE IF NOT EXISTS Products (
    Id       INT NOT NULL AUTO_INCREMENT,
    Name     VARCHAR(100),
    Category VARCHAR(50),
    Quantity INT,
    Price    DECIMAL(10, 2),
    PRIMARY KEY (Id)
);

dotnet add package MySql.Data

using MySql.Data.MySqlClient;

public static class MySqlDbHelper
{
    // ⚠ placeholder：換成你自己的 TiDB Cloud 連線資訊，別寫死真密碼
    // 欄位順序：Server → Database → Uid → Pwd → Port → SslMode
    private static readonly string connStr =
        "Server=gateway01.xxxx.prod.aws.tidbcloud.com;" +
        "Database=MyInventory;" +
        "Uid=xxxxxxxx.root;" +
        "Pwd=你的密碼;" +
        "Port=4000;" +
        "SslMode=Required;";

    // 建表：跟 SQLite 版的差別只有連線類別換成 MySql
    public static void InitDb()
    {
        using var conn = new MySqlConnection(connStr);
        conn.Open();
        string sql = @"CREATE TABLE IF NOT EXISTS Products (
            Id       INT NOT NULL AUTO_INCREMENT,
            Name     VARCHAR(100),
            Category VARCHAR(50),
            Quantity INT,
            Price    DECIMAL(10, 2),
            PRIMARY KEY (Id)
        )";
        using var cmd = new MySqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
    }

    // 新增一筆（參數化，避免 SQL injection）
    public static void InsertProduct(string name, string category, int quantity, decimal price)
    {
        using var conn = new MySqlConnection(connStr);
        conn.Open();
        string sql = @"INSERT INTO Products (Name, Category, Quantity, Price)
                       VALUES (@Name, @Category, @Quantity, @Price)";
        using var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@Name", name);
        cmd.Parameters.AddWithValue("@Category", category);
        cmd.Parameters.AddWithValue("@Quantity", quantity);
        cmd.Parameters.AddWithValue("@Price", price);
        cmd.ExecuteNonQuery();
    }

    // 讀回全部，印到 Console
    public static void PrintAllProducts()
    {
        using var conn = new MySqlConnection(connStr);
        conn.Open();
        string sql = "SELECT Id, Name, Category, Quantity, Price FROM Products";
        using var cmd = new MySqlCommand(sql, conn);
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(
                $"{reader["Id"]}\t{reader["Name"]}\t{reader["Category"]}\t" +
                $"{reader["Quantity"]}\t{reader["Price"]}");
        }
    }
}

MySqlDbHelper.InitDb();
Console.WriteLine("建表完成");

MySqlDbHelper.InsertProduct("雲端測試商品", "測試分類", 10, 99.5m);
Console.WriteLine("已新增一筆");

Console.WriteLine("目前資料：");
MySqlDbHelper.PrintAllProducts();

{
  "ConnectionStrings": {
    "TiDB": "Server=gateway01.xxxx.prod.aws.tidbcloud.com;Database=MyInventory;Uid=xxxxxxxx.root;Pwd=你的密碼;Port=4000;SslMode=Required;"
  }
}

using Microsoft.Extensions.Configuration;

var config = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json", optional: false)
    .Build();

string connStr = config.GetConnectionString("TiDB");