SQL 通过 nhibernate 调用存储过程

Call stored procedure through nhibernate

提问by gro

I need to call a stored procedure through nhibernate, but I do not know how. I have simple stored procedure:

我需要通过 nhibernate 调用存储过程，但我不知道如何调用。我有简单的存储过程：

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 tried 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 solve this problem or link me to something useful.

帮我解决这个问题或将我链接到有用的东西。

回答by Leniel Maccaferri

See if these links help you:

看看这些链接是否对您有帮助：

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

将 NHibernate 与存储过程一起使用（来自 Ayende - NHibernate 之父）

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

如何从 NHibernate 调用没有结果的存储过程？

How to execute Stored Procedure with NHibernate - Working Sample Code

如何使用 NHibernate 执行存储过程 - 工作示例代码

回答by Martijn

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

似乎你缺少一个 Query.executeUpdate() ，所以

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

应该可以工作，但以编程方式绑定变量要好得多

回答by Gerke Geurts

Here is an example of an entity mapping that uses stored procedures for insert, update and delete of database rows:

以下是使用存储过程插入、更新和删除数据库行的实体映射示例：

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="dbo"
               assembly="MyAssembly"
               namespace="MyAssembly.MyNamespace">

  <class name="MyEntity" table="my_entity" lazy="false">
    <id name="MyId" column="my_id" type="Int64">
      <generator class="native" />
    </id>
    <property name="Name" type="string" column="name" />
    <property name="Comment" type="string" column="comment" />

    <sql-insert xml:space="preserve">
      DECLARE @my_id  bigint
      EXECUTE dbo.InsertMyEntity @name = ?, @comment = ?, @my_id = @my_id OUT
      SELECT  @my_id
    </sql-insert>
    <sql-update xml:space="preserve">
      EXECUTE dbo.UpdateMyEntity @name = ?, @comment = ?, @my_id = ?
    </sql-update>
    <sql-delete xml:space="preserve">
      EXECUTE dbo.DeleteMyEntity @my_id = ?
    </sql-delete>
  </class>
</hibernate-mapping>

With this mapping you can use the ISession.Save, ISession.Updateand ISession.Deletemethods to manage your entities and keep the NHibernate first-level entity cache in sync with the database.

通过这种映射，您可以使用ISession.Save,ISession.Update和ISession.Delete方法来管理您的实体，并使 NHibernate 一级实体缓存与数据库保持同步。

Cheers, Gerke.

干杯，格克。