在NHibernate里执行存储过程

在NHibernate里执行存储过程

NHibernate 是一个基于.Net 的针对关系型数据库的对象持久化类库,来源于非常优秀的基于Java的Hibernate 关系型持久化工具.
在NHibernate里怎么执行存储过程呢?下面有一个简单的例子,使用的MS SQL Server 2000 NorthWind库里的CustOrderHist存储过程.
先建2个实体类:
1.CustOrderHist : Customer Historical Order Summary

    ///   <summary>
     ///  Customer Historical Order Summary Object Entity Class
     ///   </summary>
     public   class  CustOrderHist
    {
         private   string  productName;
         private   int  total;

         ///   <summary>
         ///  Constructor
         ///   </summary>
         ///   <param name="productName"> Product Name </param>
         ///   <param name="total"> Total Quantity </param>
         public  CustOrderHist (  string  productName, int  total)
        {
             this .productName  =  productName;
             this .total  =  total;
        }

         ///   <summary>
         ///  Product Name
         ///   </summary>
         public   string  ProductName
        {
             get  {  return  productName; }
        }

         ///   <summary>
         ///  Total Quantity
         ///   </summary>
         public   int  Total
        {
             get  {  return  total; }
        }

         ///   <summary>
         ///  Return Product Name And Quantity
         ///   </summary>
         ///   <returns></returns>
         public   override   string  ToString ( )
        {
             return   " Product Name :  "   +  productName  +   " ; Quantity:  "   +  total;
        }
    }

映射关系:

<? xml version="1.0" encoding="utf-8"  ?>
< hibernate-mapping  xmlns ="urn:nhibernate-mapping-2.2" >
   < sql-query  name ="GetCustomerOrderHistory" >
     < return-scalar  column ="ProductName"  type ="String"   />
     < return-scalar  column ="Total"  type ="int"   />
    exec CustOrderHist :CustomerID
   </ sql-query >
</ hibernate-mapping >

2.Customer:Customer表的2个字段:CustomerID And CompanyName 提供选择CustomerID

///   <summary>
     ///  Customer Object Entity Class
     ///   </summary>
     public   class  Customer
    {
         private   string  customerID;
         private   string  companyName;

         ///   <summary>
         ///  Customer ID
         ///   </summary>
         public   string  CustomerID
        {
             get  {  return  customerID; }
             set  { customerID  =  value; }
        }

         ///   <summary>
         ///  Company Name
         ///   </summary>
         public   string  CompanyName
        {
             get  {  return  companyName; }
             set  { companyName  =  value; }
        }
    }

映射关系:

<? xml version="1.0" encoding="utf-8"  ?>
< hibernate-mapping  xmlns ="urn:nhibernate-mapping-2.2" >
   < class  name ="NHibernate.Study.Entity.Customer,Entity"  table ="Customers" >
     < id  name ="CustomerID" >
       < column  name ="CustomerID"  sql-type ="string"  length ="5" />
       < generator  class ="assigned"   />
     </ id >
     < property  name ="CompanyName" >
       < column  name ="CompanyName"  sql-type ="string"  length ="40" />
     </ property >
   </ class >
</ hibernate-mapping >

再来看看Data Object Access Class:

    public   class  BllExecPro : IDisposable
    {
         private  ISession session  =   null ;

         public   void  Dispose ( )
        {
            session.Dispose( );
        }

         public  BllExecPro ( )
        {
            session  =  NHLibrary.NHFactory.OpenSession( );
        }

         public  IList < Customer >  getCustomerIDList ( )
        {
             return  session.CreateQuery(  " FROM Customer "  )
                .SetMaxResults(  10  )
                .List < Customer > ( );
        }

         public  IList < CustOrderHist >  getCustOrderHistList (  string  customerID )
        {
            IQuery query  =  session.GetNamedQuery(  " GetCustomerOrderHistory "  )
                .SetString(  " CustomerID "  , customerID )
                .SetResultTransformer(
                     new  NHibernate.Transform.AliasToBeanConstructorResultTransformer(
                     typeof ( CustOrderHist ).GetConstructors( )[ 0 ] ) );
             return  query.List < CustOrderHist > ( );
        }
    }

页面 HTML:

< form  id ="form1"  runat ="server" >
     < div >
         < asp:DropDownList  ID ="DropDownList1"  runat ="server"  AutoPostBack ="True"  DataTextField ="CompanyName"
            DataValueField ="CustomerID"  OnSelectedIndexChanged ="DropDownList1_SelectedIndexChanged"
            Width ="238px" >
         </ asp:DropDownList >
         < asp:GridView  ID ="GridView1"  runat ="server" ></ asp:GridView >     
     </ div >
     </ form >

Codebehind:

    protected   void  Page_Load (  object  sender , EventArgs e )
    {
         if  (  ! IsPostBack )
        {
            bindDropDownList( );
        }
    }

     private   void  bindDropDownList ( )
    {
        BllExecPro bllExecPro  =   new  BllExecPro( );
        DropDownList1.DataSource  =  bllExecPro.getCustomerIDList( );
        DropDownList1.DataBind( );
        bindGridView( bllExecPro,DropDownList1.SelectedValue );
    }

     private   void  bindGridView (BllExecPro bllExecPro,  string  customerID )
    {
         if  ( bllExecPro  ==   null )
            bllExecPro  =   new  BllExecPro( );
        GridView1.DataSource  =  bllExecPro.getCustOrderHistList( customerID );
        GridView1.DataBind( );
        bllExecPro.Dispose( );
    }

     protected   void  DropDownList1_SelectedIndexChanged (  object  sender , EventArgs e )
    {
        bindGridView( null , DropDownList1.SelectedValue );
    }

浏览页面,通过SQL 事件探察器可以看到在执行 bllExecPro.getCustomerIDList( ) 时实际执行的SQL 语句是：

select   top   10  customer0_.CustomerID  as  CustomerID3_, customer0_.CompanyName  as  CompanyN2_3_  from  Customers customer0_

在执行 bllExecPro.getCustOrderHistList( customerID ) 时实际执行的SQL语句是：

exec  sp_executesql N ' exec CustOrderHist @p0 ' , N ' @p0 nvarchar(5) ' ,  @p0   =  N ' 对应的customerID '