Persisting Array into database

Question

I needed help in deciding the recommended approach to insert a Array/List into database(Sql Server 2008) using a stored procedure .
The obvious approach would be to invoke the sp for each row of the array.
But there has to be a better approach to send over the list/array over the network only once.
Help is appreciated.

Answer 1

The best approach here would depend on how you would want to query the data:

• If you might want to be able to query aspects of each row in the List/Array, then inserting one Db row per one List/Array item is the best way to go.
• If you are not going to want to query items within the List/Array, and just want to save it for auditing purposes, or some other referential need where you would not never need to retrieve anything but the full set, then you could get away with serializing the whole thing to JSON and storing it in a Key/Value table where value is of type nvarchar(max).

Answer 2

But there has to be a better approach to send over the list/array over the network only once.

Assuming you have the data in a parameter, use a structured parameter. SP's can accept table valued parametes.

As such, this is a duplicate of....

Pass table valued parameter using ADO.Net

which also has the solution.