Pass Table Name as Parameter to 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 });

Answer 1

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.

Answer 2

You could check to see if the table exists first to protect from Sql injection:

string tableNameExistsCheck = "SELECT count(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName";

if (c.QuerySingle<int>(tableNameExistsCheck, new { tableName }) == 1)
{
    string sql = string.Format("... from [{0}] ...", tableName);
    var result = c.Query(sql);                    
}