NHibernate Not Using Custom SQL-Insert

[Dr. C. Hilarius]

I have several NHibernate mappings using a sql-insert element to
override the native insert statement, but I can't get this one to use
the custom SQL. No matter what I use for the identity generator, it
uses NHibernate's standard INSERT statement. Here's my mapping file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-
instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
namespace="MyProject.Domain.Order"
assembly="MyProject.Domain"
schema="dbo"
xmlns="urn:nhibernate-mapping-2.2">
<class name="OrderBase" table="Order"
polymorphism="explicit"
optimistic-lock="version"
discriminator-value="0">
<!--IDENTITY-->
<id name="Id" access="nosetter.camelcase-underscore"
column="intOrderID" type="System.Int32">
<generator class="native" />
</id>
<!--TYPE DISCRIMINATOR-->
<discriminator formula="case when intOrderStatusID = 0 then 0
else 1 end" insert="false" type="System.Int32" />
<!--VERSION-->
<version name="DateUpdated" access="nosetter.camelcase-
underscore" column="dtDateUpdated" type="Timestamp" unsaved-
value="undefined" />
<!--PROPERTIES-->
<property name="TotalItems" access="nosetter.camelcase-
underscore" column="intTotalItems" type="System.Int32"/>
<property name="TotalQuote" access="nosetter.camelcase-
underscore" column="mnyTotalQuote" type="System.Decimal"/>
<!--ASSOCIATIONS-->
<many-to-one name="Account" access="nosetter.camelcase-
underscore" class="MyProject.Domain.Customer.Account,
MyProject.Domain" column="intAccountId" />
<sql-insert>exec usp_Order_Insert @p0, @p1, @p2, @p3</sql-
insert>
</class>
</hibernate-mapping>

Here's the SQL statement NHibernate actually runs:

N'INSERT INTO dbo.Order (dtDateUpdated, intTotalItems, mnyTotalQuote,
intAccountId) VALUES (@p0, @p1, @p2, @p3); select
SCOPE_IDENTITY()',N'@p0 datetime,@p1 int,@p2 decimal(5,4),@p3
int',@p0='2010-11-29 15:39:02:480',@p1=1,@p2=4.5500,@p3=7777777

Here's the stored procedure:

CREATE PROCEDURE [dbo].[usp_Order_Insert]
@dtDateUpdated datetime
, @intTotalItems int
, @mnyTotalQuote money
, @intAccountId int
AS

BEGIN

DECLARE @existingID int
, @existingItems int
, @existingMoney money

--If the record already exists
IF EXISTS
(
SELECT *
FROM Order
WHERE intAccountID = @intAccountId
AND intOrderStatusID = 0
)
BEGIN
--Get the existing record details
SELECT @existingID = intOrderID
, @existingItems = intTotalItems
, @existingMoney = mnyTotalQuote
FROM Order
WHERE intAccountID = @intAccountId
AND intOrderStatusID = 0
--Update the existing record
UPDATE Order
SET intTotalItems = (@existingItems + @intTotalItems)
, mnyTotalQuote = (@existingMoney + @mnyTotalQuote)
, dtDateUpdated = GetDate()
WHERE intOrderID = @existingID
--Return the id of the existing record
SELECT intOrderID
FROM Order
WHERE intOrderID = @existingID
END
ELSE
BEGIN
--Insert a new record
INSERT INTO Order
(
intAccountId
, dtDateUpdated
, intTotalItems
, mnyTotalQuote
)
VALUES
(
@intAccountId
, @dtDateUpdated
, @intTotalItems
, @mnyTotalQuote
)
--Return the new record id
SELECT SCOPE_IDENTITY()
END
END

Any help would be appreciated! Thanks!!

[Fabio Maulo]

<generator class="native" />

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Fabio Maulo

[Brooke Carlson]

Thanks, Fabio! I am using "native" in my mapping file, and it still doesn't use my custom insert. At least I know I'm on the right track, though! :)

[Fabio Maulo]

now try the inverse.

remove native/identity and try again

[Brooke Carlson]

Thanks again, Fabio!

I've tried all of the following generator classes, and none of them have worked:

native
identity
increment
assigned

Given that the table auto-increments, it seems native is the correct choice.

Is there something wrong with the way I'm defining the sql-insert? Or is my mapped class malformed?

All ideas would are appreciated!
Thanks!