How to use Dapper's SqlBuilder?

Question

I can't find any documentation or examples I can follow to use the SqlBuilder class.

I need to generate sql queries dynamically and I found this class. Would this be the best option?

Answer 1

the best place to start is to checkout the dapper source code from its github repo and have a look at the SqlBuilder code. The SqlBuilder class is only a 200 lines or so and you should be able to make an informed choice on whether it is right for your needed.

An other option is to build your own. I personally went down this route as it made sense. Dapper maps select querys directly to a class if you name your class properties the same as your database or add an attribute such as displayName to map from you can use reflection to get the property names. Put there names and values into a dictionary and you can genarate sql fairly easy from there.

here is something to get you started:

first an example class that you can pass to your sqlbuilder.

public class Foo
{

    public Foo()
    {
       TableName = "Foo";
    }
    public string TableName { get; set; }

    [DisplayName("name")]
    public string Name { get; set; }

    [SearchField("fooId")]
    public int Id { get; set; }

}

This is fairly basic. Idea behind the DisplayName attribute is you can separate the properties out that you want to include in your auto generation. in this case TableName does not have a DisplayName attribute so will not be picked up by the next class. however you can manually use it when generating your sql to get your table name.

public Dictionary<string, object> GetPropertyDictionary()
    {
        var propDictionary = new Dictionary<string, object>();

        var passedType = this.GetType();

        foreach (var propertyInfo in passedType.GetProperties())
        {
            var isDef = Attribute.IsDefined(propertyInfo, typeof(DisplayNameAttribute));

            if (isDef)
            {
                var value = propertyInfo.GetValue(this, null);

                if (value != null)
                {
                    var displayNameAttribute =
                        (DisplayNameAttribute)
                            Attribute.GetCustomAttribute(propertyInfo, typeof(DisplayNameAttribute));
                    var displayName = displayNameAttribute.DisplayName;
                    propDictionary.Add(displayName, value);
                }
            }
        }

        return propDictionary;
    }

This method looks at the properties for its class and if they are not null and have a displayname attribute will add them to a dictionary with the displayname value as the string component.

This method is designed to work as part of the model class and would need to be modified to work from a separate helper class. Personally I have it and all my other sql generation methods in a Base class that all my models inherit from.

once you have the values in the dictionary you can use this to dynamically generate sql based on the model you pass in. and you can also use it to populate your dapper DynamicParamaters for use with paramiterized sql.

I hope this helps put you on the right path to solving your problems.