Use named parameters for table name in NHibernate

Question

I need to do a simple truncate on a table named myTable. So I wrote this and it works:

statelessSession
    .CreateSQLQuery("TRUNCATE TABLE myTable")
    .ExecuteUpdate();

Since I would store table name in an App.config I would "parameterize" the query. I would avoid String.Format and so I tried something like

statelessSession
    .CreateSQLQuery("TRUNCATE TABLE :tabName")
    .SetParameter("tabName", SysCfg.ConfigurationManager.AppSettings["tabName"])
    .ExecuteUpdate();

but when I run this code I get:

Additional information: could not execute native bulk manipulation query:
   TRUNCATE TABLE :seedTableName[SQL: TRUNCATE TABLE @p0]

I also tried with curly braces around parameter name but this doesn't work as well.

Where Am I wrong?

Answer 1

Table as a parameter is explained here

Table name as variable

Table names and column names need to be static, if the query is static. For dynamic table or column names, you should generate the full SQL dynamically,

So, you should build that as a pure string

statelessSession
    .CreateSQLQuery("TRUNCATE TABLE " + SysCfg.ConfigurationManager.AppSettings["tabName"])
    .ExecuteUpdate();

Answer 2

NHibernate doesn't allow you to do anything more than your underlying database. AFAICT, no db supports parameters for table names.

E.g. you can't write a script like this:

CREATE TABLE @tableName (etc etc)...

If you where bound to SQL you would resort to dynamic SQL.

Given you are using NHibernate, String.Format is what you are really after. As always, check for parameter values and ensure no malicious parameter gets inserted into your queries.