Efficiently bulk-load a SQL Server table with data from a collection using SqlBulkCopy

I did not want to create and then fill a Data[Set|Table] if the information I wanted to insert in SQL was already present in memory, in a collection!

The documentation about SqlBulCopy is on https://msdn2.microsoft.com/library/30c3y597.aspx (as of 11/8/04).

I’ve used the

Visual C# 2005 Express Edition October Community Technical Preview to build this code.

 

With the new C# yield keyword (link valid on 11/8/04), it should not be very hard to use this to bulk copy a file…

 

If you have any interest in this, please tell me and I’ll work on a nice sample to later share it in another blog entry.

 

using System;

namespace Microsoft.Partner.Services.ISV

{

   public abstract class EnumeratorBasedDataReaderBase : System.Data.IDataReader

   {

      internal EnumeratorBasedDataReaderBase( int columnsCount )

      {

         if ( columnsCount < 1 )

      throw new ArgumentException( "Has to be greater than zero", "columnsCount" );

         this.columnsCount = columnsCount;

      }

      // Number of collection item read through IDataReader (useful for diagnostic when working with SqlBulkCopy)

      internal int itemReadCount;

      public int ItemReadCount { get { return itemReadCount; } }

      #region IDataReader methods

      internal int columnsCount;

      public int FieldCount { get { return columnsCount; } }

      public abstract bool Read();

      public abstract object GetValue( int column );

      #region Inherited interface methods with no code or throwing a NotImplementedException exception

      public void Close() { }

      public void Dispose() { }

      public System.Data.DataTable GetSchemaTable() { throw new NotImplementedException( "GetSchemaTable()" ); }

      public bool IsClosed { get { throw new NotImplementedException( "IsClosed" ); } }

      public bool NextResult() { throw new NotImplementedException( "NextResult()" ); }

      public int RecordsAffected { get { throw new NotImplementedException( "RecordsAffected" ); } }

      public bool GetBoolean( int i ) { throw new NotImplementedException( "GetBoolean" ); }

      public byte GetByte( int i ) { throw new NotImplementedException( "GetByte" ); }

      public long GetBytes( int i, long fieldOffset, byte[] buffer, int bufferoffset, int length ) { throw new NotImplementedException( "GetBytes" ); }

      public char GetChar( int i ) { throw new NotImplementedException( "GetChar" ); }

      public long GetChars( int i, long fieldoffset, char[] buffer, int bufferoffset, int length ) { throw new NotImplementedException( "GetChars" ); }

      public System.Data.IDataReader GetData( int i ) { throw new NotImplementedException( "GetData()" ); }

      public string GetDataTypeName( int i ) { throw new NotImplementedException( "GetDataTypeName()" ); }

      public DateTime GetDateTime( int i ) { throw new NotImplementedException( "GetDateTime()" ); }

      public decimal GetDecimal( int i ) { throw new NotImplementedException( "GetDecimal()" ); }

      public double GetDouble( int i ) { throw new NotImplementedException( "GetDouble" ); }

      public Type GetFieldType( int i ) { throw new NotImplementedException( "GetFieldType()" ); }

      public float GetFloat( int i ) { throw new NotImplementedException( "GetFloat()" ); }

      public Guid GetGuid( int i ) { throw new NotImplementedException( "GetGuid()" ); }

      public short GetInt16( int i ) { throw new NotImplementedException( "GetInt16()" ); }

      public int GetInt32( int i ) { throw new NotImplementedException( "GetInt32()" ); }

      public long GetInt64( int i ) { throw new NotImplementedException( "GetInt64()" ); }

      public string GetName( int i ) { throw new NotImplementedException( "GetName()" ); }

      public int GetOrdinal( string name ) { throw new NotImplementedException( "GetOrdinal()" ); }

      public string GetString( int i ) { throw new NotImplementedException( "GetString()" ); }

      public int GetValues( object[] values ) { throw new NotImplementedException( "GetValues()" ); }

      public bool IsDBNull( int i ) { throw new NotImplementedException( "IsDBNull()" ); }

      public object this[string name] { get { throw new NotImplementedException( "this[string]" ); } }

      public object this[int i] { get { throw new NotImplementedException( "this[int]" ); } }

      public int Depth { get { throw new NotImplementedException( "Depth" ); } }

      #endregion

      #endregion

   }

   public class EnumeratorBasedDataReader : EnumeratorBasedDataReaderBase

   {

      public EnumeratorBasedDataReader( System.Collections.IEnumerator enumerator, GetColumnDelegate getColumnDelegate, int columnsCount )

         :base( columnsCount )

      {

         if ( enumerator == null )

            throw new ArgumentException( "Cannot be null", "enumerator" );

         if ( getColumnDelegate == null )

            throw new ArgumentException( "Cannot be null", "getColumnDelegate" );

         this.enumerator = enumerator;

         this.getColumnDelegate = getColumnDelegate;

      }

      #region IDataReader methods

      private System.Collections.IEnumerator enumerator;

      private Object data;

 

      public delegate void PrepareColumnsDelegate(Object item, ref Object data);

     public event PrepareColumnsDelegate PrepareColumns;

      public override bool Read()

      {

         if ( enumerator != null )

         {

            if ( enumerator.MoveNext() )

            {

               itemReadCount++;

               if (PrepareColumns != null)

                  PrepareColumns(enumerator.Current, ref data); // data by ref as could be used in getColumnDelegate()

               return true;

            }

            enumerator = null;

         }

         return false;

      }

      public delegate object GetColumnDelegate(int column, Object item, Object data);

      private GetColumnDelegate getColumnDelegate;

      public override object GetValue(int column)

      {

         return getColumnDelegate( column, enumerator.Current, data );

      }

      #endregion

   }

   namespace Generic

   {

      public class EnumeratorBasedDataReader<ItemT, DataT> : EnumeratorBasedDataReaderBase

      {

         public EnumeratorBasedDataReader( System.Collections.Generic.IEnumerator<ItemT> enumerator, GetColumnDelegate getColumnDelegate, int columnsCount )

            :base(columnsCount)

         {

            if ( enumerator == null )

               throw new ArgumentException( "Cannot be null", "enumerator" );

            if ( getColumnDelegate == null )

               throw new ArgumentException( "Cannot be null", "getColumnDelegate" );

            this.enumerator = enumerator;

            this.getColumnDelegate = getColumnDelegate;

         }

         #region IDataReader methods

        

         private System.Collections.Generic.IEnumerator<ItemT> enumerator;

         private ItemT item;

         private DataT data;

         public delegate void PrepareColumnsDelegate(ref ItemT item, ref DataT data);

         public event PrepareColumnsDelegate PrepareColumns;

         public override bool Read()

         {

            if ( enumerator != null )

            {

               if ( enumerator.MoveNext() )

               {

                  itemReadCount++;

                  item = enumerator.Current;

                  if (PrepareColumns != null)

                  {

                     // ItemT could be a value type so let's pass it by ref. (speed)

                     PrepareColumns(ref item, ref data);

                  }

                  return true;

               }

               enumerator = null;

            }

            return false;

         }

         public delegate object GetColumnDelegate(int column, ref ItemT item, ref DataT data);

         private GetColumnDelegate getColumnDelegate;

         public override object GetValue( int column )

         {

            return getColumnDelegate( column, ref item, ref data );

   }

         #endregion

      }

   }

}