Blogger Widgets
  • Sharing Photos using SignalR
  • TFS Extenstion - allows copy work items between projects
  • Displaying jquery progressbar with ajax call on a modal dialog
  • Managing windows services of a server via a website
  • Exploring technologies available to date. TechCipher is one place that any professional would like to visit, either to get an overview or to have better understanding.

Search This Blog

Thursday 4 April 2013

Consuming return value from stored procedure using NHibernate without mapping

After haggling around I have found a solution that works. First I have created a stored procedure as follows:

CREATE PROCEDURE GETTOTALINCOME
AS 
DECLARE
     @TOTALINCOMECOUNT MONEY;
BEGIN
     SELECT @TOTALINCOMECOUNT = SUM(INVAMOUNT) FROM INVOICE
     RETURN @TOTALINCOMECOUNT;
END;
This is a simple stored procedure that just returns a value. Now lets look at NHibernate code block that can execute the stored procedure and read the return value.

using (var session = OpenSession())
{
     var query = session.CreateSQLQuery("EXEC GETTOTALINCOME");
     object result = query.UniqueResult();
     retVal = Convert.ToInt64(result);
}
I am getting exceptions so I started debugging it and have found "result" is null. I have looked at some articles of using stored procedure, but I do not want to define mapping either FulentNHibernate mapped classes or .hbm configuration.

I have looked again into stored procedure definition, NHibernate can read the values that are returned by a "SELECT" statement but not "RETURN". So I have changed my stored procedure as follows:

CREATE PROCEDURE GETTOTALINCOME
AS 
DECLARE
     @TOTALINCOMECOUNT MONEY;
BEGIN
     SELECT @TOTALINCOMECOUNT = SUM(INVAMOUNT) FROM INVOICE
     SELECT @TOTALINCOMECOUNT;
END;
Now this works.

“We have to stop optimizing for programmers and start optimizing for users.” – Jeff Atwood

3 comments:

Copyright © 2013 Template Doctor . Designed by Malith Madushanka - Cool Blogger Tutorials | Code by CBT | Images by by HQ Wallpapers