None in this world worked with Nhibernate and oracle stored procedure before?

Question

Hi all, I am continuously posting the same kind of question but no one answering. This is the happening for the first time. Please help me if any one can.

Problem:

I have mapped my database table with NHibernate to C# class. But i need to call a stored procedure with some parameters. But NHibernate calls the stored procedure with it's own wish! (i.e. NHibernate maintains an order of sequence for the stored procedure parameters). Now my question is, How do i know this order of sequence of parameters?

Iam using ASP.Net with C# in VS 2008 SP1, Oracle and NHibernate 1.2 version

Code:

<sql-insert>
  call strored_proc(?,?,?,?,?,?,?,?)
</sql-insert>

Answer 1

15.3. Custom SQL for create, update and delete

The order of the positional parameters is currently vital, as they must be in the same sequence as NHibernate expects them.

You can see the expected order by enabling debug logging for the NHibernate.Persister.Entity level. With this level enabled NHibernate will print out the static SQL that is used to create, update, delete etc. entities. (To see the expected sequence, remember to not include your custom SQL in the mapping files as that will override the NHibernate generated static sql.)

I'd guess it's in the same order as the properties are mapped?

Answer 2

You could so something like this.

session.CreateSQLQuery("call strored_proc(:a, :b, :c, :d)")
            .SetInt64("a", 54)
            .SetInt64("b", 544)
            .SetString("c", "your value")
            .SetInt64("d", 6546546)
            .ExecuteUpdate();

Where a,b,c,d are parameters to your function.

Call .List() on it for it to return a 2 dimensional object array.

If want want to do a mapping:

<sql-query name="StoredProcName">
 call strored_proc(:a, :b, :c, :d)
</sql-query>

Then in your C# code

session.GetNamedQuery("StoredProcName")
            .SetInt64("a", 54)
            .SetInt64("b", 544)
            .SetString("c", "your value")
            .SetInt64("d", 6546546)
            .ExecuteUpdate();

or again call .List(); if you need results instead of .ExecuteUpdate();