Mass sending data to stored procedure

Question

I'm using Microsoft .NET Framework 3.5 to create a web service with VB.NET. I'm using a Stored Procedure in SQL Server 2008 so that SQL can insert all the data that I'm passing.

The problem is that in one of the servicse I need to pass around 10,000 records and it's not very efficient to run the stored procedure 10,000 times.

I read that there is a way in which you can pass an XML file with all data to the Stored Procedure but I'm not sure if that's the most efficient way. Also I couldn't make the code work, I don't know if I have to pass the XML as a String.

I'm asking for help with a method in which I can pass a lots of records to the stored procedure once and then the same instance of the Stored procedure can process all the records in a loop

Thank you all in advance.

Answer 1

There is SqlBulkCopy in .NET, but I expect you'll want to look at a table-valued parameter.

Answer 2

You can pass a text batch of statements to the database. It can be quite efficient.

Instead of creating a SqlCommand of CommandType.StoredProcedure and taking a single stored procedure name and set of parameters – which, as you suspect, will perform poorly if you round-trip to the database for each record – you can instead create a SqlCommand of CommandType.Text, and then construct a text batch containing multiple SQL statements (which would be invocations of your stored procedure.) Separate each statement with a semi-colon.

Another advantage of a text batch is that your stored procedure can be kept simple and just process a single record at a time.

But, be careful: You need to ensure your parameters are properly quoted / escaped, because creating a plain text batch instead of using CommandType.StoredProcedure (with parameters) opens you up to SQL-injection type vulnerabilities.