ClickHouse.Client 是一个适用于 ClickHouse 的 .NET 客户端，同样适用于 ByteHouse 的连接，更多介绍请参考其官方文档。

建议使用 Dotnet 版本 6.0+ 更高版本。

• 不支持 ByteHouse 的 JSONB、BitMap 类型。
• FreeSQL 不支持 UseAutoSyncStructure 为 True。
• Dapper 不支持多次设置计算组（Virtual_warehouse）。
• 如果您在使用过程中遇到其他未知限制，请联系 ByteHouse 团队处理。

# Driver
dotnet add package ClickHouse.Client

# Dapper ORM
dotnet add package Dapper

# FreeSQL ORM
dotnet add package FreeSql.Provider.ClickHouse

ByteHouse 支持通过 IAM 用户或数据库用户连接 ClickHouse C# Driver。IAM 用户与数据库用户二者差异说明如下，您可按需选择。

• IAM 用户为火山引擎访问控制（IAM）中创建的用户，其权限由 IAM 权限策略及您授予的 ByteHouse 资源和数据权限决定。IAM 用户可访问 ByteHouse 控制台，也支持通过 CLI、连接驱动、生态工具、API 等方式访问 ByteHouse。
• 数据库用户为 ByteHouse 中创建的数据库级别用户，可为其授予环境、资源和数据权限。数据库用户不可访问 ByteHouse 控制台，但支持通过 CLI、连接驱动、生态工具、API 等方式访问 ByteHouse。

更多 IAM 用户和数据库用户的介绍请参见以下文档：

• ByteHouse权限管控概述
• 管理 IAM 用户
• 管理数据库用户

使用 IAM 用户连接至 ByteHouse

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

使用数据库用户连接至 ByteHouse

"Protocol=https;Host={HOST};Port=8123;Database={DATABASE};Username={accountID_or_accountName}::{username}::{envID};Password={password};"

您可以使用以下代码连接至 ByteHouse，并开始使用标准语句开发 ByteHouse，用于查询、写入和读取数据。

• 超时时间配置：Timeout 默认为 120s。

使用 Dapper 连接

连接 ByteHouse

可参考下面代码连接至 ByteHouse，使用时注意替换连接语句中的 {Host}、{Username}、{Password}、{Database}、{VIRTUAL_WAREHOUSE_ID} 等连接信息字段，获取方式请参见获取 ByteHouse 连接信息。

const string Host = "{Host}";
    const int Port = 8123;
    const string Password = "{Password}";
    const string Username = "{Username}";
    const string Database = "{Database}";
    const string Virtual_warehouse_id = "{Virtual_warehouse_id}";

    // Connection settings (update with your ClickHouse details)
    var connectionString = $"Protocol=https;Host={Host};Port=8123;Database={Database};Username={Username};Password={Password};set_virtual_warehouse={Virtual_warehouse_id}"; 
    return new ClickHouseConnection(connectionString);

使用自定义 Query ID

static void DropTable()
    {
        using (var connection = GetClickHouseConnection())
        {
            connection.Open();
            connection.CustomSettings.Add("query_id", $"customized_{Guid.NewGuid()}");
            connection.Execute(DropTableQuery);
        }
    }

连接至 ByteHouse 并查询数据

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

namespace ClickHouse.Client.DapperExample;

public static class DapperExample
{

    const string Host = "{Host}";
    const int Port = 8123;
    const string Password = "{Password}";
    const string Username = "{Username}";
    const string Database = "{Database}";
    const string Virtual_warehouse_id = "{Virtual_warehouse_id}";

    // 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 ClickHouseConnection GetClickHouseConnection()
    {
        // Connection settings (update with your ClickHouse details)
        var connectionString = $"Protocol=https;Host={Host};Port=8123;Database={Database};Username={Username};Password={Password};set_virtual_warehouse={Virtual_warehouse_id}"; 
        return new ClickHouseConnection(connectionString);
    }

    // Method to drop the 'users' table if it exists
    static void DropTable()
    {
        using (var connection = GetClickHouseConnection())
        {
            connection.Open();
            connection.CustomSettings.Add("query_id", $"customized_{Guid.NewGuid()}");
            connection.Execute(DropTableQuery);
        }
    }

    // Method to create the 'users' table
    static void CreateTable()
    {
        using (var connection = GetClickHouseConnection())
        {
            connection.Open();
            connection.CustomSettings.Add("query_id", $"customized_{Guid.NewGuid()}");
            //Overwriting the global virtual_warehouse settings is not supported
            //connection.CustomSettings.Add("virtual_warehouse", $"{Virtual_warehouse_id}");
            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 连接

可参考下面代码连接至 ByteHouse，使用时注意替换连接语句中的 {Host}、{Username}、{Password}、{Database}、{VIRTUAL_WAREHOUSE_ID} 等连接信息字段，获取方式请参见获取 ByteHouse 连接信息。

连接 ByteHouse

const string Host = "{Host}";
    const int Port = 8123;
    const string Password = "{Password}";
    const string Username = "{Username}";
    const string Database = "{Database}";
    const string Virtual_warehouse_id = "{Virtual_warehouse_id}";

    // Configure FreeSql
    fsql = new FreeSqlBuilder()
        .UseConnectionString(DataType.ClickHouse, $"Protocol=https;Host={Host};Port={Port};Username={Username};Password={Password};Database={Database};set_virtual_warehouse={Virtual_warehouse_id}") 
        .UseAutoSyncStructure(false) // ByteHouse 暂不支持设置 true
        .Build();

连接至 ByteHouse 并查询数据

using FreeSql;
using FreeSql.DataAnnotations;

namespace ClickHouse.Client.FreeSQLExample;

public static class FreeSQLExample
{
    private static IFreeSql fsql;

    const string Host = "{Host}";
    const int Port = 8123;
    const string Password = "{Password}";
    const string Username = "{Username}";
    const string Database = "{Database}";
    const string Virtual_warehouse_id = "{Virtual_warehouse_id}";
    public static void Main(string[] args)
    {
        // Configure FreeSql
        fsql = new FreeSqlBuilder()
            .UseConnectionString(DataType.ClickHouse, $"Protocol=https;Host={Host};Port={Port};Username={Username};Password={Password};Database={Database};set_virtual_warehouse={Virtual_warehouse_id}") //如需使用数据库账号登录，请参考连接字符串章节相关内容替换
            .UseAutoSyncStructure(false) // ByteHouse 暂不支持设置 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}");
        }
    }
}