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?

Community · Accepted Answer · 2017-05-23 12:16:18Z

2

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

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Apr 28, 2016 at 9:38

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alberto Chiesa · Accepted Answer · 2016-09-07 16:31:50Z

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.

edited Sep 7, 2016 at 16:31

answered Apr 28, 2016 at 9:41

Alberto Chiesa

7,4702 gold badges35 silver badges61 bronze badges

Collectives™ on Stack Overflow

Use named parameters for table name in NHibernate

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related