none
Bulk Copy Out of a Table RRS feed

  • Question

  • What's the programmatic .NET equivalent to bulk copy OUT of a table?  I want to extract data from a table in large sets/batches.  SqlBulkCopy class makes it easy to put data IN a table but how do I get it out in an efficient manner?


    Wednesday, July 18, 2012 6:33 PM

Answers

  • Do you want select a large set of data from a SQL server database to an In-memory ado.net data table? If so Paul is right. It depends on what database you will be using, as each database has its own set of mechanism (Query Execution Plan or ..) to return row one by one or with batch for example, I am aware of

    1. A feature of oracle database known as Oracle-Bulk-Collect.
    2. ODP.NET allows developers to discover automatically the query row size and then specify the number of rows to be retrieved per round trip. This feature makes optimizing data retrieval much simpler for .NET programmers i.e. by setting reader.FetchSize=N, It retrieve N number of rows at a time with a single trip. Refer: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/OptimizePerfODPNET/OptimizePerfODPNET.htm

    As I can see you are using SQL server, unfortunately both the above technique are not supported by SQL Server but it has some nice work around.

    Option-One

    The DataAdapter provides a facility for returning batch/chunk/Page of data, through overloads of the Fill method i.e

    dapter.Fill(dataSet, currentIndex, BatchSize, "Orders");

    Refer: http://msdn.microsoft.com/en-us/library/tx1c9c2f.aspx

    Option-Two

    Ado.net DataReader provides BeginExecutionReader/EndExecutionReader and ASYNC=True in connection string to retrieve data asynchronously

    Option-Three

    A Custom Stored Procedure for paging.

    To answer your question there is no equivalent class in .net for Bulk Out as it depends on SQL Query execution plan and how you retrieve data. If you are concerned about performance then Option-Two with a move/Forward only SQL reader will do the job. If you are concerned about the Bath Size then Option-One will help you as a replacement of SQLBulkCopy (OUT).


    Lingaraj Mishra


    Saturday, July 21, 2012 7:11 AM

All replies

  • There is no such method in .NET, however; it may still be possible using SQL statements or other methods depending upon what type of databases you are working with.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, July 19, 2012 1:40 PM
  • The DBs are on SQL Server instances, nothing special there. 

    Thursday, July 19, 2012 1:54 PM
  • If you would prefer to use ADO.NET and SQL to bcp or SSIS, you can create a linked server for the destination and use SQL to insert from the source:

    http://stackoverflow.com/questions/153988/how-can-i-copy-data-records-between-two-instances-of-an-sqlserver-database

    or, you can also use OPENROWSET and specify the connection to SQL Server in the statement:

    http://msdn.microsoft.com/en-us/library/ms190312%28v=sql.105%29.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, July 19, 2012 2:41 PM
  • Fascinating, but what does it have to do with what I asked?  This question relates to a single DB and a client that needs to pull large sets of data back, potentially in blocks/subsets.  

    Thursday, July 19, 2012 3:00 PM
  • That has nothing to do with the SQLBulkCopy Class which transfers data between database sources.

    If you simply want to retrieve a set of data from a database then all you need is a SQL SELECT statement and ADO.NET (e.g SQLDataAdapter, SQLDataReader, etc.).

    If there is something you require beyond this explanation then please clarify.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, July 19, 2012 3:55 PM
  • So, SQLBulkCopy will let me push large blocks of data from client to a table very efficiently.  This is the API equivalent to "BCP in".  If I want to reverse direction i.e. "BCP out" what's the equivalent?

    Thursday, July 19, 2012 4:02 PM
  • BCP out to "what"? If you are transferring from one SQL Server database (or table in the same database) to another then SQLBulkCopy works both ways. You just swap the source and destination.

    If you want to transfer data from SQL Server to a different data source (destination) then other techniques will be required depending upon the database.

    If this doesn't address your question then I need to know exactly what the "client" is that you are referring to. You say: "I want to extract data from a table in large sets/batches". My question is: "And do what with it?"


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, July 19, 2012 4:59 PM
  • Do you want select a large set of data from a SQL server database to an In-memory ado.net data table? If so Paul is right. It depends on what database you will be using, as each database has its own set of mechanism (Query Execution Plan or ..) to return row one by one or with batch for example, I am aware of

    1. A feature of oracle database known as Oracle-Bulk-Collect.
    2. ODP.NET allows developers to discover automatically the query row size and then specify the number of rows to be retrieved per round trip. This feature makes optimizing data retrieval much simpler for .NET programmers i.e. by setting reader.FetchSize=N, It retrieve N number of rows at a time with a single trip. Refer: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/OptimizePerfODPNET/OptimizePerfODPNET.htm

    As I can see you are using SQL server, unfortunately both the above technique are not supported by SQL Server but it has some nice work around.

    Option-One

    The DataAdapter provides a facility for returning batch/chunk/Page of data, through overloads of the Fill method i.e

    dapter.Fill(dataSet, currentIndex, BatchSize, "Orders");

    Refer: http://msdn.microsoft.com/en-us/library/tx1c9c2f.aspx

    Option-Two

    Ado.net DataReader provides BeginExecutionReader/EndExecutionReader and ASYNC=True in connection string to retrieve data asynchronously

    Option-Three

    A Custom Stored Procedure for paging.

    To answer your question there is no equivalent class in .net for Bulk Out as it depends on SQL Query execution plan and how you retrieve data. If you are concerned about performance then Option-Two with a move/Forward only SQL reader will do the job. If you are concerned about the Bath Size then Option-One will help you as a replacement of SQLBulkCopy (OUT).


    Lingaraj Mishra


    Saturday, July 21, 2012 7:11 AM
  • What's the programmatic .NET equivalent to bulk copy OUT of a table?  I want to extract data from a table in large sets/batches.  SqlBulkCopy class makes it easy to put data IN a table but how do I get it out in an efficient manner?

    The fastest way to get data out of SQL Server from managed code is a simple SqlDataReader.  A SqlDataReader will stream data from the server to your client application as fast as SQL Server can return it and your application can consume it. 

    I'm not sure what you mean by batching.  Batching typically applies to importing data, not exporting.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Saturday, July 21, 2012 12:33 PM