How to get the parameter declarations as well as the SQL calls of NHibernate?

Question

Hi,

When I am working with NHibernate (I am a beginner of it btw) I am able to obtain the SQL calls to Db in my output. But I am not able to make them work after I copy and paste into Management Studio.

Because it is missing the parameter declarations.

I get something like:

SELECT this_.PK_Product as PK1_1_0_, this_.ProductCode as ProductC2_1_0_
, this_.ProductName as ProductN3_1_0_, this_.ProductCodeISO2 as ProductC4_1_0_
, this_.NotUsed as NotUsed1_0_, this_.Confirmed as Confirmed1_0_, this_.LabelRequired as LabelReq7_1_0_ FROM tProduct this_ 
WHERE (this_.NotUsed = @p0 and this_.Confirmed = @p1 and this_.LabelRequired = @p2);@p0 = False [Type: Boolean (0)], @p1 = True [Type: Boolean (0)], @p2 = False [Type: Boolean (0)]`enter code here`

When I execute this in my SQL Management Studio, I get an error:

Msg 137, Level 15, State 2, Line 1 Must declare the scalar variable "@p0".

Can I somehow tell NHibernate to add parameter declarations in the created query string?

Thanks

Answer 1

For profiling I am using SQL Server Profiler which comes with Microsoft SQL Server Management Studio or NHibernate Profiler.

In the SQL Server Profiler you see the statements together with parameter declarations and values so you can just copy and paste them into the management studio.

In the NHibernate profiler you can also see the SQL statements, but the parameters are already replaced with values (you can see the parameters name in the comments). I would highly recommend NHibernate profiler, you can download the trial version.

Answer 2

Something like this should work (not tested, I don't have your DB):

DECLARE @p0 BIT
DECLARE @p1 BIT
DECLARE @p2 BIT

SET @p0 = 0
SET @p1 = 1
SET @p2 = 0

SELECT
 this_.PK_Product as PK1_1_0_,
 this_.ProductCode as ProductC2_1_0_,
 this_.ProductName as ProductN3_1_0_,
 this_.ProductCodeISO2 as ProductC4_1_0_,
 this_.NotUsed as NotUsed1_0_,
 this_.Confirmed as Confirmed1_0_,
 this_.LabelRequired as LabelReq7_1_0_
 FROM tProduct this_ 
WHERE
 (this_.NotUsed = @p0 and this_.Confirmed = @p1 and this_.LabelRequired = @p2);