Is there a way to use SqlBulkCopy without converting the data to a DataTable?

Question

Is there a way to use SqlBulkCopy without converting the data to a DataTable? I have a list of objects (List) in RAM and I really don't want to use more memory to create the DataTable. Could it be possible to implement IDataReader on a List?

Thanks!

score 2 · Answer 1 · answered Nov 19 '09 at 00:52

I would certainly imagine that you could. BulkDataReader requires schema information; that's why you can't simply provide a List. If you design a class that implements IDataReader, you'll be providing this in your GetSchemaTable implementation.

I would simply create a DataTable myself, unless I could demonstrate a real memory issue that would justify the implementation.

score 1 · Answer 2 · answered Nov 19 '09 at 01:27

As Michael says, you can certainly implement an IDataReader which is the most efficient way of doing it but there is some extra work required. Implementing GetSchemaTable is kind of a pain to implement but it's not that bad if you use the code below as a starting point.

        var table = new DataTable( "SchemaTable" );
        table.Locale = CultureInfo.InvariantCulture;

        table.Columns.Add( new DataColumn( SchemaTableColumn.ColumnName, typeof( string ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.ColumnOrdinal, typeof( int ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.ColumnSize, typeof( int ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.NumericPrecision, typeof( short ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.NumericScale, typeof( short ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.DataType, typeof( Type ) ) );
        table.Columns.Add( new DataColumn( SchemaTableOptionalColumn.ProviderSpecificDataType, typeof( Type ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.NonVersionedProviderType, typeof( int ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.ProviderType, typeof( int ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.IsLong, typeof( bool ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.AllowDBNull, typeof( bool ) ) );
        table.Columns.Add( new DataColumn( SchemaTableOptionalColumn.IsReadOnly, typeof( bool ) ) );
        table.Columns.Add( new DataColumn( SchemaTableOptionalColumn.IsRowVersion, typeof( bool ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.IsUnique, typeof( bool ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.IsKey, typeof( bool ) ) );
        table.Columns.Add( new DataColumn( SchemaTableOptionalColumn.IsAutoIncrement, typeof( bool ) ) );
        table.Columns.Add( new DataColumn( SchemaTableOptionalColumn.IsHidden, typeof( bool ) ) );
        table.Columns.Add( new DataColumn( SchemaTableOptionalColumn.BaseCatalogName, typeof( string ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.BaseSchemaName, typeof( string ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.BaseTableName, typeof( string ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.BaseColumnName, typeof( string ) ) );
        table.Columns.Add( new DataColumn( SchemaTableOptionalColumn.BaseServerName, typeof( string ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.IsAliased, typeof( bool ) ) );
        table.Columns.Add( new DataColumn( SchemaTableColumn.IsExpression, typeof( bool ) ) );

Good idea to use an (empty) DataTable for GetSchemaTable only! Makes implementing IDataReader really easy. Memory is cheap, but you simply can't add 100 GB to a laptop ;) — realMarkusSchmidt, Nov 12 '14 at 10:35

score 0 · Answer 3 · edited Oct 24 '16 at 18:32

Look at this link http://code.msdn.microsoft.com/LinqEntityDataReader, you can actually go from your list of objects or anything that supports a IQueryable to do a projection that will be converted into a DataReader which can be passed to the SqlBulkCopy object.

var q = from o in orders
         select new 
         {
           ID=o.ID,
           ShipDate=o.ShipDate,
           ProductName=o.Product.Name,
           ...
         }
IDataReader dr = q.AsDataReader();

I think this library comes in handy as it saves you a bit of work.

Hope it helps.

score 0 · Answer 4 · answered Nov 19 '09 at 01:17

0

As you move each object into a DataTable, delete it from the List, and and you can then use SqlBulkCopy with just a little extra memory.

Then, when you are done, reverse it.

Personally, I would just create a DataTable as memory is cheap.

answered Nov 19 '09 at 01:17

James Black

41,583
10
86
166

Is there a way to use SqlBulkCopy without converting the data to a DataTable?

4 Answers4

Linked