Enhanced SQL Data Reader Adapter

The SqlBulkCopy class can be used to BCP data into a SQL Table... the WriteToServer method can take an IDataReader (usually people have a SqlClient.SqlDataReader)... unfortunately, this approach doesn't keep a good row count.

I had to provide a row count for SqlBulkCopy... I tested use of a DataTable, but found that the sequential nature (reading into the DataTable, then writing it out) was murdering performance beyond anything acceptable.

Internally the SqlBulkCopy class implementation uses one of two simple approaches for iterating over the data... when given an IDataReader, SqlBulkCopy just calls the Read() method; when given a DataTable or RowArray, SqlBulkCopy just uses MoveNext.

As it turned out, it was far easier for me to just implement my own IDataReader (about 20 minutes).

Basically, the only change is that the Read method includes a counter, which is then exposed via a readonly property.
Language:
C#
Keywords:
SqlBulkCopy
Code Snippet

using System;

 

namespace SqlHelpers

{

    public class EnhancedSqlDataReaderAdapter : System.Data.IDataReader

    {

        private readonly System.Data.SqlClient.SqlDataReader _myBase;

        public EnhancedSqlDataReaderAdapter(System.Data.SqlClient.SqlDataReader myBase) { _myBase = myBase; }

 

        // Enhancements

        private int _RowsRead = 0;

        public Int32 RowsRead { get { return _RowsRead; } }

 

        #region System.Data.IDataReader : Enhanced Implementations

        bool System.Data.IDataReader.Read()

        {

            var ReadResults = _myBase.Read();

 

            if (ReadResults)

                _RowsRead++;

 

            return ReadResults;

        }

        #endregion

 

        #region System.Data.IDataReader : Base Implementations

 

        void System.Data.IDataReader.Close() { _myBase.Close(); }

        void IDisposable.Dispose() { _myBase.Dispose(); }

 

        int System.Data.IDataReader.Depth { get { return _myBase.Depth; } }

        bool System.Data.IDataReader.IsClosed { get { return _myBase.IsClosed; } }

        bool System.Data.IDataRecord.IsDBNull(int i) { return _myBase.IsDBNull(i); }

 

        bool System.Data.IDataReader.NextResult() { return _myBase.NextResult(); }

        int System.Data.IDataReader.RecordsAffected { get { return _myBase.RecordsAffected; } }

        int System.Data.IDataRecord.FieldCount { get { return _myBase.FieldCount; } }

 

        bool System.Data.IDataRecord.GetBoolean(int i) { return _myBase.GetBoolean(i); }

        byte System.Data.IDataRecord.GetByte(int i) { return _myBase.GetByte(i); }

        char System.Data.IDataRecord.GetChar(int i) { return _myBase.GetChar(i); }

 

        System.Data.DataTable System.Data.IDataReader.GetSchemaTable() { return _myBase.GetSchemaTable(); }

        System.Data.IDataReader System.Data.IDataRecord.GetData(int i) { return _myBase.GetData(i); }

 

        string System.Data.IDataRecord.GetDataTypeName(int i) { return _myBase.GetDataTypeName(i); }

        Type System.Data.IDataRecord.GetFieldType(int i) { return _myBase.GetFieldType(i); }

        string System.Data.IDataRecord.GetName(int i) { return _myBase.GetName(i); }

        int System.Data.IDataRecord.GetOrdinal(string name) { return _myBase.GetOrdinal(name); }

       

        DateTime System.Data.IDataRecord.GetDateTime(int i) { return _myBase.GetDateTime(i); }

        decimal System.Data.IDataRecord.GetDecimal(int i) { return _myBase.GetDecimal(i); }

        double System.Data.IDataRecord.GetDouble(int i) { return _myBase.GetDouble(i); }

        float System.Data.IDataRecord.GetFloat(int i) { return _myBase.GetFloat(i); }

        Guid System.Data.IDataRecord.GetGuid(int i) { return _myBase.GetGuid(i); }

        short System.Data.IDataRecord.GetInt16(int i) { return _myBase.GetInt16(i); }

        int System.Data.IDataRecord.GetInt32(int i) { return _myBase.GetInt32(i); }

        long System.Data.IDataRecord.GetInt64(int i) { return _myBase.GetInt64(i); }

        string System.Data.IDataRecord.GetString(int i) { return _myBase.GetString(i); }

 

        object System.Data.IDataRecord.GetValue(int i) { return _myBase.GetValue(i); }

        int System.Data.IDataRecord.GetValues(object[] values) { return _myBase.GetValues(values); }

 

        long System.Data.IDataRecord.GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length) { return _myBase.GetBytes(i, fieldOffset, buffer, bufferoffset, length); }

        long System.Data.IDataRecord.GetChars(int i, long fieldOffset, char[] buffer, int bufferoffset, int length) { return _myBase.GetChars(i, fieldOffset, buffer, bufferoffset, length); }

 

        // Indexes

        object System.Data.IDataRecord.this[string name] { get { return _myBase[name]; } }

        object System.Data.IDataRecord.this[int i] { get { return _myBase[i]; } }

 

        #endregion

 

    }

}

Example

SqlBulkCopy bulkCopy

SqlDataReader srcReader

var enhancedReader = new SqlHelpers.EnhancedSqlDataReaderAdapter(srcReader);

bulkCopy.WriteToServer(enhancedReader);

RowsCopied = enhancedReader.RowsRead;


Created 2012-06-27
comments powered by Disqus
Login