NHibernate 是一个基于.Net的针对关系型数据库的对象持久化类库,来源于非常优秀的基于Java的Hibernate关系型持久化工具.在NHibernate里怎么执行存储过程呢?下面有一个简单的例子,使用的MS SQLServer2000 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;
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; } }
/// <summary> /// Constructor /// </summary> /// <param name="productName"> Product Name </param> /// <param name="total"> Total Quantity </param> public CustOrderHist ( string productName, int total) { this .productName = productName;<?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 :Customer
</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 '
原文：