c# - How to perform Dynamic SQL Query Generation in Dapper? - Stack Overflow

admin管理员组

文章数量:1386798

Question:
How can I dynamically generate SQL INSERT and UPDATE queries in a generic repository using Dapper? Currently, I'm hardcoding the property names in the SQL queries, but I want to make this dynamic so that it works for any entity. For example:

public async Task<int> AddAsync(T entity)
{
    using (IDbConnection dbConnection = new NpgsqlConnection(_connectionString))
    {
        dbConnection.Open();
        // How to dynamically generate "INSERT INTO TableName (Property1, Property2) VALUES (@Property1, @Property2)"?
        return await dbConnection.ExecuteAsync($"INSERT INTO {typeof(T).Name} VALUES (@Property1, @Property2, ...)", entity);
    }
}

Is there a way to use reflection or another approach to dynamically map the entity properties to the SQL query?

Question:
How can I dynamically generate SQL INSERT and UPDATE queries in a generic repository using Dapper? Currently, I'm hardcoding the property names in the SQL queries, but I want to make this dynamic so that it works for any entity. For example:

public async Task<int> AddAsync(T entity)
{
    using (IDbConnection dbConnection = new NpgsqlConnection(_connectionString))
    {
        dbConnection.Open();
        // How to dynamically generate "INSERT INTO TableName (Property1, Property2) VALUES (@Property1, @Property2)"?
        return await dbConnection.ExecuteAsync($"INSERT INTO {typeof(T).Name} VALUES (@Property1, @Property2, ...)", entity);
    }
}

Is there a way to use reflection or another approach to dynamically map the entity properties to the SQL query?

• c#
• postgresql
• dapper

Share Improve this question Follow edited Mar 17 at 7:56 Panagiotis Kanavos 132k1616 gold badges203203 silver badges265265 bronze badges asked Mar 17 at 7:51 Abhi Kumar PrinceAbhi Kumar Prince 111 silver badge11 bronze badge 2

• 3 github/DapperLib/Dapper.Contrib – mjwills Commented Mar 17 at 7:57
• Dapper.Contrib already contains such methods that allow you to write eg connection.Insert(new Car { Name = "Volvo" });. If you want something more .... why not use a full featured ORM like EF Core? – Panagiotis Kanavos Commented Mar 17 at 7:57

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

The Dapper.Contrib project already contains such methods. The list of available extension methods is:

T Get<T>(id);
IEnumerable<T> GetAll<T>();
int Insert<T>(T obj);
int Insert<T>(Enumerable<T> list);
bool Update<T>(T obj);
bool Update<T>(Enumerable<T> list);
bool Delete<T>(T obj);
bool Delete<T>(Enumerable<T> list);
bool DeleteAll<T>();

This allows using code like this:

connection.Insert(new Car { Name = "Volvo" });

It's not enough to use Reflection to construct the query. On one hand, reflection has a cost. If you use GetProperties on every call you'll pay that cost over and over again. On the other hand, some properties require special handling, like auto-generated keys and computed columns

Dapper avoids both issues by caching both the type metadata and the key/computed column metadata. If you check the actual implementation of Insert you'll see code like this :

var name = GetTableName(type);
var allProperties = TypePropertiesCache(type);
var keyProperties = KeyPropertiesCache(type);
var computedProperties = ComputedPropertiesCache(type);
var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList();

本文标签： cHow to perform Dynamic SQL Query Generation in DapperStack Overflow