CODEWITHSUNDEEP
dapper
bigmac
bigmac

Reputation: 2553

Pass Table Name as Parameter to Dapper

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 });

Upvotes: 22

Views: 12461

Answers (4)

bitsprint
bitsprint

Reputation: 897

If you are simply querying the table by id you can use the Dapper.Contrib package.

Add a Table attribute to the type and a Key attribute to the Id column and then use the Get(dynamic id) extension method on the Connection.

This way you do not need to write dynamic queries.

[Table("cardtype-orders")]
public class CardTypeOrder 
{
   [Key]
   public Guid Id { get; set; }
 
   public string Description { get; set; }
}

public class CardTypeOrderRepository: GenericRepository<CardTypeOrder>
{
}

public abstract class GenericRepository<T>
{
   public T Get(object id)
   {
       using (var connection = GetConnection())
       {
           return connection.Get<T>(id);
       }
   }
}

Upvotes: 0

NPearson
NPearson

Reputation: 141

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);                    
}

Upvotes: 5

P&#228;r Sandgren
P&#228;r Sandgren

Reputation: 151

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;
    }
}

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1062820

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.

Upvotes: 29

Related Questions