How to insert List into database

Question

How can I save a List in a database at once without iterating through the whole list and saving each object seperately to the database.

//So not like this solution
foreach(T t in data){
    //Call function to save with stored procedure
    Database.Save(t);
}

(Also without using the entity framework) (I only seem to get solutions based on the entity framework or Dapper.Net on StackOverflow.)

Is this even possible by sending the list in some way to the database and using a stored procedure?

@Edit Additional information

I'm currently running on SQL 2012 and want to be able to reroll everything if something did not work!

Does t map to an entity in the database? Because you can use datatables — Allan S. Hansen, Jan 18 '16 at 09:27
At it's simplest, you could manually create a bunch of INSERT statements and execute them as a single batch against the database - there are a million ways to skin this cat! — RB., Jan 18 '16 at 09:27

score 6 · Accepted Answer · edited May 23 '17 at 12:31

6

If you are using at least SQL Server 2008, then you can use a Table Valued parameter to do this. This question and answers covers the topic.

Essentially you create a Type in SQL, and then something in C# to convert you data to a IEnumerable<SqlDataRecord> which matches the type as given.

edited May 23 '17 at 12:31

Community

answered Jan 18 '16 at 09:30

NikolaiDante

OP needs to mention SQL Server version because this is not possible prior to 2008. – Nikhil Vartak Jan 18 '16 at 09:34
@nikhilvartak that's true. I'll add that information in. – NikolaiDante Jan 18 '16 at 09:35

score 2 · Answer 2 · answered Jan 18 '16 at 09:30

2

You can use Table Valued Parameter to pass collection to stored procedure. MSDN

answered Jan 18 '16 at 09:30

Ruslan K.

2 Answers2