Dapper and DynamicParameters

Dapper is great, but the documentation is definitely lacking..

I needed to construct dynamic query, with optional parameters. Tried an approach with ExpandoObject, which didn’t work. I guess I could have created a dedicated object which would hold all possible parameters, but it seems.. wrong.

So on a dapper’s tests page (which, I guess, is the documentation) found a DynamicParameters object – exactly what I needed.

This is a quick sample:

public User[] Get(UserFilter filter)
        {
            string sql = "SELECT Id, Name, Age FROM Users";
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                DynamicParameters parameters = new DynamicParameters();
                sql += " WHERE 1=1";
                if (filter.Id.HasValue)
                {
                    sql += " AND Id = @id";
                    parameters.Add("id", filter.Id.Value);
                }
 
                if (filter.Age.HasValue)
                {
                    sql += " AND Age = @age";
                    parameters.Add("age", filter.Age.Value);
                }
                // etc
                var users = connection.Query<User>(sql, parameters).ToArray();
                return users;
        }