SqlDataAdapter vs SqlDataReader for DBAs Speaking to developers about the use of SqlDataAdapter vs SqlDataReader and reading this: https://stackoverflow.com/questions/1676753/sqldataadapter-vs-sqldatareader

Yields little in the way of explaining the effect on SQL Server of switching from SqlDataAdapter to SqlDataReader. 

I understand SqlDataReader potentially increases the length of time a connection is open to SQL Server, and that certainly is a concern when it comes to efficient use of pooled connections. 

Aside from this, what is the effect on SQL Server of using SqlDataReader instead of SqlDataAdapter? Has anyone observed the effect of this change on SQL Server?
In my experience, I think of SqlDataAdapter sort of like a .net SELECT * INTO #TEMP except the #TEMP is on the client computer.  The entire result set is read from SQL Server and that connection is no longer needed.  From a SQL Server perspective, the query comes in, runs and gets out.  From a client perspective, the entire result set is loaded into memory on the client computer. This can be extremely problematic when reading large amounts of data.

On the other hand, I think of SqlDataReader as a cursor mechanism whereby a SQL Server connection is opened and the SqlDataReader serves up one row at a time.  The application might do a bunch of work between each read and that can cause the connection to SQL Server to stay open for an extended amount of time.  This technique can allow the application to process a large result set without having to cache the entire set of data on the client computer.