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>`:

```csharp
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

```csharp
// ❌ 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

```csharp
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

```csharp
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

```csharp
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)

```csharp
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)

```csharp
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