Is the BULK INSERT command asynchronous when issues from .Net?

Question

I have a bulk insert command that I am issuing through C#. Is the command asynchronous. Also is it possible for the command to return before its released the file lock on the file being inserted.

Answer 1

I would guess no, given that the BULK INSERT command is like any other, the call to Execute will block until the server says it is done.

AFAIK, there is no command that is asynchronous in SQL Server. Unless you use one of the async methods on the SqlCommand, I can't see how the BULK INSERT would be run async.

I would also assume that when the command returns, that any locks that SQL Server had on the file have been released.

It doesn't make any sense otherwise, why would it hold the locks, or not be async, for that matter?

Answer 2

It depends on how you issue it. If you just have an sql string with the phrase "BULK INSERT" and run that string using the normal (blocking) functions, then no, it's not asynchronous. If you want it to be, you could use a function pair like BeginExecuteNonQuery()/EndExecuteNonQuery() rather than just ExecuteNonQuery().

There's also a SqlBulkCopy class you can use. It also runs synchronously (the call to WriteToServer() will block.)

Finally, I'll use my psychic debugging powers on your question text to infer that what you're really dealing with is a problem with a locked file. Can you share the code you use to read that file?