NHibernate - Stream large result sets?

Question

Hi,

I have to read in a large record set, process it, then write it out to a flat file.

The large result set comes from a Stored Proc in SQL 2000.

I currently have:
var results = session.CreateSQLQuery("exec usp_SalesExtract").List();

I would like to be able to read the result set row by row, to reduce the memory foot print

Thanks

Answer 1

Why not just use SQL Server's bcp Utility: http://msdn.microsoft.com/en-us/library/aa174646%28SQL.80%29.aspx to write the file from the stored procedure. If you need to do logic on the data, modify the the procedure to do what your need.

Answer 2

NHibernate doesn't allow to do it directly.

You can do it with ADO.NET SqlDataReader using session.Connection property:

SqlCommand MyCommand = new SqlCommand("sp_SalesExtract", session.Connection);
MyCommand.CommandType = CommandType.StoredProcedure;
SqlDataReader MyDataReader = MyCommand.ExecuteReader();

while (MyDataReader.Read())
{
    // handle row data (MyDataReader[0] ...)
}

Answer 3

NHibernate is not designed for that usage. Plus, you're not really using its features.

So, in this case, it's better to use raw ADO.NET.

Answer 4

I've not tried this, but if it's good enough for Ayende...

http://ayende.com/Blog/archive/2010/06/27/nhibernate-streaming-large-result-sets.aspx