0

I have code as follows:

IEnumerable<A> data = GetData();
DataTable dataTable = ConvertDataToDataTable(data);
...//initialize SqlBulkCopy
sqlBulkCopy.WriteToServer(dataTable);

But I do not wish to build up the entire data source in memory in the DataTable. I think this would be better:

IEnumerable<A> data = GetData();
IDataReader dataReader = ConvertDataToDataReader(data);
...//initialize SqlBulkCopy    
sqlBulkCopy.WriteToServer(dataReader);

The assumption is that the data would only build up in memory up to the sqlBulkCopy.BatchSize, and then be discarded once written to SQL Server (and also that it would run faster since it can start writing to SQL Server as soon as the first batch of data is available).

However, the IDataReader interface is huge and I am reluctant to implement it myself.

Is there any other solution to this, or something out there that might help me like a ToDataReader() extension method on an IEnumerable?

Edit: Ok this is a duplicated question of Get an IDataReader from a typed List. Looks like one has to implement IDataReader from scratch, there is nothing built into the framework or into a regularly updated NuGet package.

Community
  • 1
  • 1
Ande
  • 491
  • 1
  • 7
  • 22
  • 2
    possible duplicate of [Get an IDataReader from a typed List](http://stackoverflow.com/questions/2258310/get-an-idatareader-from-a-typed-list) – Charles Mager Sep 21 '15 at 15:13
  • 1
    You could write the data to .csv file and bulk insert it. – rbm Sep 21 '15 at 15:18
  • 2
    What is the datasource at all? You don't need to implement all methods of the `IDataReader` interface. http://www.codeproject.com/Articles/228332/IDataReader-implementation-plus-SqlBulkCopy – Tim Schmelter Sep 21 '15 at 15:19
  • @rbm yes, it would be easy for me to generate a .csv for BULK INSERT as I already have an `IEnumerable.ToCsv(string fileName)` method but unfortunately I don't have a common filesystem location where my C# app server can write and the SqlServer server can read. – Ande Sep 21 '15 at 16:35
  • 1
    The linked question answers yours in the 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." I went through a similar search recently to try to do exactly what you want, the implementation you need to include is short enough it's included in that question in its entirety and even then mostly boilerplate. IMO you are spending more time worrying about future breaking changes that may or may not ever happen than you probably ever would need addressing them. – Andrew Keller Sep 21 '15 at 17:25
  • @Andrew the edit in the question is dated 2010, I was hoping something might have changed since then, looks like nothing has changed? – Ande Sep 21 '15 at 17:32
  • 1
    @Ande I dealt with this in July and feel like I searched pretty hard but did not find anything. In fact I specifically remember being ecstatic when I found that answer since I would not have to write all the code myself =) – Andrew Keller Sep 21 '15 at 17:50
  • 1
    @Ande also note the code in the answer was last updated 2014, so even though the question is quite old the solution has been updated fairly recently. – Andrew Keller Sep 21 '15 at 17:53

0 Answers0