4

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();
anupam
  • 337
  • 8
  • 19

1 Answers1

4

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.

Community
  • 1
  • 1
Sixto Saez
  • 12,610
  • 5
  • 43
  • 51
  • @Sixto:Thanks for the reply.I tried replicating the same sample.But facing problem while firing query.It is throwing exception that unable to find property of my domain object which is mapped to the table in database. – anupam May 03 '11 at 05:58
  • @Sixto:Updated question with DomainObject and Mapping and Procedure in MS SQL 2008 – anupam May 03 '11 at 06:10
  • Could please you put the detail of the exception as an update to the question? Sounds like you basically have it working but the problem is the mapping configuration. Knowing which property is failing will help troubleshoot this. – Sixto Saez May 03 '11 at 13:38
  • @SIXTO:It is working fine .I changed my code a bit and it is working fine with the code updated in question – anupam May 04 '11 at 07:17
  • @SIXTO:Can u please help me to make the same thing working with oracle. In oracle i made it work with Associative array.But i want to pass custom array like the way we did it with data table in SQL. – anupam May 04 '11 at 07:20
  • I'm not too familiar with Oracle anymore. Last major version I worked on was 8i which is pretty out-of-date. The key to get this working with Oracle is finding what mechanisms exists in NHibernate like the SetStructured and see how the Oracle providers support this. As a last resort, the source code for NHiberante is available so you could see how SetStructured is implemented for SQL Server and use that as a guide for implementing something similar for Oracle. – Sixto Saez May 05 '11 at 13:32