CODEWITHSUNDEEP
sql-servernhibernatestored-procedurespackage
anupam
anupam

Reputation: 357

While calling SQL Server stored procedure from NHibernate can I pass list or custom data type as a parameter

I tested this with Oracle. It is working fine. Because there I have Package and defined Associative Array type and Stored Procedure in Package body.

Though there are no concept of packages in SQL Server. How to make this work in SQL Server?

Domain Object Start

[Serializable]
public class Employee
{
    public virtual int EmployeeId
    {
        get;
        set;
    }
    public virtual string EmployeePassword
    {
        get;
        set;
    }

    public virtual string EmployeeName
    {
        get;
        set;
    }

    public virtual int TeamAssociatedWith
    {
        get;
        set;
    }
    public virtual string IsCaptain
    {
        get;
        set;
    }
    public virtual int NumberOfMOM
    {
        get;
        set;
    }
    public virtual int Balance
    {
        get;
        set;
    }       
}

Mapping

<?xml version="1.0" encoding="utf-8" ?>  
 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">  
   <class name="DomainObject.Employee,DomainObject" table="Employee">  
   <id name="EmployeeId" column="EMP_ID" type="int" unsaved-value="0">  
     <generator class="native">         
     </generator>  
   </id>  
   <property name="EmployeePassword" column="EMP_PASSWORD" type="string"/>  
   <property name="EmployeeName" column="EMP_NAME" type="string"/>    
   <property name="TeamAssociatedWith" column="TEAM_ASSOCIATED_WITH" type="int"/>  
   <property name="IsCaptain" column="IS_CAPTAIN" type="string"/>  
   <property name="Balance" column="BALANCE" type="int"/>  
   <property name="NumberOfMOM" column="NO_OF_MOM" type="int"/>  
 </class>  
</hibernate-mapping>

Stored procedure

CREATE PROCEDURE [dbo].[some_sp] @id IntTable READONLY   
AS  
SELECT EMP_ID,EMP_NAME,EMP_PASSWORD,
TEAM_ASSOCIATED_WITH,IS_CAPTAIN,NO_OF_MOM,BALANCE 
FROM employee; 
GO


ISQLQuery final = eventhistorysession.CreateSQLQuery("EXEC TestCustom @location = :id");

IQuery result = final.SetStructured("id", dt);

IList finalResult = result.List();

Upvotes: 4

Views: 3434

Answers (1)

Sixto Saez
Sixto Saez

Reputation: 12680

In SQL Server, stored procedures can have parameters of type table that can be used to mimic the Oracle Associative Array feature. In your situation, you'd be sending a "table" with a single row and multiple columns. There is a good example for NHibernate here in the accepted answer.

Upvotes: 4

Related Questions