[NHibernate] Use of stored procedures (iii)

Directory

Write in front

Documentation and series articles

Inquire

Summarize

Write in front

The previous article describes the use of stored procedures in the NHibernate to delete and change the operation, of course, the query is also possible, in the nhibernate can also execute arbitrary stored procedures. This article describes how to use the query's stored procedure.

Documentation and series articles

[Nhibernate] Architecture

[NHibernate] Isessionfactory Configuration

[NHibernate] Persistence class (persistent Classes)

[NHibernate] O/R Mapping Basics

[NHibernate] Collection Class (collections) mappings

[NHibernate] Association mappings

[NHibernate] Parent/child

[NHibernate] Cache (nhibernate.caches)

[NHibernate] NHibernate.Tool.hbm2net

[NHibernate] Nullables

[NHibernate] NHibernate How to map image fields in SQL Server

[NHibernate] Basic configuration and testing

[NHibernate] HQL Query

[NHibernate] Conditional query criteria

[NHibernate] additions and deletions to change the operation

[NHibernate] Transactions

[NHibernate] concurrency control

dependent objects of [NHibernate] components

[NHibernate] One-to-many relationship (cascade Delete, cascade Add)

[NHibernate] One-to-many relationship (associative query)

[NHibernate] Many-to-many relationships (associative queries)

[NHibernate] Lazy loading

[NHibernate] Load now

[NHibernate] View processing

[NHibernate] N+1 Select query problem analysis

[NHibernate] Use of stored procedures (i)

[NHibernate] Use of stored procedures (ii)

Inquire

Use node <sql-query> query based on user ID

To add a stored procedure

Create proc [dbo]. [ps_search]  @CustomerID uniqueidentifier  as begin  Select *  from Tb_customer   where CustomerID=@CustomerID  End

Use <sql-query> in the mapping file and define the name of the <sql-query> query

1   <!--requires the same level as the class node -2   <Sql-queryname= "Ps_search" >3    <returnclass= "Wolfy.shop.domain.entities.customer,wolfy.shop.domain" />4 exec Ps_search:customerid5   </Sql-query>

Test

In the data access layer, use the Getnamedquery method provided by the ISession interface to invoke the named stored procedure and pass an shaping parameter. The code is as follows:

1         /// <summary>2         ///using stored procedures to query3         /// </summary>4         /// <returns></returns>5          PublicIlist<customer>Searchcustomerbyidusingproc (Guid CustomerID)6         {7ISession session =nhibernatehelper.getsession ();8             //parameter is the name specified for the <sql-query> node in the mapping file9 Ten             returnSession. Getnamedquery ("Ps_search") One. SetGuid ("CustomerID", CustomerID) A. List<customer>(); -}

Results

The generated SQL statement

1 exec sp_executesql n'exec ps_search @p0', n'@p0 uniqueidentifier  ',@p0='ddf63750-3307-461b-b96a-7ff356540cb8' 

What if you want to return part of an entity's properties?
Modify the stored procedure to select only the fields you want

1 ALTER proc [dbo].[Ps_search]2   @CustomerID uniqueidentifier3  as4 begin5   SelectCustomerName fromTb_customer6   whereCustomerID=@CustomerID7   End 

To modify a mapping file

  <!--requires the same level as the class node -  <Sql-queryname= "Ps_search" >   <!--<return class= "Wolfy.shop.domain.entities.customer,wolfy.shop.domain"/> -    <Return-scalarcolumn= "CustomerName"type= "String"/>exec Ps_search:customerid</Sql-query>

Modify method

1         /// <summary>2         ///using stored procedures to query3         /// </summary>4         /// <returns></returns>5          Public stringSearchcustomernameusingproc (Guid CustomerID)6         {7ISession session =nhibernatehelper.getsession ();8             //parameter is the name specified for the <sql-query> node in the mapping file9             returnSession. Getnamedquery ("Ps_search")Ten. SetGuid ("CustomerID", CustomerID). Uniqueresult (). ToString (); One}

Results

Because the stored procedure is executed, the SQL statement is the same as above (modified on the basis of the original stored procedure).

The most common bugs

Encountered such an exception, can ignore it, I looked for a long time did not find a solution, and then directly. List<customer> () returns the result. Although the result is always a little less satisfied (perfectionist).

Summarize

This article describes how to use query stored procedures in NHibernate and what to be aware of, such as the <sql-query> nodes in the mapping file need to be the same level as <class>. To here nhibernate use stored procedures to delete and remove changes even if the end, say a digression, using ORM in order to write less SQL, so in the actual project using NHibernate stored procedures are relatively rare. Even if there is no way to add <sql-**> directly to the mapping file. Because you know, not necessarily others will know, so cause a certain degree of maintenance difficulty, but know this kind of tall on the writing, encountered when know what is OK.

Reference article: http://www.cnblogs.com/lyj/archive/2008/11/07/1328782.html

[NHibernate] Use of stored procedures (iii)