Linq to Sql, InsertAllOnSubmit

using linq to sql, I need to insert two records on each iteration through a loop (perhaps 2000 times)

I know I can use this technique
    table_sql tbl1 = new table_sql();
    // set the column values for table_sql
    dc.table_sqls.InsertOnSubmit(tbl1);

    table_sql tbl2 = new table_sql();
    // set the column values for table_sql
    dc.table_sqls.InsertOnSubmit(tbl2);

    Then do dc.SubmitChanges from the calling loop (because other things happen in the loop, too)

I assume, however, that it would be better to use an InsertAllOnSubmit for this in that it would cut the number of connection open/close operations in half.

How do I use InsertAllOnSubmit for this?
LVL 4
g_johnsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Fernando SotoRetiredCommented:
Hi g_johnson;

Your assumption is not correct. the InsertAllOnSubmit method will adds all entities of a collection to the DataContext in a pending insert state. So if you were to go through the loop and each time through you created an instance of and entity and set its values and add this entity to a List<EntityType> then when you completed going through the look you can add the list of entities all at once to the data context. Then when you do the dc.SubmitChanges the data context will go through the inserted entities and update the data store / database one insert at a time. The Linq query technology was NOT created to do mass updates.

Fernando
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.