Call stored procedure through nhibernate

Question

I need to call a stored procedure through nhibernate. But I did not know it make. I have simple stored procedure:

CREATE PROCEDURE InsertDoc 
    @Name nvarchar(50),   
    @Author nvarchar(50),
    @Link nvarchar(50) 
AS 
  INSERT INTO documents(name, date, author, doclink) 
  VALUES(@Name, CURRENT_TIMESTAMP, @Author, @Link)

I do this in my code:

public class documents
{
public int id;
public string name;
public DateTime date;
public string author;
public string doclink;

public void CreateDocuments(String n,String l,String u)
{
documents exSample = new documents();
exSample.name = n;
exSample.date=DateTime.Now;
exSample.author=u;
exSample.doclink=l;

using (ISession session = OpenSession())
{
using (ITransaction transaction = session.BeginTransaction())
{
//Session.CreateSQLQuery("EXEC :sp_name :start_date :end_date").SetString("sp_name", <>;)
session.CreateSQLQuery("EXEC InsertDoc @Name = N'" + exSample.name + "',@Author = N'" + exSample.author + "',@Link = N'" + exSample.doclink + "'");
// session.Save(exSample);
transaction.Commit();
} 
}
}

public ISessionFactory factory;

public ISession OpenSession()
{
if (factory == null)
{
Configuration conf = new Configuration();
conf.AddAssembly(Assembly.GetCallingAssembly());
factory = conf.BuildSessionFactory();
}
return factory.OpenSession();
}
}
}

I call the stored procedure

session.CreateSQLQuery("EXEC InsertDoc @Name = N'" + exSample.name + "',@Author = N'" + exSample.author + "',@Link = N'" + exSample.doclink + "'");

In my mapping file I have these settings:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" namespace="WebApplication1" assembly="WebApplication1">
  <class name="WebApplication1.documents" table="documents" lazy="false">
    <id name="id" access="field">
      <generator class="native" />
    </id>
    <property name="name" access="field" column="name" type="String"/>
    <property name="date" access="field" column="date" type="date"/>
    <property name="author" access="field" column="author" type="String"/>
    <property name="doclink" access="field" column="doclink" type="String"/>
  </class>
</hibernate-mapping>

help me to solve this problem or give useful link

Answer 1

See if theses links help you:

Using NHibernate With Stored Procedures (from Ayende - the father of NHibernate)

How do I call a stored procedure from NHibernate that has no result?

How to execute Stored Procedure with NHibernate - Working Sample Code

Answer 2

Seems you're missing a Query.executeUpdate() for one, so

session.CreateSQLQuery("EXEC InsertDoc @Name = N'" + exSample.name + "',@Author = N'" + exSample.author + "',@Link = N'" + exSample.doclink + "'").executeUpdate();

should work, but it's much nicer to bind your variables programaticly