Nikhil Ghuse Nikhil Ghuse - 4 months ago 25
MySQL Question

How to execute mysql stored procedure in Nhibernante

I want to create nhibernate query for my Mysql stored procedure

Answer

Hi Nhikhil,

Your question is good.

it's very easy to call stored Procedure through Nhibernate first you create stored procedure name as SelectListEmployeeSP in mysql

as follows:

CREATE PROCEDURE `SelectListEmployeeSP`()
BEGIN
SELECT * FROM Employee e;
END

And then create .bhm.xml file and write your return values as follows:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <sql-query name="SelectListEmployeeSP">
        <return-scalar column="ID" type="integer"/>
        <return-scalar column="Name" type="string"/>
        <return-scalar column="Address" type="string"/>
        call SelectListEmployeeSP
    </sql-query>
</hibernate-mapping>

And then call this procedure from .cs file as follows:

public void GetListEmployee()
        {
            var employee = NHSession.GetNamedQuery("SelectListEmployeeSP")
                                    .List<object[]>()
                                    .Select(obj => new EmployeeEntity
                                    {
                                        ID = Convert.ToInt32(obj[0]),
                                        Name = obj[1].ToString(),
                                        Address = obj[2].ToString()
                                    })
                                    .ToList();
        }

After execute above code you will get the all employee list.

Thanks and Regards - Ankush

Comments