How can I stop System.OutOfMemoryException on bulk insert

Hi I am bringing about 100000 records into memory cleaning the data and inserting into a new table. After inserting around 2000 records I get the following exception.

A first chance exception of type 'System.OutOfMemoryException' occurred in Iesi.Collections.DLL A first chance exception of type 'FluentNHibernate.Cfg.FluentConfigurationException' occurred in FluentNHibernate.DLL

I am using Fluent NHibernate. but am not 开发者_如何学Gosure if the problem is related to fluent NHibernate itself.

What is the best way to do bulk inserts to the db, I am wondering whether to use stringbuilder and build an sql query instead. Any pointers much appreciated.

Whilst I'm sure that there are many techniques that you can employ to do this kind of operation through hibernate, it seems likely that you would be better off constructing your data-cleansing operation as an SQL statement and running it on the database itself. If you need to run it regularly, you might consider a stored procedure.

Try using a stateless session:

object[] objectsToInsert = GetObjectsToInsert();

using (var statelessSession = sessionFactory.OpenStatelessSession())
using (var transaction = statelessSession.BeginTransaction())
{
    foreach (var objectToInsert in objectsToInsert)
    {
        statelessSession.Insert(objectToInsert);
    }
    transaction.Commit();
}