IDataReader vs DataTable; pros and cons

c# datatable idatareader sqlbulkcopy

Question

I'm attempting to do a significant insert/update operation.

As a result, I amSqlBulkCopy .

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();
}

However, I came upon these two variations ofWriteToServer method.

  1. Method SqlBulkCopy.WriteToServer (IDataReader)

  2. Method SqlBulkCopy.WriteToServer (DataTable)

What advantages and disadvantages does one have over the other? Which is quicker?

1
1
5/25/2015 2:56:40 PM

Accepted Answer

Both should behave similarly in the default situation; otherwise, horses.

However, there is one distinction:WriteToServer with DataTable as a variable, insert all rows fromDataTable however withIDataReader Copy begins at the reader's following row, which is (which, in the normal situation, would be assigned to the first row).

Remember, IDataReader there is a notion of current position/row index since it has a read-only/forward-only mechanism, while withDataTable Such a thing does not exist. When eachRead on IDataReader row index goes to the next row.

2
5/25/2015 3:02:32 PM

Popular Answer

overindulging inDataTable is designed for situations when you already have appropriateDT In your application, someplace.

overindulging inIDataReader The circumstances where there is a query, whether it be a formed command or just raw query text, fall under this category. Making an intermediary data container in this instance, such asDT is not essential. As a result, this overload enables you to get data straight from the reader.

Considering you haven't done eitherDT , norIDataReader The second one ought to use less resources at the very least.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow