SQL Stored Proc : How to pass a collection of files to a stored proc?

Question

My program has code that saves attachments, I want these attachments to be transferred to the database, and I am going to use a stored procedure to accomplish this.

I need to know what is the @param type to accept an array of binary files?

finally once I have this array, how to I insert this data into a SQL table?

I guess I am looking at using a byte[] for 1 file, but how do I pass from C# or .net a collection of byte arrays to the SP, and what should the param type be to accept this array of byte[]

Updated

Need a solution that will work in 2005 and 2008.

Update

I've decided to scrap the idea of having 1 large SP to process everything. Instead I am going to have smaller SPs, then handle the transaction in .net.

Do you think this would be a better solution, to handle the transaction in .net data objects?

Answer 1

If you were needing only to pass an array of values, I would suggest to format them as XML and pass them as the SQL xml datatype. You can perform select from XML with SQL just as easily as from a table.

It may also technically work if you pass binary data in XML, but I'm not sure it will be a good solution. Anyway, it is an option.

Answer 2

For SQL Server 2008, you can use the table-valued parameter feature.

This allows you to define a parameter of a table type, and then supply values to that parameter as a DataTable from your C# code.

See these blog posts and articles for more information:

• SQL Server 2008: Table-valued parameters
• Using Table-Valued Parameters in SQL Server 2008

This is a new feature of SQL Server 2008, so you won't have this in SQL Server 2000 or 2005.

UPDATE: if you need to support SQL Server 2005 as well, check out Erland Sommarskog's excellent article Arrays and Lists in SQL Server 2005. It offers a few ideas on how to accomplish this in 2005.

Marc

Answer 3

See Arrays and Lists in SQL 2005. This article aggregates pretty much every technique there is out there and discusses the advantages and problems with each approach.

Answer 4

I decided to create just one SP, and call it multiple times, one for each file I need uploaded, and keep the transaction managed in C# code.