DataTableReader Object

DataTableReader is similar to DataReader class available in ADO.net 1.x. As we all know, DataReader object can read single row of data from the database in a connected mode. In the same way, DataTableReader can be used to read data from a DataTable or DataSet object. The CreateDataReader() method in DataTable object exposes the DataTableReader object to loop through into the DataTable.

DataTableReader dtdr = dt.CreateDataReader();

Where dt is the DataTable.

Usage

The usage will be similar to DataReader object.

To loop through,

DataTableReader dtdr = dt.CreateDataReader();       

        while (dtdr.Read())

        {

            Response.Write(dtdr[0].ToString()+"<br>");

        }

Copying DataTable to another using DataTableReader

DataTable.Load() method will accept DataTableReader object as argument and does the job for us.

Usage

//Copy one dt to another

        DataTable finaldt = new DataTable();

        finaldt.Load(dt.CreateDataReader());

        dtdr = finaldt.CreateDataReader();

SqlBulkCopy for bulk copy
This is a yet another wonder that is packed with ADO.net 2.0 called SqlBulkCopy class.

Purpose

This class can be used for performing bulk copy of data from one table to another table in same database server or different server similar to DTS and bcp utility. It can also be used to bulk copy from sqlserver 2005 database table to sql server 2000 database table, keeping in mind that destination table will have all the columns matching with the one we are copying from the source.

Advantages

It results in good performance compared to insert scripts. Take a look at the post in the reference section which compares the performance between insert script and SqlBulkCopy class.

Restrictions

This object can be used only with SqlServer as destination database table.

Implementation

Overloads

SqlBulkCopy.WriteToServer (DataRow[]) 

Copies all rows from the supplied DataRow array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object. 

SqlBulkCopy.WriteToServer (DataTable) 

Copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object. 

SqlBulkCopy.WriteToServer (IDataReader) 

Copies all rows in the supplied IDataReader to a destination table specified by the DestinationTableName property of the SqlBulkCopy object. 

SqlBulkCopy.WriteToServer (DataTable, DataRowState) 

Copies only rows that match the supplied row state in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object. 

SqlBulkCopy in Action

string connectionString = "Data Source=BABULIVES;Initial Catalog=tempdb;Integrated Security=True";

        using (SqlConnection sourceConnection =

                   new SqlConnection("Data Source=BABULIVES;Initial Catalog=NorthWind;Integrated Security=True"))

        {

            sourceConnection.Open();

              using (SqlConnection destinationConnection =

                           new SqlConnection(connectionString))

                      {

                     destinationConnection.Open();

                     using (SqlBulkCopy bulkCopy =

                                      new SqlBulkCopy(destinationConnection))

                            {

                            bulkCopy.DestinationTableName = "dbo.Employees";

                            try

                            {

                                   bulkCopy.WriteToServer(reader);

                             }

                            catch (Exception ex)

                             {

                                   Response.Write(ex.Message);

                             }

                            finally

                             {

                                    reader.Close();

                             }

                     }

                    }

       }