How do I pass parameters to a stored procedure in NHibernate when one of the parameters is a list?

Question

I have a stored proc mapped as follows in NHibernate:

 <sql-query name="HistoricSearch">
     <return class="ResultItem">
     </return>
     exec dbo.SelectHistoricResultItem :StartDate, :EndDate, :ListA, :ListB, :ListC
 </sql-query>

The following code works fine if each list of search options contains a single parameter:

 IQuery query = session.GetNamedQuery("HistoricSearch");
            query.SetDateTime("StartDate", fromDate);
            query.SetDateTime("EndDate", toDate);
            query.SetParameterList("ListA", searchOptionA);
            query.SetParameterList("ListB", searchOptionB);
            query.SetParameterList("ListC", searchOptionC);
            List<ResultItem> resultItems = (List<ResultItem>)query.List<ResultItem>();
            return resultItems;

but if a list contains multiple values, I get the following error:

 Procedure or function SelectHistoricResultItem has too many arguments specified

According to the description of the SetParameterList() method, it should "Bind multiple values to a named query parameter". I assume that the problem is in the mapping file at:

 exec dbo.SelectHistoricResultItem :StartDate, :EndDate, :ListA, :ListB, :ListC

but I have no idea how else to structure this to allow for multiple parameters. Can anyone tell me how this is done?

Thanks <3

score 4 · Accepted Answer · answered Aug 09 '11 at 07:18

Ok, after some experimentation I found that this method works for me.

            query.SetParameter("ListA", string.Join(", ", searchOptionA));
            query.SetParameter("ListB", string.Join(", ", searchOptionB));
            query.SetParameter("ListC", string.Join(", ", searchOptionC));

How do I pass parameters to a stored procedure in NHibernate when one of the parameters is a list?

1 Answers1