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.
Best Answer
Get the latest version from the code on this post
Nothing like code churn in plain sight: Here is a pretty complete implementation. You can instantiate an IDataReader over IList IEnumerable, IEnumerable (ergo IQueryable). There is no compelling reason to expose a generic type parameter on the reader and by omitting it, I can allow IEnumerable<'a> (anonymous types). See tests.
The source, less xmldocs, is short enough to include here with a couple tests. The rest of the source, with xmldocs, and tests is here under Salient.Data.