Dapper DynamicParameters.ParameterNames not iterating in foreach loop

Question

My task is to log the raw SQL that dapper is going to be querying the DB with.

So what I have done is made use of the decorator pattern and created my own class that implements IDbConnection

More info can be found Why does the C# compiler choose Dapper's extension method for IDbConnection instead of the instance method on my own type?

public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null,
                                                IDbTransaction transaction = null,
                                                int? commandTimeout = null,
                                                CommandType? commandType = null)
{
    LogSqlIfEnabled(sql, param, commandType ?? CommandType.Text);
    return await SqlMapper.QueryAsync<T>(_inner, sql, param, transaction, commandTimeout, commandType);
}

private void LogSqlIfEnabled(string sql, object param, CommandType commandType)
{
    try
    {
        if (HttpContext.Current?.Session?["IsSQLLogsEnabled"] != null)
        {
            // Convert the object parameters to SqlParameters
            SqlParameter[] sqlParameters = ConvertObjectToSqlParameters(param);

            // Use the commandType name as a string for CollectSqlData
            string commandTypeName = commandType.ToString() ?? "Text";
            string query = Utility.CollectSqlData(sql, commandTypeName, sqlParameters);
            if (!string.IsNullOrEmpty(query))
                _sqlLogger.Info(query);
        }
    }
    catch (Exception ex)
    {
        _sqlLogger.Error(ex, "Error logging Dapper SQL");
    }

private SqlParameter[] ConvertObjectToSqlParameters(object param)
    {
        if (param == null)
            return new SqlParameter[0];

        // Handle DynamicParameters directly
        if (param is DynamicParameters dynamicParams)
        {
            return ConvertToSqlParameters(dynamicParams);
        }

        // Handle anonymous objects by converting properties to parameters
        var sqlParameters = new List<SqlParameter>();

        foreach (var prop in param.GetType().GetProperties())
        {
            var value = prop.GetValue(param);
            var paramName = $"@{prop.Name}";
            var sqlParam = new SqlParameter(paramName, value ?? DBNull.Value);

            // Try to set appropriate SqlDbType based on value
            if (value != null)
            {
                sqlParam.SqlDbType = GetSqlDbType(value.GetType());
            }

            sqlParameters.Add(sqlParam);
        }

        return sqlParameters.ToArray();
    }

private static SqlParameter[] ConvertToSqlParameters(DynamicParameters parameters)
{
    if (parameters == null)
        return new SqlParameter[0];

    var sqlParameters = new List<SqlParameter>();

    var x = string.Join(", ", from pn in parameters.ParameterNames select string.Format("@{0}={1}", pn, (parameters as SqlMapper.IParameterLookup)[pn]));
    //x is "" here

    foreach (var name in parameters.ParameterNames)
    {
        var value = parameters.Get<object>(name);
        var parameter = new SqlParameter(name, value ?? DBNull.Value);

        // Try to set appropriate SqlDbType based on value
        if (value != null)
        {
            parameter.SqlDbType = GetSqlDbType(value.GetType());
        }

        sqlParameters.Add(parameter);
    }

    return sqlParameters.ToArray();
}

}

referenced code: https://stackoverflow.com/a/10510471/5759460

However, when debugging, I can see that the loop is never entered, even though I know there are parameters in the DynamicParameters object. Looking at the DynamicParameters object in the debugger:

ParameterNames: Empty or not showing expected parameters parameters: Count = 0 templates: Count = 1 (contains my anonymous object)

I'm creating the DynamicParameters object like this:

var parameters = new
{
    @p_UserId = userId,
    // more parameters...
};

DynamicParameters dynamicParameters = new DynamicParameters(parameters); And then passing it to my database methods:

var results = await _dbConnection.QueryAsync<T>(spName, dynamicParameters, commandType: CommandType.StoredProcedure);

The SQL executes fine, but I can't log the parameter values because my ConvertToSqlParameters method isn't working as expected.

So Now I am thinking of using reflection to iterate through the template variable. But I just want to make sure what I am doing is alright? Or there could be a better way I am missing.

can't log the parameter values yes, because they're never part of the SQL text itself itself. These aren't placeholders that get replaced. When the database is called these will be passed outside the query itself as parameters of the RPC call. Even when the query engine compiles sql into an execution plan, the plan will include those parameters. The database will cache that execution plan and reuse it every time it sees the same SQL text, using the new parameter values — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 13 at 8:59
What are you trying to do? Both System.Data.SqlClient and Microsoft.Data.SqlClient have tracing but nowadays you can use the OpenTelemetry.Instrumentation.SqlClient package to collect standard-compliant OpenTelemetry traces and logs. You can use the built-in Event Tracing if you create your own listener, but OTEL is now the defacto standard for monitoring .... everything — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 13 at 9:18
If you want to see what happened in response to an API call for example, using OTEL means you'll end up with a trace showing the request and its parameters, and the database calls, without changing your code at all — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 13 at 9:21
@PanagiotisKanavos I just want to log raw SQL.(like you see in SSMS profiler) Such that the I can just directly copy and paste query logged and run in SSMS and see the data. — Shad
– Shad, Commented May 13 at 10:17
There's no SSMS profiler, and SQL Server Profiler or Extended Events don't embed the parameter values into the SQL text. I already explained how you can do the same, either using OTEL or SqlClient's Event Tracing. You can use ErikEJ's SqlClientExtensions too, which package a ready-made Event listener behind the SqlDataSource class. Any connections you create with SqlDataSource.Create(connectionString) can have logging enabled — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 13 at 10:25

Sinus32 · Accepted Answer · 2025-05-13 09:15:03Z

1

Your approach to solve the problem is not so great.

The good approach is to:

1. Make custom LoggingDbConnection that returns custom LoggingDbCommand in CreateCommand().

public class LoggingDbConnection : IDbConnection
{
    ...

    public IDbCommand CreateCommand()
    {
        return new LoggingDbCommand(this);
    }

    ...
}

2. Implement IDbCommand.ExecuteNonQuery(), IDbCommand.ExecuteReader(), IDbCommand.ExecuteReader(CommandBehavior) and IDbCommand.ExecuteScalar() methods in LoggingDbCommand to add logging. When these methods are called the IDbCommand.CommandText is already set and IDbCommand.Parameters collection is already filled with parameters with values.

You just have to read this two properties, no need to use reflection or create parameters by yourself. Just read what you need after Dapper fills everything.

Another advantage of this method is that you can log the real sql, not the input sql.
For eg:

var ids = new[] { 3, 7, 12 };
var sql = "SELECT * FROM Products WHERE ProductId IN @Ids";
using (var connection = new SqlConnection(connectionString))
{
  connection.Open();
  var products = connection.Query<Product>(sql, new {Ids = ids });
}

Input sql is
SELECT * FROM Products WHERE ProductId IN @Ids,
but the real one that you will see in IDbCommand.CommandText is
SELECT * FROM Products WHERE ProductId IN (@Ids0, @Ids1, @Ids2) .

edited May 13 at 9:15

answered May 13 at 9:06

Sinus32

1,1037 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Panagiotis Kanavos May 13 at 9:17

This is worse. SqlClient always had its own tracing and nowadays the de-facto standard, also supported by SqlClient and other drivers, is to use OpenTelemetry

Sinus32 May 13 at 9:25

Of course that doing logging at driver level is the one of the best options. My answer is just focused how to do some things in code manually. And I don't think that reading IDbCommand state just before execution is worse than using reflection or creating another set of parameters in really convoluted way.

Collectives™ on Stack Overflow

Dapper DynamicParameters.ParameterNames not iterating in foreach loop

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related