How to insert List<T> 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
– Allan S. Hansen, Commented Jan 18, 2016 at 9: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.
– RB., Commented Jan 18, 2016 at 9:27

Community · Accepted Answer · 2017-05-23 12:31:24Z

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.

CommunityBot

11 silver badge

answered Jan 18, 2016 at 9:30

NikolaiDante

18.7k14 gold badges83 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OP needs to mention SQL Server version because this is not possible prior to 2008.

@nikhilvartak that's true. I'll add that information in.

Ruslan K. · Accepted Answer · 2016-01-18 09:30:06Z

2

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

answered Jan 18, 2016 at 9:30

Ruslan K.

2,0011 gold badge16 silver badges20 bronze badges