You should not call an SP (or really make any kind database query) in a loop like that, but rather use set-based operations. In your case, this means using a table valued parameter (available since SQL Server 2008); code for an NHibernate IType was linked here a few posts ago: https://groups.google.com/d/msg/nhusers/EXwrwZonh_A/GYSnwkFN4EYJ
A good resource on the general best practices of using array and lists is: http://www.sommarskog.se/arrays-in-sql-2008.html On Thursday, November 20, 2014 8:00:50 PM UTC+1, AG wrote: > > Hello, > > > I am trying to make insert using a stored procedure. However I am sending > data in bulk to be inserted. I therefore want to execute my stored > procedure in batch. Is it possible to do that? Here is the code I have > now-not sure if this is the right way to do it. > > public void AddItemsAsFavourite(string customerNumber, > Dictionary<string,string> items, int shoppingListId) > { > try > { > _session.SetBatchSize(items.Count()); > _session.FlushMode = FlushMode.Commit; > > using (var tx = _session.BeginTransaction()) > { > foreach (KeyValuePair<string,string> item in items) > { > IQuery query = _session.CreateSQLQuery(" exec mySp > :customer_number, :item_code, :unit, :shopping_list_id"); > > query.SetParameter("customer_number", > customerNumber); > query.SetParameter("item_code", item.Key); > query.SetParameter("unit", item.Value); > query.SetParameter("shopping_list_id", > shoppingListId); > > query.UniqueResult(); > } > tx.Commit(); > } > _session.SetBatchSize(0); > > } > catch (Exception ex) > { > > throw; > } > } > > Thanks > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
