Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
vinoar
Posts: 4
Registered: ‎05-29-2012
Accepted Solution

How to execute Stored Procedure using NHibernate

Hi ,

 

I'm Executing a SP ,

 

   String[] DataColumn = { "ProductId", "Line", "Name", "Description", "VBDiscount", "List", "Discount", "Sell", "Quantity", "Extendedcalculatedfiled", "StockWeight", "WTCalculated", "Effectivedate" };
            NHibernate.ISession session = new Sage.Platform.Orm.SessionScopeWrapper();

            NHibernate.IQuery query = (NHibernate.IQuery)session.GetNamedQuery("SP_OpportunityProducts");
            query.SetParameter("OPPORTUNITYID", "O0L6XA100003");

            System.Collections.IList results = query.List();
            System.Collections.Generic.IList<object> tempList = query.List<object>();
            int i = 0;
            foreach (object[] data in tempList)
            {
                if (i < 5)
                {
                    Sage.Platform.ComponentModel.ComponentView cv = new Sage.Platform.ComponentModel.ComponentView(DataColumn, data);
                    results.Add(cv);
                    i++;
                }
                else { break; }
            }
            result = results;

 It Shows the Error ,

 

http://XXXX:XXXX/SlxClient/Opportunity.aspx?entityId=OMX8GA3000AC Named query not known: SP_OpportunityProducts at NHibernate.Impl.AbstractSessionImpl.GetNamedQuery(String queryName) at Sage.Platform.Orm.SessionScopeWrapper.GetNamedQuery(String queryName) at OpportunityProductDiscounts.ProductByOpp.GetOpportunityProducts_Nhib(IOpportunity Opportunity, IList& result) at (Object ) at Sage.Platform.DynamicMethod.DynamicMethodLibrary.Execute(String methodName, Object[] args)

 

 

Thanks

Vinoth Xavier

Please use plain text.
Employee
ToddHardin
Posts: 535
Registered: ‎03-30-2009

Re: How to execute Stored Procedure using NHibernate

Hello Vinoth -

I am not an expert on nHibernate, but I might be able to offer at least a little guidance.

 

The session.GetNamedQuery might not be what you are looking for.

 

I believe that nHibernate supports executing SQL statements, something like:

IQuery query = session.CreateSQLQuery("exec SP_OpportunityProducts @OpportunityId=opportunityid:, @OtherId=:smileysurprised:therid");

query.SetString("opportunityid", opportunity.id);

query.SetString("otherid", otherid);

query.Execute();

 

Note that I'm not positive on the last bit with query.Execute(), maybe it is query.List();

 

The thing is, I'm not sure how the mapping from a raw SQL result set back into the object model is handled.

 

You might look into HQL to see if there is a cleaner way to handle calling a database stored proceedure that will return a result set.

 

Also note - Sage SalesLogix Web v7.5.4 is using nHibernate v2.12 I believe.  Keep that in mind when searching the net looking for examples. The next major release of Sage SalesLogix web will use nHibernate v3.x (I think v3.2 but do not hold me to that).

 

 

Todd Hardin
SalesLogix Professional Services Group
Please use plain text.
New Member
vinoar
Posts: 4
Registered: ‎05-29-2012

Re: How to execute Stored Procedure using NHibernate

Hi Tood,

 

Thanks for your valuable reply.  Its Help Me lot, But now I face an error,

 

Error,

 

Failed to parse SQL. At System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)

 

Please use plain text.