Request to help to call a SQL Server Stored Procedure - .net

niravparekh · **Joined:** Wed Dec 10, 2008 2:14 pm **Posts:** 17

Hi,
I am new with hibernate objects.
I have to call a SQL Server Stored procedure, I tried a lot but couldnot do this, so request to help me for .hbm and .cs file and pls tell me how to call this stored procedure with parameters ?

I would like to request if someone can tell me exact syntex for .hbm file and code to call this stored procedure.

#########################################

ALTER PROCEDURE [dbo].[RulesDetails_Get]
(
@ELAID INT ,
@FamilyID INT ,
@GroupID INT ,
@SubGroupID INT ,
@ItemComponentID INT ,
@ComponentID INT
)
AS
BEGIN

SELECT
QtyAlreadyOrdered_Component,
QtyAlreadyOrdered_Family,
QtyAlreadyOrdered_Group ,
QtyAlreadyOrdered_SubGroup,
QtyAlreadyOrdered_ELA ,

MaxQtyAllowed_Component ,
MaxQtyAllowed_Family ,
MaxQtyAllowed_Group ,
MaxQtyAllowed_SubGroup ,
MaxQtyAllowed_ELA ,

QtyBalanceAvailable_Component,
QtyBalanceAvailable_Family,
QtyBalanceAvailable_Group,
QtyBalanceAvailable_SubGroup,
QtyBalanceAvailable_ELA
FROM
MyTable1
WHERE
ELAID = @ELAID
AND
FamilyID = @FamilyID

#########################################

niravparekh · **Joined:** Wed Dec 10, 2008 2:14 pm **Posts:** 17

I am providing my code inwhich I am getting error.
Can someone pls help me to find out what's wrong in this code?
Just my guess is there may be something wrong in .hbm file.

This is my stored Procedure :

Code:

ALTER PROCEDURE [dbo].[RulesDetails_Get] 
( 
@ELAID INT , 
@FamilyID INT , 
@GroupID INT , 
@SubGroupID INT , 
@ItemComponentID INT , 
@ComponentID INT 
) 
AS 
BEGIN 

SELECT 
QtyAlreadyOrdered_Component, 
QtyAlreadyOrdered_Family, 
QtyAlreadyOrdered_Group , 
QtyAlreadyOrdered_SubGroup, 
QtyAlreadyOrdered_ELA , 

MaxQtyAllowed_Component , 
MaxQtyAllowed_Family , 
MaxQtyAllowed_Group , 
MaxQtyAllowed_SubGroup , 
MaxQtyAllowed_ELA , 

QtyBalanceAvailable_Component, 
QtyBalanceAvailable_Family, 
QtyBalanceAvailable_Group, 
QtyBalanceAvailable_SubGroup, 
QtyBalanceAvailable_ELA 
FROM 
MyTable1 
WHERE 
ELAID = @ELAID 
AND 
FamilyID = @FamilyID 

This is my .hbm File

Code:

<?xml version="1.0" encoding="utf-8" ?>
<!-- Generated by MoreGen 28-Apr-2008 11:27:28 -->
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >


   <sql-query name="RulesDetails_Get" >
      <return alias="RulesDetails_Get" class="ELADAO.RulesDetails_Get, ELADAO">
         <return-property name="QtyAlreadyOrdered_Component" column="QtyAlreadyOrdered_Component"></return-property>
         <return-property name="QtyAlreadyOrdered_Family" column="QtyAlreadyOrdered_Family"></return-property>
         <return-property name="QtyAlreadyOrdered_Group" column="QtyAlreadyOrdered_Group"></return-property>
         <return-property name="QtyAlreadyOrdered_SubGroup" column="QtyAlreadyOrdered_SubGroup"></return-property>
         <return-property name="QtyAlreadyOrdered_ELA" column="QtyAlreadyOrdered_ELA"></return-property>
         
         <return-property name="MaxQtyAllowed_Component" column="MaxQtyAllowed_Component"></return-property>
         <return-property name="MaxQtyAllowed_Family" column="MaxQtyAllowed_Family"></return-property>
         <return-property name="MaxQtyAllowed_Group" column="MaxQtyAllowed_Group"></return-property>
         <return-property name="MaxQtyAllowed_SubGroup" column="MaxQtyAllowed_SubGroup"></return-property>
         <return-property name="MaxQtyAllowed_ELA" column="MaxQtyAllowed_ELA"></return-property>
         
         <return-property name="QtyBalanceAvailable_Component" column="QtyBalanceAvailable_Component"></return-property>
         <return-property name="QtyBalanceAvailable_Family" column="QtyBalanceAvailable_Family"></return-property>
         <return-property name="QtyBalanceAvailable_Group" column="QtyBalanceAvailable_Group"></return-property>
         <return-property name="QtyBalanceAvailable_SubGroup" column="QtyBalanceAvailable_SubGroup"></return-property>
         <return-property name="QtyBalanceAvailable_ELA" column="QtyBalanceAvailable_ELA"></return-property>


      </return>

      { call RulesDetails_Get(:ELAID, :FamilyID, :GroupID, :SubGroupID, :ItemComponentID, :ComponentID) }
   </sql-query>

   </hibernate-mapping>

And This is my code to call stored procedure:

Code:

NHibernate.ISession hibSession = null; 

hibSession = getHibSession(); 

IQuery q = hibSession.GetNamedQuery("RulesDetails_Get"); 

q.SetParameter("ELAID", 7); 
q.SetParameter("FamilyID", 28); 
q.SetParameter("GroupID", 79); 
q.SetParameter("SubGroupID", 219); 
q.SetParameter("ItemComponentID", 584); 
q.SetParameter("ComponentID", 563); 

IList results =   q.List(); 

wolli · **Posted:** Wed Jan 07, 2009 1:41 pm

What's the error ?

niravparekh · **Joined:** Wed Dec 10, 2008 2:14 pm **Posts:** 17

This is the error message.
"
No persister for: ELADAO.RulesDetails_Get, ELADAO, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
"

wolli · **Posted:** Thu Jan 08, 2009 4:04 am

Ah, you need a class mapping for ELADAO.RulesDetails_Get, ELADAO. As an alternative you can return scalar values and use a resultset transformer to transform the scalar values to an unmanaged entity:

http://nhforge.org/doc/nh/en/index.html#d0e9672

niravparekh · **Joined:** Wed Dec 10, 2008 2:14 pm **Posts:** 17

sorry but I didnot get this.
As you can see in my code, I have everything and I have backend class also which I am pasting here, So still I am getting the same error.

My class file for .hbm file :

Code:

using System;
using System.Collections;
using System.Collections.Generic;

namespace ELADAO
{
    /// <summary>
    /// tblContactPhone object for NHibernate mapped table tblContactPhone.
    /// </summary>
    public class RulesDetails_Get
    {
        #region Private Fields

        private int? _MaxQtyAllowed_Component;
        private int? _MaxQtyAllowed_Family;
        private int? _MaxQtyAllowed_Group;
        private int? _MaxQtyAllowed_SubGroup;
        private int? _MaxQtyAllowed_ELA;


        private int? _QtyAlreadyOrdered_Component;
        private int? _QtyAlreadyOrdered_Family;
        private int? _QtyAlreadyOrdered_Group;
        private int? _QtyAlreadyOrdered_SubGroup;
        private int? _QtyAlreadyOrdered_ELA;

        private int? _QtyBalanceAvailable_Component;
        private int? _QtyBalanceAvailable_Family;
        private int? _QtyBalanceAvailable_Group;
        private int? _QtyBalanceAvailable_SubGroup;
        private int? _QtyBalanceAvailable_ELA;


        
        #endregion //Private Fields

        #region Public Properties

                #endregion //Public Properties

        #region User Generated Code
        //
        // The code in this section is automatically copied over when MoreGen generates the class.
        //
        public virtual int? QtyBalanceAvailable_Component
        {
            get { return _QtyBalanceAvailable_Component; }
            set { _QtyBalanceAvailable_Component = value; }
        }
        public virtual int? QtyBalanceAvailable_Family
        {
            get { return _QtyBalanceAvailable_Family; }
            set { _QtyBalanceAvailable_Family = value; }
        }

        public virtual int? QtyBalanceAvailable_Group
        {
            get { return _QtyBalanceAvailable_Group; }
            set { _QtyBalanceAvailable_Group = value; }
        }

        public virtual int? QtyBalanceAvailable_SubGroup
        {
            get { return _QtyBalanceAvailable_SubGroup; }
            set { _QtyBalanceAvailable_SubGroup = value; }
        }
        public virtual int? QtyBalanceAvailable_ELA
        {
            get { return _QtyBalanceAvailable_ELA; }
            set { _QtyBalanceAvailable_ELA = value; }
        }



        public virtual int? QtyAlreadyOrdered_Component
        {
            get { return _QtyAlreadyOrdered_Component; }
            set { _QtyAlreadyOrdered_Component = value; }
        }
        public virtual int? QtyAlreadyOrdered_Family
        {
            get { return _QtyAlreadyOrdered_Family; }
            set { _QtyAlreadyOrdered_Family = value; }
        }

        public virtual int? QtyAlreadyOrdered_Group
        {
            get { return _QtyAlreadyOrdered_Group; }
            set { _QtyAlreadyOrdered_Group = value; }
        }

        public virtual int? QtyAlreadyOrdered_SubGroup
        {
            get { return _QtyAlreadyOrdered_SubGroup; }
            set { _QtyAlreadyOrdered_SubGroup = value; }
        }
        public virtual int? QtyAlreadyOrdered_ELA
        {
            get { return _QtyAlreadyOrdered_ELA; }
            set { _QtyAlreadyOrdered_ELA = value; }
        }



        public virtual int? MaxQtyAllowed_ELA
        {
            get { return _MaxQtyAllowed_ELA; }
            set { _MaxQtyAllowed_ELA = value; }
        }


        public virtual int? MaxQtyAllowed_Component
        {
            get { return _MaxQtyAllowed_Component; }
            set { _MaxQtyAllowed_Component = value; }
        }

        public virtual int? MaxQtyAllowed_Family
        {
            get { return _MaxQtyAllowed_Family; }
            set { _MaxQtyAllowed_Family = value; }
        }

        public virtual int? MaxQtyAllowed_SubGroup
        {
            get { return _MaxQtyAllowed_SubGroup; }
            set { _MaxQtyAllowed_SubGroup = value; }
        }

        public virtual int? MaxQtyAllowed_Group
        {
            get { return _MaxQtyAllowed_Group; }
            set { _MaxQtyAllowed_Group = value; }
        }

        #endregion // User Generated Code

    }
}

wolli · **Posted:** Thu Jan 08, 2009 2:01 pm

You also have to write a mapping for the class itself (properties).

niravparekh · **Joined:** Wed Dec 10, 2008 2:14 pm **Posts:** 17

I have already defined return property names in .hbm file as you can see in my code.

wolli · **Posted:** Fri Jan 09, 2009 1:12 pm

That's not the same. What you specifiy there is the mapping between the sp result and the *managed* class. Hibernate still needs no know the class itself. So you need a class mapping. As an alternative you can return a bunch of scalar values and use a resultset transformer:

Code:

sess.CreateSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")
        .SetResultTransformer(Transformers.AliasToBean(typeof(CatDTO)))

Or you can try something like this:

http://infozerk.com/averyblog/refactoring-using-object-constructors-in-hql-with-nhibernate/