title: Performance issue with SqlBulkCopy and DataTable
body: <p>I need to efficiently import large amount of data from file to database.
  I have few rrf file which contain that data, the size of a file could be > 400mb and eventually it could be > 2 million record to database from file.</p>
  
  <p>What did I do:</p>
  
  <ol>
  <li><p>I am reading needed records in DataTable.</p>
  
  <pre><code>using (StreamReader streamReader = new StreamReader(filePath))
  {
      IEnumerable&lt;string&gt; values = new List&lt;string&gt;();
  
      while (!streamReader.EndOfStream)
      {
          string line = streamReader.ReadLine().Split('|');
  
           int index = 0;
           var dataRow = dataTable.NewRow();
  
           foreach (var value in values)
           {
              dataRow[index] = value;
              index++;
           }
  
           dataTable.Rows.Add(dataRow);
      }
  }
  </code></pre></li>
  <li><p>Than within transaction (this is a critical point), I insert data from <code>DataTable</code> via <code>SqlBulkCopy</code> to database.</p>
  
  <pre><code>var bcp = new SqlBulkCopy(_sqlConnection, SqlBulkCopyOptions.Default, transaction);
  bcp.DestinationTableName = tableName;          
  bcp.WriteToServer(dataTable);
  </code></pre></li>
  </ol>
  
  <p>The problem is that since each <code>DataTable</code> could contain more than 2 million records, I takes to much RAM (around 2 GB) for <code>DataTable</code> storage.</p>
  
  <p>Things like</p>
  
  <pre><code>dataTable.Dispose();
  dataTable = null;
  </code></pre>
  
  <p>or</p>
  
  <pre><code>GC.Collect();
  GC.SuppressFinalize();
  </code></pre>
  
  <p>do not actually help. </p>
  
  <p>The <code>Batchsize</code> property of <code>SqlBulkCopy</code> has nothing to do with it, all memory is taken by the <code>DataTable</code> which stores rows that should be inserted.</p>
  
  <p>I wonder, is there efficient way to read data and use <code>SqlBulkCopy</code> with it?</p>