Bulk sending data to a stored procedure

Question

I am using Microsoft.NET Framework 3.5 to create a web service using VB.NET. I am using a stored procedure in SQL Server 2008 so that SQL can insert all the data that I pass.

The problem is that in one of the servicse I need to transfer about 10,000 records, and it is not very efficient to start the stored procedure 10,000 times.

I read that there is a way in which you can transfer the XML file with all the data to the Stored Procedure, but I'm not sure if this is the most efficient way. Also, I could not get the code to work, I do not know if I should pass XML as a string.

I ask for help with a method in which I can pass many records to a stored procedure once, and then the same instance of the stored procedure can process all the records in a loop

Thanks to everyone in advance.

Answer 1

There is SqlBulkCopy in .NET , but I expect you to want to see the table parameter .

Answer 2

You can transfer a text batch of operators to the database. It can be quite effective.

Instead of creating SqlCommand from CommandType.StoredProcedure and accepting a single stored procedure name and a set of parameters - which, you suspect, will not work well if you visit the database for each record; instead, you can create a SqlCommand from CommandType.Text , and then create a text batch containing several SQL statements (which will call your stored procedure). Separate each statement with a comma.

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

But, be careful:. You have to make sure that your parameters are correctly quoted / escaped, because creating a simple text batch instead of using CommandType.StoredProcedure (with parameters) opens you with an SQL type injection.

Answer 3

The method I use is to transfer data to a CDATA block (pipe limited in my case) to a web service, which then:

• Saves a CDATA block in a temporary file on a web server
• It then uses the bcp.exe command-line utility to bulk load data into the staging table
• It then calls a stored procedure that is configured to process all records in the staging table

much faster and less database load than calling proc for each record.

Edit: now that I read about SqlBulkCopy, I would do this instead:

• Writing CDATA Block Data to a DataTable
• Use SqlBulkCopy to place data in a staging table :-)
• It then calls a stored procedure that is configured to process all records in the staging table

Answer 4

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

It may also help: http://sqlxml.org/faqs.aspx?faq=61

Answer 5

Where I did this before with SQL Server 2000, I used OPENXML (as @EJB suggested), building an XML string in my code, passing it to the stored process in the text parameter using OPENXML to parse the XML in the relational structure and go from there, for example,

B. B.

 Dim xmlStringBuilder As System.Text.StringBuilder xmlStringBuilder = New System.Text.StringBuilder xmlStringBuilder.Append("<objects>" For Each object In Collection 'I'm not suggesting this is the best way to build XML, it is however reliable! xmlStringBuilder.Append("<object id='" & object.id.ToString & "'></object>" Next xmlStringBuilder.Append("</objects>" Dim xmlStoredProcCommand As SqlCommand Dim xmlParameter As SqlParameter xmlStoredProcCommand = New SqlCommand(connection) xmlStoredProcCommand.CommandType = CommandType.StoredProcedure xmlStoredProcCommand.CommandText = "xmlStoredProc" xmlParameter = New SqlParameter("@xmlParameter",SqlDbType.NText) xmlParameter.Value = xmlStringBuilder.ToString xmlStoredProcCommand.Parameters.Add(xmlParameter) xmlStoredProcCommand.ExecuteNonQuery 

SQL

 CREATE PROCEDURE xmlStoredProc @xmlParameter NTEXT AS BEGIN DECLARE @xmldochandle INT DECLARE @objects TABLE (objectID INT) EXEC sp_xml_preparedocument @xmldochandle OUTPUT, @xmlParameter INSERT INTO @objects (objectId) SELECT objectId FROM OPENXML(@xmldochandle, 'objects/object') WITH (objectId INT) EXEC sp_xml_removedocument @xmldochandle END 

and from there you can make your stuff with the contents of the @objects table variable.