Get an IDataReader from a typed List
- by Jason Kealey
I have a List<MyObject> with a million elements. (It is actually a SubSonic Collection but it is not loaded from the database).
I'm currently using SqlBulkCopy as follows:
private string FastInsertCollection(string tableName, DataTable tableData)
{
string sqlConn = ConfigurationManager.ConnectionStrings[SubSonicConfig.DefaultDataProvider.ConnectionStringName].ConnectionString;
using (SqlBulkCopy s = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.TableLock))
{
s.DestinationTableName = tableName;
s.BatchSize = 5000;
s.WriteToServer(tableData);
s.BulkCopyTimeout = SprocTimeout;
s.Close();
}
return sqlConn;
}
I use SubSonic's MyObjectCollection.ToDataTable() to build the DataTable from my collection. However, this duplicates objects in memory and is inefficient. I'd like to use the SqlBulkCopy.WriteToServer method that uses an IDataReader instead of a DataTable so that I don't duplicate my collection in memory.
What's the easiest way to get an IDataReader from my list? I suppose I could implement a custom data reader (like here http://blogs.microsoft.co.il/blogs/aviwortzel/archive/2008/05/06/implementing-sqlbulkcopy-in-linq-to-sql.aspx) , but there must be something simpler I can do without writing a bunch of generic code.
Edit:
It does not appear that one can easily generate an IDataReader from a collection of objects.
Accepting current answer even though I was hoping for something built into the framework.
