IDataReader vs DataTable; pros and cons

Question

I am working on doing a large scale insert/update operation.

So I am using SqlBulkCopy.

SqlConnection myConnection = new SqlConnection(myConnectionString);

try
{
    myConnection.Open();
    SqlBulkCopy myCommand = new SqlBulkCopy(myConnection);

    myCommand.DestinationTableName = "myDestinationTableName";

    //Below method has four overloads; 
    //of which I am interested in the two mentioned below.
    myCommand.WriteToServer();

    myCommand.Close();
}
finally
{
    myConnection.Close();
}

But I stumbled upon these two versions of WriteToServer method.

Question : What are the pros and cons of one over another? Which one is faster?

score 2 · Accepted Answer · answered May 25 '15 at 15:02

With the default case, both should have similar performance otherwise horses..

But there is one difference though, WriteToServer with DataTable as parameter will insert all rows from DataTable , but with IDataReader copy starts with next available row in the reader (which would be set to first row in default case).

Remember, IDataReader provides a read-only/forward only mechanism, so there is a concept of current position/row index, whereas with DataTable there is no such thing. With each Read on IDataReader row index moves to next row.

score 1 · Answer 2 · answered May 25 '15 at 15:10

Overload with DataTable is intended for the cases, when you already have suitable DT somewhere in your application.

Overload with IDataReader, in turn, is for the cases, when there is some query (either raw query text, or constructed command, it doesn't matter). In this case, creation of intermediate data container, such as DT, is unnecessary. Hence, this overload allows you to fetch data directly from reader.

Assuming, that you haven't neither DT, nor IDataReader, the second one should at least consume less resources.

IDataReader vs DataTable; pros and cons

2 Answers2