How to execute Stored Procedure using NHibernate

[vinoar]

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

[ToddHardin · Accepted solution]

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=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).

[vinoar]

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)