Pass Table Name as Parameter to Dapper

dapper

Question

Is it possible to pass in the table name as a parameter to a Dapper Query command? I'm not looking for a SQL table defined function or a SQL table variable. I want to define the table name within C# and pass it to Dapper. Here's my code, that when executed, returns an error of Must declare the table variable "@TableName"

var foo = conn.Query("SELECT * FROM @TableName WHERE Id = @Id", new { TableName = "MyTable", Id = 123 });

Accepted Answer

SQL does not support parameterized table names, and dapper is a very very thin wrapper over SQL - so: no.

You could, however, use string.format:

string sql = string.Format("... from [{0}] ...", table name);

Note that even with the [/] this has an inherent SQL injection risk.


Popular Answer

For internal, generic scripts, we use the TableNameMapper to build the sql:

public async Task<bool> DeleteAsync(int id)
{
    string tableName = Dapper.Contrib.Extensions.SqlMapperExtensions.TableNameMapper(typeof(T));
    using (var conn = new SqlConnection(ConnectionString))
    {
        int affectedRows = await conn.ExecuteAsync($"delete from {tableName} where id=@id", new { id });
        return affectedRows > 0;
    }
}


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why