C# 连接ClickHouse 数据库

在 C# 中连接 ClickHouse 数据库，您可以使用 ClickHouse.Client 库。这个库提供了对 ClickHouse 数据库的高效访问。以下是详细的步骤指南，帮助您在 C# 项目中连接和操作 ClickHouse 数据库。

1. 安装 ClickHouse.Client 包
首先，您需要在您的项目中安装 ClickHouse.Client 包。您可以使用 NuGet 包管理器来完成此操作。
使用 NuGet 包管理器控制台

Install-Package ClickHouse.Client -Version 1.4.1

使用 .NET CLI

dotnet add package ClickHouse.Client --version 1.4.1

2. 配置 ClickHouse 客户端
接下来，您需要配置 ClickHouse 客户端以连接到您的 ClickHouse 实例。以下是一个基本的配置示例。

using ClickHouse.Client;
using ClickHouse.Client.ADO;
using System;
using System.Data;namespace ClickHouseExample
{class Program{static void Main(string[] args){// 配置 ClickHouse 连接字符串string connectionString = "Host=127.0.0.1;Port=9000;Username=default;Password=;Database=default";using (var connection = new ClickHouseConnection(connectionString)){try{connection.Open();Console.WriteLine("Connected to ClickHouse!");// 执行查询using (var command = new ClickHouseCommand("SELECT * FROM system.numbers LIMIT 10", connection)){using (var reader = command.ExecuteReader()){while (reader.Read()){Console.WriteLine(reader[0]);}}}}catch (Exception ex){Console.WriteLine($"Error connecting to ClickHouse: {ex.Message}");}}}}
}

3. 创建表
如果您还没有创建表，可以使用 ClickHouse.Client 创建一个新的表。

using ClickHouse.Client;
using ClickHouse.Client.ADO;
using System;namespace ClickHouseExample
{class Program{static void Main(string[] args){string connectionString = "Host=127.0.0.1;Port=9000;Username=default;Password=;Database=default";using (var connection = new ClickHouseConnection(connectionString)){try{connection.Open();Console.WriteLine("Connected to ClickHouse!");// 创建表string createTableQuery = @"CREATE TABLE IF NOT EXISTS my_table(id UInt32,name String,description String) ENGINE = MergeTree() ORDER BY id;";using (var command = new ClickHouseCommand(createTableQuery, connection)){command.ExecuteNonQuery();Console.WriteLine("Table created successfully.");}}catch (Exception ex){Console.WriteLine($"Error connecting to ClickHouse: {ex.Message}");}}}}
}

4. 插入数据
您可以使用 ClickHouse.Client 将数据插入到 ClickHouse 中。

using ClickHouse.Client;
using ClickHouse.Client.ADO;
using System;namespace ClickHouseExample
{class Program{static void Main(string[] args){string connectionString = "Host=127.0.0.1;Port=9000;Username=default;Password=;Database=default";using (var connection = new ClickHouseConnection(connectionString)){try{connection.Open();Console.WriteLine("Connected to ClickHouse!");// 插入数据string insertQuery = @"INSERT INTO my_table (id, name, description) VALUES(1, 'Sample Document', 'This is a sample document.'),(2, 'Another Document', 'This is another sample document.');";using (var command = new ClickHouseCommand(insertQuery, connection)){command.ExecuteNonQuery();Console.WriteLine("Data inserted successfully.");}}catch (Exception ex){Console.WriteLine($"Error connecting to ClickHouse: {ex.Message}");}}}}
}

5. 查询数据
您可以使用 ClickHouse.Client 执行查询以检索数据。

using ClickHouse.Client;
using ClickHouse.Client.ADO;
using System;
using System.Data;namespace ClickHouseExample
{class Program{static void Main(string[] args){string connectionString = "Host=127.0.0.1;Port=9000;Username=default;Password=;Database=default";using (var connection = new ClickHouseConnection(connectionString)){try{connection.Open();Console.WriteLine("Connected to ClickHouse!");// 查询数据string selectQuery = "SELECT * FROM my_table";using (var command = new ClickHouseCommand(selectQuery, connection)){using (var reader = command.ExecuteReader()){while (reader.Read()){Console.WriteLine($"Id: {reader["id"]}, Name: {reader["name"]}, Description: {reader["description"]}");}}}}catch (Exception ex){Console.WriteLine($"Error connecting to ClickHouse: {ex.Message}");}}}}
}

6. 更新数据
ClickHouse 不直接支持 UPDATE 操作，但您可以使用 ALTER TABLE ... UPDATE 语句来更新数据。不过，这种操作相对复杂且性能较低，通常建议使用 INSERT 和 DELETE 组合来实现类似的效果。

using ClickHouse.Client;
using ClickHouse.Client.ADO;
using System;namespace ClickHouseExample
{class Program{static void Main(string[] args){string connectionString = "Host=127.0.0.1;Port=9000;Username=default;Password=;Database=default";using (var connection = new ClickHouseConnection(connectionString)){try{connection.Open();Console.WriteLine("Connected to ClickHouse!");// 更新数据string updateQuery = @"ALTER TABLE my_table UPDATE name = 'Updated Document' WHERE id = 1;";using (var command = new ClickHouseCommand(updateQuery, connection)){command.ExecuteNonQuery();Console.WriteLine("Data updated successfully.");}}catch (Exception ex){Console.WriteLine($"Error connecting to ClickHouse: {ex.Message}");}}}}
}

7. 删除数据
您可以使用 ClickHouse.Client 删除数据。

using ClickHouse.Client;
using ClickHouse.Client.ADO;
using System;namespace ClickHouseExample
{class Program{static void Main(string[] args){string connectionString = "Host=127.0.0.1;Port=9000;Username=default;Password=;Database=default";using (var connection = new ClickHouseConnection(connectionString)){try{connection.Open();Console.WriteLine("Connected to ClickHouse!");// 删除数据string deleteQuery = @"ALTER TABLE my_table DELETE WHERE id = 2;";using (var command = new ClickHouseCommand(deleteQuery, connection)){command.ExecuteNonQuery();Console.WriteLine("Data deleted successfully.");}}catch (Exception ex){Console.WriteLine($"Error connecting to ClickHouse: {ex.Message}");}}}}
}

总结
通过以上步骤，您可以在 C# 项目中成功连接和操作 ClickHouse 数据库。ClickHouse.Client 提供了丰富的 API 来执行各种操作，如创建表、插入数据、查询数据、更新数据和删除数据。确保您的 ClickHouse 实例正在运行，并且客户端配置正确，以便顺利进行这些操作。如果遇到任何问题，可以参考 ClickHouse.Client 的官方文档或社区资源以获取更多帮助。
参考资料：
•  ClickHouse 官方文档  https://clickhouse.com/docs/zh/getting-started/install

•  ClickHouse.Client https://github.com/yandex/ClickHouse.Client