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

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!

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.

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 ) ) );

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.

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.