how to pass variables like arrays / datatable to SQL server ?

Question

Sometimes you need to upgrade the database with many rows that you have in a datatable or you have an array full of data, instead of putting all this data together in a string and then splitting in SQL SERVER, or instead of iterating the datatable in the code row by row and updating database, is there any other way? Is there other type of variables besides the traditional ones in SQL SERVER 2005?

Answer 1

You can use Bulk Insert.

Answer 2

There's a few ways to do this.

If you're simply inserting rows, then I would create a DataTable object with the information in it, then use the SqlBulkCopy object:

SqlBulkCopy copier = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.Default);
copier.BatchSize = 500; //# of rows to insert at a time
copier.DestinationTableName = "dbo.MyTable";
copier.WriteToServer(myDataTable);

Another option is to wrap your data in xml (however you want to do that), and send it to your stored procedure (which does whatever you need it to do) using the sql 2005 'xml' data type

Answer 3

I agree with John that SqlBulkCopy or sqlxml are the best options in SQL Server 2005; note that in SQL Server 2008 you also have table valued parameters, which would be worth consideration; especially when mixed with the new merge join.

Note that if you use SqlBulkCopy, I strongly recommend bulk inserting only to a staging table - i.e. a separate table just for the import; then run a stored procedure to move the data to the live table. That way you get proper logging, and you can have a tightly scoped transaction just at the database while you run the SP (i.e. you don't need a transaction spanning all that network IO).

One other point; if you are dealing with large volumes of data, you might not want to have to load a DataTable (since that forces you to buffer all the data in memory first); as an alternative, it is also possible to write your own IDataReader that pulls data from a stream (such as a file etc); see SimpleDataReader here. SqlBulkCopy will accept data from an IDataReader very happily.

Answer 4

John mentioned using XML... and that's the approach I would use for your situation (SQL Server 2005, and making a sproc that handles the SQL for you).

Here's an example of how to get started (this is just a select statement, but you can make it an update if you want):

CREATE PROCEDURE MySproc ( @Accounts XML )
AS

SELECT
    Accounts.AccountID.query('.')
FROM
    @Accounts.nodes('//ID/text()') AS Accounts(AccountID)

GO
EXEC MySproc '<Accounts><ID>123</ID><ID>456</ID></Accounts>'