Get the Final Executable SQL from Dapper Instantly with Parameter Replacement

1. Introduction

Debugging SQL stored procedures, especially those with thousands of lines, is painful compared with high‑level languages like C#. When a stored procedure is executed through Dapper, the generated SQL still contains @ parameters, forcing developers to replace them manually.

2. Common Debugging Scenarios

Typical Dapper breakpoint shows a query such as:

SELECT
a.*
FROM dbo.ptype a
INNER JOIN dbo.PType_Price b ON a.typeId=b.PTypeID
LEFT JOIN dbo.PType_Units c ON a.typeId=c.UnitsId
WHERE a.typeId=@typeid AND a.CreateDate=@Area
AND preprice1=@preprice1 AND deleted=@deleted

Because of the @ placeholders, developers must manually substitute values, which is cumbersome. SQL Server Profiler can capture the final SQL, but in a production environment many concurrent statements make it hard to locate the one of interest.

3. Solution – Replace Parameters Before Execution

The idea is to transform the parameterized SQL into the final executable string before handing it to Dapper. The following helper class performs the replacement:

public class SqlHelper
{
    public Dictionary<string, object> Param = new Dictionary<string, object>();

    public string ReplaceParam(ref string sql)
    {
        if (Param.Count == 0) return sql;
        var sb = new StringBuilder();
        sb.Append(sql);
        foreach (var item in Param)
        {
            var paramName = item.Key;
            var paramValue = item.Value;
            var type = paramValue.GetType();
            if (type == typeof(string) || type == typeof(DateTime))
            {
                sb.Replace($"@{paramName}", $"'{paramValue}'");
            }
            else if (type == typeof(bool))
            {
                sb.Replace($"@{paramName}", paramValue.ToString() == "True" ? "1" : "0");
            }
            else
            {
                sb.Replace($"@{paramName}", paramValue.ToString());
            }
        }
        sql = sb.ToString();
        return sql;
    }
}

Usage example:

public IEnumerable<Ptype> GetPtypeDetail()
{
    var sql = @"
SELECT a.*
FROM dbo.ptype a
INNER JOIN dbo.PType_Price b ON a.typeId=b.PTypeID
LEFT JOIN dbo.PType_Units c ON a.typeId=c.UnitsId
WHERE a.typeId=@Typeid AND a.CreateDate=@CreateDate
AND preprice1=@preprice1 AND deleted=@deleted
";
    var sqlHelper = new SqlHelper();
    sqlHelper.Param.Add("Typeid", "001");
    sqlHelper.Param.Add("CreateDate", DateTime.Now);
    sqlHelper.Param.Add("preprice1", 3.62M);
    sqlHelper.Param.Add("deleted", true);
    sqlHelper.ReplaceParam(ref sql);

    using (var con = SQLServerHelper.GetConnection())
    {
        return con.Query<Ptype>(sql);
    }
}

After replacement, Dapper receives the fully‑resolved SQL, eliminating the need for manual substitution or Profiler hunting.

4. Conclusion

The helper makes the SQL passed to Dapper identical to the final statement that runs on the server. Performance impact is negligible; string replacement in C# is extremely fast, measured in micro‑ to nanoseconds. Developers can now set a breakpoint on the Dapper query, hover over the SQL variable, and instantly see the complete statement for debugging.