Hi, I´m new in NHibernate, and I´m trying to run this store procedure:

CREATE PROCEDURE mapping
@IDMAP int
AS
SELECT no_mapeo, nombre
FROM tmapeo
WHERE id = @IDMAP
go

here is my NHibernate.hbm.xml:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" >
<class name="SiseHibernate.mapeo, SiseHibernate" table="tmapeo" lazy="false" >
<id name="Id" column="id" type="int">
<generator class="native"/>
</id>
<property name="no_mapeo" column="no_mapeo" type="int"/>
<property name="nombre" column="nombre" type="string" length="40"/>
<loader query-ref="mapping"/>
</class>
<sql-query name="mapping" >
<return alias="mapping" class="SiseHibernate.mapeo, SiseHibernate">
<!--<return-property name="Id" column="id"/>-->
<return-property name="no_mapeo" column="no_mapeo"/>
<return-property name="nombre" column="nombre"/>
</return>
exec dbo.mapping IDMAP
</sql-query>

</hibernate-mapping>

and my C# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using NHibernate;
using NHibernate.Cfg;

namespace SiseHibernate
{
static class Program
{
/// <summary>
/// Punto de entrada principal para la aplicación.
/// </summary>
[STAThread]
static void Main()
{
//OpenSession();

using (ISession session = OpenSession())
{
using (ITransaction transaction = session.BeginTransaction())
{
try
{
//mapeo NuevoReg = new mapeo();
//NuevoReg.no_mapeo = 2;
//NuevoReg.nombre = "fernando";
//session.Save(NuevoReg);
//transaction.Commit();
// NuevoReg.Id = 2;

IQuery query = (IQuery)session.GetNamedQuery("MAPPING");
query.SetParameter("IDMAP", idmap); <--- I don´t understand here.
System.Collections.IList result = query.List();




Console.WriteLine("FIN");
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
transaction.Dispose();
}
}
}
}



static ISession OpenSession()
{
Configuration cfg = new Configuration();
cfg.AddAssembly("SiseHibernate");
ISessionFactory factory = cfg.BuildSessionFactory();
ISession session = factory.OpenSession();
ITransaction transaction = session.BeginTransaction();
//Console.ReadKey();
return factory.OpenSession();
}

}
}

How can I run the store procedure????, it´s the same if I use a store Procedure with updates and inserts?

Hmm.... I guess it would help if you wrote the error message you got. But what attracted my attention was your sql:

I guess this should be:

The colon is required to identify a parameter name, wich can later be passed to the Database:

Im not sure what it is, you dont understand. Please substantiate your question.
Query-Parameters are generally directly supported by the Database.
The application prepares your query against the Database Server and parameters can be set for each call to it.
Like this you avoid SQL-Injection and increase performance, if you have to call the same query with different parameters multiple times.

Greetings!
Zorgoban

Hi, thanks for answer me, it send me a message error that said:

Parameter IDEM does not exist as a named parameter in [exec dbo.mapping :IDMAP]

I don´t understand well what kind of values I have to set in the query parameter
query.SetParameter("name of my parameter", "name of column?";

Thanks !

Yes, thats what I thought. You need to put the colon in front of the parameter name inside your query, like I wrote it in my last post.

And the function means:

As far as I can see, the rest of your code looks good.

Greetings!
Zorgoban

Hi thanks, I do it but i have this error message:
could not locate named parameter [IDEM]
I set the parameter as you said me, but it doesn´t works.
Thanks !

I´m using Visual Studio 2008, and I´m trying to make it work over a c# application.
Thanks

Yes, thats right, according to the code you posted here, you dont even use a parameter named IDEM anywhere. Its probably just a typo.

I wrote the correct parameter and the application is running, but when I set :
query.list()
after
query.SetParameter("IDMAP",2);
it appears the following error :

could not execute query
[ exec dbo.mapping @p0 ]
Name:IDMAP - Value:2
[SQL: exec dbo.mapping @p0]

{"could not execute query\r\n[ exec dbo.mapping @p0 ]\r\n Name:IDMAP - Value:2\r\n[SQL: exec dbo.mapping @p0]"}

Thanks!!!

How could you write the correct parameter without query.SetParameter("IDMAP",2)? And when exactly is the application running? Please send the whole Stack-Trace. The reason is missing, why it couldnt execute the query.

I checked you code again, and neither your SP, nor the query, seemed to have an error. Is the user you use to connect to the database having the right to execute this SP?

Greetings
Zorgoban