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
}
}
}
Comments
- Anonymous
October 31, 2005
Could you please show a example to read CSV file use your reader interface? Thanks.