ClickHouse C# client

// Execute and read results
await using var reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
    Console.WriteLine($"Name: {reader.GetString("name")}");
}
```

Command methods:

- `ExecuteNonQueryAsync()` - For INSERT, UPDATE, DELETE, DDL statements
- `ExecuteScalarAsync()` - Returns first column of first row
- `ExecuteReaderAsync()` - Returns a `ClickHouseDataReader` for iterating results

---

### Using ClickHouseDataReader[​](#ado-net-reader "Direct link to Using ClickHouseDataReader")

The `ClickHouseDataReader` provides typed access to query results:

```
await using var reader = await command.ExecuteReaderAsync();

while (reader.Read())
{
    // Access by column index
    var id = reader.GetInt64(0);
    var name = reader.GetString(1);

    // Access by column name
    var email = reader.GetString("email");

    // Generic access
    var timestamp = reader.GetFieldValue<DateTime>("created_at");

    // Check for null
    if (!reader.IsDBNull("optional_field"))
    {
        var value = reader.GetString("optional_field");
    }
}
```

## Best practices[​](#best-practices "Direct link to Best practices")

### Connection lifetime and pooling[​](#best-practices-connection-lifetime "Direct link to Connection lifetime and pooling")

`ClickHouse.Driver` uses `System.Net.Http.HttpClient` under the hood. `HttpClient` has a per-endpoint connection pool. As a consequence:

- Database sessions are multiplexed through HTTP connections managed by the connection pool.
- HTTP connections are recycled automatically by the pool.
- Connections can stay alive after `ClickHouseClient` or `ClickHouseConnection` objects are disposed.

**Recommended patterns:**

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Scenario Recommended Approach|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | General use Use a singleton `ClickHouseClient`| ADO.NET / ORMs Use `ClickHouseDataSource` (creates connections that share the same pool)| DI environments Register `ClickHouseClient` or `ClickHouseDataSource` as singleton with `IHttpClientFactory` | | | | | | | |

References

When using a custom `HttpClient` or `HttpClientFactory`, ensure that the `PooledConnectionIdleTimeout` is set to a value smaller than the server's `keep_alive_timeout`, in order to avoid errors due to half-closed connections. The default `keep_alive_timeout` for Cloud deployments is 10 seconds.

Note

Avoid creating multiple `ClickHouseClient` or standalone `ClickHouseConnection` instances without a shared `HttpClient`. Each instance creates its own connection pool.

---

### DateTime handling[​](#best-practice-datetime "Direct link to DateTime handling")

1. **Use UTC whenever possible.** Store timestamps as `DateTime('UTC')` columns and use `DateTimeKind.Utc` in your code. This eliminates timezone ambiguity.
2. **Use `DateTimeOffset` for explicit timezone handling.** It always represents a specific instant and includes the offset information.
3. **Specify timezone in SQL type hints.** When using parameters with `Unspecified` DateTime values targeting non-UTC columns, include the timezone in the SQL:

   ```
   var parameters = new ClickHouseParameterCollection();
   parameters.AddParameter("dt", myDateTime);

   await client.ExecuteNonQueryAsync(
       "INSERT INTO table (dt) VALUES ({dt:DateTime('Europe/Amsterdam')})",
       parameters
   );
   ```

---

### Async inserts[​](#async-inserts "Direct link to Async inserts")

[Async inserts](/docs/optimize/asynchronous-inserts) shift batching responsibility from the client to the server. Instead of requiring client-side batching, the server buffers incoming data and flushes it to storage based on configurable thresholds. This is useful for high-concurrency scenarios like observability workloads where many agents send small payloads.

Enable async inserts via `CustomSettings` or the connection string:

```
// Using CustomSettings
var settings = new ClickHouseClientSettings("Host=localhost");
settings.CustomSettings["async_insert"] = 1;
settings.CustomSettings["wait_for_async_insert"] = 1; // Recommended: wait for flush acknowledgment

// Or via connection string
// "Host=localhost;set_async_insert=1;set_wait_for_async_insert=1"
```

**Two modes** (controlled by `wait_for_async_insert`):

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Mode Behavior Use case|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `wait_for_async_insert=1` Insert returns after data is flushed to disk. Errors are returned to the client. **Recommended** for most workloads| `wait_for_async_insert=0` Insert returns immediately when data is buffered. No guarantee data will be persisted. Only when data loss is acceptable | | | | | | | | |

Note

With `wait_for_async_insert=0`, errors only surface during flush and can't be traced back to the original insert. The client also provides no backpressure, risking server overload.

**Key settings:**

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Setting Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `async_insert_max_data_size` Flush when buffer reaches this size (bytes)|  |  |  |  | | --- | --- | --- | --- | | `async_insert_busy_timeout_ms` Flush after this timeout (milliseconds)|  |  | | --- | --- | | `async_insert_max_query_number` Flush after this many queries accumulate | | | | | | | |

---

### Sessions[​](#best-practices-sessions "Direct link to Sessions")

Only enable sessions when you need stateful server-side features, e.g.:

- Temporary tables (`CREATE TEMPORARY TABLE`)
- Maintaining query context across multiple statements
- Session-level settings (`SET max_threads = 4`)

When sessions are enabled, requests are serialized to prevent concurrent use of the same session. This adds overhead for workloads that don't require session state.

```
var settings = new ClickHouseClientSettings
{
    Host = "localhost",
    UseSession = true,
    SessionId = "my-session", // Optional -- will be auto-generated if not provided
};

using var client = new ClickHouseClient(settings);

await client.ExecuteNonQueryAsync("CREATE TEMPORARY TABLE temp_ids (id UInt64)");
await client.ExecuteNonQueryAsync("INSERT INTO temp_ids VALUES (1), (2), (3)");

var reader = await client.ExecuteReaderAsync(
    "SELECT * FROM users WHERE id IN (SELECT id FROM temp_ids)"
);
```

**Using ADO.NET (for ORM compatibility):**

```
var settings = new ClickHouseClientSettings
{
    Host = "localhost",
    UseSession = true,
    SessionId = "my-session",
};

var dataSource = new ClickHouseDataSource(settings);
await using var connection = await dataSource.OpenConnectionAsync();

await using var cmd1 = connection.CreateCommand("CREATE TEMPORARY TABLE temp_ids (id UInt64)");
await cmd1.ExecuteNonQueryAsync();

await using var cmd2 = connection.CreateCommand("INSERT INTO temp_ids VALUES (1), (2), (3)");
await cmd2.ExecuteNonQueryAsync();

await using var cmd3 = connection.CreateCommand("SELECT * FROM users WHERE id IN (SELECT id FROM temp_ids)");
await using var reader = await cmd3.ExecuteReaderAsync();
```

## Supported data types[​](#supported-data-types "Direct link to Supported data types")

`ClickHouse.Driver` supports all ClickHouse data types. The tables below show the mappings between ClickHouse types and native .NET types when reading data from the database.

### Type mapping: reading from ClickHouse[​](#clickhouse-native-type-map-reading "Direct link to Type mapping: reading from ClickHouse")

#### Integer types[​](#type-map-reading-integer "Direct link to Integer types")