ClickHouse.Client 是一个适用于 ClickHouse 的 .NET客户端，同样适用于 ByteHouse 企业版的连接，其官方文档可参见这里。

驱动安装

# Driver
dotnet add package ClickHouse.Client

# Dapper ORM
dotnet add package Dapper

# FreeSQL ORM
dotnet add package FreeSql.Provider.ClickHouse

连接字符串

"Protocol=https;Host={HOST};Port=8123;Database={DATABASE};Username=bytehouse;Password={API_KEY};"

其中 {HOST} 以及 {API_KEY}信息请参考 获取 ByteHouse 连接信息获取，{Port}默认值为8123。

使用限制

在使用 FreeSQL 客户端时，暂时不支持以下接口:

1. UseAutoSyncStructure(true)
2. CodeFirst.SyncStructure

使用示例

使用 Dapper 连接

using System.Data;
using ClickHouse.Client.ADO;
using Dapper;

namespace ClickHouse.Client.DapperExample;

public static class DapperExample
{
    // Constants for SQL queries
    const string DropTableQuery = "DROP TABLE IF EXISTS users;";

    const string CreateTableQuery = @"
        CREATE TABLE users
        (
            Id UInt32,
            Name String,
            Email String
        ) ENGINE = CnchMergeTree()
        ORDER BY Id;
    ";

    const string InsertDataQuery = @"
        INSERT INTO users (Id, Name, Email) VALUES 
        (1, 'Alice', 'alice@example.com'), 
        (2, 'Bob', 'bob@example.com');
    ";

    const string SelectUsersQuery = "SELECT Id, Name, Email FROM users";

    public static void Main(string[] args)
    {
        try
        {
            // Drop table if exists
            DropTable();
            Console.WriteLine("Table dropped successfully.");

            // Create table
            CreateTable();
            Console.WriteLine("Table created successfully.");

            // Insert data
            InsertData();
            Console.WriteLine("Data inserted successfully.");

            // Retrieve and display data
            var users = GetUsers();
            foreach (var user in users)
            {
                Console.WriteLine($"Id: {user.Id}, Name: {user.Name}, Email: {user.Email}");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"An error occurred: {ex.Message}");
        }
    }

    // Method to get the ClickHouse connection
    static IDbConnection GetClickHouseConnection()
    {
        // Connection settings (update with your ClickHouse details)
        var connectionString = "Protocol=https;Host=gateway-v2.bytehouse-cn-beijing.volces.com;Port=8123;Database={DATABASE};Username=bytehouse;Password={API_KEY};";
        return new ClickHouseConnection(connectionString);
    }

    // Method to drop the 'users' table if it exists
    static void DropTable()
    {
        using (var connection = GetClickHouseConnection())
        {
            connection.Open();
            connection.Execute(DropTableQuery);
        }
    }

    // Method to create the 'users' table
    static void CreateTable()
    {
        using (var connection = GetClickHouseConnection())
        {
            connection.Open();
            connection.Execute(CreateTableQuery);
        }
    }

    // Method to insert data into the 'users' table
    static void InsertData()
    {
        using (var connection = GetClickHouseConnection())
        {
            connection.Open();
            connection.Execute(InsertDataQuery);
        }
    }

    // Method to retrieve users from the 'users' table
    static IEnumerable<User> GetUsers()
    {
        using (var connection = GetClickHouseConnection())
        {
            connection.Open();
            return connection.Query<User>(SelectUsersQuery);
        }
    }

    // User class representing the 'users' table schema
    class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
    }
}

使用 FreeSQL 连接

using FreeSql;
using FreeSql.DataAnnotations;

namespace ClickHouse.Client.FreeSQLExample;

public static class FreeSQLExample
{
    private static IFreeSql fsql;

    public static void Main(string[] args)
    {
        // Configure FreeSql
        fsql = new FreeSqlBuilder()
            .UseConnectionString(DataType.ClickHouse, "Protocol=https;Host=gateway-v2.bytehouse-cn-beijing.volces.com;Port=8123;Username=bytehouse;Password={API_KEY};Database={DATABASE};")
            .UseAutoSyncStructure(false) // ByteHouse currently does not support true
            .Build();
        
        // Execute DDL statement to create the table if it doesn't exist
        CreateTable();
        
        // Perform operations
        InsertUser();
        BulkInsertUsers();
        QueryUsers();
    }
     
    // Method to execute DDL statement
    static void CreateTable()
    {
        var createTableSql = @"
            CREATE TABLE IF NOT EXISTS users (
                Id Int32,
                Name String,
                Email String
            ) ENGINE = MergeTree()
            ORDER BY Id";
        
        fsql.Ado.ExecuteNonQuery(createTableSql);
        Console.WriteLine("Table created or already exists.");
    }

    // Define the User entity with explicit table mapping
    [Table(Name = "users")]
    class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
    }

    // Insert a new user
    static void InsertUser()
    {
        var newUser = new User { Id = 0, Name = "John Doe", Email = "john.doe@example.com" };
        fsql.Insert(newUser).ExecuteAffrows();
        Console.WriteLine("User inserted.");
    }

    // Bulk insert users
    static void BulkInsertUsers()
    {
        var users = new List<User>();
        for (int i = 1; i <= 1000; i++)
        {
            users.Add(new User { Id = i, Name = $"John Doe {i}", Email = $"john.doe{i}@example.com" });
        }

        fsql.Insert(users).ExecuteAffrows();
        Console.WriteLine("Bulk insert completed.");
    }

    // Query users
    static void QueryUsers()
    {
        var users = fsql.Select<User>().ToList();
        foreach (var user in users)
        {
            Console.WriteLine($"Id: {user.Id}, Name: {user.Name}, Email: {user.Email}");
        }
    }
}