.net - How does SqlBulkCopy Work

Question

I am familiar with the C# SqlBulkCopy class where you can call the 'WriteToServer' method passing through a DataTable.

My question is what underlying mechanism in SQL server is used to bulk insert that data?

The reason I ask is that the bulk insert referenced in the Bulk Insert MSDN T-SQL help file requires a data file to import. Does the SqlBulkCopy create a data file?

I would like to understand this stuff to work out whether I can use the bulk insert functionality in SQL.

If I write a SQL statement that prepares all the rows to insert into a particular table (thousands of rows) can I bulk insert them into the destination table? Something like this is how I am doing it now,

INSERT INTO sync_filters (table_name, device_id, road_id, contract_id)
    SELECT * FROM dbo.sync_contract_filters (@device_id)

And the dbo.sync_contract_filters is a function to generate all the rows to insert. Can this be bulk inserted?

Answer 1

SqlBulkCopy does not create a data file. It streams the data table directly from the .Net DataTable object to the server using the available communication protocol (Named Pipes, TCP/IP, etc...) and insert the data to the destination table in bulk using the same technique used by BCP.