linq to sql vs ado.net - performance in terms of connection being open

I have to get records from a table(about 40 columns), process each record, call a web service on this record, wait fo开发者_运维问答r its response and update the record to database.

Now, I see 2 options.

1. Linq to Sql

2. ADO.Net with Typed Dataset

(I leave the option of DataReader for all the extra work I have to do.)

2 closes the connection soon after fetching the data, I can process data offline and submit changes later, i.e, I don't have keep connection open for so long. With 1, in-order to be able to submit changes at the end, I have to keep the connection open all the time.

Do you think 2 is the always the best way whenever changes need to submitted after a certain period of processing or am I missing something?

---

Neither Linq to SQL or Entity Framework keep the connection open while you are working with the fetched objects. If you want to take advantage of the change tracking capabilities of the various contexts, you need to keep the context object in scope, but that doesn't mean the connection to the database remains open during that period. In actuality, the connection is only open while you are iterating on the results (databinding) and when you call SubmitChanges/SaveChanges. Otherwise the connetion is closed.

These technologies use ADO.Net DataReaders and command objects under the covers. There's still no concept of open cursors like you had back in the VB6 days.

---

L2S or Entity Framework introduce a bit of overhead, but honestly the time you save is well worth it and whenever you perform a Linq query on your objects the SQL is optimized for you.

Plain old ADO.Net is old school - Linq is the way to go now.

---

Plain old ADO.Net is old school - Linq is the way to go now.

Microsoft caters to newbies and sells itself to the masses as a "hobbyist" language.

LINQ allows developers to query a datasource who have no business accessing a data source.

Having deep knowledge and a brain enough to write your own optimized T-SQL is perhaps is a desireable skill, not laughable as, "old school".

As a software engineer a person should move in the direction of writing software which can perform as efficient and fast as possible. The whole "saving development time" argument for choosing a higher level construct because it is all too simple to implement speaks to how crappy the program will perform if stacked up next to a senior engineers version.

Don't get sold on latest and greatest offerings that make coding "faster". If you are smart enough to do it yourself, then do it yourself and avoid having a LINQ syntax write T-SQL for you.

There is a measure of scale in the business world where microseconds count, and being a valuable commodity to a company will put your deep knowledge to the test when your software needs to run as fast as you know how to make it.

In short, it is never out of style to have deep knowledge.