ResourcePool.Docs/SMARTPOOL_DAPPER_CLICKHOUSE.md

SmartPool - Dapper + ClickHouse.Driver Integration

Overview

Dapper WORKS with ClickHouse.Driver, but with specific requirements for parameter binding.

✅ Correct Usage

Dictionary Parameters (Required)

ClickHouse.Driver requires parameters to be passed as Dictionary<string, object>:

using Dapper;
using ClickHouse.Driver.ADO;

// ✅ CORRECT: Use Dictionary<string, object>
var sql = "SELECT * FROM table WHERE id = {id:Int32} AND name = {name:String}";
var parameters = new Dictionary<string, object>
{
    { "id", 42 },
    { "name", "test" }
};

using var connection = new ClickHouseConnection(connectionString);
var result = await connection.QueryAsync<MyClass>(sql, parameters);

Parameter Placeholder Syntax

ClickHouse uses {paramName:Type} syntax:

Type	Placeholder	Example
String	{name:String}	WHERE name = {name:String}
Int32	{id:Int32}	WHERE id = {id:Int32}
Int64	{id:Int64}	WHERE id = {id:Int64}
Float64	{price:Float64}	WHERE price > {price:Float64}
DateTime	{dt:DateTime}	WHERE created_at > {dt:DateTime}

❌ NOT Supported

Anonymous Objects

// ❌ WRONG: Anonymous objects do NOT work with ClickHouse.Driver
var result = await connection.QueryAsync<MyClass>(
    "SELECT * FROM table WHERE id = {id:Int32}",
    new { id = 42 }  // NOT SUPPORTED
);

Real Examples from ProxyMetadataRepository

SELECT Query

public async Task<List<SmartProxyServer>> GetProxiesByAccessTokenAsync(
    string accessToken,
    string? ipVersion = null,
    string? protocol = null,
    string? country = null,
    CancellationToken cancellationToken = default)
{
    var sql = @"
        SELECT DISTINCT
            m.id AS Id,
            m.host AS Host,
            m.port AS Port,
            m.protocol AS Protocol,
            m.ip_version AS IpVersion,
            m.country AS Country
        FROM smart_pool_meta.proxy_metadata FINAL m
        INNER JOIN smart_pool_meta.proxy_access_mapping FINAL a 
            ON m.cluster = a.cluster
        WHERE a.access_token = {access_token:String}
          AND m.status = 1";

    // Build parameters dictionary
    var parameters = new Dictionary<string, object>
    {
        { "access_token", accessToken }
    };

    if (!string.IsNullOrEmpty(ipVersion))
    {
        sql += " AND m.ip_version = {ip_version:String}";
        parameters["ip_version"] = ipVersion;
    }

    using var connection = _clickHouseContext.smart_pool_meta;
    var result = await connection.QueryAsync<SmartProxyServer>(sql, parameters);
    return result.ToList();
}

SELECT Single Row

public async Task<SmartProxyServer?> GetProxyByIdAsync(
    int proxyId,
    CancellationToken cancellationToken = default)
{
    var sql = @"
        SELECT 
            id AS Id,
            host AS Host,
            port AS Port
        FROM smart_pool_meta.proxy_metadata FINAL
        WHERE id = {proxy_id:Int32} AND status = 1
        LIMIT 1";

    var parameters = new Dictionary<string, object>
    {
        { "proxy_id", proxyId }
    };

    using var connection = _clickHouseContext.smart_pool_meta;
    return await connection.QueryFirstOrDefaultAsync<SmartProxyServer>(sql, parameters);
}

INSERT Query

public async Task<bool> UpsertProxyAsync(
    SmartProxyServer proxy,
    CancellationToken cancellationToken = default)
{
    var sql = @"
        INSERT INTO smart_pool_meta.proxy_metadata 
        (id, host, port, protocol, ip_version, country, status, updated_at)
        VALUES 
        ({id:Int32}, {host:String}, {port:Int32}, {protocol:String}, 
         {ip_version:String}, {country:String}, {status:Int32}, now64(3))";

    var parameters = new Dictionary<string, object>
    {
        { "id", proxy.Id },
        { "host", proxy.Host },
        { "port", proxy.Port },
        { "protocol", proxy.Protocol },
        { "ip_version", proxy.IpVersion },
        { "country", proxy.Country ?? "" },
        { "status", proxy.Status }
    };

    using var connection = _clickHouseContext.smart_pool_meta;
    var result = await connection.ExecuteAsync(sql, parameters);
    return result > 0;
}

Benefits

✅ Advantages

1. Type Safety: ClickHouse type system enforced at SQL level
2. SQL Injection Prevention: Parameters are properly escaped
3. Clean Code: No manual string escaping needed
4. Performance: Efficient parameter binding
5. Object Mapping: Automatic mapping to POCOs

🔧 Trade-offs

1. Dictionary Only: Must use Dictionary<string, object>, not anonymous objects
2. Type Annotations: Must specify ClickHouse types in placeholders
3. Manual Dictionary Building: More verbose than anonymous objects

Dapper Methods Supported

Method	Purpose	Example
QueryAsync<T>	SELECT returning multiple rows	connection.QueryAsync<SmartProxyServer>(sql, params)
QueryFirstOrDefaultAsync<T>	SELECT returning 0 or 1 row	connection.QueryFirstOrDefaultAsync<SmartProxyServer>(sql, params)
ExecuteAsync	INSERT/UPDATE/DELETE	connection.ExecuteAsync(sql, params)
QuerySingleAsync<T>	SELECT returning exactly 1 row	connection.QuerySingleAsync<int>(sql, params)

Migration Summary

Before (String Replacement)

var sql = $@"
    SELECT * FROM table 
    WHERE id = {proxyId} 
      AND name = '{EscapeString(name)}'";

using var connection = _clickHouseContext.db;
await connection.OpenAsync();

using var command = connection.CreateCommand();
command.CommandText = sql;

using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    // Manual object mapping
    var obj = new MyClass
    {
        Id = reader.GetInt32(0),
        Name = reader.GetString(1)
    };
}

After (Dapper + Dictionary)

var sql = @"
    SELECT * FROM table 
    WHERE id = {id:Int32} 
      AND name = {name:String}";

var parameters = new Dictionary<string, object>
{
    { "id", proxyId },
    { "name", name }
};

using var connection = _clickHouseContext.db;
var result = await connection.QueryAsync<MyClass>(sql, parameters);

Best Practices

1. Always use Dictionary parameters - Never inline values
2. Specify ClickHouse types - Use proper type annotations
3. Handle nulls - Convert null to empty string or default value
4. Use FINAL modifier - For ClickHouse tables with updates
5. Map to POCOs - Let Dapper handle object mapping

References

• ClickHouse.Driver: https://www.nuget.org/packages/ClickHouse.Driver
• Dapper: https://github.com/DapperLib/Dapper
• ClickHouse C# Integration: https://clickhouse.com/docs/integrations/csharp

---

Status: ✅ Production Ready

Date: 2026-01-22

Version: ClickHouse.Driver 0.9.0 + Dapper 2.1.35